Shulou(Shulou.com)05/31 Report--

This article mainly introduces the mysql replication principle and practical application of the example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

The replication function is to replicate the data from one mysql database to one or more mysql slave databases.

How replication works: all DDL and DML statements executed on the master server are recorded in binary logs, which are obtained by the slave server connected to it, copied to the slave library, and saved as relay logs

This process is done by a thread called IO thread, and another thread called SQL thread executes the statements in the relay log sequentially.

There are many topological forms of replication:

1. Traditional replication, one master and multiple slaves, one master server and multiple slave servers.

2, chain replication, one server replicates from the main library, and another server replicates from this server, and the intermediate server is also called relay main library.

3. Master master replication, and the two master libraries accept write and replication to each other.

4. Multi-source replication, one slave library, replication from multiple master libraries.

First, how to operate replication

1. Enable binary logging on the main library.

2. Create a replication user on the main library.

3. Set up a unique server_id in the training.

4. Back up data from the main library.

5. Restore the data backed up by the master library on the slave library.

6. Execute CHANGE MASTER TO command.

7. Start copying.

2. The specific operation steps are as follows:

1. On the main library, enable binary logging and set server_id.

# set server_idserver_id = enable binlog log log-bin = mysql-bin

2. Create a replication user on the main library

Create user 'username' @'% 'identified by' password'; grant replication slave on *. * to 'username' @'%'

3. Set server_id on the slave library

# set server_idserver_id = 10

4. Back up the data on the main database

Mysqldump-u root-p-- all-databases-- routines-- events-- triggers-- single-transaction-- master-data > Export path

5. Restore the data exported by the master library on the slave library

Mysql-u root-p-f

< 主库备份文件.sql 6、在从库上执行 CHANGE MASTER TO 命令 CHANGE MASTER TO MASTER_HOST='主库IP',MASTER_USER='主库复制用户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='二进制日志名称',MASTER_LOG_POS=二进制日志位置; 二进制日志名称和二进制日志位置，已经在备份主库文件中包含了，类似如下所示： CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=47845; 7、从库上运行 start slave，然后 show slave status\G; 查看复制状态; 三、设置主主复制 假设主库分别是master1和master2。 1、设置master2为只读 set @@global.read_only = on; 2、在master2上创建复制用户，如果存在，则不用创建了 create user '用户名'@'%' identified by '密码';grant replication slave on *.* to '用户名'@'%'; 3、确保master2已开启二进制日志，检查master2上的二进制日志的坐标 show master status; 4、根据第2步的信息，在master1上执行 CHANGE MASTER TO 命令 CHANGE MASTER TO MASTER_HOST='MASTER2主机IP',MASTER_USER='MASTER2复制用户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='二进制日志名称',MASTER_LOG_POS=二进制日志位置; 5、在master1上开启 slave 模式 start slave; 6、设置master2为可读写 set @@global.read_only = off; 四、设置多源复制 设置 server3 为 server1 和 server2 的从库。 1、设置 server1 和 server2 的二进制日志和server_id，具体操作可参考上面。 2、在 server1 和 server2 上创建复制用户，具体操作可参考上面。 3、在 server3 上设置 server_id。 4、备份 server1 和 server2 的数据。 5、在 server3 上恢复 server1 和 server2 上备份的数据。 6、在 server3 上，将复制存储库从 FILE 改为 TABLE， stop slave;set global master_info_repository = 'TABLE';set global relay_log_info_repository = 'TABLE'; 还需要在配置文件中修改： [mysqld]master-info-repository = TABLErelay-log-info-repository = TABLE 7、在 server3 上，执行 CHANGE MASTER TO 命令，并命名通道名 CHANGE MASTER TO MASTER_HOST='server1主机IP',MASTER_USER='server1复制用户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='server1二进制日志名称',MASTER_LOG_POS=server1二进制日志位置 FOR CHANNEL 'server1';CHANGE MASTER TO MASTER_HOST='server2主机IP',MASTER_USER='server2复制用户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='server2二进制日志名称',MASTER_LOG_POS=server2二进制日志位置 FOR CHANNEL 'server2'; 8 、在 server3 上，为每个通道执行 START SLAVE FOR CHANNEL 语句 start slave for channel 'server1';start slave for channel 'server2'; 9、查看同步状态，show slave status\G; 要获取指定通道的从库状态，show slave status for channel '通道名称'\G; 五、设置复制筛选器 可以选择要复制哪些表或数据库，在主库上，可以使用--binlog-do-db 和 --binlog-ignore-db 选项来选择要记录变更的数据库，以控制二进制日志。更好的方法是控制从库。 1、复制指定数据库 CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2); 2、复制指定表 CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1'); 3、如果想使用通配符来选择表 CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.tb_%'); 4、忽略数据库 CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2); 5、忽略指定表 CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1'); 六、将从库由主从复制切换到链式复制 比如现在服务器A为主库，服务器B和服务器C为从库，复制于服务器A。现在想把服务器C作为服务器B的从库。 1、在服务器C上停止从库运行 stop slave;show slave status\G; 记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值 2、在服务器B上停止从库运行 stop slave;show slave status\G; 记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值 3、将服务器B的日志位置与服务器C的进行比较，找出哪一个是服务器A最新同步，通常，服务器C先停止从库运行，服务器B的日志会更靠前。 4、在服务器C上，使用 START SLAVE UNTIL 语句将其同步到服务器B的日志位置： START SLAVE UNTIL MASTER_LOG_FILE='上一步中服务器B日志名称', MASTER_LOG_POS=上一步中服务器B日志位置; 5、在服务器C上，检查 show slave status 中 Exec_Master_Log_Pos 和 Until_Log_Pos 两者应该相同。 6、在服务器B上，查看主库状态，启动从库。 show master status;start slave;show slave status\G; 7、在服务器C上，停止从库运行，执行 CHANGE MASTER TO 命令。 stop slave;CHANGE MASTER TOMASTER_HOST='服务器B的IP',MASTER_USER='服务器B复制用户',MASTER_PASSWORD='密码',MASTER_LOG_FILE='上一步中通过show master status获取日志名称',MASTER_LOG_POS=上一步中通过show master status获取日志位置; 8、在服务器C上，启动复制并查看状态 start slave;show slave status\G; 七、将链式复制切换到主从复制 服务器A->

Server B-> server C, what should I do if I want server C to be the slave library of server A directly?

1. On server B, stop running from the slave library and record the status of the master library

Stop slave;show master status\ G

2. On server C, make sure that the delay of the slave library has been caught up, and that Relay_Master_Log_File and Exec_Master_Log_Pos should be equal to the state of the master library on server B.

Once the delay is overtaken, the slave library is stopped.

Stop slave

3. On server B, get the log coordinate values (Relay_Master_Log_File and Exec_Master_Log_Pos) of server A from show slave status, and start the slave library.

Show slave status\ Gbomet start slave

4. On server C, stop running from the library and execute the CHANGE MASTER TO command to point to server A.

Replication user', MASTER_PASSWORD=' password'of stop slave;CHANGE MASTER TO MASTER_HOST=' server An of IP',MASTER_USER=' server A, log obtained in the previous step of MASTER_LOG_FILE=', log location obtained in the previous step of MASTER_LOG_POS=

5. On server C, open the slave library and check the status.

Start slave;show slave status\ G

8. Set delayed replication

Why do you need to delay replication? it is possible that a catastrophic statement was executed on the main library, and you have to restore it at a point in time in the backup. If the database size is too large, this will lead to long downtime.

To avoid this, you can use a deferred slave library. If there is a disaster and the delayed slave library has not yet executed this catastrophic statement, you can stop replication and let the slave library skip the disaster statement. Finally, promote the slave library to the master library.

1. Stop running from the library

Stop slave

2. Set the delay time in seconds

CHANGE MASTER TO MASTER_DELAY = 3600bot start slave

3. Check the slave status

Show slave status\ G

SQL_Delay: the number of seconds behind the master database from the slave library.

SQL_Remaining_Delay: the number of seconds left in the delay, which is NULL when the delay is maintained.

State of the Slave_SQL_Running_State:SQL thread

IX. Set up GTID replication

The global transaction identifier GTID is a unique identifier created in the program and associated with each transaction committed on the main library. The identifier is unique, not only on the master library, but also on other slave libraries.

All the copies described above need to indicate the location of the binaries and the starting point of the replication, and if you switch one from the main library of the library to another, you have to get the location of the binaries again, which can be troublesome.

To avoid, you can use GTID-based replication, and mysql uses GTID to automatically detect the location of binary logs.

1. Start GTID in my.cnf in all databases

[mysqld] gtid_mode = ONenforce-gtid-consistency = 1skip_slave_start

2. Set the master library to read-only to ensure that the data of the master library is consistent with that of the slave library.

Set @ @ global.read_only = on

3. Restart all slave libraries to make GTID effective.

4. Restart the main library.

5. Execute the CHANGE MASTER TO command on the slave library to set up GTID replication

CHANGE MASTER TOMASTER_HOST=' main library IP',MASTER_PORT=3306,MASTER_USER=' replicates user', MASTER_PASSWORD=' password', MASTER_AUTO_POSITION=1

6. Execute start slave; on all slave libraries and check the status.

Setting up semi-synchronous replication

By default, replication is asynchronous, and the master database does not know whether the write operation reaches the slave library. If there is a delay between the master library and the slave library, the master library collapses, and the data that has not yet reached the slave library will be lost.

To solve this problem, in semi-synchronous replication, the main library waits until at least one of the written data is received from the library.

1. On the main library, install the rpl_semi_sync_master plug-in

Install plugin rpl_semi_sync_master SONAME 'semisync_master.so'

Use the following under windows:

Install plugin rpl_semi_sync_master SONAME 'semisync_master.dll'

2. Confirm that the plug-in has been activated

Select plugin_name, plugin_status from information_schema.plugins where plugin_name like'% semi%'

3. Turn on semi-synchronous replication and adjust the timeout

Set @ @ global.rpl_semi_sync_master_enabled=1;set @ @ global.rpl_semi_sync_master_timeout=100

4. Install the rpl_semi_sync_slave plug-in on the slave library

Install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so'

Use the following under windows:

Install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll'

5. Confirm that the plug-in has been activated

Select plugin_name, plugin_status from information_schema.plugins where plugin_name like'% semi%'

6. On the slave library, enable semi-synchronous replication and restart the slave IO thread

Set global rpl_semi_sync_slave_enabld = 1 * stop SLAVE IO_THREAD;START SLAVE IO_THREAD

7. Check the semi-synchronous status as follows

Show status like 'rpl_semi_sync_master_clients'

Check the number of clients that are semi-synchronously connected to the main library

Show status like 'rpl_semi_sync_master_status'

The main library switches between asynchronous and semi-synchronous replication, with on for semi-synchronous and off for asynchronous.

Thank you for reading this article carefully. I hope the article "sample Analysis of replication principles and practical applications in mysql" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.