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:
主:
log-bin=mysql-bin
server-id=1
从:
server-id=2
注解:server-id必须唯一。如果默认为0，则拒绝连接主服务器。
2.主:
1).启动master的Mysql服务
2).创建用户,让slave服务器用来连接用.
CREATE USER 'mysqlUser'@'ip'>
GRANT REPLICATION SLAVE ON *.* TO 'mysqlUser'@'ip';
例如:
CREATE USER ‘test’@’192.168.4.193’>
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文件下添加
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_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_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’;
页:
[1]