mysql容器之间的replication配置实例详解

2023-07-07 00:04:03 90

背景

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

Masterserver：MySQLcontainermysql_masteronNAS

NASserverIP:192.168.1.108
mysql_masterinnerip:172.17.0.6

Slaveserver:MySQKcontainermysql_slaveonMacmini

MacminidockerhostIP:192.168.1.139
mysql_slaveinnerip:172.17.0.2

准备MySQLcontainer

准备mysql_master

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

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

创建用于测试的mastermysqlnode

[root@TNAS-2664disk4]#dockerrun-d-p3307:3306-eMYSQL_ROOT_PASSWORD=123456--namemysql_master-v/mnt/md1/disk4/mysql:/var/lib/mysql-v/mnt/md1/disk4/mysql-files:/var/lib/mysql-filesmysql
3bebf0e21df6d9034ce8275b14ebb1616e11f5e2678b1e084d03c087ed91a72a

查看当前NAS上运行的mysql的containerID

[root@TNAS-2664~]#dockerps
CONTAINERIDIMAGECOMMANDCREATEDSTATUSPORTSNAMES
40db0be51460mysql"docker-entrypoint..."44secondsagoUp29seconds33060/tcp,0.0.0.0:3307->3306/tcpmysql_master
db5f6a287a21mautic/mautic"/entrypoint.shap..."2weeksagoUp11days0.0.0.0:8082->80/tcpmautic
dc1eac509c70qianliu/mediawikiwithcomposer"docker-php-entryp..."2weeksagoUp11days0.0.0.0:8086->80/tcpsarawiki
b5c0a00f5f42mysql"docker-entrypoint..."2weeksagoUp11days0.0.0.0:3306->3306/tcp,33060/tcpmysql2
911c0a8987baqianliu/mediawikiwithcomposer"docker-php-entryp..."2weeksagoUp11days0.0.0.0:8083->80/tcpqianliuwiki

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

dockercp40db0be51460:/etc/mysql/my.cnf.

在my.cnf加入以下配置

server-id=1
gtid-mode=ON#(replicatedbyGTID)
enforce_gtid_consistency=1#(replicatedbyGTID)
log-bin=master-log
binlog_format=mixed
expire-logs-days=14
sync-binlog=1
log-bin-trust-function-creators=1

#MASTERDB#
binlog-ignore-db=mysql,information_schema,performance_schema,sys
auto-increment-increment=2
auto-increment-offset=1

#SLAVEDB#
replicate-ignore-db=mysql,information_schema,performance_schema,sys
relay_log=relay-log
log-slave-updates=ON

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

dockercpmy.cnf40db0be51460:/etc/mysql/

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

[root@TNAS-2664~]#dockerexec-itmysql_master/bin/bash
root@40db0be51460:/#mysql-uroot-p123456
mysql:[Warning]World-writableconfigfile'/etc/mysql/my.cnf'isignored.
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.

更改my.cnf的权限为664

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

重启mysql_slave以使my.cnf生效

[root@TNAS-2664~]#dockerrestartmysql_master

9.进入mysql_master查看masterstatus

mysql>showmasterstatus;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
|master-log.000001|156||mysql,information_schema,performance_schema,sys||
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1rowinset(0.00sec)

mysql>exit

准备mysql_slave容器

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

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

创建用于测试的mysql_slavecontainer

/Volumes/MacintoshHDD_Data/mysql_slave_dbdockerrun-d-p3307:3306-eMYSQL_ROOT_PASSWORD=123456--namemysql_slave-v/Volumes/MacintoshHDD_Data/mysql_slave_db/mysql:/var/lib/mysql-v/Volumes/MacintoshHDD_Data/mysql_slave_db/mysql-files:/var/lib/mysql-filesmysql

查看当前macmini上运行的mysql_slave的containerID

/Volumes/MacintoshHDD_Data/mysql_slave_dbdockerps
CONTAINERIDIMAGECOMMANDCREATEDSTATUSPORTSNAMES
8623ac99e5d4mysql"docker-entrypoint.s…"5secondsagoUp4seconds33060/tcp,0.0.0.0:3307->3306/tcpmysql_slave

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

dockercp8623ac99e5d4:/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

#MASTERDB#
binlog-ignore-db=mysql,information_schema,performance_schema,sys
auto-increment-increment=2
auto-increment-offset=2

#SLAVEDB#
replicate-ignore-db=mysql,information_schema,performance_schema,sys
relay_log=relay-log
log-slave-updates=ON

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

dockercpmy.cnf8623ac99e5d4:/etc/mysql/

重启mysql_slave以使my.cnf生效

dockerrestartmysql_slave

进入mysql_slave查看masterstatus

mysql>showmasterstatus;
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
|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|
+------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1rowinset(0.00sec)

准备用于复制的mysql用户

准备mysql_master中用来复制的mysqluser

mysql>CREATEUSER'slave'@'192.168.1.139'IDENTIFIEDBY'slave';
QueryOK,0rowsaffected(0.59sec)

mysql>CREATEUSER'slave'@'172.17.0.2'IDENTIFIEDBY'slave';
QueryOK,0rowsaffected(0.60sec)

mysql>GRANTREPLICATIONSLAVEON*.*TO'slave'@'192.168.1.139';
QueryOK,0rowsaffected(0.19sec)

mysql>GRANTREPLICATIONSLAVEON*.*TO'slave'@'172.17.0.2';
QueryOK,0rowsaffected(0.19sec)j

mysql>flushprivileges;
QueryOK,0rowsaffected(0.10sec)

mysql>exit

准备mysql_slave中用来复制的mysqluser

mysql>CREATEUSER'slave'@'192.168.1.108'IDENTIFIEDBY'slave';
QueryOK,0rowsaffected(0.02sec)

mysql>CREATEUSER'slave'@'172.17.0.6'IDENTIFIEDBY'slave';
QueryOK,0rowsaffected(0.01sec)

mysql>GRANTREPLICATIONSLAVEON*.*TO'slave'@'192.168.1.108';
QueryOK,0rowsaffected(0.01sec)

mysql>GRANTREPLICATIONSLAVEON*.*TO'slave'@'172.17.0.6';
QueryOK,0rowsaffected(0.01sec)

mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)

mysql>

replication配置

配置mysql_master

mysql>changemastertomaster_host='192.168.1.139',master_user='slave',master_password='slave',master_log_file='slave-log.000001',master_port=3307,master_log_pos=1460;
QueryOK,0rowsaffected,2warnings(1.17sec)

mysql>changemastertomaster_host='192.168.1.139',master_user='slave',master_password='slave',master_auto_position=1,get_master_public_key=1;
QueryOK,0rowsaffected,2warnings(0.45sec)

配置mysql_slave

mysql>changemastertomaster_host='192.168.1.108',master_user='slave',master_password='slave',master_log_file='master-log.000001',master_port=3307,master_log_pos=156;
QueryOK,0rowsaffected,2warnings(0.15sec)

mysql>changemastertomaster_host='192.168.1.108',master_user='slave',master_password='slave',master_auto_position=1,get_master_public_key=1;
QueryOK,0rowsaffected,2warnings(0.14sec)

开启复制

mysql_master上startslave
mysql>startslave;
QueryOK,0rowsaffected,1warning(0.00sec)

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

mysql>showslavestatus\G;
***************************1.row***************************
Slave_IO_State:Connectingtomaster
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:errorconnectingtomaster'slave@192.168.1.139:3307'-retry-time:60retries:2message:Accessdeniedforuser'slave'@'172.17.0.1'(usingpassword: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:Slavehasreadallrelaylog;waitingformoreupdates
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:21011013: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:
1rowinset(0.01sec)

ERROR:
Noqueryspecified

mysql>exit

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

mysql>showslavestatus;
mysql>showslavestatus\G;
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
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:Slavehasreadallrelaylog;waitingformoreupdates
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:
1rowinset,1warning(0.01sec)

ERROR:
Noqueryspecified

mysql_slave上创建用户

mysql>CREATEUSER'slave'@'172.17.0.1'IDENTIFIEDBY'slave';
QueryOK,0rowsaffected(0.01sec)

mysql>GRANTREPLICATIONSLAVEON*.*TO'slave'@'172.17.0.1';
QueryOK,0rowsaffected(0.00sec)

mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)

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

mysql>showslavestatus\G;
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
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:Slavehasreadallrelaylog;waitingformoreupdates
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:
1rowinset(0.01sec)

ERROR:
Noqueryspecified

测试复制
mysql_slave上创建test_db_slave

mysql>CREATEDATABASEtest_db_slave;
QueryOK,1rowaffected(0.01sec)

mysql_master上创建test_db_master

mysql>CREATEDATABASEtest_db_master;
QueryOK,1rowaffected(0.24sec)

查看mysql_slave上的databases

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|sys|
|test_db_master|
|test_db_slave|
+--------------------+
6rowsinset(0.00sec)

mysql>

查看mysql_master上的databases

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|sys|
|test_db_master|
|test_db_slave|
+--------------------+
6rowsinset(0.00sec)

mysql>

至此，mysqlreplication配置完毕。

到此这篇关于mysql容器之间的replication配置的文章就介绍到这了,更多相关mysql容器的replication配置内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

mysql容器之间的replication配置实例详解

热门推荐

随机推荐