架构图:
异步复制
- 首先确保主从上都安装了相同版本的mysql。(附:mysql的安装)
在主库上,新增一个用户从库查询的账号,并赋权:
create user 'repl'@'%' identified by '123456';
GRANT ALL PRIVILEGES ON . TO 'repl'@'%' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON . To 'repl'@'%';
flush privileges;修改主库my.cnf配置,开启bin-log,并设置server-id的值,重启
log-bin=/acdata/mysql/log/mysql-bin.log
server-id=1修改从库my.cnf配置,设置server-id值,不可与主库重复
server-id=2
dump主库数据到从库恢复(此步骤操作时,建议停掉主库执行)
./mysql/bin/mysqldump -uroot -p --all-databases > /acdata/20211020.sql
登录从库
mysql> source /acdata/20211020.sql主库flush,并获取偏移量
mysql> flush logs;
Query OK, 0 rows affected (0.73 sec)
mysql> show master status; | ||||
---|---|---|---|---|
File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
mysql-bin.000031 | 607 |
1 row in set (0.00 sec)
根据file和位置,开启从库的同步
CHANGE MASTER TO MASTER_HOST='172.17.32.51',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456' ,MASTER_LOG_FILE='mysql-bin.000031',MASTER_LOG_POS=607;
开启同步,并查询同步状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
主库可以执行show processlist;查询同步进程
mysql> show processlist;
| 2143030 | repl | 172.17.32.65:34576 | NULL | Binlog Dump | 1863 | Master has sent all binlog to slave; waiting for more updates | NULL