How MySQL copies database data _ MySQL

This article mainly introduces the basic process of MySQL copying database data and explains some Slave configurations. For more information, see Copy

Replication is the process of copying data from one MySQL server (master) to another or multiple MySQL servers (slaves. replication is performed asynchronously. the server load balancer does not need to keep the connection continuously to receive data from the master. based on different configurations, you can copy all databases, specified databases, or even specified tables of a database.

The purpose of the replication function is:

Outward scaling solution-improves performance by distributing loads across multiple servers. in this environment, all write and update operations are performed on the master server, while read operations occur on one or more slaves servers.
Data security-because the data is copied to the slave, and the slave can pause the replication process, you can back up the data on the slave server without damaging the master data.
Analysis-real-time data is created on the master, but data analysis can be performed on the slave server without affecting the performance of the master.
Long-distance data distribution-if the branch needs a copy of the data of the primary company to work, you can create a local copy by copying, so that you do not need to access the master server for a long time.
MySQL replication is unidirectional and asynchronous, which is the opposite of the synchronous replication feature of MySQL Cluster. mySQL5.5 supports semi-sync (semisynchronous), that is, after the commit on the master node, it does not return immediately, but waits for at least one slave to confirm that the current transaction has been received and recorded, return.

The best Replication method is related to the data presentation mode and the storage engine Selected. The core Replication format is either: SBR (Statement Based Replication)-Copy all SQL statements, and RBR (Row Based Replication) -- Only copies the changed rows. of course, there are also three solutions to choose from: MBR (Mixed Based Replication), which is also the default mode for versions later than MySQL 5.5.

Copy configuration

The replication between MySQL servers uses the binary log mechanism. updates and changes to the master node are recorded in logs as events. The information in logs is recorded in different formats as they change. slaves is configured to read logs from the master and execute events in binary logs to the slave local database. once the master node starts the binary log function, all statement operations will be recorded, and each slave will receive a copy of the entire log content. the responsibility of slave is to determine which statement in the log needs to be executed, but we cannot configure the master to only record some specific events. if not specified separately, all the events in the binary log of the master server are executed on the slave. if necessary, you can configure slave to only apply events from a specific database or table.

Each slave maintains a binary log file record and records the locations where records have been read and processed. this indicates that multiple slaves can connect to the master and execute different parts of the log. because slave controls this process by itself, the disconnection and connection of a single slave will not affect the operations of the master. at the same time, because each slave records the location in the binary log, slaves can be disconnected, reconnected, and started from the recorded location.

The Master and each slave must be assigned a unique ID (server_id may be used). In addition, the server name and position of the master of the slave must be notified ). you can CHANGE the master to in a session. the detailed information is recorded in the master.info file.

1. how to start replication

1.1 Create a user for copying

Each slave must connect to the master using the standard MySQL User name and password. any account can, as long as the replication slave permission is granted. although it is not necessary to create a user for replication, it is clear that the username and password of the account used for replication are stored in master.info in plaintext, therefore, it is better to create one for security considerations. for example:

      mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.158.1.100' IDENTIFIED BY 'testpass';

That is, an account with the username "repl" and password "testpass" is created. all slaves can use the same account, of course, we can also create a logon account for each slave.

1.2 configure Master

First, you must enable the binary log function of the master, and then set a unique server-id for the master -- 1 ~ A positive integer between p, li {white-space: pre-wrap;} 232-1. for example, set it in my. cnf or my. ini:

      [mysqld]      log-bin=master-bin      server-id=1

Note: To create the maximum possible stability and consistency during the use of InnoDB transactions, you need to use the innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 options. at the same time, make sure that: skip-networking = 0 otherwise slave and master will not be able to communicate.

1.3 configure Slave

The only thing we need to configure on slave is to specify a unique server-id for slave. it is not necessary to enable the binary log function on Slave, but it can be used for data backup or disaster recovery on slave, you can also use slave as part of the more complex replication topology architecture (for example, when a slave is used as the master of another slaver ).

1.4 obtain Master information

To configure server load balancer replication, you need to know the current location of the master in its binary log. in this way, when server load balancer starts the replication process, it will know to process the event from the current point. if data already exists on the master and needs to be synchronized to other slaves before the replication starts, you have to stop the master statement, get the current location, and then export the data. to obtain the master status information, follow these steps:

Run:

mysql>FLUSH TABLES WITH READ LOCK 

To prevent all write operations, including the InnoDB commit operation. Note that the "lock" can be released only when the client is exited.
Pass:

mysql>SHOW MASTER STATUS

To determine the current binary log file and the displacement (offset)
P, li {white-space: pr

1.5 configure Master information on Slave

mysql> CHANGE MASTER TO-> MASTER_HOST='master_host_name',-> MASTER_USER='replication_user_name',-> MASTER_PASSWORD='replication_password',-> MASTER_LOG_FILE='master_bin_log_file_name',-> MASTER_LOG_POS='recorded_log_position';

2. select a Copy format

Each binary log format has its own advantages and disadvantages. for most users, MBR provides the best results. however, when you need to select an SBR or RBR for a specific task, you can use the following comparison to determine which one is more suitable:

Advantages of SBR:

The proven technology from MySQL3.23
Less data is written to the log. when the update or deletion affects many rows, the SBR will use less storage space, which means less time is required for import or recovery.
The log file contains the changes made by all statement operations, so it can be used to audit the database.
Disadvantages of SBR:

Statement Statements (Statements) are not secure for SBR, and not all Statements that modify data can be copied using SBR. any behavior identified is hard to be copied, such as DELETE or UPDATE with LIMIT or ORDER
INSERT... SELECT requires more row locks than RBR
You need to scan the UPDATE of the entire table (because no index is used in the WHERE clause) to lock more rows than RBR.
For InnoDB, the INSERT with AUTO_INCREMENT will block other non-conflicting INSERT statements.
For complex statements, slave must evaluate and execute before updating or inserting, while RBR only needs to run different statements to apply them.
The stored procedure executes the same NOW ()
The specified UDFs must be applied to all slaves.
The tables on the master and slave must be (almost) the same
RBR advantages:

All changes can be copied. this is the safest replication mode, but the mysql database will not be copied, and mysql will be considered as a special node database.
This technology is the same as many other database management systems. Therefore, many understandings on other systems can be transferred to MySQL.
The Master needs less locking to execute: INSERT... SELECT, INSERT contains AUTO_INCREMENT, and WHERE does not use the UPDATE/DELETE key value.
Slaves requires less lock when executing INSERT, UPDATE/DELETE operations.
Disadvantages of RBR:

RBR is bound to generate more log data.
You cannot use log to know which statements are executed, but you can use mysqlbinlog to check which data is changed.
Related commands

• Mysql> show slave hosts -- View all Slave information connected to the Master
• Mysql> show master status -- View Master status information
• Mysql> show slave status -- View Slave status information
• Mysql> show binary logs -- View all binary logs
• Mysql> show binlog events [IN log_file] -- view events IN binary logs