7.主从过滤复制和延迟复制()-其他

7.主从过滤复制和延迟复制()

1.延时复制　

   配置延迟复制

# 从库上执行
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;   # 加上这一行
mysql>start slave;

   查看结果： show slave status

SQL_Delay: 300
SQL_Remaining_Delay: 266
slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

• SQL_Delay: 从库要落后主库的秒数
• SQL_Remaining_Delay: When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field is NULL.
• Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value of the SQL thread as displayed by SHOW PROCESSLIST.

   停止延迟复制

mysql> stop slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_delay=0;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

参考：https://dev.mysql.com/doc/refman/5.7/en/replication-delayed.html

2.过滤复制

　　过滤复制有数据库级别和表级别的过滤，其中数据库级别参数有()　表级别的有()

replicate-do-db|replicate-ignore-db

replicate-ignore-db

--replicate-do-table|--replicate-ignore-table

--replicate-ignore-table

　　也可以用库级别的参数+表级别的参数进行搭配用来进行过滤。然后这里要默认的

binlog_format=ROW.

　　从库中配置：　

 binlog_format = row
 replicate_ignore_db=test
 replicate_do_table=test1.s

    查看：show slave status\G;

Replicate_Ignore_DB: test
Replicate_Do_Table: test1.s

参考：https://dev.mysql.com/doc/refman/8.0/en/replication-rules-examples.html 

1.延时复制　

   配置延迟复制

# 从库上执行
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;   # 加上这一行
mysql>start slave;

   查看结果： show slave status

SQL_Delay: 300
SQL_Remaining_Delay: 266
slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

• SQL_Delay: 从库要落后主库的秒数
• SQL_Remaining_Delay: When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field is NULL.
• Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value of the SQL thread as displayed by SHOW PROCESSLIST.

   停止延迟复制

mysql> stop slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to master_delay=0;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected, 1 warning (0.01 sec)

参考：https://dev.mysql.com/doc/refman/5.7/en/replication-delayed.html

2.过滤复制

　　过滤复制有数据库级别和表级别的过滤，其中数据库级别参数有()　表级别的有()

replicate-do-db|replicate-ignore-db

replicate-ignore-db

--replicate-do-table|--replicate-ignore-table

--replicate-ignore-table

　　也可以用库级别的参数+表级别的参数进行搭配用来进行过滤。然后这里要默认的

binlog_format=ROW.

　　从库中配置：　

 binlog_format = row
 replicate_ignore_db=test
 replicate_do_table=test1.s

    查看：show slave status\G;

Replicate_Ignore_DB: test
Replicate_Do_Table: test1.s

参考：https://dev.mysql.com/doc/refman/8.0/en/replication-rules-examples.html