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]部分,增加(去掉注釋)下列選項:

1. [mysqld] 
2. log-bin=mysql-bin 
3. server-id=1 

修改后,重啟mysql服務,如果沒有設置server-id或者是設置其為默認值 0，master將會拒絕所有slave的連接請求,使用InnoDB的事務在進行復制的時候,應該設置innodb_flush_log_at_trx_commit=1和sync_binlog=1以獲取最佳的穩定性,確保 skip-networking選項是禁止的,否則如果網絡被禁止了,從庫將無法與主庫進行交流.

主庫中需要為從庫創建連接用戶,主庫上的任何具有REPLICATION SLAVE權限的用戶都可以作為從庫的連接用戶.

1. mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; --Vevb.com 
2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com'; 

如果在主庫上已經存在數據了，在與從庫進行同步之前，必須先停止主庫上的處理進程，然后獲取當前二進制日志文件的坐標并dump它的數據。

下面的步驟用于獲取主庫的二進制日志的坐標,使用命令行控制臺連接到主庫上,執行如下命令：

mysql> FLUSH TABLES WITH READ LOCK;

使用 UNLOCK TABLES 釋放鎖:

1. mysql > SHOW MASTER STATUS; 
2. +------------------+----------+--------------+------------------+ 
3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
4. +------------------+----------+--------------+------------------+ 
5. | mysql-bin.000003 | 73       | test         | manual,mysql     | 
6. +------------------+----------+--------------+------------------+ 

如果在啟用log-bin之前,主庫已經運行一段時間了,則使用SHOW MASTER STATUS命令查看到的日志文件名和位置將會是空的,在這種情況下,在從庫中指定日志文件和位置分別為''和4.

如果在進行從庫同步復制之前,主庫中已經存在數據了,請保持讀鎖,使用下面的方法將主庫的數據復制到從庫中,使用mysqldump創建要復制的所有數據庫的一個快照,然后導入到從庫中.

1. # 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的話,你可以在從庫上進行二進制日志備份和崩潰后的恢復,或者將從庫作為一個復雜的復制拓撲網絡的一部分(例如,從庫作為一個其它從庫的主庫).

建立從庫到主庫的連接:

1. mysql> CHANGE MASTER TO 
2.     ->     MASTER_HOST='master_host_name', 
3.     ->     MASTER_USER='replication_user_name', 
4.     ->     MASTER_PASSWORD='replication_password', 
5.     ->     MASTER_LOG_FILE='recorded_log_file_name', 
6.     ->     MASTER_LOG_POS=recorded_log_position; 

例如:

1. 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:

1. mysql> show variables like 'server_id'; 
2. +---------------+-------+ 
3. | Variable_name | Value | 
4. +---------------+-------+ 
5. | server_id     | 0     | 
6. +---------------+-------+ 
7. 1 row in set (0.01 sec)