MySQL master-slave replication principle and points to note

Written in front

I have been writing a special topic on Mycat recently. Since many of my friends are going out for interviews recently, they asked me if I could briefly write down the master-slave replication principles and precautions of MySQL, because I was asked these questions in previous interviews. Me: Sure, it’s arranged! !

Master-slave replication principle

(1) The master records data changes in the binary log, which is the file specified by the configuration file log-bin. These records are called binary log events.

(2) The slave reads the binary log events in the master through the I/O thread and writes them to its relay log;

(3) The slave redoes the events in the relay log and executes the event information in the relay log one by one locally to complete the local storage of the data, thereby reflecting the changes to its own data (data replay).

Precautions

(1) The operating system versions and bit numbers of the master and slave servers are consistent;

(2) The versions of the Master and Slave databases must be consistent;

(3) The data in the Master and Slave databases must be consistent;

(4) The master starts binary logging, and the server_id of the master and slave must be unique within the local area network;

Configure master-slave replication steps

Master database

(1) Install the database;

(2) Modify the database configuration file, specify the server_id, and enable binary logging (log-bin);

(3) Start the database and check the current log and position number.

(4) Log in to the database and authorize the data replication user (the IP address is the slave IP address. If it is a two-way master-slave, you also need to authorize the local IP address. In this case, your own IP address is the slave IP address);

(5) Back up the database (remember to lock and unlock it);

(6) Send backup data to Slave;

(7) Start the database;

The above steps are for a successful one-way master-slave setup. To build a two-way master-slave setup, you need to:

(1) Log in to the database and specify the address, user, password and other information of the Master (this step is only required for bidirectional master-slave);

(2) Enable synchronization and check the status;

Configuration on Slave

(1) Install the database;

(2) Modify the database configuration file and specify server_id (if a bidirectional master-slave system is being built, binary log log-bin must also be enabled);

(3) Start the database and restore the backup;

(4) Check which log is currently being viewed and what its position number is (this step is not required for one-way master-slave communication, but is required for two-way master-slave communication);

(5) Specify the Master's address, user, password, and other information;

(6) Enable synchronization and check the status.

Okay, that’s all for today. We will implement MySQL master-slave replication in various ways in the future. I’m Binghe, see you next time! !

The above is the detailed content of the principle of MySQL master-slave replication and the points that need attention. For more information about MySQL master-slave replication, please pay attention to other related articles on 123WORDPRESS.COM!