mysql双主架构配置

主从同步复制原理

复制分成三步：
1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
2. slave将master的binary log events拷贝到它的中继日志(relay log)；
3. slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了这一过程：

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。

MySQL5.6以前的版本复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。 MySQL5.6版本参数slave-parallel-workers=1 表示启用多线程功能。

MySQL5.6开始，增加了一个新特性，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

官方文档：http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

MySQL双主（主主）架构方案思路是:
1.两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；
2.masterA是masterB的主库，masterB又是masterA的主库，它们互为主从；
3.两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务，，要求两台服务器同网段）；
4.所有提供服务的从服务器与masterB进行主从同步（双主多从）;
5.建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）；
这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；

但是也有几个不足的地方:
1.masterB可能会一直处于空闲状态（可以用它当从库，负责部分查询）；
2.主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据，这样可能会造成一定程度的同步延时；

架构的简易图如下:

双主架构配置

安装mysql就不说了，这里说安装好之后，配置文件要怎么配置，这里示例只配置双主，不配置从库。
我这里有A、B两台服务器，用于配置双主，它们分别为：
A：10.37.129.5
B：10.37.129.6

以下是A服务器的mysql配置文件，B服务器的mysql配置文件几乎一样（只有两行不一样，后面说哪两行）：

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt="MySQL [\d]> "
no-auto-rehash

[mysqld]
port = 3306
socket = /tmp/mysql.sock
default_authentication_plugin = mysql_native_password

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0

#服务器1
server-id = 1

#自增id从1开始
auto_increment_offset = 1

#每次自增2(否则会默认自增1)
auto_increment_increment = 2

#指定二进制日志名称为“mysql-bin”，指定该名称就是打开了二进制日志功能,MASTER主服务器必须打开此项，因为从库正是通过bin-log日志来同步数据的
log-bin = mysql-bin

#bin-log日志格式，有ROW/STATEMENT/MIXED，STATEMENT数据量小速度快，ROW复制最安全、MIXED自动选择前两种中的一种作为最优复制方式，关于它们的区别的官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html
binlog-format=ROW

#该选项让从库写入那些“来自主库的更新”，并把这个更新写入到从库自己的bin-log日志中,一台服务器既做主库又做从库此选项必须要开启，而且只要开启了log-bin，该选项就默认开启，官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#option_mysqld_log-slave-updates
log-slave-updates=true

#当启用时，服务器通过只允许执行可以使用GTID安全地记录的语句来强制GTID一致性。在启用基于GTID的复制之前，必须将此选项设置为ON。
enforce-gtid-consistency=ON

#gtid是： global transaction identifiers，设置该项必须保证enforce-gtid-consistency=ON，并且要设置在enforce-gtid-consistency=ON之后。
gtid-mode=ON

#用于设置从服务器用什么样的方式记录主服务器状态，有两种选择，一种是用文件“FILES”，另一种是用InnoDB引擎的表“TABLE”，(ver >= 8.0.2默认值为TABLE，否则默认值为FILES)
master-info-repository=TABLE

#relay-log的基准名称(不包含后缀)，设置后，relay-log会根据这个基准名称加一个数字后缀，不设置会有默认值，请看：https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_relay_log
relay-log=relay-bin

#从库relay-log-index的名称，可以不设置，一般不设置的话，就是$host_name-relay-bin-$channel.index，不过channel可能是空，https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#option_mysqld_relay-log-index
#relay-log-index=slave-relay-bin.index

#用于设置从服务器用什么样的方式记录中继日志(realy log)，同样有FILE和TABLE两种
relay-log-info-repository=TABLE

#从库同步主库状态的间隔(方式)，与master-info-repository的值有关，如果master-info-repository=FILE，也就是如果从库是用文件来存储主库的状态，那么当sync-master-info大于0时，则从库每sync_master_info个事件同步一次。如果为0,MySQL服务器不执行master.info文件到磁盘的同步;相反，服务器依赖于操作系统定期刷新它的内容，就像其他文件一样。
如果master_info_repository = TABLE，也就是从库用表来存储主库的状态，那么当sync-master-info大于0时，从库每sync_master_info个事件更新一次表，否则不更新。
sync-master-info=1

#从库进行数据复制时的并行线程数：https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#option_mysqld_slave-parallel-workers
slave-parallel-workers=0

#自己看文档吧：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_sync_binlog
sync_binlog=0

#是否向bin-log中写入校验和，如果不写就设置为NONE，如果写，就要设置对应的算法，目前只支持：CRC32。https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_binlog-checksum
binlog-checksum=CRC32

#主库是否检测校验和
master-verify-checksum=1

#从库是否检测校验和
slave-sql-verify-checksum=1

#直接看官网文档吧：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_rows_query_log_events
binlog_rows_query_log_events=1

#expire_logs_days=5

#binlog单文件最大值
max_binlog_size=10M

#忽略不同步主从的数据库
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

skip-character-set-client-handshake
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

skip-name-resolve
#skip-networking
back_log = 300

max_connections = 100
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 512k
sort_buffer_size = 2M
join_buffer_size = 1M
key_buffer_size = 4M

thread_cache_size = 8

ft_min_word_len = 4

log_bin = mysql-bin
binlog_format = mixed
binlog_expire_logs_seconds = 604800

log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 200

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

sql_mode=NO_ENGINE_SUBSTITUTION

[mysqldump]
quick
max_allowed_packet = 500M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

A服务器的这两行与B服务器不一样：

server-id = 1   #服务器1
auto_increment_offset = 1       #自增id从1开始

B服务器：

server-id = 2   #服务器2
auto_increment_offset = 2       #自增id从2开始

server-id：不一样很容易理解，因为两台服务器要使用不同“server-id”来区分。
auto_increment_offset：在表id被设置为自增的情况下，自增id的起始值。

由于两台机要相互复制数据，并且保证数据完全一样（即同一条数据，在A机id是1，在B机也必须是1，这样不管访问哪台机的数据库都能保证一样），所以A、B两台机的id必须错开，比如A从1开始自增，每次自增2，即A的id是：1，3，5，7，9这样的，B从2开始自增，也是每次自增2，则B的id是：2，4，6，8，10，这样，当有数据插入到A机，那么A机的数据复制到B机后还是保持原来的id，不会与B机冲突，B机的数据复制到A机时，也是一样，保持原来id，不会冲突，这样相互复制后，两台机都有10条数据，id都是从1到10。

auto_increment_offset这个属性，就是用来设置id的起始值的，而auto_increment_increment属性则用来设置自增量的，但由于我们两台机都设置的自增2，所以这个属性在两台机都是一样的。

A服务器与双主有关的配置

#服务器1
server-id = 1
#自增id从1开始
auto_increment_offset = 1
#每次自增2(否则会默认自增1)
auto_increment_increment = 2
#设置二进制日志base名，相当于打开了二进制功能，MASTER主服务器必须打开此项
log-bin = mysql-bin
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
#强制gtid一致性，开启gtid时必须开启该项，并且最好位于gtid-mode=on之前
enforce-gtid-consistency=ON
开启gtid模式
gtid-mode=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=10M  #binlog单文件最大值

#忽略不同步主从的数据库(等号右边就是你不想同步的数据库名)
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

B服务器主要配置

#服务器2
server-id = 2
#自增id从2开始
auto_increment_offset = 2
#每次自增2(否则会默认自增1)
auto_increment_increment = 2
#设置二进制日志base名，相当于打开了二进制功能，MASTER主服务器必须打开此项
log-bin = mysql-bin
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
#强制gtid一致性，开启gtid时必须开启该项，并且最好位于gtid-mode=on之前
enforce-gtid-consistency=ON
开启gtid模式
gtid-mode=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=10M  #binlog单文件最大值

#忽略不同步主从的数据库(等号右边就是你不想同步的数据库名)
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

配置好后，两台重启两台服务器的mysql，它让加载新配置文件，然后创建用户用于同步的用户。

创建同步用户

在A机mysql上创建一个用户给B机，让B机可以通过这个用户连接A机，所以这里的ip是B机的ip：

create user 'repl'@'10.37.129.6' identified by '123456';

在A机给创建的用户授权并刷新权限：

grant replication slave on *.* to 'repl'@'10.37.129.6';
flush privileges;

同理，在B机的mysql上创建用户给A机，让A机可通过这个用户连接B机，所以这里写的是A机的ip：

create user 'repl'@'10.37.129.5' identified by '123456';

在B机给创建的用户授权：

grant replication slave on *.* to 'repl'@'10.37.129.5';
flush privileges;

注：replication slave是一个整体，它表示允许从库的某个用户去请求主库并获取主库上的更新：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave。

注意，两台机都需要创建一遍这个用户，但是ip要写对方的ip，因为复制bin-log日志是从服务器请求主服务器，所以必须在主库创建一个账号以允许从库登录，由于双主架构两台机互为主从，帮两台机都要设置一次。

配置同步信息并启动同步

首先要说一下，因为A、B机互为主从，当A机mysql被插入数据，那么A机就是主库，B机自然就是从库角色；反之，B机被插入数据，B机就是主库角色，A机就是从库角色。

而配置同步信息都是在从库设置的，设置什么呢？就是设置：告诉从库，它的主库在哪里（通过ip或域名(master_host)和端口(master_port)告诉它），用哪个账号(master_user)和密码(master_password)可以连接它，以及它的主库的最新bin-log日志文件文件名是什么(master_log_file)，以及该bin-log日志已经记录到哪个位置(master_log_pos)。

因为A、B都可能是从库，所以A、B都要配置一遍这个同步信息。

配置A机的同步信息：
先在B机mysql运行以下命令，查看B机的mysql状态：

show master status;

注：在A机上配置同步，其实就是把A机当成从库，那么自然B机就是主库，因为同步是从库读取主库bin-log日志，所以我们现在要先查看B机(主库)的状态。

B机(主库)当前状态如下：

MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      151 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

我们根据上边B机的状态，来设置A机的主库信息(注意，设置A机的主库信息，意思是告诉A机，谁是它的主库，因为此时它本身是作为从库，在A机mysql执行以下命令)：

change master to master_host='10.37.129.6',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=151;

change master to命令表示把该库的主库修改为指定的数据库（通过master_host、master_port、master_user、master_password，master_log_file、master_log_pos共6个参数来指定主库的ip、端口、用户、密码、主库bin-log日志文件名、主库bin-log日志目前记录到哪个位置了）。

A机mysql启动slave：

start slave;

start slave表示启动从库(这里是A机)的两个线程:
1、I/O线程，该线程会连接远程的B机的mysql，并把它的bin-log日志拷贝到本机(A机)的中继日志中(relay-log)。
2、sql线程，该线程会读取本机的中继日志(relay-log)中的数据，并把这些数据恢复到本机(A机)的mysql对应的表中，这样，B机的数据就被“同步”过来了。

A机查看从库状态(即它自己作为从库的状态)：

show slave status\G;

显示有如下状态则正常：

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

配置B机的同步信息：
先查看A机的主库(在A机执行以下mysql命令)：

MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000014 |      151 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

告诉B机，它的主库是谁(在这里它的主库就是A机mysql)，以及它的主库的相关信息(在B机mysql执行以下命令)：

change master to master_host='10.37.129.5',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000014',master_log_pos=151;

B机mysql启动slave（B机mysql运行以下命令）：

start slave;

start slave表示启动从库(这里是B机)的两个线程:
1、I/O线程，该线程会连接远程的A机的mysql，并把它的bin-log日志拷贝到本机(即B机)的中继日志中(relay-log)。
2、sql线程，该线程会读取本机中继日志(relay-log)中的数据，并把这些数据恢复到本机(即B机)自己的mysql对应的表中，这样，A机的数据就被“同步”过来了。

B机查看从库状态(即它自己作为从库的状态)：

show slave status\G;

显示有如下状态则正常：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

修改其中一个mysql的数据库，看另一个会不会变，正常是会同步过来的！

开启GTID功能

什么是gtid功能(mysql5.6加入)？请查看：GTID Format and Storage

关于GTID Auto-Positioning：GTID Auto-Positioning

在两台机上，分别执行以下语句：

stop slave;
change master to MASTER_AUTO_POSITION=1;
start slave;

以下是MASTER_AUTO_POSITION = 1的介绍，你可以在CHANGE MASTER TO Syntax 链接中搜索“With MASTER_AUTO_POSITION = 1 set” 找到以下内容：

When MASTER_AUTO_POSITION = 1 is used with CHANGE MASTER TO, the slave attempts to connect to the master using the GTID-based replication protocol. This option can be used with CHANGE MASTER TO only if both the slave SQL and slave I/O threads are stopped. Both the slave and the master must have GTIDs enabled (GTID_MODE=ON, ON_PERMISSIVE, or OFF_PERMISSIVE on the slave, and GTID_MODE=ON on the master). Auto-positioning is used for the connection, so the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and the use of either or both of these options together with MASTER_AUTO_POSITION = 1 causes an error. If multi-source replication is enabled on the slave, you need to set the MASTER_AUTO_POSITION = 1 option for each applicable replication channel.

With MASTER_AUTO_POSITION = 1 set, in the initial connection handshake, the slave sends a GTID set containing the transactions that it has already received, committed, or both. The master responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the slave. This exchange ensures that the master only sends the transactions with a GTID that the slave has not already recorded or committed. If the slave receives transactions from more than one master, as in the case of a diamond topology, the auto-skip function ensures that the transactions are not applied twice. For details of how the GTID set sent by the slave is computed, see Section 17.1.3.3, “GTID Auto-Positioning”.

直接使用GTID同步

其实5.6以后的数据库，如果要使用gtid，就不需要像前面那样先指定master_log_file,master_log_pos，然后再开启gtid，而是可以直接开启GDid，然后执行以下语句指定master即可(这样根本就不需要查看另一台机的master status了)：
A机：

change master to master_host='10.37.129.6',master_port=3306,master_user='repl',master_password='123456',MASTER_AUTO_POSITION=1;
start slave;

B机：

change master to master_host='10.37.129.5',master_port=3306,master_user='repl',master_password='123456',MASTER_AUTO_POSITION=1;
start slave;

其中一方down机处理方法

如果其中一方down机，则机器正常后，你想直接用start slave是会报错的：

Slave failed to initialize relay log info structure from the repository

你需要先reset slave，再start slave就正常了，其中未down机的那台肯定会有新数据，它些新数据，会被同步到重新启动slave的那台机中，所以你无需手动导数据。

三主集群

其实做三主也是可以的，原理和双主一样，只不过因为有三台，所以主从关系需要构成循环，即：
1、A为B的主，B为C的主，C又为A的主；
2、配置与双主类似，先考虑A为B的主，那么A上自然要创建一个供B连接的同步用户(供B连接意味着ip写B的ip)，同理B上要创建一个供C连接的同步用户，而C上要创建供A连接的同步账号；
3、两个重要参数设置，自增id要错开，三台分别从1，2，3开始，每台的自增数都是3(因为总共有三台)：

# A机
auto_increment_offset = 1
auto_increment_increment = 3

# B机
auto_increment_offset = 2
auto_increment_increment = 3

# C机
auto_increment_offset = 3
auto_increment_increment = 3

4、5.6以上的版本，三台支分别做GTID同步即可。

其他的没有什么特别的，具体可查看：MySQL实现主主同步（三台服务器）

本文参考：https://www.cnblogs.com/ygqygq2/p/6045279.html