Centos6.5 Mysql5.7 多主一从 （多台主机上拉取的数据 合并到同一台数据库）

我也是多文档参考，摸着石头过河，花了不少时间，不过最终是配置成功（在配置的时候如果遇到错误，请自己找度娘解决）

以下是转过来的部分做了修改：尊重原创，原创无价

#### 解决思路：

1、主数据库使用Innodb引擎，并设置sql_mode为 NO_AUTO_CREATE_USER

2、从库开启多实例，将多个主库里面的数据通过主从复制同步到同一个数据目录。从库的每个实例对应一个主库。多个实例使用同一个数据目录。

3、从库使用Myisam引擎，关闭从库默认的innodb引擎,Myisam引擎可以访问同一个数据的表。

4、设置主库和从库的sql_mode都为NO_AUTO_CREATE_USER，只有这样主库的innodb引擎的sql同步到从库的时候才能执行成功。

#### 方案架构图：

                                             

#### 环境说明：

主库-1：192.168.1.1

主库-2：192.168.1.2

从库-3：192.168.1.3

---

#### 实现步骤：（Mysql安装步骤这里不在描述）

1、主数据库配置文件，多个主库配置文件除了server-id不能一样其他都一样。（基本配置信息配置好，mysql可以启动，其他配置需要每一项添加，每添加一次需要启动mysql，没有免费的午餐.......）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83

[root@masterdb01 ~] #cat /etc/my.cnf [client] port= 3306 socket=  /tmp/mysql .sock [mysqld] port = 3306 basedir =  /usr/local/mysql datadir =  /data/mysql character- set -server = utf8mb4 default-storage-engine  = InnoDB socket =  /tmp/mysql .sock skip-name-resolv = 1 open_files_limit = 65535  back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size   = 32M max-heap-table-size = 32M #query-cache-type  = 0 query-cache-size  = 0 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 51 query_cache_size = 32M tmp_table_size = 96M max_heap_table_size = 96M query_cache_type=1 log-error= /data/logs/mysqld .log slow_query_log = 1 slow_query_log_file =  /data/logs/slow .log long_query_time = 0.1 # BINARY LOGGING # server- id  = 1 log-bin          =  /data/binlog/mysql-bin log-bin-index    = /data/binlog/mysql-bin .index expire-logs-days = 14 sync_binlog = 1 binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 1024M log_slave_updates #binlog_format = row  binlog_format = MIXED    // 这里使用的混合模式复制 relay_log_recovery = 1 #不需要同步的表 replicate-wild-ignore-table=mydb.sp_counter #不需要同步的库 replicate-ignore-db = mysql,information_schema,performance_schema key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover transaction_isolation = REPEATABLE-READ innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 5734M innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1024M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_locks_unsafe_for_binlog = 0 [mysqldump] quick max_allowed_packet = 32M

2、从库配置文件。多个从库配置文件除了server-id不能一样其他都一样。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218

[root@slavedb01 ~]# cat /etc/my.cnf [client] port= 3306 socket= /tmp/mysql.sock [mysqld_multi] # 指定相关命令的路径 mysqld     =  /usr/local/mysql/bin/mysqld_safe mysqladmin =  /usr/local/mysql/bin/mysqladmin ##复制主库1的数据## [mysqld2] port = 3306 basedir = /usr/local/mysql datadir = /data/mysql character-set-server = utf8mb4 #指定实例1的sock文件和pid文件 socket = /tmp/mysql.sock pid-file=/data/mysql/mysql.pid skip-name-resolv = 1 open_files_limit = 65535  back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size   = 32M max-heap-table-size = 32M query-cache-size  = 0 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 51 query_cache_size = 32M tmp_table_size = 96M max_heap_table_size = 96M query_cache_type=1 #指定第一个实例的错误日志和慢查询日志路径 log-error=/data/logs/mysqld.log slow_query_log = 1 slow_query_log_file = /data/logs/slow.log long_query_time = 0.1 # BINARY LOGGING# # 指定实例1的binlog和relaylog路径为/data/binlog目录 # 每个从库和每个实例的server_id不能一样。 server-id = 2 log-bin          = /data/binlog/mysql-bin log-bin-index    =/data/binlog/mysql-bin.index relay_log = /data/binlog/mysql-relay-bin relay_log_index = /data/binlog/mysql-relay.index master-info-file = /data/mysql/master.info relay_log_info_file = /data/mysql/relay-log.info read_only = 1 expire-logs-days = 14 sync_binlog = 1 #需要同步的库，如果不设置，默认同步所有库。 #replicate- do -db = xxx #不需要同步的表 replicate-wild-ignore-table=mydb.sp_counter #不需要同步的库 replicate-ignore-db = mysql,information_schema,performance_schema binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 1024M log_slave_updates =1 #binlog_format = row  binlog_format = MIXED relay_log_recovery = 1 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover #设置默认引擎为Myisam，下面这些参数一定要加上。 default -storage-engine=MyISAM default -tmp-storage-engine=MYISAM #关闭innodb引擎 skip-innodb innodb = OFF disable-innodb #设置sql_mode模式为NO_AUTO_CREATE_USER sql_mode = NO_AUTO_CREATE_USER #关闭innodb引擎 loose-skip-innodb loose-innodb-trx=0  loose-innodb-locks=0  loose-innodb-lock-waits=0  loose-innodb-cmp=0  loose-innodb-cmp-per-index=0 loose-innodb-cmp-per-index-reset=0 loose-innodb-cmp-reset=0  loose-innodb-cmpmem=0  loose-innodb-cmpmem-reset=0  loose-innodb-buffer-page=0  loose-innodb-buffer-page-lru=0  loose-innodb-buffer-pool-stats=0  loose-innodb-metrics=0  loose-innodb-ft- default -stopword=0  loose-innodb-ft-inserted=0  loose-innodb-ft-deleted=0  loose-innodb-ft-being-deleted=0  loose-innodb-ft-config=0  loose-innodb-ft-index-cache=0  loose-innodb-ft-index-table=0  loose-innodb-sys-tables=0  loose-innodb-sys-tablestats=0  loose-innodb-sys-indexes=0  loose-innodb-sys-columns=0  loose-innodb-sys-fields=0  loose-innodb-sys-foreign=0  loose-innodb-sys-foreign-cols=0     ##复制主库2的数据## [mysqld3] port = 3307 basedir = /usr/local/mysql datadir = /data/mysql character-set-server = utf8mb4 #指定实例2的sock文件和pid文件 socket = /tmp/mysql3.sock pid-file=/data/mysql/mysql3.pid skip-name-resolv = 1 open_files_limit = 65535  back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size   = 32M max-heap-table-size = 32M query-cache-size  = 0 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 51 query_cache_size = 32M tmp_table_size = 96M max_heap_table_size = 96M query_cache_type=1 log-error=/data/logs/mysqld3.log slow_query_log = 1 slow_query_log_file = /data/logs/slow3.log long_query_time = 0.1 # BINARY LOGGING # # 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录， # 这里指定实例2的binlog日志为/data/binlog2目录 # 每个从库和每个实例的server_id不能一样。 server-id = 22 log-bin          = /data/binlog2/mysql-bin log-bin-index    =/data/binlog2/mysql-bin.index relay_log = /data/binlog2/mysql-relay-bin relay_log_index = /data/binlog2/mysql-relay.index master-info-file = /data/mysql/master3.info relay_log_info_file = /data/mysql/relay-log3.info read_only = 1 expire-logs-days = 14 sync_binlog = 1 #不需要复制的库 replicate-ignore-db = mysql,information_schema,performance_schema binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 1024M log_slave_updates =1 #binlog_format = row  binlog_format = MIXED relay_log_recovery = 1 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover #设置默认引擎为Myisam default -storage-engine=MyISAM default -tmp-storage-engine=MYISAM #关闭innodb引擎 skip-innodb innodb = OFF disable-innodb #设置sql_mode模式为NO_AUTO_CREATE_USER sql_mode = NO_AUTO_CREATE_USER #关闭innodb引擎，下面这些参数一定要加上。 loose-skip-innodb loose-innodb-trx=0  loose-innodb-locks=0  loose-innodb-lock-waits=0  loose-innodb-cmp=0  loose-innodb-cmp-per-index=0 loose-innodb-cmp-per-index-reset=0 loose-innodb-cmp-reset=0  loose-innodb-cmpmem=0  loose-innodb-cmpmem-reset=0  loose-innodb-buffer-page=0  loose-innodb-buffer-page-lru=0  loose-innodb-buffer-pool-stats=0  loose-innodb-metrics=0  loose-innodb-ft- default -stopword=0  loose-innodb-ft-inserted=0  loose-innodb-ft-deleted=0  loose-innodb-ft-being-deleted=0  loose-innodb-ft-config=0  loose-innodb-ft-index-cache=0  loose-innodb-ft-index-table=0  loose-innodb-sys-tables=0  loose-innodb-sys-tablestats=0  loose-innodb-sys-indexes=0  loose-innodb-sys-columns=0  loose-innodb-sys-fields=0  loose-innodb-sys-foreign=0  loose-innodb-sys-foreign-cols=0 [mysqldump] quick max_allowed_packet = 32M ```

以上的主机和从机配置好后，要添加以下配置项（多文件参考，没办法，都是摸着石头过河....）

数据库的应用场景颇多，如 数据库双机同步，一主多从，多主多从，多主一从等；

下文记录多主一从的配置及测试。

大多数复制场景中是一主或者一主多从。

这种拓扑用于高可用性场景，读写分离。主机负责写入数据，丛集负责读数据，横向扩展读取程序。但是，多主一从是写入多个数据库实例，最后合并成一个结果。

Z 1 [Note] A temporary password is generated for root@localhost: T<e-hd0cgI!d　　

多主一从使得从机从各主机同步接收业务信息（transactions），这样可以一部服务器为多个主机服务器备份，合并数据表，联合数据。（无去重）

 

　　                                                      　　

 

MySQL 版本：5.7.10

配置机器：两主一从

1，从机配置，保证从机的仓库都存在一个表；

1 2 3

[mysqld] master-info-repository= TABLE relay-log-info-repository= TABLE

确保无误后，可以检查一下，如下显示则配置正常：

1 2 3 4 5 6 7 8

mysql> SHOW VARIABLES LIKE '%repository%' ; + ---------------------------+-------+ | Variable_name | Value | + ---------------------------+-------+ | master_info_repository | TABLE | | relay_log_info_repository | TABLE | + ---------------------------+-------+ 2 rows in set (0.00 sec)

接下来修改 server_id ，这里用了ip的后三位：

1 2	[mysqld] server-id=141

输入下面代码可以捡测：

1 2 3 4 5 6 7

mysql> SHOW VARIABLES WHERE VARIABLE_NAME = 'server_id' ; + ---------------+-------+ | Variable_name | Value | + ---------------+-------+ | server_id | 141 | + ---------------+-------+ 1 row in set (0.00 sec)

---

 多主一从最关键的工作就是保证在你的两（或多）个源数据里面不要有相同的主键，特别是当你在用AUTO_INCREMENT 这一列时，如果有两个一样的主键可以想象同步到从机时数据就会紊乱。这里有一个可替代的方法作为参考。

在配置时最好关掉GTID，不然在mysql initialize 初始化时会遇到一个问题，并且这些记录会被复制到从机上去。假设你最开始启动了GTID，配置完成后，然后你试图在从机上复制。当你检查主机的状态时（输入 SHOW MASTER STATUS）， 你会看到这样的结果，主机上138个执行记录，现在会被复制到从机上：

1 2 3 4 5 6 7 8

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 1286 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 73fdfd2a-9e36-11e5-8592-00a64151d129:1-138 1 row in set (0.00 sec)

而在从机上 SHOW SLAVE STATUS 你会看到一些错误：

1	Last_Error: Error 'Can' t create database 'mysql' ; database exists ' on query. Default database: ' mysql '. Query: ' CREATE DATABASE mysql;

以及 RETRIEVED GTID SET  会显示 已取回138个记录复制到了从机：

1 2 3 4

mysql> SHOW SLAVE STATUS\G ... Retrieved_Gtid_Set: 73fdfd2a-9e36-11e5-8592-00a64151d129:1-138 ..

当然，如果配置完成前关掉了GTID那就不会有这些错误了。

 

三台机器配置完成之后，输入 SHOW MASTER STATUS 显示如下：

1 2 3 4 5 6 7 8

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000002 Position: 398 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

 

到此，整个架构已经创建，下面进行一些测试。如图，创建一个漫画收藏数据库：

从机

1 2 3 4 5 6 7 8 9 10

CREATE DATABASE `comicbookdb`; use comicbookdb; CREATE TABLE `comics` ( `comic_id` int (9) NOT NULL AUTO_INCREMENT, `comic_title` varchar (60) NOT NULL , `issue_number` decimal (9,0) NOT NULL , `pub_year` varchar (60) NOT NULL , `pub_month` varchar (60) NOT NULL , PRIMARY KEY (`comic_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1;

在主机和从机上都可以运行同样的SQL 语句。（因为在主机的一些列上我们用了AUTO_INCREMENT ，你可能觉得从机上就不用AUTO_INCREMENT 了。但是因为我们不会对从机做任何写入操作，所以你仍旧可以运行同样的语句，之后再去修改主键。后文会有详述。）

 

当数据从多个主机复制合并到一个从机时，复制程序将会处理 AUTO_INCREMENT 的问题。

下面在从机上创建表时对于comic_id 列时，没有声明 AUTO_INCREMENT出现了错误，

1 2 3

mysql> SHOW SLAVE STATUS\G... Last_SQL_Error: Error 'Field ' comic_id ' doesn' t have a default value ' on query. Default database: ' comicbookdb '. Query: ' INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '5' , '2014' , '03' )' ...

为了处理comic_id的主键问题，最方便的方法就是在主机中配置 auto_increment_increment ，在my.cnf  或者是my.ini 中：

1 2	[mysqld] auto_increment_increment = 2

加这个变量需要重启mysql服务，但是你也可以直接在命令行操作，如下：

1 2	mysql> SET @@auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec)

验证一下：

1 2 3 4 5 6 7

mysql> SHOW VARIABLES WHERE VARIABLES_NAME = 'auto_increment_increment' ; + -----------------------------+-------+ | Variable_name | Value | + -----------------------------+-------+ | auto_increment_increment | 2 | + -----------------------------+-------+ 1 row in set (0.00 sec)

如上 每个主键的增量就是2，只要每个主机的初始值不同就可以了。但不可以简单的设 0 或 1，因为如果是0的话，它会默认恢复到1，这样会引起冲突。所以我们设一个较大的值，最低位设置为 0 和 1，代码如下：

主机 # 1

1 2 3 4 5 6 7 8 9 10

CREATE DATABASE `comicbookdb`; use comicbookdb; CREATE TABLE `comics` ( `comic_id` int (9) NOT NULL AUTO_INCREMENT, `comic_title` varchar (60) NOT NULL , `issue_number` decimal (9,0) NOT NULL , `pub_year` varchar (60) NOT NULL , `pub_month` varchar (60) NOT NULL , PRIMARY KEY (`comic_id`) ) ENGINE=InnoDB AUTO_INCREMENT=100000;

 

主机 # 2

1 2 3 4 5 6 7 8 9 10

CREATE DATABASE `comicbookdb`; use comicbookdb; CREATE TABLE `comics` ( `comic_id` int (9) NOT NULL AUTO_INCREMENT, `comic_title` varchar (60) NOT NULL , `issue_number` decimal (9,0) NOT NULL , `pub_year` varchar (60) NOT NULL , `pub_month` varchar (60) NOT NULL , PRIMARY KEY (`comic_id`) ) ENGINE=InnoDB AUTO_INCREMENT=100001;

（这里的初始值在建表的时候设置，同样也可以在配置里面通过 auto_increment_offset 进行设置。）

建表完毕，在所有主机上启动 GTID。这里从机也启动了GTID， 以防之后在这部从机之下加另一台从机。启动GTID，需要修改配置：

1 2 3 4

[mysqld] auto_increment_increment = 2 gtid-mode = on enforce-gtid-consistency = 1

重启每个server之后，可以检查一下每一台主机状态，状态一致：

1 2 3 4 5 6 7 8

mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000005 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

接下来对所有的主从都做了一个服务器重置（reset master），非必要。重置会清空所有的binnary log ，并且新建一个二值日志。

1 2 3 4 5 6 7 8 9 10 11

mysql> RESET MASTER; Query OK, 0 rows affected (0.00 sec)   mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

可以见到新的 binary log（mysql-bin.000001），开始位置是154.  我们往主机插入一些数据，然后再看看主机状态。

 

主机 #1

1 2 3 4 5 6 7 8 9 10 11

mysql> INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '1' , '2014' , '01' ); Query OK, 1 row affected (0.02 sec)   mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 574 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1 1 row in set (0.00 sec)

可见 插入的 GTID 是 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1 ，冒号前面部分是主机的UUID，这个信息可以在data目录下的auto.cnf里面查到。

主机 #1

1 2 3

# cat auto.cnf [auto] server-uuid=63a7971c-b48c-11e5-87cf-f7b6a723ba3d

再插入一行数据：

主机 #1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '2' , '2014' , '02' ); Query OK, 1 row affected (0.05 sec)   mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 994 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2 1 row in set (0.00 sec)   mysql> select * from comics; + ----------+-------------+--------------+----------+-----------+ | comic_id | comic_title | issue_number | pub_year | pub_month | + ----------+-------------+--------------+----------+-----------+ | 100001 | Fly Man | 1 | 2014 | 01 | | 100003 | Fly Man | 2 | 2014 | 02 | + ----------+-------------+--------------+----------+-----------+ 2 rows in set (0.00 sec)

会发现这个数值变成了2，那么我们再在第二号主机插入两行数据，并且查看状态：

主机 #2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '3' , '2014' , '03' ); mysql> INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '4' , '2014' , '04' );   mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000005 Position: 974 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2 1 row in set (0.00 sec)   mysql> select * from comics; + ----------+-------------+--------------+----------+-----------+ | comic_id | comic_title | issue_number | pub_year | pub_month | + ----------+-------------+--------------+----------+-----------+ | 100002 | Fly Man | 3 | 2014 | 03 | | 100004 | Fly Man | 4 | 2014 | 04 | + ----------+-------------+--------------+----------+-----------+ 2 rows in set (0.00 sec)

主机#2有不同的UUID, 这也是我们分辨GTID对应于哪一步主机。那我们现在已经有两组GTID的会复制到从机上。当然，从机也有自己的UUID.

主机#1 跟主机#2的GTID设置：

1 2	63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2 75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2

在此，通常我会确认下从机是没有运行的：

从机

1 2	mysql> show slave status\G Empty set (0.00 sec)

不同于平时的复制，在多主复制中，你需要为每一台主机创建一个通道，为这个通道命名。

这里称之为 “master-142”（主机-142）和 “master-143”（主机143）去匹配server_id（就像IP一样）。接下来就演示如何开启主机#1(server_id=142)的数据复制。（关键是在这里需要为从机建立两个通道）

从机

FOR CHANNEL 'master-142';（要特别注意这个，是在同一个数据库里建立两个通道）

1 2	mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.142' , MASTER_USER= 'replicate' , MASTER_PASSWORD= 'password' , MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-142' ; Query OK, 0 rows affected, 2 warnings (0.23 sec)

1 2	mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.143' , MASTER_USER= 'replicate' , MASTER_PASSWORD= 'password' , MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-143' ; Query OK, 0 rows affected, 2 warnings (0.23 sec)

以下是启动 停止 显示 Slave  配置以上通道时一定要关闭个个通道的Slave

SHOW SLAVE STATUS FOR CHANNEL 'master-142'\G

 
STOP  SLAVE FOR CHANNEL 'master-142'
 
START SLAVE FOR CHANNEL 'master-142';



SHOW SLAVE STATUS FOR CHANNEL 'master-143'\G
 
STOP  SLAVE FOR CHANNEL 'master-143'
 
START SLAVE FOR CHANNEL 'master-143';

这里有两个警告，可以忽略。

1 2 3 4 5 6 7 8 9 10

mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level : Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. *************************** 2. row *************************** Level : Note Code: 1760 Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2 rows in set (0.00 sec)

现在我们可以开启从机通道“master-142”：

1 2	mysql> START SLAVE FOR CHANNEL 'master-142' ; Query OK, 0 rows affected (0.03 sec)

这个命令同时启动了SQL_THREAD 跟 IO_THREAD。将来你会考虑停止一个或者多个线程，所以这里对应有一些语法，如何指定一个需要修改的通道：

1 2	START SLAVE SQL_THREAD FOR CHANNEL 'master-142' ; START SLAVE IO_THREAD FOR CHANNEL 'master-142' ;

也可以发一个简单的命令“START SLAVE” 为需要执行复制操作的通道来开启这两个线程。从机启动，可以见到GTID被取出并且开始写入数据库。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-142' \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.142 ... Master_UUID: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d ... Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates ... Retrieved_Gtid_Set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2 Executed_Gtid_Set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2 Auto_Position: 1 ... Channel_Name: master-142

检查从机，可以见到相关数据：　　

1 2 3 4 5 6 7 8

mysql> select * from comics; + ----------+-------------+--------------+----------+-----------+ | comic_id | comic_title | issue_number | pub_year | pub_month | + ----------+-------------+--------------+----------+-----------+ | 100001 | Fly Man | 1 | 2014 | 01 | | 100003 | Fly Man | 2 | 2014 | 02 | + ----------+-------------+--------------+----------+-----------+ 2 rows in set (0.00 sec)

主机#1 完成之后，我们开始配置主机#2：

1	CHANGE MASTER TO MASTER_HOST= '192.168.1.143' , MASTER_USER= 'replicate' , MASTER_PASSWORD= 'password' , MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-143' ;

然后再次确认从机状态：

从机

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-143' \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.143 ... Master_UUID: 75e2e1dc-b48e-11e5-83bb-1438deb0d51e ... Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates ... Retrieved_Gtid_Set: 75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2 Executed_Gtid_Set: 63a7971c-b48c-11e5-87cf-f7b6a723ba3d:1-2, 75e2e1dc-b48e-11e5-83bb-1438deb0d51e:1-2, Auto_Position: 1 ... Channel_Name: master-143

我们可以见到从机已经获取到两个GTID，并且已经在执行。再看看数据库表的内容，也能够发现四行数据都已经合并到了从机：

1 2 3 4 5 6 7 8 9 10

mysql> select * from comics; + ----------+-------------+--------------+----------+-----------+ | comic_id | comic_title | issue_number | pub_year | pub_month | + ----------+-------------+--------------+----------+-----------+ | 100001 | Fly Man | 1 | 2014 | 01 | | 100002 | Fly Man | 3 | 2014 | 03 | | 100003 | Fly Man | 2 | 2014 | 02 | | 100004 | Fly Man | 4 | 2014 | 04 | + ----------+-------------+--------------+----------+-----------+ 4 rows in set (0.01 sec)

复制过程处理了自增 AUTO_INCREMENT 的值。如果检查一下sql语句执行复制的原话，你会有所发现：

1 2 3 4

INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '1' , '2014' , '01' ) INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '2' , '2014' , '02' ); INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '3' , '2014' , '03' ); INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '4' , '2014' , '04' );

在主机上生成的自增值会随着声明传递给从机。检查主机上的binary log， 来到comic_id 这一列 “SET INSERT_ID = 100001”，整段会随着sql语句一起传给从机；

从机

1 2 3 4 5 6 7 8 9 10 11

# mysqlbinlog mysql-bin.000001 ... # at 349 #160106 21:08:01 server id 142 end_log_pos 349 CRC32 0x48fb16a2 Intvar SET INSERT_ID=100001 /*!*/ ; #160106 21:08:01 server id 142 end_log_pos 543 CRC32 0xbaf55210 Query thread_id=1exec_time=0 error_code=0 use `comicbookdb` /*!*/ ; SET TIMESTAMP =1452132481 /*!*/ ; INSERT INTO COMICS (comic_title, issue_number, pub_year, pub_month) VALUES ( 'Fly Man' , '1' , '2014' , '01' ) /*!*/ ; ...

3、设置主库sql_mode,Mysql5.6默认需要在启动文件文件里面设置sql_mode才可以生效。

1 2 3 4 5 6 7

# cat /etc/init.d/mysqld #other_args="$*"   # uncommon, but needed when called from an RPM upgrade action             # Expected: "--skip-networking --skip-grant-tables"             # They are not checked here, intentionally, as it is the resposibility             # of the "spec" file author to give correct arguments only. #将上面默认的#other_args开启后改为 other_args= "--sql-mode=NO_AUTO_CREATE_USER"

5、在两台主库上面分别授权复制账号

1 2 3 4 5

#需要授权三个从库的ip可以同步 mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.3'  IDENTIFIED BY  'rep123' ; mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.4'  IDENTIFIED BY  'rep123' ; mysql> GRANT REPLICATION SLAVE ON *.* TO rep@ '192.168.1.5'  IDENTIFIED BY  'rep123' ; mysql> flush privileges;

6、在三个从库分别开启同步。

1 2 3 4 5 6 7

#进入第一个实例执行 $ mysql -S   /tmp/mysql .sock mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.1' ,MASTER_USER= 'rep' ,MASTER_PASSWORD= 'rep123' ,MASTER_LOG_FILE= 'mysql-bin.000001' ,MASTER_LOG_POS=112;   #进入第二个实例执行 $ mysql -S   /tmp/mysql3 .sock mysql> CHANGE MASTER TO MASTER_HOST= '192.168.1.2' ,MASTER_USER= 'rep' ,MASTER_PASSWORD= 'rep123' ,MASTER_LOG_FILE= 'mysql-bin.000001' ,MASTER_LOG_POS=112;

7、测试数据同步

    在二个主数据库分别建表和插入数据，到从库查看可以看到二个主库同步到同一个从库上面的所有数据。

---

Mysql5.6多主一从的坑

1、Mysql5.6默认的引擎是innodb默认同步的时候一定要把主和从的sql_mode模式里面的NO_ENGINE_SUBSTITUTION这个参数关闭。如果不关闭innodb同步到从库上面的sql将会找不到innodb引擎导致同步失败。

2、在mysql5.6开启多实例的时候第一次启动的时候在你数据库的安装目录里面（/usr/local/mysql/)会生成my.cnf配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。