MySQL Replication 主从同步

一、配置

https://dev.mysql.com/doc/refman/8.0/en/replication-options-reference.html

MySQL 复制过滤详解：https://developer.aliyun.com/article/59268

master

[mysqld]
server-id = 1

#log_bin = on # 8 版本默认启用，5 版本需要手动开启
#log_bin_basename = binlog
#log-bin = binlog
#log-bin-index = binlog.index
# 建议在 slave 端做过滤，避免影响 master 端日志文件完整性
#binlog-do-db = test_table # 写入日志
#binlog-ignore-db = mysql,sys,performance_schema,information_schema # 不写入日志

slave

[mysqld]
server-id = 2
super_read_only = on # 禁止手动 CURD，开启后不影响主从同步

# 不需要同步的库和表，显式配置后，未配置的库表将会被同步
# replicate-ignore-db = mysql
# replicate-wild-ignore-table = mysql.%
# replicate-ignore-db = sys
# replicate-wild-ignore-table = sys.%
# replicate-ignore-db = performance_schema
# replicate-wild-ignore-table = performance_schema.%
# replicate-ignore-db = information_schema
# replicate-wild-ignore-table = information_schema.%

# 需要同步的库，不配置表示同步所有
replicate-wild-do-table = db_a.%
replicate-wild-do-table = db_b.%
# replicate-do-db = db_a
# replicate-do-db = db_b
# 需要同步的表
# replicate-do-table = db_a.table_a
# replicate-do-table = db_a.table_b

二、开启同步

在此之前需要保证，不需要同步之外的库表完全一致，否则会同步失败

master

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-repuser.html

-- 创建同步账户，不推荐直接使用 root
DROP USER repl;
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看 master 服务器状态，File 和 Postion 对应的值要记录下来，下面要用到
SHOW MASTER STATUS;
-- 查看从节点
SHOW SLAVE HOSTS;

slave

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-additionalslaves.html

-- 停止同步
STOP SLAVE;

-- 配置主节点信息，MASTER_LOG_FILE 对应 File，MASTER_LOG_POS 对应 Postion
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'binlog.000009',
MASTER_LOG_POS = 1237;

-- 开启同步
START SLAVE;

-- 查看状态，Slave_IO_Running 和 Slave_SQL_Running 必须为 YES
SHOW SLAVE STATUS;

查看信息

https://dev.mysql.com/doc/refman/8.0/en/replication-threads-monitor-main.html

SHOW VARIABLES LIKE '%server_id%';
SHOW VARIABLES LIKE '%log_bin%';
SHOW VARIABLES LIKE '%server_uuid%';
SHOW VARIABLES LIKE '%datadir%';

SHOW PROCESSLIST;
SHOW BINLOG EVENTS;

canal 方案：https://github.com/alibaba/canal

https://github.com/alibaba/canal/blob/master/admin/admin-web/src/main/resources/canal_manager.sql
默认 admin 123456

docker run -d -it 
-e server.port=8089 
-e canal.adminUser=admin 
-e canal.adminPasswd=admin 
-e spring.datasource.address=10.74.2.71:3306 
-e spring.datasource.database=canal_manager 
-e spring.datasource.username=root 
-e spring.datasource.password=root 
-p 8089:8089 
--name=canal-admin -m 1024m canal/canal-admin

docker run -d -it 
-e canal.admin.manager=10.74.2.71:8089 
-e canal.admin.user=admin 
-e canal.admin.passwd=4ACFE3202A5FF5CF467898FC58AAB1D615029441 
-e canal.admin.port=11110 
-e canal.port=11111 
-e canal.metrics.pull.port=11112 
-p 11110:11110 -p 11111:11111 -p 11112:11112 
--name=canal-server -m 4096m canal/canal-server

---

https://dev.mysql.com/doc/refman/8.0/en/replication.html

https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

https://blog.csdn.net/u013068184/article/details/107691389

https://blog.51cto.com/davewang/1858470