MySQL5.6主從復制(讀寫分離)方案

?

MySQL5.6主從復制(讀寫分離)方案

https://yq.aliyun.com/articles/24255

?

摘要：?一、前言：為什么MySQL要做主從復制（讀寫分離）？ 通俗來講，如果對數據庫的讀和寫都在同一個數據庫服務器中操作，業務系統性能會降低。 為了提升業務系統性能，優化用戶體驗，可以通過做主從復制（讀寫分離）來減輕主數據庫的負載。

一、前言：為什么MySQL要做主從復制（讀寫分離）？

通俗來講，如果對數據庫的讀和寫都在同一個數據庫服務器中操作，業務系統性能會降低。

為了提升業務系統性能，優化用戶體驗，可以通過做主從復制（讀寫分離）來減輕主數據庫的負載。

而且如果主數據庫宕機，可快速將業務系統切換到從數據庫上，可避免數據丟失。

二、MySQL主從復制（讀寫分離）和集群的區別：

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

1、主從復制（讀寫分離）：一般需要兩臺及以上數據庫服務器即可（一臺用于寫入數據，一臺用于同步主的數據并用于數據查詢操作）。

局限性：

（1）配置好主從復制之后，同一張表，只能對一個服務器寫操作。如果在從上執行了寫操作，而之后主也操作了這張表，或導致主從不同步；據說可以配置成主主方式，但我還沒有研究到。

（2）主數據庫服務器宕機，需要手動將業務系統切換到從數據庫服務器。無法做到高可用性（除非再通過部署keepalive做成高可用方案）。

2、集群是由N臺數據庫服務器組成，數據的寫入和查詢是隨機到任意一臺數據庫服務器的，其他數據庫服務器會自動同步數據庫的操作。

任何一臺數據庫宕機，不會對整個集群造成大的影響。

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

三、回歸正題，接下來開始MySQL5.6.12的主從復制教程：

1、MySQL5.6開始主從復制有兩種方式：基于日志（binlog）；基于GTID（全局事務標示符）。

需要注意的是：GTID方式不支持臨時表！所以如果你的業務系統要用到臨時表的話就不要考慮這種方式了，至少目前最新版本MySQL5.6.12的GTID復制還是不支持臨時表的。

所以此篇教程主要是告訴大家如何通過日志（binlog）方式做主從復制！

2、MySQL官方提供的MySQL Replication教程：

http://dev.mysql.com/doc/refman/5.6/en/replication.html

這個官方教程強烈建議大家閱讀（需要一定的英語閱讀能力哦！不行就google翻譯后再閱讀吧~）。

3、準備工作：

（1）配置MySQL主從復制（讀寫分離）之前，需要在主從兩臺服務器先安裝好MySQL5.6。

（2）目前最新的MySQL5.6 GA版本是MySQL5.6.12（點此下載MySQL5.6.12源碼包）。

個人推薦Linux（RedHat/CentOS 6.4）源碼編譯安裝，具體可以看本站這篇教程：RedHat/CentOS源碼編譯安裝MySQL5.6.12

（3）注意：

（a）如果你需要用于生產環境，安教程安裝MySQL時不要急著做mysql啟動操作。建議把mysql初始化生成的/usr/local/mysql/mysql.cnf刪除，然后把你優化好的mysql配置文件my.cnf放到/etc下。

（b）建議主備兩臺服務器在同一局域網，主備兩臺數據庫網絡需要互通。

（4）我的環境：

主數據庫IP：192.168.100.2

從數據庫IP：192.168.100.3

4、修改主數據庫的的配置文件：

1?????[mysqld]?
2?????server-id=1?
3?????log-bin=mysqlmaster-bin.log?
4?????sync_binlog=1?
5?????#注意：下面這個參數需要修改為服務器內存的70%左右?
6?????innodb_buffer_pool_size?=?512M?
7?????innodb_flush_log_at_trx_commit=1?
8?????sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO?
9?????lower_case_table_names=1?
10?????log_bin_trust_function_creators=1

修改之后要重啟mysql：

#?/etc/init.d/mysql?restart

附一個我已優化過的主數據庫配置文件：點此下載

5、修改從數據庫的的配置文件（server-id配置為大于1的數字即可）：

1?????[mysqld]?
2?????server-id=2?
3?????log-bin=mysqlslave-bin.log?
4?????sync_binlog=1?
5?????#注意：下面這個參數需要修改為服務器內存的70%左右?
6?????innodb_buffer_pool_size?=?512M?
7?????innodb_flush_log_at_trx_commit=1?
8?????sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO?
9?????lower_case_table_names=1?
10?????log_bin_trust_function_creators=1

修改之后要重啟mysql：

?#?/etc/init.d/mysql?restart

附一個我已優化過的從數據庫配置文件：點此下載

6、SSH登錄到主數據庫：

（1）在主數據庫上創建用于主從復制的賬戶（192.168.100.3換成你的從數據庫IP）：

1?????#?mysql?-uroot?-p?
2?????mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'repl'@'192.168.100.3'?IDENTIFIED?BY?'repl';

（2）主數據庫鎖表（禁止再插入數據以獲取主數據庫的的二進制日志坐標）：

mysql>?FLUSH?TABLES?WITH?READ?LOCK;

（3）然后克隆一個SSH會話窗口，在這個窗口打開MySQL命令行：

1?????#?mysql?-uroot?-p?
2?????mysql>?SHOW?MASTER?STATUS;?
3?????+------------------------+----------+--------------+------------------+-------------------+?
4?????|?File???????????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|?
5?????+------------------------+----------+--------------+------------------+-------------------+?
6?????|?mysqlmaster-bin.000001?|??????332?|??????????????|??????????????????|???????????????????|?
7?????+------------------------+----------+--------------+------------------+-------------------+?
8?????1?row?in?set?(0.00?sec)?
9?????mysql>?exit;

在這個例子中，二進制日志文件是mysqlmaster-bin.000001，位置是332，記錄下這兩個值，稍后要用到。

（4）在主數據庫上使用mysqldump命令創建一個數據快照：

#mysqldump?-uroot?-p?-h127.0.0.1?-P3306?--all-databases??--triggers?--routines?--events?>all.sql?
#?接下來會提示你輸入mysql數據庫的root密碼，輸入完成后，如果當前數據庫不大，很快就能導出完成。

（5）解鎖第（2）步主數據的鎖表操作：

mysql>?UNLOCK?TABLES;

7、SSH登錄到從數據庫：

（1）通過FTP、SFTP或其他方式，將上一步備份的主數據庫快照all.sql上傳到從數據庫某個路徑，例如我放在了/home/yimiju/目錄下；

（2）從導入主的快照：

#?cd?/home/yimiju?
#?mysql?-uroot?-p?-h127.0.0.1?-P3306?<?all.sql?
#?接下來會提示你輸入mysql數據庫的root密碼，輸入完成后，如果當前數據庫不大，很快就能導入完成。

（3）給從數據庫設置復制的主數據庫信息（注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值）：

#?mysql?-uroot?-p?
mysql>?CHANGE?MASTER?TO?MASTER_HOST='192.168.100.2',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=332;?
#?然后啟動從數據庫的復制線程：?
mysql>?START?slave;?
#?接著查詢數據庫的slave狀態：?
mysql>??SHOW?slave?STATUS?\G?
#?如果下面兩個參數都是Yes，則說明主從配置成功！?
Slave_IO_Running:?Yes?
Slave_SQL_Running:?Yes

（4）接下來你可以在主數據庫上創建數據庫、表、插入數據，然后看從數據庫是否同步了這些操作

版權聲明：本文內容由互聯網用戶自發貢獻，本社區不擁有所有權，也不承擔相關法律責任。如果您發現本社區中有涉嫌抄襲的內容，歡迎發送郵件至：yqgroup@service.aliyun.com?進行舉報，并提供相關證據，一經查實，本社區將立刻刪除涉嫌侵權內容。