MySQL主从同步、主主互备

网上看到的文档，自己测试成功可用，笔记一下。

一、实验环境
1.系统:CentOS7
2.MySql:MySql5.7.16
3、服务器：主(master):192.168.4.193
从(slave): 192.168.4.194
二、具体步骤

1. 主从服务器MySql关闭 ，修改配置文件my.cnf
   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 ‘test’@’192.168.4.193’ IDENTIFIED BY ‘test1234！’; 
GRANT REPLICATION SLAVE ON . TO ‘test’@’192.168.4.193.’; 

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.000001 | 1608 | | | |
+------------------+----------+--------------+------------------+-------------------+
记住上面两个字段数值：mysql-bin.000001,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数据库

SHOW SLAVE STATUS;
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.4.13’, 
MASTER_USER=’test’, 
MASTER_PASSWORD=’test1234！’, 
MASTER_LOG_FILE=’mysql-bin.000001’, 
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.4.193
Master_User: qfmyy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1608
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
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.4.193
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
同步成功!

---

主主同步：
把master、slave角色互换再执行上诉步骤即可。
错误代码:
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 =’test’;

转载于:https://blog.51cto.com/bilibili/2059686