Introduction:
MySQL's master-slave synchronization is an asynchronous replication process that replicates from one master to another slave. The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.
To achieve MySQL master-slave synchronization, you must first open the master side of the Binarylog (mysql-bin) function, otherwise it will not be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. The Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bi" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) N "parameter entry.
Principle:
(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.
Describes the process of replication:
Specific configuration process:
1. Main Library configuration:
Open the file with VI/ETC/MY.CNF, modify the file, and add the changes under [mysqld]:
server-id = 1 # 这是数据库ID,此ID是唯一的,主库默认为1,其他从库以此ID进行递增,ID值不能重复,否则会同步出错;log-bin = mysql-bin # 二进制日志文件,此项为必填项,否则不能同步数据;binlog_format=row # bilog设置为row模式 防止复制出错
2. From the Library configuration:
Open the file with VI/ETC/MY.CNF, modify the file, and add the changes under [mysqld]:
server_id = 2log-bin=mysql-binrelay_log=mysql-relay-bin# 不指定以下参数则全库同步#replicate-do-table=test.test_tb 同步某张表#binlog-do-db = testcreate 需要同步的数据库,如果需要同步多个数据库;则继续添加此项。#binlog-ignore-db = mysql 不需要同步的数据库;
3. Need to restart master/slave library 4. Create a sync account with the main library:
create user ‘replica‘@‘%‘ identified by ‘123456‘;grant replication slave,replication client,reload,super on *.* to ‘replica‘@‘%‘ identified by ‘123456‘;
5. Go to sync from library
It is necessary to keep the database data consistent with the master and slave synchronization before synchronization is enabled.
# 从库启动slave:# (MASTER_LOG_FILE与MASTER_LOG_POS在主库运行SHOW MASTER STATUS;取得)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; --查看slave状态 确保Slave_IO_Running: Yes Slave_SQL_Running: Yes
"MySQL" master-slave Asynchronous replication configuration