MySQL 一些tips

    发现不知道怎么了，工作久了好多知识都忘了，也无怪乎，system admin就是这样的特点：学的多，杂，忘得快，闲话少说，最近在搞mysql的一些事，也对此做个总结，

1，备份mysql假如是Innodb，那么single-transaction是非常重要的一个option,曾有同事在一个关键的master上备份某个db然后给slave同步没加这个导致锁表，所有的用户都无法写入信息，打开general_log可以看到这个参数所做的事情，

1.set session transaction isolation level repeatable read
2.start transaction /*!40100 with consistent snapshot*/
3.unlock tables

可见该参数设置事务隔离级别为重复读，然后获取了一致性的快照，然后释放锁。

细节参见：http://blog.csdn.net/sunny5211/article/details/7896383

官方docs :
The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.3.9.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE)

consistent read
A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of the undo log. This technique avoids some of the locking issues that can reduce concurrency by forcing transactions to wait for other transactions to finish.
With the repeatable read isolation level, the snapshot is based on the time when the first read operation is performed. With the read committed isolation level, the snapshot is reset to the time of each consistent read operation.
Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. Because a consistent read does not set any locks on the tables it accesses, other sessions are free to modify those tables while a consistent read is being performed on the table.
For technical details about the applicable isolation levels, see Section 14.3.9.2, “Consistent Nonlocking Reads”.

但是DDL语句是无法隔离的，比如alter、drop、rename和truncate等。

其实备份可以在某个slave上show slave status, 然后暂停stop slave,记下 Relay_Master_Log_File & Exec_Master_Log_Pos  ，mysqldump备份出来，然后load到新的slave，接着change master to ，记得log和pos写上述的2个，至于为什么可以看m/s复制原理以及相应的解释，

·       Read_Master_Log_Pos

·       The position in the current master binary log file up to which the I/O thread has read.

Relay_Log_File

·       The name of the relay log file from which the SQL thread is currently reading and executing.

2. master-data 这个可以自动获取binlog和pos，但是也有不好的地方，同样看log(执行flush，需要reload权限,flush logs也会锁表）

FLUSH TABLES
FLUSH TABLES WITH READ LOCK
SHOW MASTER STATUS

            第一行，关闭表，刷缓存的数据到table,第二行：

             Closes all open tables and locks all tables for all databases with a global read
               lock until you explicitly release the lock by executing UNLOCK TABLES. This is a very
               convenient way to get backups if you have a file system such as Veritas or ZFS tha
               t can take snapshots in time.      
可以看到该操作需要获取一个全局的read lock ,假定在备份之前某个Huge select没完成，那么master-data一定会等待，严重的是，在没有执行完FLUSH TABLES WITH READ LOCK之前，其他的所有update、delete等更改操作都将会被阻塞！万一是full scan 而且无索引，那么db类似瘫痪了，其实在《high performance MySQL》作者也有说：
http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-with-read-lock/

3. Innodb 优化有关的几个重要参数，

4.远程大量load data,导致网卡带宽跑满,可以考虑以下方法：
1.删index之后再create .
2.分成insert into ,bulk insert 然后sleep ,这个可以降低slave延迟，但估计效率不高。

3.在MYISAM表中插入大量记录的时候先禁用到KEYS后面再建立KEYS,具体表现语句：
Alter TABLE TABLE1 DISABLE KEYS;Alter TABLE TABLE1 ENABLE KEYS;
而对于INNNODB 表在插入前先 set autocommit=0;完了后：set autocommit=1;这样效率比较高。
4.还有innodb的auto_trx_之类的参数对结果影响很大。

====未完====