This is an operations job, but the back-end developer must understand it as well.

Note: This article uses the latest docker version, which comes with the compose command. Non-current players can simply use the docker-compose command at run time.

configuration

File structure

For convenience and ease of management, my directory structure is like this, for reference only:

├─ master │ ├─ data │ ├─ Bass exerciseslog. | │ └ ─ ─ the error log │ └ ─ ─ my. The CNF ├ ─ ─ slave1 │ ├ ─ ─ data │ ├ ─ ─log. | │ └ ─ ─ the error log │ └ ─ ─ my. The CNF └ ─ ─ slave2 ├ ─ ─ data ├ ─ ─log│ ├ ── error. Log ├ ─ ch.pdfCopy the code

According to the directory structure, we can see that there is a master and two slave structure. The data directory is used to store MySQL data, log/error.log is used to store MySQL exception output, and my.cnf is the MySQL configuration file.

Compose the file

Note:

  1. The network must be defined, otherwise the containers cannot communicate, otherwise how can the slave find the master?
  2. On an internal network, containers access each other through ports inside containers, not host ports.
  3. Data volumeerror.logIs for MySQLlog-errorConfiguration items must be created first; otherwise, the following message will be reported:File Not FoundThe container cannot be started due to an error.
  4. Data volumeerror.logSince it is created on the host, mysql users inside the container do not have access to the reportPermission DenyFailed to start the container, so configuration is requiredentrypointExecute the command at container startup for authorization.
Network: mysql-master-slave-network: driver: bridge ipam: config: -subnet: mysql-master-slave-network: driver: bridge ipam: config: -subnet: 172.25.0.0/24 services: mysql-master: image: mysql container_name: mysql-master ports: -3307 :3306 volumes: 172.25.0.0/24 services: mysql-master: image: mysql container_name: mysql-master ports: -3307 :3306 volumes: - "./master/data:/var/lib/mysql" - "./master/log/error.log:/var/log/mysql/error.log" - "./master/my.cnf:/etc/mysql/my.cnf" environment: MYSQL_ROOT_PASSWORD: 123456 MYSQL_DATABASE: test entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld" restart: unless-stopped networks: Mysql-master-slave-network: ipv4_address: 172.25.0.101 mysql-slave-1: image: mysql container_name: mysql-slave-1 ports: - 3308:3306 volumes: - "./slave1/data:/var/lib/mysql" - "./slave1/log/error.log:/var/log/mysql/error.log" - "./slave1/my.cnf:/etc/mysql/my.cnf" environment: MYSQL_ROOT_PASSWORD: 123456 MYSQL_DATABASE: test entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld" restart: unless-stopped networks: Mysql-master-slave-network: ipv4_address: 172.25.0.102 mysql-slave-2: image: mysql container_name: mysql-slave-2 ports: - 3309:3306 volumes: - "./slave2/data:/var/lib/mysql" - "./slave2/log/error.log:/var/log/mysql/error.log" - "./slave2/my.cnf:/etc/mysql/my.cnf" environment: MYSQL_ROOT_PASSWORD: 123456 MYSQL_DATABASE: test entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld" restart: unless-stopped networks: Mysql - master - slave - network: ipv4_address: 172.25.0.103Copy the code

My CNF configuration

  • Master

    [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = Log bind-address = 0.0.0.0 secure-file-priv = NULL max_connections = 16384 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect ='SET NAMES utf8mb4' skip-name-resolve skip-host-cache Server_id = 1 # ID, do not repeat log-bin = mysql-bin # open binlog binlog-do-db = test # open binlog Log-slave-updates # Log the slave server to its own binary log file sync_binlog = 1 # Control the binlog write frequency auto_increment_offset = 1 # auto_increment_increment = 1; # auto_increment = 1; Expire_logs_days = 7 # bin, log_bin_trust_function_creators = 1 # Custom config should go here! includedir /etc/mysql/conf.d/Copy the code

  • The only difference is server_id 2 and 3, which is different from Master.

    [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = Log bind-address = 0.0.0.0 secure-file-priv = NULL max_connections = 16384 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci init_connect ='SET NAMES utf8mb4' skip-name-resolve skip-host-cache server_id = log-bin = mysql-bin log-slave-updates sync_binlog = 0 innodb_flush_log_at_trx_commit = 0 replicate-do-db = test slave-net-timeout = 60 log_bin_trust_function_creators = 1 # Custom config should go here ! includedir /etc/mysql/conf.d/Copy the code

The system starts after the configuration is complete.

Master-slave setup

  1. Obtain the Master status.

    Log in to Master and enter show Master status\G, File refers to the current bin-log File, Position refers to the current offset, and the next command starts from here. So we need to remember these two pieces of information that the slave library relies on to start copying.

    mysql> show master status\G
*************************** 1. row* * * * * * * * * * * * * * * * * * * * * * * * * * *File: mysql-bin000003.
         Position: 156
     Binlog_Do_DB: test
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
Copy the code

  2. Log in to the slave database and clear the original slave information.

    mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Copy the code

  3. Configure slave information for the slave database.

    Note that master_port=3306 because the container internal network accesses the container’s interface, not the host’s interface. Reading the Master’s File and Position information is configured here.

    I have enabled remote connection in my.cnf, so it can be connected directly here. If the bind-address is not configured according to the preceding configuration, please enable it. Otherwise, the slave database cannot log in to the master database. To see if the slave can connect to the master, try using the mysql client to connect to the master from the slave.

    mysql> change master to master_host='172.25.0.101',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=156;
Query OK, 0 rows affected, 8 warnings (0.05 sec)
Copy the code

  4. Start the slave connection.

    mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01sec)Copy the code

  5. Check the slave status. If the values of Slave_IO_Runnig and Slave_SQL_Running processes are Yes, they are successfully started and the replication test can be performed.

    The Slave_IO process is responsible for host communication, connecting to the primary library, and pulling the primary library bin-log to the relay-log. The Slave_SQL process is responsible for executing commands to perform operations in relay-log.

    If one of them is not Yes, you can check the details in the error.log file.

    mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.25. 0101.
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin000003.
          Read_Master_Log_Pos: 156
               Relay_Log_File: 34d0770589c6-relay-bin000002.
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin000003.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB:
                            ...
1 row in set.1 warning (0.01 sec)
Copy the code

  • Copy the test

    Create a new table in the host’s test library, and the slave library creates a new table. Add, delete, and change data in the table, the slave database will add, delete, and change data.

Disable slave library updates

Set global read_only=1; Command to restrict the write operations of common users, including adding, deleting, modifying, creating, modifying, and deleting tables.

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
Copy the code

Settings lose effect?

But if you follow the steps above, you will find that you can still execute successfully (WTF?) :

mysql> drop table t1;
Query OK, 0 rows affected (0.07 sec)
Copy the code

This is because this action only restricts the normal user, not the superuser, and the root can do whatever it wants. If you want to restrict root, you have to play this:

mysql> flush tables with read lock; Query OK, 0 rows affected (0.02sec) mysql>set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
Copy the code

However, the slave library cannot copy the master library because it also locks the Slave_SQL process and cannot write to it. Therefore, the super user is not able to move. Use unlock tables; Unlock flush tables with read lock; At this point, the slave can replicate the primary operation.

Correct replication

Set global read_only=1; set global read_only=1; It will take effect. In addition, ensure that the super accounts of the secondary database root can only be used for local login. External login and write operations are not allowed.

Then why doesn’t the top just do the right thing? If you like repetition that much, let’s give you a taste of it

To change the user, the main database three:

CREATE USER 'slave'@The '%' IDENTIFIED WITH sha256_password BY 'slave123';
GRANT REPLICATION SLAVE ON*. *TO 'slave'@The '%';
FLUSH privileges;
show master status\G
Copy the code

From the library:

stop slave;
reset slave;
change master to master_host='172.25.0.101',master_user='slave',master_password='slave123',master_port=3306,master_log_file='mysql-bin.******',master_log_pos=*****;
start master;
Copy the code

Pit a lot, some pit is their own logic errors, some pit is insufficient understanding of MySQL, step on the affirmation is very annoying, but the investigation is to have the right to speak, experienced before there is real effort.