MySQL分區與主從的學習筆記

MySQL分區與主從是我們在性能優化上是常用到的幾個知識點了，對於高級管理員對於MySQL分區與主從都是了解的非常深入了，下面我們一起來看看小編在學習MySQL分區與主從一些記錄。

1 分區相關

MySQL5.6中，分區技術只支持水平分區，而不支持垂直分區，分區支持大部分的存儲引擎，但是MERGE, CSV, FEDERATED存儲引擎不支持。

使用KEY或者LINEAR KEY進行分區是支持NDB（網絡數據庫）的，但是其他分區方式不支持。
在MySQL中查看是否是支持分區技術，使用命令 SHOW PLUGINS 進行查看，如果存在名稱為 partition 的插件，狀態為 ACTIVE 則說明分區技術可用。
MySQL5.6支持顯式的分區查詢，如 SELECT * FROM T PARTITION(p0, p1) WHERE c < 5，可以只從p0,p1分區進行查詢

支持的分區類型
：
RANGE 根據給定列的值的范圍進行分區。
LIST 與RANGE類似，不過它通過判斷給定列的值是否在獨立的值的集合中進行分區。
HASH 這種分區根據對列進行操作的自定義表達式的值進行分區。這個自定義的函數可以包含任何可用的MySQL表達式，但是需要產生一個非負的值，擴展類型有LINEAR HASH.
KEY 這種分區方式與HASH類似，區別是只需要提供一列或者多列的值，通過MySQL自己的哈希函數進行計算，擴展類型有LINEAR KEY.
常用的分區方式可以通過日期， 時間 進行分區。當使用KEY或者LINEAR KEY 方式進行分區的時候，可以直接使用TIME, DATETIME, DATE作為分區列而不需要執行任何的修改（常用時間函數：TO_DAYS(), YEAR, TO_SECONDS, WEEKDAY(),MONTH等）。

2 主從相關

主庫和從庫都需要配置一個唯一的ID（server-id)， 每一個從庫都必須配置主庫的host名， 日志文件名和配置文件中的位置。

2.1 配置Master

修改 my.cnf 或者 my.ini 文件，在[mysqld]部分，增加（去掉注釋）下列選項：
[mysqld]
log-bin=mysql-bin
server-id=1

修改後，重啟mysql服務
如果沒有設置server-id或者是設置其為默認值 0，master將會拒絕所有slave的連接請求。 使用InnoDB的事務在進行復制的時候，應該設置innodb_flush_log_at_trx_commit=1和sync_binlog=1以獲取最佳的穩定性。 確保 skip-networking選項是禁止的，否則如果網絡被禁止了，從庫將無法與主庫進行交流。
主庫中需要為從庫創建連接用戶，主庫上的任何具有REPLICATION SLAVE權限的用戶都可以作為從庫的連接用戶。
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
如果在主庫上已經存在數據了，在與從庫進行同步之前，必須先停止主庫上的處理進程，然後獲取當前二進制日志文件的坐標並dump它的數據。
下面的步驟用於獲取主庫的二進制日志的坐標：
使用命令行控制台連接到主庫上，執行如下命令：

mysql> FLUSH TABLES WITH READ LOCK;
使用 UNLOCK TABLES 釋放鎖
 mysql > SHOW MASTER STATUS;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000003 | 73       | test         | manual,mysql     |
 +------------------+----------+--------------+------------------+
 
如果在啟用log-bin之前，主庫已經運行一段時間了，則使用SHOW MASTER STATUS命令查看到的日志文件名和位置將會是空的，在這種情況下，在從庫中指定日志文件和位置分別為''和4.
如果在進行從庫同步復制之前，主庫中已經存在數據了，請保持讀鎖，使用下面的方法將主庫的數據復制到從庫中。
使用mysqldump創建要復制的所有數據庫的一個快照，然後導入到從庫中。
# mysqldump -uroot --lock-all-tables --events --all-databases --master-data > dbdump.db
導入dbdump.db文件，使用命令shell> mysql < dbdump.db

2.2 配置slave

修改配置文件
[mysqld]
server-id=2
在從庫中，為了進行復制過程，並不需要啟用log-bin。但如果在從庫中啟用log-bin的話，你可以在從庫上進行二進制日志備份和崩潰後的恢復，或者將從庫作為一個復雜的復制拓撲網絡的一部分（例如，從庫作為一個其它從庫的主庫）。
建立從庫到主庫的連接：
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
例如：
mysql> change master to master_host='10.58.91.4', master_user='repl', master_password='888888',master_log_file='mysql-bin.000002', master_log_pos=385;
注意： 復制不能使用UNIX socket文件，只能使用TCP/IP連接主庫。
啟動從庫
mysql> start slave;
如果出現無法連接的問題，請檢查是否服務器iptables對端口有限制

3 常用命令

授權任意主機以root方式訪問
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
查看server-id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.01 sec)