MySql5.7.16主从同步

环境:

1.系统:CentOS7
2.MySql:MySql5.7.16
主(master):192.168.133.161
从(slave):192.168.133.166

步骤

1.两个服务器MySql全部关闭
1).my.cnf:
主:
[mysqld]
log-bin=mysql-bin
server-id=1
从:
[mysqld]
server-id=2
注解:server-id必须唯一。如果默认为0，则拒绝连接主服务器。
2.主:
1).启动master的Mysql服务
2).创建用户,让slave服务器用来连接用.

CREATE USER 'mysqlUser'@'ip' IDENTIFIED BY 'mysqlPassword';
GRANT REPLICATION SLAVE ON *.* TO 'mysqlUser'@'ip';

例如:
CREATE USER ‘qfmyy’@’192.168.133.%’ IDENTIFIED BY ‘Langman082522’;
GRANT REPLICATION SLAVE ON . TO ‘qfmyy’@’192.168.133.%’;
IP地址可用*表示,用以所有ip都可以连接
注解:简单创建用户，授予REPLICATION SLAVE权限。访问限制，密码，用户名等,根据实际情况各自设置
3).获取日志坐标

设置读锁
FLUSH TABLES WITH READ LOCK;
查看日志坐标
SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 1608 | | | | +------------------+----------+--------------+------------------+-------------------+

记住上面两个字段数值

3).导出master所有数据

mysqldump --all-databases --master-data -uroot -p > /tmp/dbdump.db

然后解锁:

UNLOCK TABLES;

把dbdump.db复制到slave服务器的tmp目录下
3.从:
1).my.cnf文件下添加
[mysqld]
skip-slave-start=true
read_only=ON
relay-log=relay-bin
relay-log-index=relay-bin.index
2).启动slave数据库
3).

CHANGE MASTER TO
MASTER_HOST='IP',
MASTER_USER='mysqlUser',
MASTER_PASSWORD='mysqlPassword',
MASTER_LOG_FILE='日志File',
MASTER_LOG_POS=日志Position;

例如:
CHANGE MASTER TO
MASTER_HOST=’192.168.133.161’,
MASTER_USER=’qfmyy’,
MASTER_PASSWORD=’Langman082522’,
MASTER_LOG_FILE=’mysql-bin.000006’,
MASTER_LOG_POS=1608;
4).将/tmp/dbdump.db 导入数据库

/bin/mysql -uroot -p < /tmp/dbdump.db

5).查看slave状态

show slave status \G

Slave_IO_State:
Master_Host: 192.168.133.161
Master_User: qfmyy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1608
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: No
Slave_SQL_Running: No
看到:
Slave_IO_Running: No
Slave_SQL_Running: No
6).启动slave:

start slave;

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.133.161
Master_User: qfmyy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1608
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
看到
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
同步成功!

—————————————————————————————

错误代码:
1130:原因有多种
1).没有彻底关闭selinux防火墙原因

vim /etc/sysconfig/selinux

SELINUX=enforcing 隐藏 SELINUXTYPE=targeted 隐藏 SELINUX=disabled

这个要重启服务器
2).运行访问ip段
主:

update user set host = '%' where user ='mysqlUser';
flush privileges;

例如:update user set host = ‘%’ where user =’qfmyy’;
1593:由于克隆虚拟机原因,导致

相同:
vim 数据存放地址/auto.cnf
随便修改service-uuid的值
如:

重启mysql