mysql实践操作 注意的东西

表设计

MySQL中innodb表主键设计原则

主键设计的原则：

1. 一定要显式定义主键

2. 采用与业务无关的单独列

3. 采用自增列

4. 数据类型采用int，并尽可能小，能用tinyint就不用int，能用int就不用bigint

5. 将主键放在表的第一列

这样设计的原因：

1. 在innodb引擎中只能有一个聚集索引，我们知道，聚集索引的叶子节点上直接存有行数据，所以聚集索引列尽量不要更改，而innodb表在有主键时会自动将主键设为聚集索引，如果不显式定义主键，会选第一个没有null值的唯一索引作为聚集索引，唯一索引涉及到的列内容难免被修改引发存储碎片且可能不是递增关系，存取效率低，所以最好显式定义主键且采用与业务无关的列以避免修改；如果这个条件也不符合，就会自动添加一个不可见不可引用的6byte大小的rowid作为聚集索引

2. 需采用自增列来使数据顺序插入，新增数据顺序插入到当前索引的后面，符合叶子节点的分裂顺序，性能较高；若不用自增列，数据的插入近似于随机，插入时需要插入到现在索引页的某个中间位置，需要移动数据，造成大量的数据碎片，索引结构松散，性能很差

3. 在主键插入时，会判断是否有重复值，所以尽量采用较小的数据类型，以减小比对长度提高性能，且可以减小存储需求，磁盘占用小，进而减少磁盘IO和内存占用；而且主键存储占用小，普通索引的占用也相应较小，减少占用，减少IO，且存储索引的页中能包含较多的数据，减少页的分裂，提高效率

4.将主键放在表的第一列好像是习惯，原因我也不知道，试了下是可以放在其他列的......

数据恢复

最好分库备份后，分库导入，这是最干净的

mysql结构同步

1.利用Navicat实现MySQL数据库结构对比和同步

点击比对后，就会出现同步过程，此时仅仅是比对，还未真正进行同步。点击右下角的运行同步按钮，即可实现源端数据库表结构同步至目标端数据库表结构

2.MySQL结构自动同步工具-schemasync

测试数据生成

我是用tpcc生成20仓库，里有9个表，数据量大概为1.5G左右，我的是KVM服务器，内存是6G，6核，所以速度应该受限了，估计盘也不行~~

5.6

注：开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里

5.7

mysql.user表中的plugin更改成not null，5.7开始不再支持mysql_old_password的认证插件，推荐全部使用mysql_native_password。从低版本升级到5.7的时候，需要处理两个兼容性问题

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);

FLUSH PRIVILEGES;

Statement violates GTID consistency: CREATE TABLE ... SELECT(一般用存储过程替换)

在MySQL5.7里，通过一个新的命令，可以支持在线动态修改，而不须重启mysql进程就生效

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1,db2);

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(db1,db2);

CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(db1.t1);

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(db2.t2);

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('db.t%');

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('db%.a%');

CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=((from_db, to_db));

http://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html

安全

sql更新数据的后悔药 //

show variables like 'autocommit';

set autocommit = 0;

更改后当你UPDATE ，DELECT,DROP操作后猛然发现错误时，可以

rollback；

使数据恢复到没有修改的状态

最后切记，操作完数据确保无误时

commit；

使数据修改生效，不然你直接exit后所有的更改会都没有保存

最好的选择是针对那条特定的语句禁用二进制日志:

　　mysql> SET SQL_LOG_BIN = 0;

　　mysql> # Run local transaction

原理

oracle的logical standby应该采用类似row-based模式 ，而 physical standby既不是statement，也不是 row，而直接应用redo log的物理复制

本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1842412，如需转载请自行联系原作者