How to configure master-slave replication for MySQL Databases

MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.

Why do I use master-slave replication?

1. The master server/slave server settings increase robustness. When the master server encounters a problem, you can switch to the slave server as a backup.

2. By splitting the customer query load between the master server and slave server, a better customer response time can be obtained. But do not perform updates on the Master/Slave servers at the same time, which may cause conflicts.

3. Another advantage of using replication is that one slave server can be used for backup without interfering with the master server. During the backup process, the master server can continue to process updates.

MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When start slave is issued, an I/o thread is created from the server to connect to the master server and send binary logs to the master server. The master server creates a thread to send the binary log content to the slave server. Read the content sent by the BINLOG dump thread of the master server from the server I/O thread and copy the data to the local file in the data directory of the slave server, that is, the relay log. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in logs. The show processlist statement can be used to query information about replication on the master server and slave server.

The default relay log uses a file name in host_name-relay-bin.nnnnnn format, where host_name is the slave server host name and nnnnnnnn is the sequential column number. Create a continuous relay log file with a continuous serial number, starting from 000001. Track the relay log index file from the server to identify the currently used relay logs. The default relay log index file name is the host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log, the relay log is automatically deleted.

The slave server creates two more state files --master.info and relay-log.info in the data directory. Status files are stored on the hard disk and will not be lost when the slave server is closed. When starting the next time from the server, read these files to determine how many binary logs it has read from the master server and how much it will process its own relay logs.

Set master-slave replication:

1. Ensure that the MySQL version installed on the master server and slave server is the same, and preferably the latest stable version of MySQL.

2. Set a connection account for replication on the master server. This account must be granted the replication slave permission. If your account is only used for replication (recommended), you do not need to grant any other permissions.

Mysql> grant replication slave on *.* -> TO 'replicase' @ '% .yourdomain.com' identified by 'slavepass ';

3. Execute the flush tables with read lock statement to clear all TABLES and block write statements:

Mysql> flush tables with read lock;

Do not exit the mysql client program. Enable another terminal to take snapshots of the Data Directory of the master server.

Shell> cd/usr/local/mysql/ Shell> tar-cvf/tmp/mysql-snapshot.tar./data

If the user account on the slave server is different from the master server, you may not want to copy the mysql database. In this case, the database should be excluded from the archive. You do not need to include any log files, master.info or relay-log.info files in the archive.

When the flush tables with read lock is effective (that is, the mysql client does not exit), READ the current binary log name and offset value on the master server:

Mysql> show master status; + --------------- + ---------- + -------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + --------------- + ---------- + -------------- + ------------------ + | Mysql-bin.003 | 73 | test | manual, mysql | + --------------- + ---------- + -------------- + ------------------ +

The File column shows the log name, while the Position column shows the offset. In this example, the binary log value is mysql-bin.003 and the offset is 73. Record this value. These values will be used for setting slave servers later. They represent the replication coordinates. The slave server should start from this point and perform new updates from the master server.

 

If -- logs-bin is not enabled when the MASTER server is running, the log name and location values displayed in show master status are empty. In this case, when you specify the log files and locations of the slave server in the future, the values required are null strings ('') and 4.

After obtaining the snapshot and recording the log name and offset, return to the previous middle end to re-enable the Write activity:

Mysql> unlock tables;

4. Make sure that the [mysqld] section of the my. cnf file on the master server host contains a log-bin option. This part should also have a server-id = Master_id option, where master_id must be a positive integer between 1 and 232-1. For example:

[Mysqld]

Log-bin

Server-id = 1

If no options are provided, add them and restart the server.

5. Stop the mysqld service on the slave server and add the following lines to its my. cnf file:

[Mysqld]

Server-id = 2

The slave_id value is the same as the Master_id value. It must be a positive integer between 1 and 232-1. In addition, the slave server ID must be different from the master server ID.

6. store the data in the backup data directory. Ensure that the permissions on these files and directories are correct. The user running MySQL on the server must be able to read and write files, just as on the master server.

Shell> chown-R mysql: mysql/usr/local/mysql/data

7. Start the slave server. Execute the following statement on the slave server and replace the option value with the actual value of your system:

Mysql> CHANGE MASTER -> MASTER_HOST = 'master _ host_name ', -> MASTER_USER = 'replication _ user_name ', -> MASTER_PASSWORD = 'replication _ password ', -> MASTER_LOG_FILE = 'recorded _ log_file_name ', -> MASTER_LOG_POS = recorded_log_position;

8. Start the slave server thread:

Mysql> start slave;

After these programs are executed, the slave server should connect to the master server and supplement any updates that have occurred since the snapshot.

9. If a replication error occurs, an error message will also appear in the server's error log (HOSTNAME. err.

10. When copying from the server, the file master.info and HOSTNAME-relay-log.info are found in its data directory. The slave server uses these two files to track the number of master server binary logs that have been processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand what it means. Even so, it is best TO use the change master to statement.