mysql容器之间的replication配置实例详解
背景
上周公司培训了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配置内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票!