mysql讀寫分離

標籤：最佳化   進入   sftp   ast   電池   stat   解決方案   server   coff   

為什麼MySQL要做主從複製（讀寫分離）？

通俗來講，如果對資料庫的讀和寫都在同一個資料庫伺服器中操作，業務系統效能會降低。 
為了提升業務系統效能，最佳化使用者體驗，可以通過做主從複製（讀寫分離）來減輕主要資料庫的負載。 
而且如果主要資料庫宕機，可快速將業務系統切換到從資料庫上，可避免資料丟失。

MySQL主從複製（讀寫分離）和叢集的區別：

我對MySQL也是剛開始研究，不是很專業。我的理解是：

主從複製（讀寫分離）：

一般需要兩台及以上資料庫伺服器即可（一台用於寫入資料，一台用於同步主的資料並用於資料查詢操作）。 
局限性：

配置好主從複製之後，同一張表，只能對一個伺服器寫操作。如果在從上執行了寫操作，而之後主也操作了這張表，或導致主從不同步；據說可以配置成主主方式，但我還沒有研究到。主要資料庫伺服器宕機，需要手動將業務系統切換到從資料庫伺服器。無法做到高可用性（除非再通過部署keepalive做成高可用方案）。叢集是由N台資料庫伺服器組成，資料的寫入和查詢是隨機到任意一台資料庫伺服器的，其他資料庫伺服器會自動同步資料庫的操作。

任何一台資料庫宕機，不會對整個叢集造成大的影響。

局限性

我經過測試才知道目前mysql叢集版本（MySQL Cluster）只能對NDB儲存引擎的資料進行叢集同步，如果是INNODB或其他的MySQL儲存引擎是不行的。這個也導致了我放棄了在業務系統中應用這種方案。

迴歸正題

MySQL5.6開始主從複製有兩種方式：基於日誌（binlog）；基於GTID（全域事務標示符）。 
需要注意的是：GTID方式不支援暫存資料表！所以如果你的業務系統要用到暫存資料表的話就不要考慮這種方式了，至少目前最新版本MySQL5.6.12的GTID複製還是不支援暫存資料表的。 
所以此篇教程主要是告訴大家如何通過日誌（binlog）方式做主從複製！ 
Master :192.168.15.141 
Slave:192.168.15.142 
如何安裝mysql我就不細說了，請看linux下安裝mysql

設定檔

master

vi /etc/my.cnf[mysqld] server-id = 1log-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1 datadir=/var/lib/mysql character-set-server=utf8 init_connect=‘SET NAMES utf8‘

什麼是sync_binlog？ 
當每個sync_binlog’th寫入該二進位日誌後，MySQL伺服器將它的二進位日誌同步到硬碟上(fdatasync())。請注意如果在autocommit模式，每執行一個語句向二進位日誌寫入一次，否則每個事務寫入一次。 預設值是0，不與硬碟同步。值為1是最安全的選擇，因為崩潰時，你最多丟掉二進位日誌中的一個語句/事務；但是，這是最慢的選擇(除非硬碟有電池備份緩衝，從而使同步工作較快)。 
slave:

server-id=2 datadir=/var/lib/mysql character-set-server=utf8 init_connect=‘SET NAMES utf8‘

在master資料庫設定用來同步的slave使用者權限啟動資料庫

[root@localhost ~]# service mysqld start

進入mysql

[[email protected] ~]# mysql -uroot -p123456mysql> GRANT REPLICATION SLAVE ON *.* TO ‘<root>‘@‘<192.168.15.142>‘ IDENTIFIED BY ‘<123456>‘;Query OK, 0 rows affected (0.10 sec)

主要資料庫鎖表（禁止再插入資料以擷取主要資料庫的的二進位日誌座標）

>FLUSH TABLES WITH READ LOCK;

在主要資料庫上使用mysqldump命令建立一個資料快照

[root@localhost ~]# mysqldump -u root -pnew-password  --databases drp > /root/test.sql

解鎖第（2）步主要資料的鎖表操作SSH登入到從資料庫

通過FTP、SFTP或其他方式，將上一步備份的主要資料庫快照all.sql上傳到從資料庫某個路徑，例如我放在了/root/目錄下；

從匯入主的快照

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -P3306< test.sqlEnter password: You have mail in /var/spool/mail/root

給從資料庫設定複製的主要資料庫資訊（注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值）

MASTER_LOG_FILE和MASTER_LOG_POS在主要資料中查看

mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.15.141‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘new-password‘,MASTER_LOG_FILE=‘mysqlmaster-bin.000001‘,MASTER_LOG_POS=3931mysql> START slave;Query OK, 0 rows affected (0.74 sec)

查看重資料庫的狀態

mysql> SHOW slave STATUS \G

如果下面兩個參數都是Yes，則說明主從配置成功！ 
 
slave_sql_running:負責自己的slave mysql進程 
Slave_IO_Running:負責與主機的io通訊 
查看報錯： 
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 
意思就是從上的server_id和主的一樣的，經查看發現從上的/etc/my.cnf中的server_id=1這行我沒有注釋掉（在下面複製部分我設定了server_id），於是馬上把這行注釋掉了，然後重啟mysql，發現還是報同樣的錯誤。 
解決方案： 
查看主從資料庫中my.conf的server_id是否重複，如果重複，改一下再重啟。 
這樣當主要資料庫插入資料，建立表或者建立資料庫時，就可以自動同步到從資料庫了~

mysql讀寫分離