mysql主主互备跟主从互备区别_MySQL主从同步、主主互备

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

一、实验环境

1.系统:CentOS7

2.MySql:MySql5.7.16

3、服务器：主(master):192.168.4.193

从(slave): 192.168.4.194

二、具体步骤

主从服务器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’;