1, Design
First, docker is installed in the default reader server, as shown below
Next, build a mysql master-slave architecture
Start two virtual machines with ip 192.168.96.126 and 192.168.96.127126 as the master and 127 as the slave.
2, Pull image
docker pull mysql:5.7
If docker pull mysql is used, the latest version of mysql is pulled by default (mysql version 8.0.15 when the article is published), then the following configuration method is invalid
3, Create the required directories for mysql on both servers
The purpose of creating a file directory is to facilitate the mapping between the mysql container of docker and the file directory of the host
1.126 on the master server
[root@SJS SJS]# mkdir /usr/local/mysqlData/master/cnf -p [root@SJS SJS]# mkdir /usr/local/mysqlData/master/data -p
2.127 from the server
[root@SJS SJS]# mkdir /usr/local/mysqlData/slave/cnf -p
4, Define profiles for both servers
1.126 on the master server
[root@SJS SJS]# vim /usr/local/mysqlData/master/cnf/mysql.cnf //The contents are as follows [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql symbolic-links=0 character-set-server = utf8 #skip-networking innodb_print_all_deadlocks = 1 max_connections = 2000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 28M key_buffer_size = 4M thread_cache_size = 8 query_cache_type = 1 query_cache_size = 8M query_cache_limit = 2M ft_min_word_len = 4 log-bin = mysql-bin server-id = 1 binlog_format = mixed performance_schema = 0 explicit_defaults_for_timestamp #lower_case_table_names = 1 interactive_timeout = 28800 wait_timeout = 28800 #Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
2.127 from the server
[root@SJS SJS]# vim /usr/local/mysqlData/slave/cnf/mysql.cnf //The content is the same as on the primary server
5, Create service on master server
//View mysql image id [root@SJS mysqlData]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql 5.7 697daaecf703 7 months ago 448MB //Note to modify the image id [root@SJS SJS]# docker run -itd -p 3306:3306 --name mysqlmaster -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=masterpwd 697daaecf703 //At this time, the primary server mysql has been started [root@SJS mysqlData]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 7d5769ab3d37 697daaecf703 "docker-entrypoint.s..." About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp mysqlmaster
Explain docker run -itd -p 3306:3306 --name mysqlmaster -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=masterpwd 697daaecf703 command
- -itd: background operation
- -p: Docker container port and host host port are mapped. Host port: docker container port
- -v: Mapping between docker container file directory and host file directory. Host Directory: docker container directory
- -e: username = "xxx", set environment variable
6, Add the user reader in the main server mysql
Create a reader account and modify the permissions so that any ip can access it for use from the server
[root@SJS mysqlData]# docker exec -it 7d5769ab3d37 /bin/bash root@7d5769ab3d37:/# mysql -u root -pmasterpwd //Grant reader user rights mysql> GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'readerpwd'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye //Exit terminal interaction root@7d5769ab3d37:/# exit exit
7, Create a service on the slave server and configure master-slave replication
1. Create service
[root@SJS mysqlData]# docker run -itd -p 3306:3306 --name mysqlslave -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=slavepwd 697daaecf703 //mysql started from server [root@SJS mysqlData]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 9696cc5c2787 697daaecf703 "docker-entrypoint.s..." 5 seconds ago Up 4 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp
2. Configure master-slave replication
View information about the primary server
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000009 | 591 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
The above table shows that the log file currently written by the master server is mysql-bin.000009(File field) and the current location is 591(Position field). These two fields are used to configure master-slave replication below
Configure replication from server
[root@SJS mysqlData]# docker exec -it 9696cc5c2787 /bin/bash
root@9696cc5c2787:/# mysql -u root -pslavepwd
//Specify host
mysql> change master to master_host='192.168.184.126',master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000009',master_log_pos=591;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
//start slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.184.126
Master_User: reader
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 591
Relay_Log_File: 9696cc5c2787-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000009
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: 591
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: 1
Master_UUID: 41c37b86-6378-11eb-9bf5-0242ac110002
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)
Explain the change command. The function of this command is to connect to the master server.
change master to master_host='192.168.96.126',master_user='reader',master_password='readerpwd',master_port=3306,master_log_file='mysql-bin.000009',master_log_pos=591;
change... to: make a copy link
master_host: primary server ip
master_port: the open port number of the primary server
master_user: add a user reader in the main server mysql
master_password: the password of the user
master_log_file: specifies which log file Slave starts copying data from, that is, the value of the file field mentioned above
master_log_pos: which Position to start reading, that is, the value of the Position field mentioned above
Talk about the configuration information of the slave server
mysql> show slave status\G
*************************** 1. row ***************************
//The following indicates that the IO thread and SQL thread on the slave side are running, indicating that the master-slave replication has been successful
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8, Testing
1. Connect to the master server
//Create database test mysql> create database test; Query OK, 1 row affected (0.00 sec) +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
2. Test slave server
Create database test mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
At this time, the operations performed by the master server will be synchronized to the slave server, while the operations of the slave service will not be synchronized to the master server
9, Problems encountered during construction
1.Slave failed to initialize relay log info structure from the repositorymysql> reset slave;
Reason: unable to initialize relay log
Solution: execute reset slave; Command to delete the master-slave replication link
The reset slave does the following things:
1. Delete slave_master_info ，slave_relay_log_info data in two tables;
2. Delete all relay log files and re create new relay log files;
3. The gtid will not be changed_ Executed or gtid_ Value of purged
mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository mysql> reset slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_host='192.168.96.126',master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000009',master_log_pos=591; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
2.[ERROR] Slave I/O: error connecting to master
Reason: change master to master_ The ip address of host = '192.168.96.126' is wrong. You must pay attention to this.
3. There are garbled codes and document numbers from the database
Use the following command to view the character set
show variables like 'char%';
View main library character set
Check the character set from the service library and find that the Latin format is used. Just change it to utf8
Change character to utf8
Find the location of the MySQL host configuration file from the server. The directory of this example is / usr/local/mysqlData/master/cnf/mysql.cnf
[root@SJS cnf]# vim mysql.cnf //Add client node [client] default-character-set=utf8 //Restart the container [root@SJS cnf]# docker restart 9696cc5c2787
It is normal to view the data from the server
