mysql master and slave of Liunx service management

Keywords: MySQL Database Oracle SQL

Introduction of master and slave

What is master-slave?

MySQL master-slave replication is one of its most important functions. Master-slave replication refers to that one server acts as the master database server and another or more servers act as the slave database server, and the data in the master server is automatically copied to the slave server. For multi-level replication, the database server can act as a host or a slave. The basis of MySQL master-slave replication is that the master server records binary logs for database modification, and the slave server automatically updates the binary logs of the master server.

Role of master and slave

  • Real time disaster recovery for failover
  • Separation of reading and writing, providing query service
  • Backup to avoid business impact

Problems solved by master and slave

  • data distribution
  • load balance
  • backups
  • High availability and fault tolerance lines

Several forms of master-slave


One master and one slave (lowest cost):

  • It is not used to improve program performance. It is mainly used for hot standby of data (that is, if the master node is hung up, the slave node can act as the master node), so as to improve program availability and disaster tolerance.
  • There is no data consistency issue because it is read from only one node.
  • Although you can do hot backup, you cannot do data backup (not highly available). If you accidentally perform DROP in the master node, the slave will synchronize this operation immediately, so you cannot retrieve data in the slave

One master and many slaves (usually 2-4 Slave):

  • Select a node for master backup. If the master hangs up, slave will serve as the master node to continue the service (for example, master is the emperor, slave is the crown prince, and if the emperor dies, the crown prince will take the responsibility immediately)
  • Select another node for slow query or statistics

Dual master:

  • The usage scenario is usually that most of the concurrency in your business is caused by writing, which can't be carried

  • How to ensure that data does not overlap? If the ID is an integer, the module operation can be used for allocation; if the ID is a string, the hash operation can be used for allocation

  • Disadvantages:

    • If a node is hung up, the whole data will be disordered. Do not use it until you have to
    • The slave database that provides services after [master a] needs to wait until [Master b] has synchronized the data before synchronizing the data from [master a]. This may cause a certain delay

Cascade synchronization:

  • It is mainly used to relieve the pressure of the master. Since the master needs both write operation and read operation by multiple slave nodes, the slave in the middle performs partial pressure operation

  • If the master fails, the remaining slave nodes will naturally form a natural cluster structure

    Ring multimaster (strong processing power, but very dangerous):

  • It is omitted in the figure. Each master corresponds to multiple slave nodes

  • If one master fails, the whole architecture will basically collapse (for example, Cao Cao in the battle of red cliff, connecting all the ships)

Principle of master-slave replication

  • The main database records all write operations to the binlog log and generates a log dump thread. The binlog log is passed to the I/O thread of the slave database
  • Generate two threads from the library, one I/O thread and one SQL thread
    • The I/O thread requests the binlog of the main database and writes the binlog to the relay log file
    • The SQL thread will read the logs in the relay log file and parse them into specific operations to achieve the consistency of master-slave operations and final data consistency

It can be simplified into three steps:

  1. Master logs the changes to the binary log.
  2. Slave copies the binary log of Master to its relay log (Relay_log )
  3. Slave redoes the events in the relay log, changing the data that reflects itself

Master slave replication configuration

Master-slave replication configuration steps:

  1. Make sure that the slave database is the same as the data in the master database
  2. Create a synchronization account in the master database and authorize it to the slave database
  3. Configure primary database (modify profile)
  4. Configure from database (modify profile)

mysql master-slave configuration

There are two cases. The first is that the master database and the slave database are the same, and the other is different from the slave database

Step 1: first check whether the contents of the master and slave databases are the same

//The main database has no content now
[root@longnian ~]# mysql -uroot -p'longnian123.' -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

//From the database, there is no content now
[root@longnian ~]# mysql -uroot -p'longnian123.'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

//If the master database is different from the slave database, the master database must be made a complete backup to import the data into the slave database. Then lock the main database, but during this period, the main database cannot exit the database!
//Shrink table operation
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Step 2: create a synchronization account in the master database and authorize it to the slave database

mysql> create user 'repl'@'192.168.159.132' identified by 'repl123.';
Query OK, 0 rows affected (0.05 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.159.132';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Step 3: configure the master database

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
//Add the following
log-bin=mysql-bin
server-id=1
symbolic-links=0

Step 4: restart the service and view the main database status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Step 5: configure the slave database

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
//Add the following
server-id=2     
relay-log=mysql-relay-bin
symbolic-links=0

Step 6: configure and start master-slave replication and view the status

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.16.12.128',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.33 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.159.132
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     
            Slave_SQL_Running: Yes     
              Replicate_Do_DB:
          Replicate_Ignore_DB: 

Step 7: Test
Create a new database on the primary database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

To see if the data is synchronized from the database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Posted by grimz on Fri, 19 Jun 2020 05:48:13 -0700