“This is the 10 days of my participation in the More Text Challenge. For more details, see more Text Challenge.”
MySQL master/slave replication
Obtaining MySQL Image
Search for mysql, the first two are mysql official images
[root@msr ~]# docker search mysql NAME DESCRIPTION STARS OFFICIAL AUTOMATED mysql MySQL is a widely used, The open - source base... 9686 [OK] Mariadb Mariadb is a community-developed fork of MyS... 3523 [OK] mysql/mysql-server Optimized mysql server Docker images. Create... 706 [OK]Copy the codePull mysql image to local, because mysql image has many versions, corresponding to mysql version: mysql:tag
[root@msr ~]# docker pull mysql:5.7
Copy the codeRun the MySQL image generation container
Run the mysql image to generate the container. The user name is root and the password is 123456
/ root @ MSR ~ # docker run - d - name mysql5.7 -p 3306:3306 - v/opt/workspace/mysql/data: / var/lib/mysql - v /opt/workspace/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 master [root@slave ~]# docker run -d --name Mysql5.7 - p - 3306-3306 - v/opt/workspace/mysql/data: / var/lib/mysql - v/opt/workspace/mysql/conf/my CNF: / etc/my CNF - e MYSQL_ROOT_PASSWORD=123456 slaveCopy the codeAs for the mysql configuration file my.cnf, you can prepare it yourself or obtain it by using the following command
[root@msr ~]# docker run -i --rm mysql5.7 cat /etc/my.cnf > my.cnf
Copy the codeMySQL master/slave configuration
Example Modify the mysql configuration file
Master:
[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
Open the log - bin #
log-bin = /var/lib/mysql/mysql-bin
# Set service ID, master and slave cannot be consistent
server‐id = 1
Set the database to be synchronized
# binlog ‐ do ‐ db = user_db
# Shield system library synchronization
binlog‐ignore‐ db = mysqlbinlog‐ignore‐ db = information_schemabinlog‐ignore‐ db = performance_schemaexpire-logs-days = 14
max-binlog-size = 500M
default-time-zone='+ 08:00'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
Copy the codeSlave:
[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
Open the log - bin #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
max-binlog-size = 500M
server-id = 2
The location of the # relay_log
relay_log=mysql-relay-bin
log-slave-updates = 1
Set the database to be synchronized
#replicate_wild_do_table=user_db.%
# Shield system library synchronization
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
# read-only
read-only = 1
default-time-zone='+ 08:00'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
Copy the codeRestart the master and slave
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
Copy the codeNext, create a data synchronization user in the Master database and grant user slave REPLICATION slave and REPLICATION CLIENT rights to synchronize data between the Master and slave databases. The first step is to get inside the Docker container, which can also be done through tools such as Navicat.
[root@msr ~]# docker exec -it master /bin/bash root@207f37f4c64a:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1633 Server version: 5.7.30-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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 USER 'slave'@'%' IDENTIFIED BY '123456'. Query OK, 0 rows affected (0.01 SEC) mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'SLAVE '@'%'; Query OK, 0 rows affected (0.00 SEC) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeRestarting the database
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
Copy the codeExecute in salve
Change master to master_host='192.168.74.130', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos= 154, master_connect_retry=30;Copy the codeMaster_host: The address of the Master, Refers to the container’s independent IP, can pass docker inspect – format = ‘{% raw %} {{. NetworkSettings. IPAddress}} {% endraw %}’ container name | container id ` IP query container, This command is used to view the Host of the container and can be used in the same server. Different servers need to use the real IP address of the service.
Master_port: Indicates the port number of the Master, which refers to the port number of the container
Master_user: user used for data synchronization
Master_password: indicates the password of the user used for synchronization
Master_log_file: Specifies the log File from which Slave begins to copy data. This is the value of the File field mentioned above
Master_log_pos: Which Position to start reading from, that is, the value of the Position field mentioned above
Master_connect_retry: Retry interval (in seconds) if the connection fails. The default value is 60 seconds
View the slave status
show slave status\\G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * Slave_IO_State: Master_Host: 192.168.74.130 Master_User: slave Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 154 Relay_Log_File: edu-mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: No Slave_SQL_Running: No 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: 154 Relay_Log_Space: 154 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: Error connecting to master '[email protected]:3306' -retry-time: 60 retries: 10 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 200628 09:03:36 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)Copy the codeNormally, Slave_IO_Running: No and Slave_SQL_Running: No. Because we haven’t started the master-slave replication process yet. Run the start slave command to start the master/slave replication process, and then run the show slave status \G command to query the master/slave synchronization status.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.74.130
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
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: 154
Relay_Log_Space: 531
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: 0994ae5c-b83a-11ea-9769-0242ac110003
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)
Copy the codeSlave_IO_State: Waiting for master to send event: indicates that the master data is Waiting. Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicates that the primary/secondary replication is enabled. In this case, you can test whether the data synchronization is successful.
Errors may occur during the configuration
Slave_IO_State: Connecting to master or Slave_IO_Running:Connectiog. The secondary repository is trying to connect to the primary repository.
- The Host address of the primary database is incorrect
- Is the port incorrect?
- The user and password used to connect to the primary repository are incorrect
- Whether master_log_file and master_log_pos are inconsistent with those of the master library