经常用于MySQL Replication相关的命令和选项

最新推荐文章于 2021-04-09 14:51:32 发布

原创最新推荐文章于 2021-04-09 14:51:32 发布 · 121 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了MySQL中设置复制用户、授权、更改主从配置的方法，并展示了如何启动与停止复制进程。此外，还提供了针对不同数据库的复制过滤示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql> CREATE USER 'repl'@'192.168.1.50' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.50';
CHANGE MASTER TO
MASTER_HOST='10.0.17.152',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=24218404,
MASTER_CONNECT_RETRY=10;
show master status;
show slave status\G;
show processlist\G;
mysql> start slave io_thread;
mysql> start slave sql_thread;
mysql> stop slave io_thread;
mysql> stop slave sql_thread;
- Replication slave 1 should use --replicate-wild-do-table=databaseA.%.
- Replication slave 2 should use --replicate-wild-do-table=databaseB.%.
- Replication slave 3 should use --replicate-wild-do-table=databaseC.%.(指定数据库的所有数据库的表)
reset master
mysqlbinlog --start-position=243387732 forummysql01-bin.002937
mysqlbinlog ./forummysql01-bin.002937 > /home/jss/bin-002937.log
tail -50 /home/jss/bin-002937.log
选项

server-id = 323307

log_slave_updates = 1

auto_increment_increment=2

auto_increment_offset=2

read-only（保证只读）
log-bin
MASTER_CONNECT_RETRY=10

replicate-do-db
replicate-do-table
replicate-ignore-db
replicate-ignore-table
replicate-wild-do-table
slave-skip-errors

max_allowed_packet