Master-slave advantages

• Avoid database single point of failure: The primary server replicates data to the secondary server in real time and asynchronously. When the primary database is down, you can select a secondary server to upgrade it to the primary server to prevent database single point of failure.
• Improve query efficiency: according to the characteristics of database access, the master database can be used to insert, delete and update data and other write operations, while the slave database is specially used for data query operations, so that the query operations are shared to different slave servers to improve the database access efficiency.

Docker installation

Application container engine Docker(1) : Docker installation – The Gold (juejin. Cn)

The early stage of the plan

address	master-slave	The name of the	version	server_id
127.0.0.1:3311	The main	master	Mysql: 8.0.28	1
127.0.0.1:3312	from	slave1	Mysql: 8.0.28	2
127.0.0.1:3313	from	slave2	Mysql: 8.0.28	3

Points to be noted in the master/slave configuration

(1) The operating system versions and bits of the primary and secondary servers are consistent;

(2) The versions of the Master and Slave databases must be the same;

(3) Data in Master and Slave databases should be consistent;

(4) If binary logs are enabled for Master, the server_id of Master and Slave must be unique on the LAN.

Create a container data volume

Create a data volume to which the Docker container is mounted

master

• mkdir -p /mydata/mysql-cluster/master/conf
• mkdir -p /mydata/mysql-cluster/master/data
• mkdir -p /mydata/mysql-cluster/master/logs

slave1

• mkdir -p /mydata/mysql-cluster/slave1/conf
• mkdir -p /mydata/mysql-cluster/slave1/data
• mkdir -p /mydata/mysql-cluster/slave1/logs

slave2

• mkdir -p /mydata/mysql-cluster/slave2/conf
• mkdir -p /mydata/mysql-cluster/slave2/data
• mkdir -p /mydata/mysql-cluster/slave2/logs

Note: Create the data storage directory: data, and configure the data storage directory: conf. Log storage directory: logs

What is a container data volume?

• Persistence of containers
• Inheritance + sharing data between containers

A volume is a directory or File that exists in one or more containers and is mounted to the container by a Docker, but is not a federated File System and thus provides some features for storing or sharing data continuously, bypassing the Union File System.

Volumes are designed to persist data and are completely independent of the lifetime of the container, so Docker does not delete its mounted volumes when the container is deleted.

Features:

• 1: Data volumes can share or reuse data between containers
• 2: Changes in the volume take effect immediately
• 3: Changes in the data volume are not included in the update of the mirror
• 4: The life cycle of the data volume persists by default, even if the container is deleted.

Configure the my.cnf file

master

Create a configuration file: vi/mydata/mysql cluster/master/conf/my CNF

Copy the following:

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] # Collation -server= UTf8MB4_unicode_ci # Secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # Default is 1 server_id=1 # enable binary log-bin=mysql-bin # Read -only=0 # Maximum number of connections # max_connections=500 # Set the default time zone Default-time_zone ='+8:00' # 0: case sensitive # 1: case insensitive lower_case_table_names=1 ## The default format is statement) binlog_format=mixed # Database to be synchronized binlog-do-db=demo_ds_0 binlog-do-db=demo_ds_1 # Ignore the database to be synchronized binlog-ignore-db=mysql binlog-ignore-db=sys binlog-ignore-db=information_schema binlog-ignore-db=performance_schemaCopy the code

slave1

Create a configuration file: vi/mydata/mysql cluster/slave1 / conf/my CNF

Copy the following:

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] # Collation -server= UTf8MB4_unicode_ci # Secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # Server_id =2 # enable binary log-bin=mysql-bin # Show variables like "%relay%" relay-log = relay-log # Secondary library read-only Read -only=1 # max_connections=500 # Set default time zone Default-time_zone ='+8:00' # 0: case sensitive # 1: case insensitive lower_case_table_names=1 ## The default format is statement) binlog_format=mixed # Database to be synchronized binlog-do-db=demo_ds_0 binlog-do-db=demo_ds_1 # Ignore the database to be synchronized replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schemaCopy the code

slave2

Create a configuration file: vi/mydata/mysql cluster/slave2 / conf/my CNF

Copy the following:

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] # Collation -server= UTf8MB4_unicode_ci # Secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # secure_file_priv = # Server_id =3 # enable binary log-bin=mysql-bin # Show variables like "%relay%" relay-log = relay-log # Secondary library read-only Read -only=1 # max_connections=500 # Set default time zone Default-time_zone ='+8:00' # 0: case sensitive # 1: case insensitive lower_case_table_names=1 ## The default format is statement) binlog_format=mixed # Synchronize database binlog-do-db=demo_ds_0 binlog-do-db=demo_ds_1 # Ignore synchronize database replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schemaCopy the code

Argument parsing

• Parameters written to the master library configuration file (valid for all slave libraries)
  • Binlog_do_db = List of library names // Libraries that allow synchronization
  • Binlog_ignore_db = List of library names // Libraries that are not allowed to synchronize
• Parameters written to the slave configuration file (local to slave library only)
  • Replicate_do_db = list of database names // Specifies the database to be synchronized only
  • Replicate_ignore_db = List of database names // Specifies the unsynchronized databases
• Three control the character set of the mysql client
  • character_set_client    
  • character_set_connection
  • character_set_results
• Sets the default character set for the database
  • character_set_database    
• Set the default character set for all of the above
  • character_set_server
• Utf8mb4 is the character set supported after MySQL5.5.3. If you want to use this character set, you must use MySQL >= 5.5.3
• Mysql character set: show variables like ‘%char%’
• init_connect
  • 1. The super user must be used to configure init_connect. Common users do not have corresponding permissions.
  • 2. The SQL executed implicitly in init_connect is only for ordinary users, but not for super users. Therefore, it is necessary to manage MySQL account permissions properly during the normal use of MySQL.
• Summary of the SKip_name_resolve parameter
• Starting with mysql 8.0, mysql defaultCHARSETIs no longerLatin1Student: Yes, change toutf8mb4(Refer to the link), and the default COLLATE is changed toutf8mb4_0900_ai_ci.utf8mb4_0900_ai_ciBasicallyunicodeFurther subdivision of,0900The unicode Collation Algorithm version,aiMeans accent insensitive, such as E, E, E, e and E are insensitive.Related references 1.Related references 2

Create mysql container

Mysql mirror warehouse address | Docker Hub

master

docker run -p 3311:3306 --name my-mysql-master \
-v /mydata/mysql-cluster/master/conf:/etc/mysql \
-v /mydata/mysql-cluster/master/logs:/var/log/ mydata/mysql/mysql \ - v - cluster/master/data: / var/lib/mysql \ - e MYSQL_ROOT_PASSWORD = root \ - d mysql: 8.0.28
Copy the code

Docker logs -f -t –tail 10 my-mysql-master

slave1

docker run -p 3312:3306 --name my-mysql-slave1 \
-v /mydata/mysql-cluster/slave1/conf:/etc/mysql \
-v /mydata/mysql-cluster/slave1/logs:/var/log/ mydata/mysql/mysql \ - v - cluster/slave1 / data: / var/lib/mysql \ - e MYSQL_ROOT_PASSWORD = root \ - d mysql: 8.0.28
Copy the code

slave2

docker run -p 3313:3306 --name my-mysql-slave2 \
-v /mydata/mysql-cluster/slave2/conf:/etc/mysql \
-v /mydata/mysql-cluster/slave2/logs:/var/log/ mydata/mysql/mysql \ - v - cluster/slave2 / data: / var/lib/mysql \ - e MYSQL_ROOT_PASSWORD = root \ - d mysql: 8.0.28
Copy the code

Argument parsing

• -p 3311:3306 :3306 of the container maps 3311 of the external server
• –name my-mysql-master: The container name is my-mysql-master
• – restart=always: When Docker restarts, the container automatically starts.
• – Privileged =true: Indicates that the root in the container has the real root permission. Otherwise, the root in the container only has the external common user permission
• – v/mydata/mysql cluster/slave2 / logs: / var/log/mysql: mapping the log file
• – v/mydata/mysql cluster/slave2 / data: / var/lib/mysql: mapping data directory
• – v/mydata/mysql cluster/slave2 / conf: / etc/mysql: mapping configuration file
• -e MYSQL_ROOT_PASSWORD=root: specifies the password used to map the mysql root user
• -d mysql:8.0.28 Starts the specified version from the backend

Remote access configuration

Master and Slave2 perform the same operations as slave2

1. Into the container

docker exec -it my-mysql-master /bin/bash

2. Mysql login

mysql -u root -p

3. Create a user

create user 'ljw'@'%' identified by 'root';

4. Give the remote user all privileges on all tables

grant all privileges on \*.\* to 'ljw'@'%' with grant option;

With Grant option indicates that it has grant permission.

5. Check whether you have the remote login permission

select user,host,Grant_priv from user;

6. Changing encryption Rules (Optional)

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

7. Refresh the permissions

flush privileges;

Set the synchronous

Create test statements

Create database demo_ds_0 on master, Slave1, slave2, or copy a full backup of the data from the master database to the slave database

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

Copy a full backup of the master database to the slave database (keep the data consistent before synchronization)

1. Export primary library data

[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]# docker exec -it my-mysql-master /bin/bash
root@3abc17925d1a:/# mysqldump -uroot -proot demo_ds_0 > /home/demo_ds_0.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@3abc17925d1a:/# ls /home/
demo_ds_0.sql
root@3abc17925d1a:/# exit
exit
[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]#  docker cp my-mysql-master:/home/demo_ds_0.sql /mydata/mysql-cluster/master
[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]# 
/mydata/mysql-cluster/master
Copy the code

2. Import data to slave libraries slave1 and Slave2

[root@iZbp1bunl8t8qf63wqsy0iZ master]# docker cp /mydata/mysql-cluster/master/demo_ds_0.sql my-mysql-slave1:/home [root@iZbp1bunl8t8qf63wqsy0iZ master]# docker exec -it my-mysql-slave1 /bin/bash root@a53ea2fb4208:/# ls /home demo_ds_0.sql root@a53ea2fb4208:/# root@a53ea2fb4208:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 8.0.28 MySQL Community Server - GPL Copyright (C) 2000, 2022, Oracle and/or its affiliates. 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> show databases;+--------------------+ | Database | +--------------------+ | demo_ds_0 | | information_schema | | mysql | | Performance_schema | | sys | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 5 rows in the set (0.01 SEC)
mysql>use demo_ds_0
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  source /home/demo_ds_0.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Copy the code

Checking master Status

You can check the master status after running the master and slave databases without error

Master Run the SHOW master STATUS command

Set master information from the library

• Slave1 and Slave2 perform the following operations respectively

change REPLICATION SOURCE to SOURCE_HOST='127.0.0.1',SOURCE_PORT=3311,SOURCE_USER='root',SOURCE_PASSWORD='root',SOURCE_LOG_FILE='mysql-bin.000004',SOURCE_LOG_POS=927
Copy the code

• This topic describes the change REPLICATION SOURCE command

Start synchronization from the library

Distribute execution across two slave databases: start slave;

Viewing synchronization Status

To look at threads: show processList

Split the execution between two slave databases: show slave status;

• If Slave_IO_Running or Slave_SQL_Running is not yes, synchronization cannot be disabled.stop slave;And keep the master table structure and data consistent with the slave table structure and dataSHOW MASTER STATUSInformation, andagainSet the master information in slave,againStart synchronizing again.
• The Last_IO_Errno,Last_IO_Error,Last_SQL_Errno,Last_SQL_Error field displays the reason for the synchronization failure
• When both values are yes, we try to insert a record into the master to observe the data changes and slave synchronization status.

INSERT INTO demo_ds_0.test
(id, name)
VALUES(3.'hello');
Copy the code

Binlog three formats

There are three main types of mysql replication: SQL statement-based replication (SBR), Row-based replication (RBR), and mixed-mode replication (MBR). A binlog can be in three formats: STATEMENT, ROW, and MIXED.

STATEMENT Mode (SBR)

Each SQL statement that modifies data is logged in the binlog. The advantages are that each SQL statement and data changes in each row do not need to be recorded, reducing the amount of binlog logs, saving I/O, and improving performance. Disadvantages are inconsistencies in master-slave in some cases (sleep(), last_insert_id(), user-defined functions(UDF), etc.)

ROW mode (RBR)

Instead of recording the context information for each SQL statement, only which data was modified and to what extent. And there are no problems with stored procedures, or functions, or triggers being called and fired incorrectly in certain cases. The disadvantage is that a large number of logs are generated, especially when alter table is used.

MIXED Mode (MBR)

If the above two modes are used together, the STATEMENT mode is used to store binlogs. If the STATEMENT mode cannot be used, the ROW mode is used to store binlogs. The MySQL database selects a log storage mode based on the SQL STATEMENT.

There was a problem starting the container

The iptables problem

docker: Error response from daemon: driver failed programming external connectivity on endpoint my-mysql-master (b54146ce2090e21146a7c840735a3f0495dbc98858a4668230f9acf2c99a8ec8): (iptables failed: Iptables --wait -t NAT -a DOCKER -p TCP -d 0/0 --dport 3311 -j DNAT --to-destination 172.17.0.6:3306! -i docker0: iptables: No chain/target/match by that name. (exit status 1)).Copy the code

An error was reported due to firewall restart or docker-registered iptables chain failure

The solution

Systemctl restart Docker is used to restart the iptables chain. Docker is used to restart the iptables chain

Query docker’s chain iptables -l or iptables -t nat-nl

Docker Error response from Daemon: Driver failed programming external connectivity on endpoint

Secure_file_priv problem

Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
Copy the code

Error: secure_file_priv affects read/write files.

• secure_file_priv =: Empty does not restrict the import or export of mysqld
• secure_file_priv =NULL: NULL limits mysqld from allowing imports or exports
• secure_file_priv =/var/lib/mysql-filesMysqld import/export is restricted to /var/lib/mysql-files/ (subdirectories are not allowed)

The solution

• Do not limit
  • Windows: modify my.ini to add secure_file_priv= to [mysqld]
  • [mysqld] add secure_file_priv= to my.cnf

Lower_case_table_names problem

Different lower_case_table_names settings for server ('1') and data dictionary ('0')
Copy the code

Mysql in Linux is case-sensitive by default: lower-case-table-names=0

The solution

The official resolution

After initialization, is is not allowed to change this setting.So "lower_case_table_names" needs to be set together with --initialize .
Copy the code

This setting is not allowed to change after mysql is initialized. Therefore “lower_case_table_names” needs to be set with –initialize.

/mydata/mysql-cluster/master/data/ rm -rf /mydata/mysql-cluster/master/data/* Back up data before deleting it. Then restart initialization.

Public Key Retrieval is not allowed problem

Remote login package error:

Public Key Retrieval is not allowed
Copy the code

Public Key Retrieval is not allowed when using dbeaver to connect to MySQL 8.0

1. The simplest solution is to add allowPublicKeyRetrieval= True to the end of the link

2. If sha256_password authentication is used, the password must be protected by TLS during transmission. However, if the RSA public key is unavailable, you can use the public key provided by the server. You can specify the server’s RSA public key through ServerRSAPublicKeyFile in the connection, or the AllowPublicKeyRetrieval=True parameter to allow clients to retrieve the public key from the server; AllowPublicKeyRetrieval=True May cause malicious agents to obtain plaintext passwords through manin-the-middle (MITM) attacks. Therefore, AllowPublicKeyRetrieval=True is disabled by default and must be explicitly enabled

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your password '; flush privileges;Copy the code

Mysql_native_password =mysql_native_password =mysql_native_password =mysql_native_password;

Restarting the mysql service