mysql【双主模式配置】

前提准备

主机	系统	IP	mysql
`mysql01`	`centos7`	`192.168.41.141`	已部署`5.7.31`
`mysql02`	`centod7`	`192.168.41.142`	已部署`5.7.31`

原理

MySQL中有一种日志叫做 bin日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句。主从复制的原理其实就是从服务器向主服务器请求这个日志文件，主服务器会把这个 bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

主服务器必须启用二进制日志（log-bin），记录任何修改了数据库数据的事件；
从服务器开启一个线程（I/O Thread)把自己扮演成 MySQL 的客户端，通过 MySQL 协议，请求主服务器的二进制日志文件中的事件；
主服务器启动一个线程（Dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主服务器就会从第一个日志文件中的第一个事件一个一个发送给从服务器；
从服务器接收到主服务器发送过来的数据把它放置到中继日志（relay log）文件中。并记录该次请求到主服务器的具体哪一个二进制日志文件内部的哪一个位置（主服务器中的二进制文件会有多个，其名结尾以6位数递增）；
从服务器启动另外一个线程（SQL Thread），把 relay log 中的事件读取出来，并在本地再执行一次。

双主架构思路

两台mysql都可读写，互为主备
两台主库之间做高可用,可以采用haproxy、keepalived等方案

双主配置

主要配置文件/etc/my.cnf

修改配置

vim /etc/my.cnf

# 添加以下配置
# id 保证唯一
server-id=1
# 开启binlog 日志
log-bin=mysql-bin
# 自增ID保证不冲突, 一主1,3,5 另一主2,4,6
auto-increment-increment=2
auto-increment-offset=1
# 将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
# 忽略同步的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=sys
# binlog文件大小限制
max_binlog_size=1024M
# binlog格式
binlog-format=ROW

另一台配置

server-id=2
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates=true
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=sys
max_binlog_size=1024M
binlog-format=ROW
#binlog_rows_query_log_events=on # 在row 模式的binlog中包含SQL EVENTS（即SQL语句也会保留）
#master-info-repository=TABLE
#relay-log-info-repository=TABLE
#slave-skip-errors=all # 忽略复制产生的错误
#slave-skip-errors=1062,1032,1060 # 跳过已知错误,主键冲突、表已存在等错误代码如1062,1032,1060等
#relay-log-purge = 1    #是否自动清空不再需要中继日志时。默认值为1(启用)
#expire_logs_days = 30  #超过 30 天的 binlog 删除

slave_skip_errors参数说明:

slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors, 默认情况下该参数值是off.
mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
一些error code代表的错误如下：
    1007：数据库已存在，创建数据库失败
    1008：数据库不存在，删除数据库失败
    1050：数据表已存在，创建数据表失败
    1051：数据表不存在，删除数据表失败
    1054：字段不存在，或程序文件跟数据库有冲突
    1060：字段重复，导致无法插入
    1061：重复键名
    1068：定义了多个主键
    1094：位置线程ID
    1146：数据表缺失，请恢复数据库
    1053：复制过程中主服务器宕机
    1062：主键冲突 Duplicate entry '%s' for key %d

重启mysql

# 根据安装方式选择
service mysql restart

添加主从同步账户

-- mysql01
grant replication slave on *.* to 'slave'@'192.168.41.142' identified by '123456';
flush privileges;

-- mysql02
grant replication slave on *.* to 'slave'@'192.168.41.141' identified by '123456';
flush privileges;

查看主库状态

-- 分别在mysql01,mysql02查看, 这里由于都是新安装的，status完全一样，实际可能不一样
-- 记住file的名字和position的值，因为主从同步，从数据库是通过读取主数据库的日志文件来完成同步的，所以需要文件名字和日志的当前位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      604 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置同步信息

-- mysql01
change master to master_host='192.168.41.142',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=604;

start slave;

-- mysql02
change master to master_host='192.168.41.141',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=604;

start slave;

查看同步状态

-- 分别在mysql01,mysql02查看同步状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.141
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes      -- 状态正常
            Slave_SQL_Running: Yes      -- 状态正常

测试

-- 随便在 mysql01 或 mysql02 上创建库、表或者数据都能同步到另一台机器上
create database test;

CREATE TABLE `t_type` (
                            `type_id` INT(2) NOT NULL AUTO_INCREMENT COMMENT '资源类型id',
                            `type_name` VARCHAR(32) NOT NULL COMMENT '资源类型名称',
                            `type_remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
                            `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                            `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                            PRIMARY KEY (`type_id`),
                            UNIQUE KEY (type_name)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into t_type(type_name, type_remark) VALUES ('菜单资源','用于确定页面菜单资源，分多级菜单');
insert into t_type(type_name, type_remark) VALUES ('按钮资源','用于确定页面按钮资源');
insert into t_type(type_name, type_remark) VALUES ('路由资源','用于确定网关路由资源');

开启MySQL5.6之后引入的GTID复制功能

vim /etc/my.cnf
# 增加以下配置,开启gtid
gtid-mode=on
enforce-gtid-consistency=on
# 修改完my.cnf需要重启mysql才能生效

-- mysql01,mysql02分别执行以下命令
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)

GTID又叫全局事务ID（Global Transaction ID），是一个已提交事务的编号，并且是一个全局唯一的编号。MySQL5.6版本之后在主从复制类型上新增了GTID复制。

GTID！MySQL复制中的核武器

双主配置之前数据备份

使用mysqldump命令,MySQL操作mysqldump命令详解

-- 导出主数据库的数据
mysqldump -uroot -p --opt --all-databases >/all_databases.sql

-- 导入数据
mysql -u root -p  < /all_databases.sql

-- 或者使用source导入
-- mysql> source /all_databases.sql;