Detailed analysis of copying in Mysql

This article mainly introduces the detailed analysis of replication in Mysql. It introduces the basic concepts, uses, implementation methods and centralized modes, and then shares the specific implementation code, which has certain reference value. Friends who need it can learn more.

1.mysql replication concept

refers to transmitting the DDL and DML operations of the primary database to the replication server through the binary log, and then transmitting them to the replication server on the replication server. These log files are re-executed, keeping the data on the replicate and master servers in sync. During the replication process, one server acts as the master and one or more other servers act as slaves. The master rewrites updates to binary log files and maintains an index of the files to track log rotation. These logs record updates sent to slave servers. When a slave connects to the master, it notifies the master of the location of the last successful update that the slave read in the log. The slave accepts any updates that have occurred since then, then blocks and waits for the master to be notified of new updates.

2. Purpose of replication

Synchronize data through master-slave replication, and then separate reading and writing ( mysql-proxy) to improve the concurrent load capacity of the database, or to be used as a primary and backup machine to ensure that the application can be switched to the backup machine to continue running in a short period of time after the host stops responding.

Advantages:

(1) The database cluster system has multiple database nodes. In the event of a single node failure, other normal nodes Services can continue to be provided.
(2) If a problem occurs on the master server, you can switch to the slave server
(3) Query operations can be performed on the slave server through replication, which reduces the access pressure on the master server and achieves data distribution and load balancing
(4) Backup can be performed on the slave server to avoid affecting the service of the master server during backup.

3. Replication implementation (3 methods)

(1) DRBD is a software-based, shared-nothing, Storage replication solution for mirroring block device content between servers.
(2) Mysql cluster (also known as mysql cluster). Mysql replication (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, and you can even start two mysqld instances on one physical server host. One serves as the master and the other serves as the slave to complete the configuration of the replication environment. However, in actual application environments, you can use the mysql replication function to build other replication architectures that are more conducive to expansion according to 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, one or more mysql servers as the slave, and copying the master's data to the slave. In practical applications, the master-slave architecture mode is the most commonly used for mysql replication. Generally, under this architecture, the write operations of the system are performed in the master, while the read operations are dispersed to various slaves. Therefore, this architecture is particularly suitable for the high read and write problems of the current Internet.

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

(1) Master enables binary logs. The operation of enabling binary logs is described in detail in Log Management.
(2) The I/O process on the slave connects to the master and requests the log content from the specified position of the specified log file (or from the beginning of the log).
(3) After receiving the I/O process request from the slave, the master reads the log information after the specified position of the specified log according to the request information through the I/O process responsible for replication, 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 and the location of the bin-log in which the returned information has been sent to the master.
(4) After receiving the information, the Slave I/O process will add the received log content to the end of the relay-log file on the slave side, and read the file name and bin-log file name of the master side. The location is recorded in the master-info file.
(5) After Slave's sql process detects the new content in relay-log, it will immediately parse the content of relay-log and execute it on its own.

4. Centralized mode of mysql replication

In versions after mysql5.1, the improvement in replication is the introduction of new replication Technology - Row-based replication. This technology is to focus on the records that have changed in the table, rather than copying the previous binlog mode. Starting from mysql5.1.12, the following three modes can be used to achieve this.

(1) SQL statement-based replication (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 the master-slave replication mode is very simple. Just add another parameter based on the previously set 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) Master server:

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

Enable binary logs on the main server:

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

OFF means the binary log is closed

3 steps to open the log:

①Open the mysql installation directory/my.cnf
② Find the label [mysqld], add the following line in the line below this label:

log_bin[filename]

In this statement, log- bin indicates to open the 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（数据库名称）

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

Binlog-ignore-db-db_name（数据库名称）

③Restart the mysql service. You can see the "binary_log.numeric number" file in the mysql installation directory/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 numerical number in the file name will increase.

After successful startup, 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：每一个数据库服务器都要指定一个唯一的server-id，通常主服务器为1，master和slave的server-id不能相同。
Binlog-do-db：表示需要复制的数据库，这里以xscj为例
Binlog-ignore-db：表示不需要复制的数据库

Create the user 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 and save it in /data /binary_dump.txt file, and then import it into the slave machine. The specific execution statements are 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, the configuration is as follows:

Server-id=2 ##设置从服务器id
Master-host=192.168.11.129
Master-user=rep_user
Master-password=  ##设置连接主服务器的密码
Replicate-do-db ##设置你要同步的数据库，可以设置多个
Master-port=<port> ##配置端口号

重启slave，在slave主机的mysql重新执行如下命令，关闭slave服务
Mysql>stop slave;
设置slave实现复制相关的信息，执行如下命令
Mysql>change master to
>master_host=&#39;&#39;,
>master_user=&#39;&#39;,
>master_password=&#39;&#39;,
>master_log_file=&#39;binary_log.000007&#39;,
>master_log_pos=120;

输入:show slave status\G用于提供有关从服务器线程的关键参数信息。

Commonly used commands are as follows

##Slave startStart the replication thread##Slave stop##Reset slaveShow slave statusShow slave status\gShow master status\GShow master logsChange master toRelated recommendations:

#Options	Function
Stop replication thread
Reset replication thread
Show replication thread status
Show replication thread status (displayed in separate lines)
Show the status of the master database (displayed in separate lines)
Display master database log
Dynamic changes to the configuration of the main database	##Show processlistv
Show which threads are running	Have you all learned it? Hurry up and give it a try.

Summary of methods for copying table structures in mysql_MySQL

Copying data tables in MySQL Tutorial on how to transfer data to a new table_MySQL

Overview, installation, faults, techniques, and tools of MySQL replication (Shared by Huo Ding)

The above is the detailed content of Detailed analysis of copying in Mysql. For more information, please follow other related articles on the PHP Chinese website!