基于mysql5.6.27整理
目录
1、主从复制
2、主主复制
MySQL数据同步我们可以通过配置就可以轻松完成,主要有主从复制和主主复制
一、主从复制
个人有两台屌丝机,一个在阿里云上,一个在腾讯云上,为了安全考虑会将截图中的ip将会被隐藏,其使用内网ip替代,如下
阿里云机器:192.168.1.1(主)
腾讯云机器:192.168.2.2(备)
1、确认主MySQL(192.168.1.1)是否开启binglog,如果没开启,请开启binglog
2、在192.168.1.1中创建一个192.168.2.2主机中可以登录的mysql用户,
mysql>grant replication slave on *.* to 'sysc'@'192.168.2.2' identified by '123456';
mysql>flush privileges;
3、查看主MySQL (192.168.1.1)的binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 984885 | db_mblog | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、告知备(192.168.2.2)主的binlog信息,在192.168.2.2中执行:
change master to master_host='192.168.1.1',master_user='sysc',master_password='123456',master_port=4401,master_log_file='mysql-bin.000006',master_log_pos=984885;
说明:change master语句介绍见这里
5、在192.168.2.2中执行
mysql>start slave; #开启复制
mysql>show slave status\G #查看主从复制是否配置成功
当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常,如下
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: sysc
Master_Port: 4401
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1115
Relay_Log_File: VM_0_10_centos-relay-bin.000007
Relay_Log_Pos: 1278
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
二、主主复制
主主复制即在两台MySQL主机内都可以变更数据,而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对,就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题,例如,主键重复,server-id不能重复等等
1、确认主MySQL(192.168.2.2)是否开启binglog,如果没开启,请开启binglog
2、在192.168.2.2中创建一个192.168.1.1主机中可以登录的mysql用户,
mysql>grant replication slave on *.* to 'sysc'@'192.168.1.1' identified by '123456';
mysql>flush privileges;
3、查看主MySQL (192.168.2.2)的binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 984885 | db_mblog | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、告知备(192.168.1.1)主的binlog信息,在192.168.1.1中执行:
change master to master_host='192.168.2.2',master_user='sysc',master_password='123456',master_port=4401,master_log_file='mysql-bin.000006',master_log_pos=984885;
说明:change master语句介绍见这里
5、在192.168.1.1中执行
mysql>start slave; #开启复制
mysql>show slave status\G #查看主从复制是否配置成功
当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常,如下
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.2
Master_User: sysc
Master_Port: 4401
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1115
Relay_Log_File: VM_0_10_centos-relay-bin.000007
Relay_Log_Pos: 1278
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
个人以下问题:
个人遇到以下问题: