MENU

Mysql主主同步方案

2019 年 07 月 07 日 • 阅读: 1180 • Centos,运行环境,技术文章

mysql

主从同步复制原理

在开始之前,我们先来了解主从同步复制原理。

复制分成三步:

  1. master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
  2. slave将master的binary log events拷贝到它的中继日志(relay log);
  3. slave重做中继日志中的事件,将改变反映它自己的数据。
    下图描述了这一过程:

mysql

好了,我废话不多说了,直接讲怎么实现这个过程吧。
假设我有两个服务器:
masterA的ip地址:192.168.10.1
masterB的ip地址:192.168.10.2
首先安装mysql环境, 我的服务器(Linux)选择安装宝塔面板了,数据库选择安装 Mysql5.6 , 宝塔是一键安装不属于另设置, 等安装好了之后 选择:
mysql
在配置修改界面里写入:
这里是首先配置 masterA ,也就是我当前服务器作为主,
[mysqld]下面写入:

gtid-mode=on
enforce_gtid_consistency=on
log-slave-updates = true  #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-bin=mysql-bin
binlog_format=mixed
server-id = 1   
auto_increment_offset = 1
auto_increment_increment = 2 #奇数ID
expire_logs_days = 10
slow_query_log=1
replicate-ignore-db = mysql 
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix
skip-character-set-client-handshake 
init-connect='SET NAMES utf8'
character-set-server=utf8 
wait_timeout=1800 
interactive_timeout=1800 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]

如果这些值存在,请修改=后面的参数即可, 修改完成后点击保存并重启mysql服务。

这里是首先配置 masterB ,也就是我第二个主服务器:
[mysqld]下面写入:

gtid-mode=on
enforce_gtid_consistency=on
log-slave-updates = true  #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-bin=mysql-bin
binlog_format=mixed
server-id = 2  
auto_increment_offset = 2
auto_increment_increment = 2 #奇数ID
expire_logs_days = 10
slow_query_log=1
replicate-ignore-db = mysql 
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix
skip-character-set-client-handshake 
init-connect='SET NAMES utf8'
character-set-server=utf8 
wait_timeout=1800 
interactive_timeout=1800 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]

如果这些值存在,请修改=后面的参数即可, 修改完成后点击保存并重启mysql服务。
接下来在 masterA 服务器上 通过ssh 登录mysql,具体登录代码:

mysql -h地址 -u账号 -p密码

如:mysql -hlocalhost -uroot -p123456

2. 添加主从同步账户

mysql> grant replication slave,reload,super on *.* to 2free@192.168.10.2 identified by '123456';
mysql> flush privileges;

这里解释下参数: to 2free@192.168.10.2 // @前面是要创建的数据库账号 ,@后面是你B服务器的IP地址, identified by 里面是 设置的2free账号的密码 A服务器配置B服务器的参数!

同样在masterB

mysql> grant replication slave,reload,super on *.* to 2free@192.168.10.1 identified by '123456';
mysql> flush privileges;

2.2 查看主库的状态

masterA上:

mysql> show master status;

结果:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

masterA上:

mysql> show master status;

结果:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 437      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.3 配置同步信息:

masterA上:

mysql> STOP slave;
mysql> change master to master_host='192.168.10.2',master_port=3306,master_user='2free',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=437;   //这里master_host为B服务器的IP地址,master_user之前创建的账号,master_password之前创建的账号2free的密码,master_log_file为B服务器的File,master_log_pos为B服务器的Position.
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

masterB上:

mysql> STOP slave;
mysql> change master to master_host='192.168.10.1',master_port=3306,master_user='2free',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;
mysql> start slave;
mysql> show slave status\G;

显示有如下状态则正常:

Slave_IO_Running: Yes
    
Slave_SQL_Running: Yes

3. 开启MySQL5.6的GTID功能

masterA和masterB分别执行如下命令::

mysql> stop slave;
返回:Query OK, 0 rows affected (0.00 sec)
mysql> change master to MASTER_AUTO_POSITION=1;
返回:Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
返回:Query OK, 0 rows affected (0.00 sec)

大功告成,此时可以到宝塔创建一个数据库看看,AB服务器都会同步。
一些配置:

master端:

binlog-do-db=xxx  //二进制日志记录的数据库(多数据库用逗号,隔开)
binlog-ignore-db=xxx  //二进制日志中忽略数据库 (多数据库用逗号,隔开)

举例说明:

binlog-do-db=YYY 需要同步的数据库,不在内的不同步。(不添加这行表示同步所有)

增加通配符的两个配置

replicate-wild-do-table=db_name.%   只复制哪个库的哪个表
replicate-wild-ignore-table=mysql.%   忽略哪个库的哪个表

slave端

replicate-do-db    设定需要复制的数据库(多数据库使用逗号,隔开)
replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号,隔开)
replicate-do-table  设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表 
replicate-wild-do-table 同replication-do-table功能一样,但是可以通配符
replicate-wild-ignore-table 同replication-ignore-table功能一样,但是可以加通配符

如:

    replicate-do-db=test
    replicate-do-db=zhengchengjun
    replicate-ignore-db=test2
编辑从服务器上的配置参数,指定test数据库,zhengjun数据库被复制,test2数据库不会被复制。
最后编辑于: 2020 年 01 月 26 日
返回文章列表 文章二维码
本页链接的二维码
打赏二维码