Shulou(Shulou.com)06/01 Report--

Brief introduction:

Master-slave synchronization of Mysql is an asynchronous replication process that replicates from one Master to another Slave. The whole replication process between Master and Slave is mainly completed by three threads, of which two threads (Sql thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side.

In order to realize the master-slave synchronization of MySQL, we must first turn on the BinaryLog (mysql-bin) function on the Master side, otherwise it cannot be realized. Because the whole replication process is actually Slave takes the log from the Master side and then performs the various operations recorded in the log in full sequence on its own. You can open the Binary Log of MySQL by using the "- log-bin" parameter option during the startup of MySQL Server, or by adding the "log-bin" parameter item to the mysqld parameter group in the my.cnf configuration file (the parameter section after [mysqld] identification).

Principle:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

The following figure describes the replication process:

Specific configuration process:

1. Master library configuration:

Open the file with vi / etc/my.cnf, make changes to the file, and add changes under [mysqld]:

Server-id = 1 # this is the database ID, this ID is unique, the master library defaults to 1, other slave libraries increment with this ID, the ID value cannot be repeated, otherwise there will be synchronization errors; log-bin = mysql-bin # binary log files, this is required, otherwise the data cannot be synchronized; binlog_format=row # bilog is set to row mode to prevent replication errors 2. From the library configuration:

Open the file with vi / etc/my.cnf, make changes to the file, and add changes under [mysqld]:

Server_id = 2log-bin=mysql-binrelay_log=mysql-relay-bin# do not specify the following parameters, then full library synchronization # replicate-do-table=test.test_tb synchronize a table # binlog-do-db = databases that need to be synchronized, if you need to synchronize multiple databases, continue to add this item. # binlog-ignore-db = mysql databases that do not require synchronization; 3. After configuration, you need to restart master-slave library 4. The main library creates a synchronous account: create user 'replica'@'%' identified by' 123456 grant replication slave,replication client,reload,super on *. * to 'replica'@'%' identified by' 123456 grant 5. Enter the slave library to enable synchronization

The database data to be synchronized by the master and slave needs to be kept consistent before synchronization is turned on.

# launch slave:# from the library (MASTER_LOG_FILE and MASTER_LOG_POS get by running SHOW MASTER STATUS; in the main library) CHANGE MASTER TO MASTER_HOST='192.168.1.60', MASTER_USER='replica', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=875;start slave;show slave status\ G;-- check the status of slave to make sure Slave_IO_Running: Yes Slave_SQL_Running: Yes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.