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:
- Master logs the changes to the binary log.
- Slave copies the binary log of Master to its relay log (Relay_log )
- Slave redoes the events in the relay log, changing the data that reflects itself
Master slave replication configuration
Master-slave replication configuration steps:
- Make sure that the slave database is the same as the data in the master database
- Create a synchronization account in the master database and authorize it to the slave database
- Configure primary database (modify profile)
- 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)