MySQL 主从同步
配置主节点
开启 binlog, 配置全局唯一的 server-id
[mysqld]
log-bin=mysql-bin
server-id=1
主节点上创建同步的用户
mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';
获取主节点二进制文件的位置
blocks COMMIT operations for InnoDB tables.
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+
data snapshots
如果主节点存在数据，则需要将数据备份并同步至各个 slave 节点
- 通过
mysqldump的方式（推荐方式），特别是使用InnoDB的引擎
shell> mysqldump --all-databases --master-data > dbdump.db
# 或
shell> mysqldump --all-databases --master-data=2 --single-transaction > dbdump.db （该操作会记录 pos 位子，并执行 FLUSH TABLES WITH READ LOCK）
- 通过 raw data file 的方式，
InnoDB的引擎不推荐
shell> mysqladmin shutdown
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata
配置从节点
主节点释放 read lock
mysql> UNLOCK TABLES;
配置从节点全局唯一的 server-id
[mysqld]
server-id=2
max_allowed_packet=1G
从节点导入主节点的备份
shell> mysql < fulldb.dump
从节点指向主节点
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
开启 slave
mysql> START SLAVE;
查看同步状态
mysql> SHOW SLAVE STATUS\G
