主从复制:
在两个完全一致的环境下==============
(1)初始化环境。
同步时间:
yum -y install ntp ntpdate
ntpdate cn.pool.ntp.org
hwclock --systohc
关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
环境规划:
主机:
在主库上:
修改主库配置文件:开启log-bin功能,并指定server_id为1.这里log-bin必须指定,
不然的话bin-log日志会根据主机名来命名,假如主库的主机名改变了,那主从复制就失效了。
添加
vim /etc/my.cnf
[mysqld]
server_id=27 #从比主大
log-bin=mysql-bin
log-bin-index=master-bin.index
重启服务:
[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql
创建一个用于让从数据库连接的用户 #在主库创建
mysql> create user ‘copy’@’%’ identified with mysql_native_password by ‘Cloudbu@123’;
mysql> grant replication slave on . to ‘copy’@’%’; #授权
刷新授权表信息
mysql> flush privileges; fiush tables with read lock; #锁表
获取主节点当前binary log文件名和位置(position)
mysql> show master status;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000001 | 155 | | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)
在从库上:
修改配置文件,必须指定中继日志的名称
[mysqld]
server_id=66
relay-log=relay-log
relay-log-index=relay-log.index
[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# systemctl start mysql
在从(Slave)节点上设置主节点参数
CHANGE MASTER TO
MASTER_HOST=‘192.168.42.175’,
MASTER_USER=‘copy’,
MASTER_PASSWORD=‘Cloudbu@123’,
MASTER_LOG_FILE=‘mysql-bin.000001’,
MASTER_LOG_POS=825;
查看主从同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.42.175
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 155
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: