一、数据相同的两个主机 (两个主机同步后从库不会拥有主库原本的数据)
1、主库及从库上都要进行的操作。
①、同步时间
[root@localhost ~]# yum -y install ntp ntpdate
[root@localhost ~]# ntpdate cn.pool.ntp.org
[root@localhost ~]# hwclock --systohc
②、关闭防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
2、主库上
①、修改配置文件(/etc/my.cnf)
[mysqld]
server_id=27
log-bin=mysql-bin
log-bin-index=master-bin.index
②、重启服务(restart也可以,需要时间较长,也有可能启动不了)
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
③、创建一个用于让从数据库来连接的用户
mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';
mysql> grant replication slave on *.* to 'copy'@'%';
④、刷新授权表信息
mysql> flush privileges;
⑤、获取主节点当前binary log文件名和位置(position)[从库中应用]
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1027
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.43 sec)
3、从库上
①、修改配置文件(/etc/my.cnf)
[mysqld]
server_id=66
relay-log=relay-log
relay-log-index=relay-log.index
②、重启服务(restart也可以,需要时间较长,也有可能启动不了)
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
③、在从(Slave)节点上设置主节点参数(mysql中设置)
CHANGE MASTER TO
MASTER_HOST='192.168.142.88', (主库ip)
MASTER_USER='copy',
MASTER_PASSWORD='CHUYE123@chuye',(主库密码)
MASTER_LOG_FILE='mysql-bin.000001',(主库查询知)
MASTER_LOG_POS=658;(主库查询知)
④、查看主从同步状态
mysql> show slave status\G
⑤、开启主从同步
mysql> start slave;
⑥、再次查看主从同步状态
mysql> show slave status\G
4、测试
结果:主库上,创建数据库、表等均会同步到从库上
二、在两个数据库不一致的情况下(全部同步)
1、主库及从库上都要进行的操作。
(1)同步时间
[root@localhost ~]#yum -y install ntp ntpdate
[root@localhost ~]# ntpdate cn.pool.ntp.org
[root@localhost ~]# hwclock --systohc
(2)关闭防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
2、主库上
(1)修改配置文件(/etc/my.cnf)
添加如下:
[mysqld]
server_id=27
log-bin=mysql-bin
log-bin-index=master-bin.index
(2)重启服务
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
(3)创建一个用于让从数据库连接的用户
mysql> create user 'copy'@'%' identified with mysql_native_password by 'Cloudbu@123';
mysql> grant replication slave on *.* to 'copy'@'%';
(4)刷新授权表信息
mysql> flush privileges;
(5)初始化备库
mysql> flush tables with read lock;
[root@localhost ~]# mysqldump -uroot -pCloudbu@123 --routines --events --triggers --master-data=2 --flush-logs --all-databases > /backup/all_`date +%F`.sql
[root@localhost ~]# scp all_2019-07-21.sql 192.168.42.176:/root
3、从库上
(1)同步
[root@localhost ~]# mysql -uroot -pCloudbu@123 <all_2019-07-21.sql
(2)修改配置文件(/etc/my.cnf)
添加如下:
[mysqld]
server_id=66
relay-log=relay-log
relay-log-index=relay-log.index
(3)重启服务
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
(4)在从(Slave)节点上设置主节点参数(mysql中设置)
CHANGE MASTER TO
MASTER_HOST='192.168.142.88', (主库ip)
MASTER_USER='copy',
MASTER_PASSWORD='CHUYE123@chuye',(主库密码)
MASTER_LOG_FILE='mysql-bin.000001',(主库查询知)
MASTER_LOG_POS=658;(主库查询知)
(5)查看主从同步状态
mysql> show slave status\G
(6)开启主从同步
mysql> start slave;
(7)再次查看主从同步状态
mysql> show slave status\G
4、测试
主库上,创建数据库、表等均会同步到从库上