Mysql's basic process for replicating database data is detailed _mysql

Copy

Replication is the process of copying data from one MySQL server (master) to another or multiple MySQL servers (slaves). Replication is performed asynchronously--slaves servers do not need to continuously maintain connections to receive master data. Depending on the configuration, You can copy all databases, or a specified database, or even a table specified by a database.

The purpose of using the replication feature is to:

Outward-extending solution-improves performance by spreading the load across multiple servers. In this environment, all write and update operations are performed on the master server, while the read operation occurs on one or more slaves servers.
Data security-Because the data is replicated to slave, and slave can suspend the replication process, you can backup on the slave server without destroying master data
Analysis-real-time data is created on master, but data analysis can be slave on the server without impacting Master's performance
Long-distance data distribution-if a branch requires a copy of the data from the main company to work, you can create a local copy by copying without having to access the master server for a long time
MySQL replication is one-way asynchronous, which is exactly the reverse of the MySQL cluster replication feature. MySQL5.5 supports semi-synchronous (semisynchronous), which is not immediately returned after a commit on master. Instead, wait until at least one slave confirms that the current transaction has been received and recorded, and then returns.

The best replication methods are related to how the data is presented and the storage engine selected, and there are two core replication formats: SBR (Statement Based Replication)--Copy all SQL statements, and RBR (Row Based Replication)- Only the changed rows are replicated. Of course, there are three options to choose from: MBR (Mixed Based Replication), which is also the default mode for later versions of MySQL5.5.

Replication Configuration

Replication between MySQL servers uses the binary logging mechanism. Updates and changes to master are recorded as events (event) in the log, and the information in the log is recorded in different formats depending on the change. Slaves is configured to read logs from Master and executes the events in the binary log to the slave local database. Once master initiates binary logging, all statement operations are logged, and each slave receives a copy of the entire log content. Slave's responsibility is to decide which statement in the log needs to be executed, And we can't simply log certain events by configuring master. If you do not specify otherwise, all events in the primary server binary log are executed on slave. If necessary, you can also configure slave to apply only events from a specific database or table.

Each slave maintains a record of the binary log file and records where it has been read and processed. This indicates that multiple slaves can connect to master and perform different parts of the log because slave controls the process itself, disconnecting and connecting individual slave, Does not affect the operation of master. Also because each slave records the position in the binary log, the slaves can disconnect, connect, and start at the record location.

Master and each slave must give a unique ID (possibly using server_id), and must also inform the host slave its master, the log file name and location (position). You can pass the change MASTER in a session To change, details are recorded in the Master.info file.

1. How to start replication

1.1 Create a user for replication

Each slave must be connected to master using a standard MySQL username and password, and any account can be granted replication slave permissions. Although it is not necessary to create a single user for replication, But you need to be clear that the username and password of the account used for replication are stored in plaintext in the master.info, so it is better to create one for security reasons. such as:

      Mysql>grant REPLICATION SLAVE on *.* to ' repl ' @ ' 192.158.1.100 ' identified by ' testpass ';

That is to create a user named "Repl", the password is "Testpass" account, all the slaves can use the same account, of course, we can also create a login account for each slave.

1.2 Configuration Master

First, you have to turn on Master's binary logging, followed by the master setting up a unique Server-id-1~p, li {white-space:pre-wrap;} A positive integer between 232-1. If you set the following in MY.CNF or My.ini:

      [Mysqld]

      Log-bin=master-bin

      server-id=1

It is important to note that in order to achieve maximum possible stability and consistency in the creation of replication when using InnoDB transactions, you need to use the following two options: Innodb_flush_log_at_trx_commit=1 and Sync_binlog=1. And at the same time ensure that: skip-networking=0 otherwise slave and master will not be able to communicate.

1.3 Configuration Slave

The only thing we need to configure on slave is to specify a unique server-id for slave. The binary logging feature on the slave is not required to open, but can be used for data backup or disaster data recovery on slave, and slave can also be used as part of a more complex replication topology (for example, a slave as Master of other slaver).

1.4 Getting Master Information

In order to configure slave replication, you need to know the current location of master in the binary log so that when the slave begins the replication process, it knows that the event starts at the current point. If data already exists on master, And the data needs to be synchronized to other slaves before the replication starts, then you have to have master stop processing the statements, get the current location, and then export the data. To get master's status information, you need to go through the following steps:

Perform:

To block all write operations, including InnoDB commits. Note that the "lock" can only be released if you exit the connection client at this time.
Pass:

Mysql>show MASTER STATUS

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

1.5 Configuring Master information on the 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. Choice of copy Format

Each binary log format has its own advantages and disadvantages, and for most users, the MBR provides the best results. But when you need to select SBR or RBR for specific tasks, you can determine which is more appropriate by comparing the following:

Advantages of SBR:

From the beginning of the MySQL3.23, the proven technology
Less data is written to the log. When updates or deletions affect many rows, SBR uses less storage space, which means less time to import or restore
The log file contains the changes made by all statement operations and can therefore be used to audit the database
The disadvantages of SBR:

Statement formulation (statements) is unsafe for SBR, and not all statements that modify data can be replicated using SBR. Any behavior that is determined is difficult to replicate, such as delete or update with limit or order by
INSERT ... SELECT requires more number of row locks than RBR
Need to scan the entire table for update (because no index is used in where) to lock more rows than RBR
For InnoDB, using a auto_increment insert blocks other non-conflicting inserts
For complex statements, slave must be evaluated and executed before updating or inserting, and RBR only needs to run a different statement application.
The stored procedure executes the same now ()
The identified UDFs must be applied to all slaves.
The table on master and slave must be (almost) the same
Advantages of RBR:

All changes can be replicated, which is the safest mode of replication. But MySQL database will not be replicated, MySQL will be considered a special node database
This technology, like many other database management systems, can be transferred to MySQL on many other systems.
Master requires less locking to execute: INSERT ... There are auto_increment in the Select,insert, and update/delete with no key values in the where
Slaves requires less locking when performing insert,update/delete
The disadvantage of RBR:

RBR is bound to generate more log data
You can't know what statements are executed through log, and then you can see through mysqlbinlog what data has been changed.
Related commands

• Mysql>show slave hosts--View all slave information connected to master
• Mysql>show Master Status--view master state 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 event in binary log