主从模式:
mysql主从架构中其实就一个主在工作,而从就相当于一个备份机器,从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。主主模式:
主主复制,就是在mysql主从架构上让mysql主实现监测从的日志变化,从而实现两台机器相互同步。多主多从模式:
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 mysql2
vito@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 = 101
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
/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 -A
Database 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 -A
Database 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
global
log haproxy-logger local0 notice
# user haproxy
# group haproxy
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
listen mysql-cluster
bind 0.0.0.0:3306
mode tcp
#option mysql-check user haproxy_check (This is not needed as for Layer 4 balancing)
option tcp-check
balance 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 check
server mysql2 mysql2:3306 check
# Enable cluster status
listen mysql-clusterstats
bind 0.0.0.0:8080
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats 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 = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
COMMENT = '用户表';
用golang写的一个程序,链接一下数据库,测试一下是否正常,golang源代码如下:
package main
import (
"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