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，如需轉載請自行聯系原作者