MySQL 5.6 Replication Replication FAQ

Please refer to MySQL official documentation reference Manual, version 5.6.10.

Replication allows data to be copied from one MySQL database (Master Master Library) to another or multiple MySQL databases (slave from the library). By default, replication is asynchronous, and slave does not need to maintain a persistent connection to get updates from master. This means that updates can work on remote connections, even temporary intermittent connections, such as dial-up connection services. Depending on the configuration, you can replicate all databases on master, or a specified database, or even a specified data table in a database.

Here is a question about the replication feature, which is often asked by novices.

1. Does the slave have to be permanently connected to master?

No need. Slave can shut down or lose connectivity for a few hours or even days, and then reconnect to get updates. For example, you can establish replication on a dial-up connection network, which only has an irregular or short time connection. This also means that at any given point in time, slave is not guaranteed to be fully consistent with master unless you take certain special measures.

To ensure that you can get updates when the slave loses connectivity, you cannot remove binary binary log files that contain incomplete replication content from master. Asynchronous replication can maintain a working condition: slave can continue to read binary binary logs from the point at which the event was last read.

2. Does replication have to turn on network functionality on master and slave?

Yes, you must turn on network functionality on master and slave. If the network is not connected, slave cannot connect to master to transmit binary binary logs. Check the configuration file on each server and the skip-networking option is not turned on.

3. How do I know how long slave has been updated than master? Or, how do you know the date slave was last updated?

Check the Seconds_behind_master column in the SHOW SLAVE STATUS output. When slave's SQL thread executes a read event from master, it modifies its own time for the event's point-in-time timestamp. (That's why timestamp can be well copied.) The time column in the show processlist output shows the difference in the number of seconds between the slave SQL thread's last replication event and the actual time of the slave server in the timestamp. (This sentence is very hard to translate.) You can use this value to determine the date when the event was last copied. If your slave has been disconnected from master for 1 hours and then reconnected, you can immediately see a large time value, such as 3600, in the Times column in the SHOW processlist output. This is because slave is executing statements that are more than 1 hours ago.

4. How do I force Master to block updates until slave can keep up?

Execute on Master:

Mysql> FLUSH TABLES with READ LOCK; mysql> SHOW MASTER STATUS;

Records the value of the copied coordinates in the show statement output. (The file name and location of the current binary binary log file)

Execute in Slave: (parameter uses the value obtained in the previous step)

mysql> SELECT master_pos_wait (' Log_name ', log_pos);

The SELECT statement remains running until slave is completed and synchronized with master based on the specified log file and location, and then the SELECT statement returns.

In master execution: (unblock update limit)

Mysql> UNLOCK TABLES;

5. What issues do I need to know about setting up two-way replication?

MySQL replication does not currently support any locking protocols between master and slave to guarantee the atomicity of updates across servers. (atomicity: All steps that are part of a transaction either occur or do not occur.) For example, client a updates on Master1, and at the same time, before the update propagates to Master2, client B also makes a different update to client A on Master2, so when client A's update arrives at Master2, The table data it is going to handle will be different from the Master1 on the Master2, and the same problem will be encountered when the updates on the Master1 are propagated to the. This means that you cannot connect two servers together for two-way replication unless you are sure that your updates are not order-independent, or that you take some methods in the client code to handle unordered updates.

You should also know that two-way replication does not actually improve performance, and each server needs to make the same number of updates as if you had only one server. The only difference is that a bit of lock contention can be reduced because updates from another server are in sequence in slave. Even so, this benefit can be offset by network delays.

6. How to use replication to improve the performance of your system?

Set up a server as Master, and all writes are done on it. Then set up as many slave servers as possible in the budget and rack capacity range, allocating read operations on the master and slave servers. You can also start slave with the following parameters and get a speed boost on the slave side. Slave uses a non-transactional MyISAM table to replace the InnoDB table, eliminating the overhead of transactions for higher speeds.

7. How do I write client code to use performance-enhanced replication within my app?

Refer to "Application replication in scale-out", which is a good picture.

8. When and how far can MySQL replication improve the performance of my system?

MySQL replication can maximize performance in such a system: frequent read operations, infrequent write operations. In principle, with single-master and multi-slave scenarios, you can extend the system by adding slave to the bandwidth of the network, or your master cannot withstand the increased load of updates.

To determine how many slave you want to use before you can stabilize the benefits you have, determine how much performance your site can achieve, you must be aware of your query patterns, and then empirically determine the total amount of reads and writes on typical master and slave. (After a large paragraph of the content of the example is not translated, lazy ... ^_^)

9. How can I use replication to provide redundancy or high availability?

How you implement redundancy depends entirely on your app and app environment settings. High-availability scenarios (failover) require proactive monitoring, and the use of custom scripts or third-party tools to provide MySQL with failover to slave support.

To handle this process manually, you should be able to switch from a failed master to a pre-configured slave, modify the DNS settings, and make your application connect to the new server.

10. How do I set Master to use a binary binary log format based on statement statement-based or with data row row-based?

Check the Binlog_format system variables:

mysql> SHOW VARIABLES like ' Binlog_format ';

The displayed value may be one of STATEMENT, ROW, MIXED. Mixed mode uses row-row-based-based replication, but automatically switches to a statement-statement-based-based log under certain conditions.

11. How do I set slave to use row row-based-based replication?

Slave automatically learns which format should be used.

12. How can I prevent the GRANT and REVOKE statements from being replicated to the slave server?

The boot server uses the--replicate-wild-ignore-table=mysql.% option to ignore the data tables in the replication MySQL database.

13. Can replication work across platforms? (such as master running on Linux, while slaves is running on Mac OS X or Windows)

Yes! (I like that)

14. Can replication work on different hardware architectures? (such as master running on a 32-bit platform while slaves is running on the platform)

Yes! (I'd like that more.)

MySQL 5.6 Replication Replication FAQ