MySQL 5.6 Replication FAQ

MySQL 5.6 Replication FAQ

For the original article, see MySQL official Reference Manual, version 5.6.10.
The replication function allows you to copy data from one MySQL database (master database) to another or multiple MySQL databases (slave database ). By default, replication is asynchronous, and slave does not need to maintain a persistent connection to obtain updates from the master. This means that the update can work on remote connections or even temporary intermittent connections, such as the dial-up connection service. Depending on the configuration, you can copy all the databases on the master, or the specified database, or even the specified data table in a database.
The following are frequently asked questions about the replication function.
1. Must the slave be permanently connected to the master?
No. Slave can shut down or lose connection for several hours or even days, and then reconnect for updates. For example, you can create a copy on the dial-up connection network, and the dial-up network only has a connection of an indefinite or short time. This also means that slave cannot be completely consistent with the master at any given point in time unless you take some special measures.
To ensure that you can get updates when slave loses connection, you cannot remove binary log files that contain unfinished copies from the master. Asynchronous replication can maintain the condition that slave can continue to read binary logs from the last event point.
2. Do I have to enable the network function on the master and slave for replication?
Yes, you must enable the network function on the master and slave. If the network is not connected, slave cannot connect to the master to transmit binary logs. Check the configuration file on each server. The skip-networking option is not enabled.
3. How do I know how long the slave lags behind the master node? Or, how do I know the date of the last slave update?
Check the Seconds_Behind_Master column in the output of show slave status. When the slave SQL thread executes a read event from the master, it modifies its time to Timestamp. (This is why TIMESTAMP can be copied well .) In the Time column of the show processlist output, the number of seconds between the Time point timestamp in the last replication event of the slave SQL thread and the real Time of the slave server is displayed. (This sentence has been translated very hard .) You can use this value to determine the date of the last copy event. If your slave has been disconnected from the master for one hour and then reconnects, you can immediately see a large Time value in the Time column of the show processlist output, such as 3600. This is because slave is executing the statement more than one hour ago.
4. How to force the master to stop updates until the slave can be followed up?
Run the following command on the master:
Mysql> flush tables with read lock;
Mysql> show master status; record the value of the replication agent in the output of the SHOW statement. (File name and location of the current binary log file)
Run the following command on slave: (the parameter uses the value obtained in the previous step)
Mysql> SELECT MASTER_POS_WAIT ('Log _ name', log_pos); this SELECT statement keeps running until slave completes synchronization with the master Based on the specified log file and location, then the SELECT statement returns.
Run the following command on the master node: remove the update restriction)
Mysql> unlock tables; 5. Set bidirectional replication. What do I need to know?
MySQL replication currently does not support any lock protocols between the master and slave to ensure the atomicity of cross-Server distribution updates. (Atomicity: all the steps or actions that are part of a transaction do not occur, or do not occur .) For example, client A makes an update on master1. At the same time, before the update is propagated to master2, client B also makes A different update on master2. Therefore, when the update of client A reaches master2, the table data to be processed will be different from the update on master1, and the update on master2 will also face the same problem. This means that, unless you are sure that your updates are irrelevant to the order, or you take some methods in the client code to handle unordered updates, you cannot connect the two servers into two-way replication.
You should also know that two-way replication does not actually improve more performance. Each server needs to make the same number of updates, just as if you only have one server. The only difference is that the lock competition can be reduced because updates from another server are sequential on the slave. Even so, this benefit may be offset by network latency.
6. How can I use replication to improve system performance?
Set a server as the master, and all write operations are completed on it. Then, you can set as many slave servers as possible within the budget and rack capacity range to allocate read operations to master and slave servers. You can also set the following parameters to enable slave to speed up the slave. Slave replaces InnoDB tables with non-transactional MyISAM tables to eliminate the overhead of transactions for higher speeds.
-- Skip-innodb
-- Low-priority-updates
-- Delay-key-write = ALL 7. How can I write client code in my application to use performance-enhanced replication?
Refer to "using replication for horizontal scaling". The figure below is good.

8. When can MySQL replication improve the performance of my system?
MySQL replication can achieve the maximum efficiency in such a system: frequent read operations and infrequent write operations. In principle, using the single master and multi-slave solutions, you can increase the number of slave instances to extend the system until the network bandwidth is exceeded, or your master cannot withstand the load increase of updates.
To determine how much slave you need to use before the benefits are stabilized and how much Performance Improvement your site can achieve, you must know your query mode, then, the total number of reads and writes on the typical master and slave is determined based on experience. (I will not translate the content in the next section, so I am lazy... ^_^)
9. How can I use replication to provide redundancy or high availability?
How to implement redundancy depends on your application and application environment settings. High-availability solutions (Failover) require proactive monitoring and use custom scripts or third-party tools to support MySQL migration to slave upon failure.
To manually handle this process, you should be able to switch from a faulty master to a pre-configured slave, modify DNS settings, and connect your application to a new server.
10. How do I set whether the master uses the statement-based or the data row-based binary log format?
Check binlog_format system variables:
Mysql> show variables like 'binlog _ format'; the displayed value may be one of STATEMENT, ROW, and MIXED. In MIXED mode, row-based replication is used, but logs based on statement-based are automatically switched under specific conditions.
11. How do I configure slave to use row-based replication?
Slave will automatically know which format should be used.
12. How can I prevent the GRANT and REVOKE statements from being copied to the slave server?
Start the server and use the -- replicate-wild-ignore-table = mysql. % option to ignore copying data tables in the mysql database.
13. Can replication work across platforms? (For example, if the master runs in Linux, and slaves runs on Mac or Windows)
Yes! (I like this)
14. Can replication work on different hardware architectures? (For example, if the master runs on a 64-bit platform, and slaves runs on a 32-bit platform)
Yes! (I like it more)