Replication
Replication
is a way to configure a MySQL database to update on a remote server whenever
the local client is updated.
Replication allows you to take one database, make an
exact copy of it on another server, and set one of them (the slave) to take all
its updates from the other (the master).
http://www.howtoforge.com/mysql_database_replication
Configure master
¨ In
your my.ini file put these lines:
[mysqld]
log-bin
= allen_binary
binlog-do-db=exampledb
server-id=1
log-bin indicates that the master will use binary update
logging, binlog-do-db indicates which database we will use and server-id is a
unique number to differentiate each of the master and slave machines. By
convention, the master is usually set to 1, and the slaves from 2 upward:
¨ Now
in the mysql shell, we will grant a permission to a slave to replicate. The
slave user will be replication_user, with a password of replication_pwd:
• GRANT
REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
• GRANT
RELOAD ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
• GRANT
SUPER ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';
¨ You
can also type this
>FLUSH privileges
Once you have given the desired privileges for your user,
you will need to FLUSH privileges in order to complete the setup and to make the
new settings work.
¨ Then
use the database you want to replicate
>use replicationdb;
>show master status;
¨ Then
leave the MySQL shell:
>quit;
¨ master-host
= 192.168.4.100
¨ master-user
= replication_user
¨ master_password
= replication_pwd
¨ server-id
= 3
¨ master-connect-retry=60
¨ replicate-do-db
= replication_db
There are two possibilities to get existing tables and data
from replicationdbfrom master to slave
¨ make
a database dump
mysqldump -u root -proot
-- opt replicationdb> C:replicationdb.sql
Transfer this file to your slave server
¨ load data from master
If you want to go the LOAD DATA FROM MASTER;
¨ Now
we will configure the slave:
¨ First
create database replicationdb then quit mysql by “quit” command
¨ In
the my.ini or my.cfg file, put these lines
Replace the master-host setting with the actual IP address
of the master server, Master-user is the user created on the master for
replication as well as the master-password. The server_id can be any number, as
long as it's not the same as the server_id on the master:
¨ Then
quit and copy the sql
¨ Then
on the mysql shell copy the replicationdb data from the master to the slave
>mysql -u root
replicationdb< C:replicationdb.sql
¨ Then
we restart MySQL
If you have not imported the
master replicationdb with the help of an SQL dump, then lets use the LOAD DATA
FROM MASTER;
>mysql -u root
>LOAD DATA FROM MASTER;
>quit;
*If you
have phpMyAdmin installed on the slave you can now check if all
tables/data from the master replicationdb is also available on
the slave replicationdb.
¨ Next
step is to stop the slave to finish up the Slave configuration.
mysql -u root -p
Enter password:
SLAVE STOP;
Enter password:
SLAVE STOP;
¨ replace
the values appropriately:
>CHANGE MASTER TO
MASTER_HOST='192.168.0.100', MASTER_USER=‘replication_user', MASTER_PASSWORD='replication_pwd',
MASTER_LOG_FILE=‘allen_binary.000001', MASTER_LOG_POS=106;
¨ MASTER_HOST is
the IP address or hostname of the master (in this example it is 192.168.0.100).
¨ MASTER_USER is
the user we granted replication privileges on the master.
¨ MASTER_PASSWORD is
the actual password gave to the user on the master.
¨ MASTER_LOG_FILE is
the file MySQL gave, back when you ran SHOW MASTER STATUS; on
the master.
¨ MASTER_LOG_POS is
the position MySQL gave back when you also ran SHOW MASTER STATUS; on
the master.
¨ Then
start the slave.
>START SLAVE;
And now, be aware that if your
master server has already been using binary logging, you'll need to reset the
binary log with RESETMASTER so that the slave can start updating from
the beginning of the first binary log. Now start the slave server and connect.
Once you've connected, check the slave status to see if replication has begun
properly:
Configuring Master and Slave Files
Master Configuration File Options
¨ Slave
Configuration File Options
Master Configuration File
Options
¨ log-bin=filename
¡ Activates
binary logging. This option must be present on the master for replication to
occur. To clear the log, run RESET MASTER, and do not forget to run RESET SLAVE
on all slaves.
¡ log-bin-index=filename
¡ Specifies
the name of the binary log index file (which lists the binary log files in
order, so the slave will always know the active one). The default is
hostname.index
¨ sql-bin-update-same
¡ If
set, setting SQL_LOG_BIN to either 1 or 0 will automatically set SQL_
LOG_UPDATE to the same value, and vice versa. SQL_LOG_UPDATE should not be
needed any more, so this option is unlikely to be used.
¡ binlog-do-db=database_name
¡ Only
logs updates to the binary log from the database_name database. All other
databases are ignored.
¨ binlog-ignore-db=
database_name
¡ Logs
all updates to the binary log except from the database_name database. You can
also set the database to ignore on the slave.
¨ master-host=host
¡ Specifies
the hostname or IP address of the master to which to connect. Has to be set for
replication to begin. Once replication has begun, the master.info data will
determine this, and you'll need to use a CHANGE MASTER TO statement to change
it.
¡ master-user=username
¡ Specifies
the username the slave will connect to the master with. The user should have
REPLICATION SLAVE permission on the master.
¡ master-password=password
¡ Specifies
the password with which the slave will connect to the master.
¨ master-port=portnumber
¡ Specifies
the port the master listens on (defaults to the value of MYSQL_ PORT, usually
3306).
¨ master-connect-retry=
seconds
¡ If
the connection between the master and slave goes down, MySQL will wait this
many seconds before trying to reconnect (default 60).
¡ master-ssl
¡ Specifies
that replication take place using Secure Sockets Layer (SSL).
¨ master-ssl-key=key_name
¡ If
SSL is set to be used (the master-ssl option), this specifies the master SSL
key filename.
¡ master-ssl-cert=
certificate_name
¡ If
SSL is set to be used (the master-ssl option), this specifies the master SSL
certificate name.
¡ master-info-file=filename
¡ Specifies
the master information file (default master.info in the data directory), which
keeps track of the point in the binary logs that the slave is at in the
replication process.
Slave Configuration File
Options
¨ report-host
¡ Specifies
the hostname or IP address that the slave will announce itself as to the master
(for use during a SHOW SLAVE HOSTS statement). Not set by default. Other
methods of determining the host are not reliable; hence the need for this
option.
¡ report-port
¡ Specifies
the port for connecting to the slave reported to the master. You should only
need this if the slave is on a nondefault port, or connection takes place in a
nonstandard way.
¡ replicate-do-table=
db_name.table_name
Ensures that the slave only
replicates the specified table name, from the specified database. You can use
this option multiple times to replicate multiple tables
¨ replicate-ignore-table=
db_name.table_name
¡ Tells
the slave not to replicate a statement that updates the specified table (even
if other tables are also updated by the same statement). You can specify
multiple instances of this option.
¨ replicate-wild-do-table=
db_name.table_name
¡ Tells
the slave to replicate statements only where they match the specified table
(similar to the replicate_do_table option), but where the matchtakes into
account wildcards. For example, where the table name isdb%.tb%, the match will
apply to any database beginning with the letters db, and any table
beginning with the letters tb.
¨ replicate-wild-ignore-table=db_
name.table_name
¡ Tells
the slave not to replicate a statement that updates the specified table, even
if other tables are also updated by the same statement, similar to the
replicate-ignore-table option, except that wildcards are taken into account.
For example, where the table name is db%.tb% replication will not be performed
where the database begins with the letters db, and the table begins with
the letters tb). You can specify multiple instances of this option.
¨ replicate-ignore-db=
database_name
¡ Tells
the slave not to replicate any statement when the current database is
database_name. You can use this option multiple times to specify multiple
databases to ignore.
¨ replicate-do-db=
database_name
¡ Tells
the slave thread to replicate a statement only when the database is
database_name. You can use this option multiple times to replicate multiple
databases.
¨ log-slave-updates
¡ Tells
the slave to log replicated updates to the binary log. Not set by default. If
you plan to use the slave as a master to another slave, you'll need to set this
option.
¨ replicate-rewrite-db=
master_database->slave_database
¡ If
the database on your slave has a different name to that on the master, you'll
need to map the relationship with this option.
¨ slave-skip-errors=
[err_code1,err_ code2,... | all]
¡ When
replication encounters an error, it will stop (since an error usually means the
data is inconsistent, and manual steps are needed). This option tells MySQL to
continue replicating if the error is one of the listed errors. Error codes are
supplied as a number (the same number given in the error log) and separated by
a comma. You can also use the all option to cater for any possible errors. You
should not normally use this option, as mistaken use of it can lead to your
data getting out of sync with the master, with no realistic way of getting it
back in sync besides recopying the master data.
¨ skip-slave-start
¡ With
this option set, replication will not begin when the server starts. You can
manually begin it with the SLAVE START command.
¨ slave_compressed_protocol=#
¡ If
set to 1, then MySQL uses compression to transfer the data between slave and
master if both servers support this.
¨ slave_net_timeout=#
¡ Determines
the number of seconds to wait for more data from the master before a read is
aborted.
Slave Replication Commands
¨ start
and stop the replication process, respectively.
¨ returns
information about the slave, including the important fact whether the slave is
connected to the master (Slave_IO_Running), replication is running
(Slave_SQL_Running), what binary log is being used (Master_Log_File and
Relay_Master_Log_ File), and what position is current in the binary log
(Read_Master_Log_Pos and Exec_ master_log_pos).
¨ This
statement is an important one for keeping the replication in sync or starting
it off at the right place. The MASTER_LOG_FILE refers to the binary log on the
master from which the slave must start replicating, and the MASTER_LOG_POS is
the position in that file. (You'll see examples of this later in the chapter.)
This statement is also used when the master fails, and you need to change the
master to which the slave connects. The full set of CHANGE MASTER TO options
is:
¨ CHANGE
MASTER TO MASTER_HOST = 'master_hostname', MASTER_USER='replication_username',
MASTER_PASSWORD=''replication_user_password', MASTER_PORT='master_port',
MASTER_LOG_FILE='master_binary_logfile',
MASTER_LOG_POS='master_binary_log_position'
LOAD DATA FROM MASTER
takes a copy of the data on the
master and brings it onto the slave. Currently, this is not useful for large
datasets or for situations where the master cannot be unavailable for long, as
it acquires a global read lock when copying the data. It also updates the value
of MASTER_LOG_FILE and MASTER_LOG_POS. Currently it only works with MyISAM
tables. This statement is likely to become the standard way of preparing the
slave in future, so be sure to read your latest documentation.
No comments:
Post a Comment