Comprehensive interpretation of MySQL master-slave replication, from principle to installation and configuration

Why do we need master-slave replication?

1. In a complex business system, there is a scenario where a SQL statement needs to lock the table, resulting in the temporary inability to use the read service, which greatly affects the running business. Use master-slave replication to let the master database be responsible for writing and the slave database for reading. In this way, even if the master database locks the table, the normal operation of the business can be guaranteed by reading from the slave database.

2. Perform hot backup of data

3. Expansion of architecture. As the volume of business increases, the I/O access frequency is too high and cannot be met by a single machine. In this case, multiple databases are used to store data, reduce the frequency of disk I/O access, and improve the I/O performance of a single machine.

What is mysql master-slave replication?

MySQL master-slave replication means that data can be copied from a MySQL database server master node to one or more slave nodes. MySQL uses asynchronous replication by default, so that slave nodes do not have to access the master server all the time to update their own data. Data updates can be performed on remote connections. Slave nodes can copy all databases or specific databases or specific tables in the master database.

MySQL replication principle

principle:

(1) The master server records data changes in a binary log. When data on the master changes, the changes are written to the binary log.

(2) The slave server will detect whether the master binary log has changed at a certain time interval. If it has changed, it will start an I/OThread to request the master binary event.

(3) At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save them in the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node. Finally, the I/OThread and SQLThread will enter a sleep state and wait to be awakened next time.

That is:

• The slave library will generate two threads, one I/O thread and one SQL thread;
• The I/O thread will request the binlog of the master database and write the obtained binlog to the local relay-log file;
• The master library will generate a log dump thread to transfer binlog to the slave library I/O thread;
• The SQL thread reads the logs in the relay log file and parses them into SQL statements and executes them one by one;
  

Notice:

1--The master records the operation statement in the binlog log, and then grants the slave remote connection permission (the master must enable the binlog binary log function; usually for data security considerations, the slave also enables the binlog function).

2--Slave starts two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the master's binlog content into the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave's database, so as to ensure that the slave data is consistent with the master data.

3--MySQL replication requires at least two MySQL services. Of course, MySQL services can be distributed on different servers, or multiple services can be started on one server.

4--Mysql replication is best to ensure that the Mysql version on the master and slave servers is the same (if the version consistency cannot be met, then ensure that the version of the master node is lower than the version of the slave node)

5--Time between master and slave nodes needs to be synchronized

Specific steps:

1. The slave database connects to the master database by manually executing the change master to statement, providing all the conditions for the connected user (user, password, port, IP), and letting the slave database know the starting position of the binary log (file name position number); start slave

2. Establish a connection between the IO thread of the slave library and the dump thread of the master library.

3. The slave IO thread initiates a binlog request to the master based on the file name and position number provided by the change master to statement.

4. The master database dump thread sends the local binlog to the slave database IO thread in the form of events based on the slave database's request.

5. Receive binlog events from the library IO thread and store them in the local relay-log. The transmitted information will be recorded in master.info

6. Apply relay-log from the SQL thread of the database and save the applied records to relay-log.info. By default, the applied relays will be automatically purged.

MySQL master-slave replication installation and configuration

1. Basic setup preparation

operating system:

centos6.5

mysql version:

5.7

Two virtual machines:

node1:192.168.85.11 (primary)

node2:192.168.85.12 (slave)

2. Install MySQL database

For detailed installation and uninstallation steps, refer to the corresponding documents

3. Create databases in two databases

--Note that both machines must execute

create database msb;

4. Configure the following on the primary (node1) server:

Modify the configuration file and execute the following command to open the MySQL configuration file

vi /etc/my.cnf

Add the following configuration information to the mysqld module

log-bin=master-bin #Binary file name

binlog-format=ROW #Binary log format, there are three formats: row, statement, and mixed. Row means copying the changed content instead of executing the command on the slave server. Statement means executing the SQL statement executed on the master server and executing the same statement on the slave server. MySQL uses statement-based replication by default, which is more efficient. Mixed means that statement-based replication is used by default. Once it is found that statement-based replication cannot be accurately replicated, row-based replication will be used.

server-id=1 #Requires that each server's id must be different

binlog-do-db=msb #Synchronized database name

5. Configure the account authorization for logging in from the server to the master server

--Authorization Operation

set global validate_password_policy=0;

set global validate_password_length=1;

grant replication slave on *.* to 'root'@'%' identified by '123456';

--Refresh permissions

flush privileges;

6. Configuration of slave server

Modify the configuration file and execute the following command to open the MySQL configuration file

vi /etc/my.cnf

Add the following configuration information to the mysqld module

log-bin=master-bin #Name of the binary file binlog-format=ROW #Format of the binary file server-id=2 #Server ID

7. Restart the mysqld service of the primary server

Restart mysql service

service mysqld restart

Log in to mysql database

mysql -uroot -p

Check the status of the master

show master status;

8. Restart the slave server and make relevant configurations

Restart mysql service

service mysqld restart

Login to mysql

mysql -uroot -p

Connect to the main server

change master to master_host='192.168.150.11',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=334;

Start slave

start slave

Check the slave status

show slave status\G (note there is no semicolon)

9. At this time, you can add and delete related data on the master server and view the related status on the slave server.

The knowledge about database and other Java related knowledge has been uploaded to my code cloud. You can pick it up if you need it.

Personal code cloud address

The above is a comprehensive interpretation of MySQL master-slave replication, from principles to installation and configuration details. For more information about MySQL master-slave replication, please pay attention to other related articles on 123WORDPRESS.COM!