docker安装MySQL主从复制
docker 使用2台服务器搭建 mysql 主从复制 准备2台 centos7服务器,它们的 ip分别是 192.168.124.122,192.168.124.129 122服务器用来做主服务器,129服务器用来做从服务器 mysql 的版本使用 5.7
1. 主服务器配置
主 mysql容器别名为 mysql5.7-master
cd /opt
mkdir mysql
chmod 777 mysql
docker search mysql
docker pull mysql:5.7
docker run --privileged= true \
--name= mysql5.7-master --restart= always \
-it -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD= 123456 \
-v /opt/mysql:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-d mysql:5.7 --lower_case_table_names= 1
2. 从服务器配置
从 mysql容器别名为 mysql5.7-slaver 创建文件夹
cd /opt
mkdir mysql
chmod 777 mysql
docker search mysql
docker pull mysql:5.7
docker run --privileged= true \
--name= mysql5.7-slaver --restart= always \
-it -p 3306:3306 \
-e MYSQL_ROOT_PASSWORD= 123456 \
-v /opt/mysql:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-d mysql:5.7 --lower_case_table_names= 1
3. 主服务器修改配置文件
将主 mysql容器的 mysql.cnf 文件复制到 /opt/mysql/conf.d目录下
docker cp mysql5.7-master:/etc/mysql/mysql.cnf /opt/mysql/conf.d
vim mysql.cnf
[ mysqld]
log-bin= mysql-bin
server-id= 101
将修改后的 mysql.cnf 文件从 /opt/mysql目录下复制回主 mysql容器中
docker cp /opt/mysql/mysql.cnf mysql5.7-master:/etc/mysql/conf.d/
cd /opt/mysql/
rm -rf mysql.cnf
docker restart mysql5.7-master
4. 主mysql容器创建账号
重启主mysql后,进入容器内创建用于主从复制的账号
docker exec -it mysql5.7-master /bin/bash
mysql -u root -p123456
create user 'slavecopyuser' @'%' identified with mysql_native_password by 'copy123456' ;
grant replication client,replication slave on *.* to 'slavecopyuser' @'%' ;
flush privileges;
用户创建完成后,查看主服务器 master_log_file,master_log_pos 两个参数
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 785 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set ( 0.00 sec)
mysql>
这里记下 mysql-bin.000001 和 785
5. 修改从mysql 容器配置文件
步骤和主mysql容器一样,先将配置文件从容器里复制出来,修改后再复制回去
docker cp mysql5.7-slaver:/etc/mysql/conf.d/mysql.cnf /opt/mysql/conf.d
vim mysql.cnf
[ mysqld]
log-bin= mysql-bin
server-id= 102
docker cp /opt/mysql/conf.d/mysql.cnf mysql5.7-slaver:/etc/mysql/conf.d/
docker restart mysql5.7-slaver
6. 在从mysql容器中开启主从复制
docker exec -it mysql5.7-slaver bash
mysql -u root -p123456
配置连接主服务器的信息 这里需要在主mysql容器中记下的 mysql-bin.000001 和 785 master_host 为主mysql容器的 ip 地址 master_user 为用于复制的账号 master_password 为用于复制的账号的密码
change master to master_host= '192.168.124.122' ,master_user= 'slavecopyuser' ,master_password= 'copy123456' ,master_log_file= 'mysql-bin.000001' ,master_log_pos= 785;
start slave;
show slave status\G
Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 说明没问题 如果是 No 或者 connect 说明没接上
mysql> show slave status \G
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1. row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168 .124 .122
Master_User: slavecopyuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql- bin. 000001
Read_Master_Log_Pos: 785
Relay_Log_File: 1 a1fb7345722- relay- bin. 000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql- bin. 000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 785
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 654729 b1- 48 e1- 11 ec- 8 dd9- 0242 ac110007
Master_Info_File: / var/ lib/ mysql/ master. info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set ( 0.00 sec)
mysql>
7. 测试主从复制
docker exec - it mysql5. 7 - master / bin/ bash
mysql - uroot - plzns123456
create database auditdb default character set utf8 collate utf8_general_ci;
CREATE USER 'lzns' @'% ' IDENTIFIED BY ' lzns123456';
grant select , insert , update , create on auditdb. * to lzns;
flush privileges ;
select User , host from mysql. user ;
show databases ;
8. 宿主机上传xytdev.sql文件到容器
docker cp / opt/ mysql/ xytdev. sql mysql5. 7 - master:/ var/ lib/ mysql
9. 导入sql脚本
docker exec - it mysql5. 7 - master / bin/ bash
mysql - uroot - plzns123456
use xytdev;
source / var/ lib/ mysql/ xytdev. sql ;
登录从mysql,发现在主mysql创建的数据已经同步到从mysql,说明主从复制搭建成功。 到这一步,表示mysql主从搭建成功。