幽灵资源网

mysql容器之间的replication配置实例详解
数据库 2024/12/23 佚名
3 2
幽灵资源网 Design By www.bzswh.com

背景

上周公司培训了MySQL replication, 这个周末打算用所学来实践操作一下。

Master server：MySQL container mysql_master on NAS

  • NAS server IP: 192.168.1.108
  • mysql_master inner ip: 172.17.0.6

Slave server: MySQK container mysql_slave on Mac mini

  • Mac mini docker host IP: 192.168.1.139
  • mysql_slave inner ip: 172.17.0.2

准备MySQL container

准备mysql_master

创建两个目录用来存放MySQL文件

mkdir -p /mnt/md1/disk4/mysql
mkdir -p /mnt/md1/disk4/mysql-files

创建用于测试的master mysql node

[root@TNAS-2664 disk4]# docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql_master -v /mnt/md1/disk4/mysql:/var/lib/mysql -v /mnt/md1/disk4/mysql-files:/var/lib/mysql-files mysql
3bebf0e21df6d9034ce8275b14ebb1616e11f5e2678b1e084d03c087ed91a72a

查看当前NAS上运行的mysql的container ID

[root@TNAS-2664 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
40db0be51460 mysql "docker-entrypoint..." 44 seconds ago Up 29 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp mysql_master
db5f6a287a21 mautic/mautic "/entrypoint.sh ap..." 2 weeks ago Up 11 days 0.0.0.0:8082->80/tcp mautic
dc1eac509c70 qianliu/mediawikiwithcomposer "docker-php-entryp..." 2 weeks ago Up 11 days 0.0.0.0:8086->80/tcp sarawiki
b5c0a00f5f42 mysql "docker-entrypoint..." 2 weeks ago Up 11 days 0.0.0.0:3306->3306/tcp, 33060/tcp mysql2
911c0a8987ba qianliu/mediawikiwithcomposer "docker-php-entryp..." 2 weeks ago Up 11 days 0.0.0.0:8083->80/tcp qianliuwiki

使用docker cp命令将my.cnf复制到host机器上进行更改

docker cp 40db0be51460:/etc/mysql/my.cnf .

在my.cnf 加入以下配置

server-id = 1
 gtid-mode = ON # (replicated by GTID)
 enforce_gtid_consistency =1 #(replicated by GTID) 
 log-bin = master-log
 binlog_format = mixed
 expire-logs-days = 14
 sync-binlog = 1
 log-bin-trust-function-creators= 1
 
 # MASTER DB # 
 binlog-ignore-db = mysql,information_schema,performance_schema,sys
 auto-increment-increment = 2
 auto-increment-offset = 1
 
 # SLAVE DB #
 replicate-ignore-db = mysql,information_schema,performance_schema,sys
 relay_log = relay-log
 log-slave-updates = ON

将my.cnf 用docker cp 复制到mysql_master 容器中

docker cp my.cnf 40db0be51460:/etc/mysql/

进入mysql_slave，发现my.cnf因为权限文件 is ignored,这将导致刚刚写入到my.cnf的配置无法生效

[root@TNAS-2664 ~]# docker exec -it mysql_master /bin/bash
root@40db0be51460:/# mysql -uroot -p123456
mysql: [Warning] World-writable config file '/etc/mysql/my.cnf' is ignored.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.

更改my.cnf的权限为664

root@40db0be51460:/# chmod 644 /etc/mysql/my.cnf
root@40db0be51460:/# exit

重启mysql_slave以使my.cnf生效

[root@TNAS-2664 ~]# docker restart mysql_master

9.进入mysql_master查看master status

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB  | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-log.000001 | 156 | | mysql,information_schema,performance_schema,sys |  |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit

准备mysql_slave 容器

在Mac mini上创建两个目录用来存放MySQL文件

mkdir -p /Volumes/MacintoshHDD_Data/mysql_slave_db/mysql
mkdir -p /Volumes/MacintoshHDD_Data/mysql_slave_db/mysql-files

创建用于测试的mysql_slave container

/Volumes/MacintoshHDD_Data/mysql_slave_db  docker run -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql_slave -v /Volumes/MacintoshHDD_Data/mysql_slave_db/mysql:/var/lib/mysql -v /Volumes/MacintoshHDD_Data/mysql_slave_db/mysql-files:/var/lib/mysql-files mysql

查看当前macmini上运行的mysql_slave的container ID

/Volumes/MacintoshHDD_Data/mysql_slave_db  docker ps
CONTAINER ID IMAGE  COMMAND   CREATED  STATUS  PORTS    NAMES
8623ac99e5d4 mysql  "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp mysql_slave

使用docker cp命令将my.cnf复制到host机器上进行更改

docker cp 8623ac99e5d4:/etc/mysql/my.cnf .

在my.cnf 加入以下配置

server-id   = 2
 gtid-mode   = ON
 enforce_gtid_consistency =1
 log-bin   = slave-log
 binlog_format   = mixed
 expire-logs-days  = 14
 sync-binlog   = 1
 log-bin-trust-function-creators= 1
 
 # MASTER DB # 
 binlog-ignore-db  = mysql,information_schema,performance_schema,sys
 auto-increment-increment = 2
 auto-increment-offset  = 2
 
 # SLAVE DB #
 replicate-ignore-db  = mysql,information_schema,performance_schema,sys
 relay_log   = relay-log
 log-slave-updates  = ON

将my.cnf 用docker cp 复制到mysql_master 容器中

docker cp my.cnf 8623ac99e5d4:/etc/mysql/

重启mysql_slave以使my.cnf生效

docker restart mysql_slave

进入mysql_slave查看master status

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB    | Executed_Gtid_Set   |
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| slave-log.000001 | 1460 |  | mysql,information_schema,performance_schema,sys | f102ae13-5341-11eb-a578-0242ac110002:1-5 |
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

准备用于复制的mysql用户

准备mysql_master中用来复制的mysql user

mysql> CREATE USER 'slave'@'192.168.1.139' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.59 sec)

mysql> CREATE USER 'slave'@'172.17.0.2' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.60 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.139';
Query OK, 0 rows affected (0.19 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.17.0.2';
Query OK, 0 rows affected (0.19 sec)j

mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

mysql> exit

准备mysql_slave中用来复制的mysql user

mysql> CREATE USER 'slave'@'192.168.1.108' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'slave'@'172.17.0.6' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.108';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.17.0.6';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

replication配置

配置mysql_master

mysql> change master to master_host='192.168.1.139',master_user='slave',master_password='slave',master_log_file='slave-log.000001',master_port=3307, master_log_pos=1460;
Query OK, 0 rows affected, 2 warnings (1.17 sec)

mysql> change master to master_host='192.168.1.139',master_user='slave',master_password='slave',master_auto_position=1,get_master_public_key=1;
Query OK, 0 rows affected, 2 warnings (0.45 sec)

配置mysql_slave

mysql> change master to master_host='192.168.1.108',master_user='slave',master_password='slave',master_log_file='master-log.000001',master_port=3307, master_log_pos=156;
Query OK, 0 rows affected, 2 warnings (0.15 sec)

mysql> change master to master_host='192.168.1.108',master_user='slave',master_password='slave',master_auto_position=1,get_master_public_key=1;
Query OK, 0 rows affected, 2 warnings (0.14 sec)

开启复制

mysql_master上start slave
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看slave status,发现并没有成功启动复制。出现错误 Access denied for user 'slave'@'172.17.0.1' (using password: YES) 需要到mysql_slave上创建响应的用户和权限，

mysql> show slave status \G;
*************************** 1. row ***************************
  Slave_IO_State: Connecting to master
   Master_Host: 192.168.1.139
   Master_User: slave
   Master_Port: 3307
  Connect_Retry: 60
  Master_Log_File:
  Read_Master_Log_Pos: 4
  Relay_Log_File: relay-log.000001
  Relay_Log_Pos: 4
 Relay_Master_Log_File:
  Slave_IO_Running: Connecting
  Slave_SQL_Running: Yes
  Replicate_Do_DB:
  Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
  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: 0
  Relay_Log_Space: 156
  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: 1045
  Last_IO_Error: error connecting to master 'slave@192.168.1.139:3307' - retry-time: 60 retries: 2 message: Access denied for user 'slave'@'172.17.0.1' (using password: YES)
  Last_SQL_Errno: 0
  Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
  Master_Server_Id: 0
   Master_UUID:
  Master_Info_File: mysql.slave_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: 210110 13:02:12
 Last_SQL_Error_Timestamp:
  Master_SSL_Crl:
  Master_SSL_Crlpath:
  Retrieved_Gtid_Set:
  Executed_Gtid_Set:
  Auto_Position: 1
  Replicate_Rewrite_DB:
   Channel_Name:
  Master_TLS_Version:
 Master_public_key_path:
 Get_master_public_key: 1
  Network_Namespace:
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> exit

mysql_slave 启动slave并查看slave status发现一切正常

mysql> show slave status;
mysql> show slave status\G;
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.1.108
   Master_User: slave
   Master_Port: 3307
  Connect_Retry: 60
  Master_Log_File: master-log.000001
  Read_Master_Log_Pos: 156
  Relay_Log_File: relay-log.000002
  Relay_Log_Pos: 373
 Relay_Master_Log_File: master-log.000001
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB:
  Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
  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: 156
  Relay_Log_Space: 576
  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: 1
   Master_UUID: 9627309d-5341-11eb-aaa3-0242ac110006
  Master_Info_File: mysql.slave_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: f102ae13-5341-11eb-a578-0242ac110002:1-5
  Auto_Position: 1
  Replicate_Rewrite_DB:
   Channel_Name:
  Master_TLS_Version:
 Master_public_key_path:
 Get_master_public_key: 1
  Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

mysql_slave 上创建用户

mysql> CREATE USER 'slave'@'172.17.0.1' IDENTIFIED BY 'slave';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.17.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再次查看mysql_master上的slave status，一切正常

mysql> show slave status \G;
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.1.139
   Master_User: slave
   Master_Port: 3307
  Connect_Retry: 60
  Master_Log_File: slave-log.000001
  Read_Master_Log_Pos: 2022
  Relay_Log_File: relay-log.000002
  Relay_Log_Pos: 2237
 Relay_Master_Log_File: slave-log.000001
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB:
  Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
  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: 2022
  Relay_Log_Space: 2440
  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: 2
   Master_UUID: f102ae13-5341-11eb-a578-0242ac110002
  Master_Info_File: mysql.slave_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: f102ae13-5341-11eb-a578-0242ac110002:1-7
  Executed_Gtid_Set: f102ae13-5341-11eb-a578-0242ac110002:1-7
  Auto_Position: 1
  Replicate_Rewrite_DB:
   Channel_Name:
  Master_TLS_Version:
 Master_public_key_path:
 Get_master_public_key: 1
  Network_Namespace:
1 row in set (0.01 sec)

ERROR:
No query specified

测试复制
mysql_slave上创建test_db_slave

mysql> CREATE DATABASE test_db_slave;
Query OK, 1 row affected (0.01 sec)

mysql_master上创建test_db_master

mysql> CREATE DATABASE test_db_master;
Query OK, 1 row affected (0.24 sec)

查看mysql_slave上的databases

mysql> show databases;
+--------------------+
| Database  |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
| sys  |
| test_db_master |
| test_db_slave |
+--------------------+
6 rows in set (0.00 sec)

mysql>

查看mysql_master上的databases

mysql> show databases;
+--------------------+
| Database  |
+--------------------+
| information_schema |
| mysql  |
| performance_schema |
| sys  |
| test_db_master |
| test_db_slave |
+--------------------+
6 rows in set (0.00 sec)

mysql>

至此，mysql replication配置完毕。

标签：
mysql容器,replication配置,mysql容器
幽灵资源网 Design By www.bzswh.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
幽灵资源网 Design By www.bzswh.com
www.bzswh.com 幽灵资源网
143,552互联网资源
29,117高清电影
46,608技术教程
224,608站长资源

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
幽灵资源网 Design By www.bzswh.com