Detailed analysis of replication in Mysql

1.MySQL replication concept

It means transferring the DDL and DML operations of the primary database to the replication server through binary logs, and then re-executing these log files on the replication server to keep the data of the replication server and the primary server synchronized. In the replication process, one server acts as the master, and one or more other servers act as slaves. The master rewrites updates to the binary log file and maintains an index of the file to track log rotations. These logs record updates sent to the slave servers. When a slave connects to a master, it informs the master of the location of the last successful update that the slave read in the log. The slave accepts any updates that occur from that point on, and then blocks and waits for notification of new updates from the master.

2. Purpose of Copying

Data is synchronized through master-slave replication, and read-write separation (mysql-proxy) is used to improve the concurrent load capacity of the database, or it is used as a master-slave design to ensure that the application can be switched to the backup machine and continue to run in a very short time after the host stops responding.

Advantages:

(1) The database cluster system has multiple database nodes. If a single node fails, other normal nodes can continue to provide services.
(2) If a problem occurs on the master server, it can be switched to the slave server. (3) Through replication, query operations can be performed on the slave server, reducing the access pressure on the master server and achieving data distribution and load balancing. (4) Backups can be performed on the slave server to avoid affecting the service of the master server during the backup period.

3. Implementation of replication (3 methods)

(1) DRBD is a software-implemented, shared-nothing storage replication solution that mirrors the contents of block devices between servers.
(2) MySQL cluster (also known as MySQL cluster). MySQL replication itself is a relatively simple structure, that is, a slave server (slave) reads the binary log from a master server (master) and then parses and applies it to itself.
(3) A simple replication environment only requires two hosts running MySQL. You can even start two mysqld instances on one physical server host. One is used as the master and the other as the slave to complete the replication environment. However, in actual application environments, you can use the MySQL replication function to build a variety of other more scalable replication architectures based on actual business needs, such as the most commonly used master-slave architecture.
The master-slave architecture refers to using one MySQL server as the master and one or more MySQL servers as slaves to copy the master's data to the slaves. In actual application scenarios, the master-slave architecture mode is the most commonly used for MySQL replication. Generally, under this architecture, the system's write operations are performed in the master, while the read operations are distributed to each slave. Therefore, this architecture is particularly suitable for the current high read and write problems of the Internet.

The Mysql database replication operation is roughly divided into the following steps:

(1) The master enables binary logging. The operation of enabling binary logging is described in detail in Log Management.
(2) The I/O process on the slave connects to the master and requests the log content after the specified position of the specified log file (or from the beginning of the log).
(3) After the master receives the I/O process request from the slave, the I/O process responsible for replication reads the log information after the specified position of the specified log according to the request information and returns it to the slave's I/O. In addition to the information contained in the log, the returned information also includes the name of the bin-log file that has been sent to the master side and the location of the bin-log.
(4) After the slave's I/O process receives the information, it adds the received log content to the end of the slave's relay-log file in sequence, and records the file name and location of the master's bin-log read into the master-info file.
(5) After the slave's SQL process detects the new content in the relay-log, it will immediately parse the content of the relay-log and execute it on itself.

4. Centralized mode of mysql replication

In versions after MySQL 5.1, the improvement in replication is the introduction of a new replication technology - row-based replication. This technology focuses on the records that have changed in the table, rather than the previous binlog mode. Starting from MySQL 5.1.12, this can be achieved using the following three modes.

(1) SQL statement-based replication (SBR)
(2) Row-based replication (rbr)
(3) Mixed-mode replication (mbr)

Correspondingly, there are three formats of binlog: statement, row, and mixed. In Mbr mode, sbr mode is the default. The binlog format can be changed dynamically at runtime. The method of setting the master-slave replication mode is very simple. Just add one more parameter based on the previous replication configuration, as follows:

binlog_format = "statement"
#binlog_format="row"
#binlog_format=”mixed”

Of course, you can also dynamically modify the binlog format at runtime

Mysql> set session binlog_format="statement"

5. Control the main server operation

Master: 192.168.11.139
Slave: 192.168.11.130

(1) Primary server:

mysql> show variables like '%datadir%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| datadir | /application/mysql/data/ |
+---------------+--------------------------+

Enable binary logging on the primary server:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
row in set (0.00 sec)

OFF means binary logging is closed

Steps to enable log 3:

①Open mysql installation directory/my.cnf
② Find the [mysqld] tag, and add the following statement in the line below it:

log_bin[filename]

In this statement, log-bin indicates that a binary file is to be opened; filename is the name of the binary log. If not specified, the default is the host name followed by -bin as the file name, and it is stored in the datadir directory by default. If you specify binary_log here to generate binary files only for the specified database, you need to add the following statement

Binlog-do-db=db_name (database name)

If you do not generate binary file logs for the specified database, you need to add the following statement

Binlog-ignore-db-db_name (database name)

③Restart the mysql service. You can see the "binary_log.digital number" file in the MySQL installation directory/data folder, such as binary_log.00001. Each time the MySQL service is restarted, the binary file will be regenerated and the number in the file name will increase.

After the boot is successful, modify the MySQL configuration file my.cnf and set the server-id. The code is as follows

Server-id=1
Binlog-do-db=xscj
Binlog-ignore-db=mysql
Server-id=1: Each database server must be assigned a unique server-id, usually 1 for the master server. The server-ids of the master and slave servers cannot be the same.
Binlog-do-db: indicates the database that needs to be copied. Here, xscj is used as an example. Binlog-ignore-db: indicates the database that does not need to be copied.

Create the users required for replication on the master

mysql> grant replication slave on *.* to rep_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec

mysql> show master status\G
*************************** 1. row ***************************
      File: binary_log.000001
    Position: 303
  Binlog_Do_DB: 
Binlog_Ignore_DB: 
row in set (0.00 sec)

Back up the data of the master host, save it in the /data/binary_dump.txt file, and then import it to the slave machine. The specific execution statement is as follows

[root@localhost bin]# mysqldump -h localhost>/data/binary_dump.txt

(2) Control slave server operations

Modify the database configuration file of the slave server and configure it as follows:

Server-id=2 ##Set the slave server id
Master-host=192.168.11.129
Master-user=rep_user
Master-password= ##Set the password to connect to the master server Replicate-do-db ##Set the database you want to synchronize. You can set multiple Master-port=<port> ##Configure the port number Restart the slave and re-execute the following command on the mysql of the slave host to shut down the slave service Mysql>stop slave;
Set the slave to implement replication-related information and execute the following command Mysql>change master to
>master_host='',
>master_user='',
>master_password='',
>master_log_file='binary_log.000007',
>master_log_pos=120;

Input: show slave status\G is used to provide key parameter information about the slave server thread.

Commonly used commands are as follows

The above is all the content of this article about the detailed analysis of replication in Mysql. I hope it will be helpful to everyone. Welcome to refer to: Introduction to fuzzy query method using instr in MySQL, Code analysis of user variables in MySQL query statements, Detailed explanation of JSON data type operation in MySQL operations, etc. If there are any deficiencies, please leave a message to point them out. If there are problems, we can fix them. Things are not static.