Some tips on Mysql _mysql

1. Large batch of random data import InnoDB very slow how to solve?

InnoDB because of the relationship between the primary key clustered index, if there is no primary key or primary key not sequence, the import will be slower, how to quickly migrate data to InnoDB? With the power of the MyISAM is very reliable, first close the InnoDB buffer Pool, the memory empty out, build a MyISAM table without any index, and then just insert it, concurrent_insert=2, at the end of the file with the insertion, speed, When the insertion is complete, ALTER table adds the index, remembers the Engine=innodb, and turns the MyISAM to InnoDB, which is much faster than inserting the data directly into the InnoDB.

2. How to quickly and greatly revise a large quantity in a row based dual master copy?

Under the dual master structure of a<->b, assuming that there is only one service available, this is our common architecture, which requires large quantities of data revisions, how to do the fastest? Batch submissions with stored procedures? There are a lot of limitations, and sometimes it is not possible to break one or more of the SQL into a few paragraphs, how to do? Binlog is not a very good tool?! Row format Binlog,slave in the application is the direct use of the handler API, and did not take the SQL parsing, very fast, basically IO operation, then we can directly execute the backup on the SQL, the resulting row binlog to the host, will soon be corrected, are basically faster than write stored procedures.

3. Row format replication How do I implement replicate-do-db without a library name?

Although MySQL has replicate-do-db this parameter, the Binlog must be "db.table" under row format to be effective, and use is not valid for row format. Now I have a instance, I just need to copy a few libraries of master, but in row format, SQL does not use DB prefix, how to do? You can do this, the main library needs to export the library, do not need to export the structure of the library, in the slave to import these data and structure, configuration Skip-slave-errors=all, so master copied over the Binlog, as long as the discovery of a library has a table structure, will not be reported to find the table, will not block replication, but update/delete come over without data will be skipped error, the indirect implementation of the REPLICATE-DO-DB.

4. A<–>B–>C–>D structure switch to A<–>B, c<–>d structure appears slave_lag has been increasing often how to avoid?

This situation is common with a dual master cluster that separates a set of dual master clusters, such as separating a partial library from the original cluster. Too fast switching b–>c to C<–>d can easily lead to the emergence of Slave_lag, and has been growing, because the A&LT;–&GT;B cluster generated SQL, along with server_id to c–>d this m-s, when a, B-generated SQL after C,D has not digested the change master as C<–>d, it will cause this write SQL to transfer back and forth between c,d, because c,d all think that the SQL is not generated by itself, so do not destroy, write their own after the Binlog, So Slave_lag has been growing.
Avoid the method is very simple, part of the write-cut to C, first disconnect b–>c copy, and so on, see D has no slave_lag, and then change master for C<–>d, so a,b pass over the SQL are digested.

5. When there are many duplicate data in the table, how do you delete these duplicate data fastest?

Many DBAs should have encountered a problem where you need to add a unique index to some fields in a table, and there are issues in the field that need to be cleaned up again. Generally in the process of always want to keep only one in the database, the other delete, but such SQL write out is always inefficient, how to do? In fact, you can switch ideas, the repetition of the selection of a come out, save to a temporary table, and then delete all the duplicates in the original table, and then the temporary table of the database all inserted into the original library, which is more general and efficient practice.