1. 主从模式：
   mysql主从架构中其实就一个主在工作，而从就相当于一个备份机器，从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。

2. 主主模式：
   主主复制，就是在mysql主从架构上让mysql主实现监测从的日志变化，从而实现两台机器相互同步。

3. 多主多从模式：
   MMM 即Master-Master Replication Manager for MySQL（mysql 主主复制管理器）
   Percona XtraDB Cluster

Docker 部署 HAProxy Mysql集群方案图

第一步：准备配置文件和存储文件夹

vito@caas:/opt2/mysql$ ls -al
总用量 24
drwxr-xr-x 2 root root 4096 6月  25 10:08 haproxy
drwxr-xr-x 2 root root 4096 6月  25 10:02 logs
drwxrwxrwx 6 root root 4096 6月  25 11:02 mysql1
drwxrwxrwx 6 root root 4096 6月  25 11:02 mysql2vito@caas:/opt2/mysql$ cd mysql1
vito@caas:/opt2/mysql/mysql1$ ls -al
总用量 24
drwxr-xr-x 2 root         root   4096 6月  25 10:52 backup
drwxr-xr-x 2 root         root   4096 6月  25 10:52 conf.d
drwxr-xr-x 6 guest-gkd7rd docker 4096 6月  25 15:52 data
drwxrwxrwx 2 root         root   4096 6月  25 14:26 log

分别对文件夹说明：

• backup：存放初始化脚本
• conf.d ： 存放mysql配置文件
• data ： 存放mysql数据文件
• log ： 存放日志文件
  以上文件设置权限 999:999，创建两个配置文件：
  /opt2/mysql/mysql1/conf.d/server1.cnf

[mysqld]server-id = 101log_bin = /var/log/mysql/mysql-bin.logbinlog_do_db = mydatabind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1character_set_server = utf8collation_server = utf8_general_ci[mysql]default_character_set = utf8

/opt2/mysql/mysql2/conf.d/server2.cnf

[mysqld]
server-id = 102 # Remember this is only Integer per official documentation
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydata
bind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1
character_set_server = utf8
collation_server = utf8_general_ci
[mysql]
default_character_set = utf8

创建用户replicator，密码repl1234or，授权该用户访问任何IP,grantt可以授权某个用户访问指定的ip地址，我们这里的设置是该用户访问任何ip。下面采用脚本的方式实现
/opt2/mysql/mysql1/backup/initdb.sql

use mysql;
create user 'replicator'@'%' identified by 'repl1234or';
grant replication slave on *.* to 'replicator'@'%';
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'server_id';

创建用户replicator，密码repl1234or，授权该用户访问任何IP,下面采用脚本的方式实现
/opt2/mysql/mysql2/backup/initdb.sql

use mysql;
create user 'replicator'@'%' identified by 'repl1234or';
grant replication slave on *.* to 'replicator'@'%';
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'server_id';

登录mysql，查看bin-log日志是否开启，确认是开启状态，如下：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

第二步：使用配置文件启动mysql

启动mysql1
注意其中的挂载目录，挂载了我们自己定义的配置文件

docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=mysqlpass -e MYSQL_DATABASE=mydata -dit -p 33061:3306 -v /opt2/mysql/mysql1/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/mysql1/data:/var/lib/mysql -v /opt2/mysql/mysql1/log:/var/log/mysql -v /opt2/mysql/mysql1/backup:/backup -h  mysql1 mysql

启动mysql2
注意其中的挂载目录，挂载了我们自己定义的配置文件

docker run --name mysql2 --link mysql1 -e MYSQL_ROOT_PASSWORD=mysqlpass -e MYSQL_DATABASE=mydata -dit -p 33062:3306 -v /opt2/mysql/mysql2/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/mysql2/data:/var/lib/mysql -v /opt2/mysql/mysql2/log:/var/log/mysql -v /opt2/mysql/mysql2/backup:/backup -h  mysql2 mysql

第三步：连接两个docker容器

因为容器重启后ip地址会改变，我们采用修改hosts文件的方式来链接两个容器：

# 提取容器mysql2的ip地址mysql2ip=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' mysql2)# 添加mysql2的ip到mysql1中的hosts文件中docker exec -i mysql1 sh -c "echo '$mysql2ip  mysql2  mysql2' >> /etc/hosts"# 确认上面的命令是否成功执行
docker exec -i mysql1 sh -c "cat /etc/hosts"# 确认容器是否链接成功
docker exec -ti mysql2 sh -c "ping mysql1"
docker exec -ti mysql1 sh -c "ping mysql2"

能ping通，说明两个容器链接成功

第四步：初始化mysql脚本

执行mysql1的初始化脚本，在bakup文件夹下：

vito@caas:/opt2/mysql/mysql1$ docker exec -ti mysql1 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> source /backup/initdb.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | mydata       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
1 row in set (0.00 sec)

上面的 File | Position两个字段后面有用，在下一步中MASTER_LOG_FILE，MASTER_LOG_POS是这里查询出来的结果.。
同样在容器mysql2中执行初始化脚本，步骤同上：

vito@caas:~$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> source /backup/initdb.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | mydata       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
1 row in set (0.00 sec)

第五步：执行同步命令

在容器mysql1中，登录mysql，执行如下语句

stop slave;
CHANGE MASTER TO MASTER_HOST = 'mysql2', MASTER_USER = 'replicator',MASTER_PASSWORD = 'repl1234or', MASTER_LOG_FILE = 'mysql-bin.000003',MASTER_LOG_POS = 154;
start slave;

在容器mysql2中，登录mysql，执行如下语句:

stop slave;
CHANGE MASTER TO MASTER_HOST = 'mysql1', MASTER_USER = 'replicator',MASTER_PASSWORD = 'repl1234or', MASTER_LOG_FILE = 'mysql-bin.000003',MASTER_LOG_POS = 154;
start slave;

查看slave状态：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

Slave_IO_Running: Yes， Slave_SQL_Running: Yes 两个yes表示设置成功。

第六步：准备日志收集容器

docker pull voxxit/rsyslog
docker run --name haproxy-logger -dit -h haproxy-logger -v /opt2/mysql/logs:/var/log/ voxxit/rsyslog

第七步：准备HAProxy 容器

准备配置文件，配置文件目录：
/opt2/mysql/haproxy/haproxy.cfg

globallog haproxy-logger local0 notice# user haproxy# group haproxy
defaultslog globalretries 2timeout connect 3000timeout server 5000timeout client 5000
listen mysql-clusterbind 0.0.0.0:3306mode tcp#option mysql-check user haproxy_check  (This is not needed as for Layer 4 balancing)option tcp-checkbalance roundrobin# The below nodes would be hit on 1:1 ratio. If you want it to be 1:2 then add 'weight 2' just after the line.server mysql1 mysql1:3306 checkserver mysql2 mysql2:3306 check
# Enable cluster status
listen mysql-clusterstatsbind 0.0.0.0:8080mode httpstats enablestats uri /stats realm Strictly\ Privatestats auth status:keypas5

启动HAProxy容器：

docker run --name mysql-cluster -dit \-h mysql-cluster \--link mysql1:mysql1cl  --link mysql2:mysql2cl \--link haproxy-logger:haproxy-loggercl \-v /opt2/mysql/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro \-p 33060:3306 -p 38080:8080 \haproxy:latest

第八步：确认容器是否工作正常

caas:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                              NAMES
c56801d6401a        haproxy:latest      "/docker-entrypoin..."   2 hours ago         Up 2 hours          0.0.0.0:33060->3306/tcp, 0.0.0.0:38080->8080/tcp   mysql-cluster
4831e91d8eb3        voxxit/rsyslog      "rsyslogd -n"            2 hours ago         Up 2 hours          514/tcp, 514/udp                                   haproxy-logger
00d99d4acba1        mysql               "docker-entrypoint..."   3 hours ago         Up 2 hours          0.0.0.0:33062->3306/tcp                            mysql2
10c54e299b01        mysql               "docker-entrypoint..."   3 hours ago         Up 3 hours          0.0.0.0:33061->3306/tcp                            mysql1

如果四个容器工作正常，说明服务启动正常。
登录mysql1，创建表格，命令如下：

vito@caas:~$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE IF NOT EXISTS `mydata`.`user` (->  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',->  `user_name` VARCHAR(45) NOT NULL COMMENT '',->  `user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '',->  `user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '',->  PRIMARY KEY (`user_id`))->  ENGINE = InnoDB->  AUTO_INCREMENT = 1->  DEFAULT CHARACTER SET = utf8->  COLLATE = utf8_general_ci->  COMMENT = '';
Query OK, 0 rows affected (0.03 sec)mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| hello            |
| mytest           |
| user             |
+------------------+
3 rows in set (0.00 sec)

创建表格的mysql命令如下：

CREATE TABLE IF NOT EXISTS `mydata`.`user` (`user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',`user_name` VARCHAR(45) NOT NULL COMMENT '用户名称',`user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',`user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',PRIMARY KEY (`user_id`))ENGINE = InnoDBAUTO_INCREMENT = 1DEFAULT CHARACTER SET = utf8COLLATE = utf8_general_ciCOMMENT = '用户表';

用golang写的一个程序，链接一下数据库，测试一下是否正常，golang源代码如下：

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql"
)func main() {insert()
}//插入demo
func insert() {//db, err := sql.Open("mysql", "root:@/test?charset=utf8")db, err := sql.Open("mysql", "root:mysql1pass@tcp(127.0.0.1:33060)/mydata?charset=utf8")checkErr(err)stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)checkErr(err)res, err := stmt.Exec("liu", 30, 2)checkErr(err)id, err := res.LastInsertId()checkErr(err)fmt.Println(id)
}

执行该程序，分别登录mysql1和mysql2数据库查看结果：
发现两个数据库可以同步

mysql> select * from user;
+---------+-----------+----------+----------+
| user_id | user_name | user_age | user_sex |
+---------+-----------+----------+----------+
|       1 | tony      |       20 |        1 |
|       2 | liu       |       30 |        2 |
|       3 | liu       |       30 |        2 |
+---------+-----------+----------+----------+

经过测试，停掉一个mysql2，仍然执行插入操作，能够正常插入，不会报错，再重新启动mysql2以后，继续插入，两个数据库数据完美同步。

参考：https://vnextcoder.wordpress.com/2016/09/22/haproxy-mysql-cluster-on-docker/
参考：https://vnextcoder.wordpress.com/2016/09/19/mysql-master-master-replication-setup-on-docker/
参考：https://blog.csdn.net/chengxuyuanyonghu/article/details/71136172