Detailed parsing of replication in Mysql

1. mysql replication concept

The DDL and DML operations of the primary database are passed to the replication server through binary logs, and then these log files are re-executed on the replication server, so that the data of the replication server and the primary server are synchronized. During replication, one server acts as the master server (master) and one or more other servers act as slave servers (slaves). The master server rewrites updates to the binary log file and maintains 1 index of the file to track log loops. These logs can record updates sent to the slave server. When a slave connects to the master, it notifies the master of the location of the last successful update read by the slave in the log. The slave server accepts any updates that have occurred since then, then blocks and waits for the master server to notify of new updates.

2. Purpose of reproduction

The data is synchronized by master-slave replication (master-slave), and then the concurrent load capacity of the database is improved by read-write separation (mysql-proxy), or it can be used as the design of the master and standby machine to ensure that the application can be switched to the standby machine to continue running in a short time after the host stops responding.

Advantages:

(1) The database cluster system has multiple database nodes. When a single node fails, other normal nodes can continue to provide services.
(2) If there is a problem on the master server, you can switch to the slave server
(3) Through replication, the query operation can be executed on the slave server, which reduces the access pressure of the master server and realizes data distribution and load balancing
(4) Backup can be done on the slave server to avoid affecting the service of the master server during backup.

3. Implementation of replication (3 methods)

(1) DRBD is a software-implemented, sharing-free, inter-server storage replication solution that mirrors block device content.
(2) Mysql cluster (also known as mysql cluster). Mysql replicaion (replication) itself is a relatively simple structure, that is, a slave server (slave) reads binary logs from a master server (master), and then parses and applies them to itself.
(3) A simple replication environment requires only two hosts running mysql, and even two mysqld instances can be started on one physical server host. One is used as master and the other is used as slave to complete the matching of replication environment. However, in the actual application environment, we can use the replication function of mysql to build other replication architectures that are more conducive to expansion, such as the most commonly used master-slave architecture.
Master-slave architecture refers to using one mysql server as master and one or more mysql servers as slave, and copying the data of master to slave. In practical applications, the master-slave architecture pattern is the most commonly used for mysql replication. Generally, under this architecture, the write operation of the system is carried out in master, while the read operation is distributed to each slave, so this architecture is especially suitable for the problem of high reading and writing on the Internet.

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

(1) master enables binary logging. The operation of enabling binary log is described in detail in log management.
(2) The I/O process above slave connects to master and requests the log contents from the specified location in the specified log file (or from the original log).
(3) After receiving the request from the I/O process from the slave, the I/O process responsible for copying reads the log information after the specified position of the specified log according to the request information, and returns it to the I/O of the slave. In addition to the information contained in the log, the returned information also includes the name of the bin-log file where the returned information has reached the master side and the location of bin-log.
(4) After receiving the information, the I/O process of Slave adds the received log contents to the end of relay-log file at slave end in turn, and records the file name and position of bin-log at master end to master-info file.
(5) After the sql process of Slave detects the new content in relay-log, it will immediately parse the content of relay-log and execute it in itself.

4. Centralized mode of mysql replication

The replication improvement in mysql after 5.1 was the introduction of a new replication technology-row-based replication. Instead of copying the binlog schema, this technique focuses on the records that have changed in the table. Starting with mysql 5.1. 12, it can be implemented in the following three modes.

(1) Replication based on sql statement (statement-base 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 format of binlog can be changed dynamically at runtime. The method of setting master-slave replication mode is very simple, just add one parameter to the previous replication configuration, as follows:

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

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

Mysql> set session binlog_format= " statement " 

5. Control master server operations

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/ |
+---------------+--------------------------+

Turn on binary logs on the primary server:

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

OFF indicates that binary logs are off

Open log 3 steps:

① Open the mysql Installation Direction/my. cnf
② Find the label [mysqld], and add the following statement in one line below this label:

log_bin[filename]

In this statement, log-bin indicates that you want to open a binary file; filename is the name of the binary log. If not specified, the default is the host name followed by-bin as the file name, which is stored in the datadir directory by default. Specify binary_log here. If you only generate binary files 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 mysql service. You can see the file "binary_log. Number number" under the mysql installation director/data folder, such as binary_log. 00001. Every time the mysql service is restarted in the future, the binary file will be regenerated, and the number number in the file name will be increased once.

After booting successfully, modify the configuration file my. cnf of mysql and set server-id with the following code

Server-id=1
Binlog-do-db=xscj
Binlog-ignore-db=mysql
Server-id=1 : Per 1 Each database server must be specified 1 Individual only 1 Adj. server-id Usually the primary server is 1 , master And slave Adj. server-id Can't be the same. 
Binlog-do-db Represents the database that needs to be replicated, where the xscj For example 
Binlog-ignore-db Represents a database that does not need to be replicated 

Users needed to create replication on 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 master host, save it in /data/binary_dump. txt file, and then import it into slave slave. 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 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 for connecting to the primary server 
Replicate-do-db ## Set the database you want to synchronize, and you can set multiple databases 
Master-port=<port> ## Configure port number 

 Restart slave , in slave Host's mysql Re-execute the following command to close slave Services 
Mysql>stop slave;
 Settings slave To implement replication-related information, 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 Used to provide key parameter information about the slave server thread. 

Common commands are as follows

选项	功能
Slave start	启动复制线程
Slave stop	停止复制线程
Reset slave	重置复制线程
Show slave status	显示复制线程状态
Show slave status\g	显示复制线程状态（分行显示）
Show master status\G	显示主数据库的状态（分行显示）
Show master logs	显示主数据库日志
Change master to	动态改变到主数据库的配置
Show processlistv	显示有哪些线程正在运行

The above is the article on Mysql replication of the full details of the analysis, I hope to help you. Welcome to see: mysql using instr fuzzy query method introduction, mysql query statement using code analysis of user variables, MySQL operation of JSON data type operation details, etc. If there are deficiencies, welcome to leave a message. If there is a problem, we will change it. Things are not 10% unchanged.