一、master主库
1、全备主库，全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
2、备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-2023.sql
[root@master ~]# scp /opt/all-2023.sql root@192.168.111.137:/opt/
3、解锁
mysql> unlock tables;
4、在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'%' identified by '123456';
mysql> grant replication slave on *.* to 'repl'@'%';
mysql> flush privileges;
5、配置主数据库
vim /etc/my.cnf //在[mysql]这段后面添加 log-bin=mysql-bin //添加 启用binlog日志 server-id=1 //添加 数据库服务器唯一标识符，主库的server-id值必须比从库的小
6、重启数主库的mysql服务，查看主库的状态
systemctl restart mysqld //查看主库的状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
二、slave从库
1、在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
mysql -uroot -p123456 < /opt/all-2023.sql
2、配置从数据库
vim /etc/my.cnf //在[mysql]这段后面添加 server-id=2 //添加 从库的server-id比主库的大 relay-log=mysql-relay-bin //添加
3、重启mysql服务
systemctl restart mysqld
4、配置并启动主从复制
mysql> reset slave; mysql> change master to master_host='192.168.111.135', master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154; mysql> start slave;
mysql> show slave status \G