Configure MySQL master-slave replication in Linux

MySQL supports unidirectional and asynchronous replication. During the replication process, a Linux server acts as the master server, and one or more other servers act as slave servers. The master server writes updates to binary log files.

MySQL supports unidirectional and asynchronous replication. During the replication process, a Linux server acts as the master server, and one or more other servers act as slave servers. The master server writes updates to binary log files.

MySQL supports unidirectional and asynchronous replication. During the replication process, a Linux server acts as the master server, 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 a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in the 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 the form of a host_name-relay-bin.nnnnnn, where host_name is the slave server host name and nnnnnnnn is the serial 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.