Tips on mysql

1. How to solve the problem of slow import of large batches of disordered data into InnoDB?

Because of the primary key clustered index relationship, InnoDB is slower and slower to import without a primary key or non-sequence primary key. How can we quickly migrate data to InnoDB? With the power of MyISAM, It is very reliable. First, we should close the Buffer Pool of InnoDB, leave the memory empty, create a MyISAM table without any indexes, and then just insert it. concurrent_insert = 2, concurrent Insertion at the end of the file. The speed is just now. After the insertion is complete, alter table adds the index. Remember that there is ENGINE = InnoDB and MyISAM is switched to InnoDB, this is far faster than inserting unordered data into InnoDB directly.

2. Under ROW-based dual Master replication, how can we quickly perform mass correction?

Under the dual Master structure of a <-> B, assuming that only one server provides services, this is A common architecture and requires mass data correction. How can we do it as quickly as possible? Can I use a stored procedure for batch submission? There are many restrictions. Sometimes one or more SQL statements cannot be split into several segments. What should I do? Isn't binlog a good tool ?! In the ROW format, Slave directly uses the Handler API in the application, and does not use SQL parsing. The speed is very fast, basically I/O operations, then we can directly execute the corrected SQL statement on the slave database, and the ROW binlog generated will be uploaded to the host, which will soon be completed, basically faster than the write stored procedure.

3. How does ROW-based Replication implement replicate-do-db without database names?

Although MySQL has the replicate-do-db parameter, the "db. table" method must be used in the binlog of the ROW format to take effect. USE is invalid for the ROW format. Now I have an Instance. I only need to copy several databases of the Master, but it is in ROW format and SQL does not use the db prefix. What should I do? This can be done by exporting the database required by the master database, and exporting the database structure that is not required. Import the data and Structure in Slave and configure skip-slave-errors = all, in this way, as long as the database has a table structure, the binlog copied by the Master node does not report that the table cannot be found, and the replication will not be blocked. However, if no data is found during UPDATE/DELETE, the system will skip the error, indirectly implements replicate-do-db.

4. A <-> B-> C-> D structure switch to A <-> B, C <-> D structure. How can I avoid the constant increase of Slave_lag?

In this case, a dual-Master cluster is usually separated from a dual-Master cluster, for example, a part of the database is separated from the original cluster. Switching B-> C to C <-> D too fast may easily lead to slave_lag in the master and slave databases and keep increasing because of the SQL statements produced by cluster A <-> B, along with server_id to C-> D in the M-S, when A, B generated SQL in C, D has not been fully digested to CHANGE MASTER to C <-> D, this will cause the write SQL to be transmitted back and forth between C and D, because C and D both think that this SQL is not produced by themselves, so it is not destroyed. After they are executed, they are written into the binlog, as a result, Slave_Lag continues to grow.
The method to avoid this is very simple. After some data is written to C, the replication of B-> C will be disconnected first. Wait a moment and check that Slave_Lag is no longer available on D, change master to C <-> D, so that the SQL statements passed by A and B are completely digested.

5. How can I delete many duplicate data in a table as quickly as possible?

When you need to add a unique index to some fields in the table, and the fields need to be cleaned up repeatedly, many DBAs should have encountered this problem. In general, I always want to keep only one record in the database and delete others. However, such an SQL statement is always inefficient to write. What should I do? In fact, you can change your mind by selecting one of the duplicates, saving them to a temporary table, deleting all the duplicates in the original table, and then inserting all the databases in the temporary table into the original database, this is a common and efficient approach.