MYSQL主從復(fù)制和讀寫分享配置

1、MYSQL主從復(fù)制和讀寫分享配置創(chuàng)建時間：2016/4/15 星期五 下午 3:41更新時間：2016/4/18 星期一 下午 4:52作者：pengguangzhi2006  一、準(zhǔn)備工作：（1）配置MySQL主從復(fù)制（讀寫分離）之前，需要在主從兩臺服務(wù)器先安裝好MySQL5.6。（2）目前最新的MySQL5.6 GA版本是MySQL5.6.12。個人推薦Linux（RedHat/CentOS 6.4）源碼編譯安裝，具體可以看本站這篇教程：RedHat/CentOS源碼編譯安裝MySQL5.6.12（3）注意：（a）如果你需要用于生產(chǎn)環(huán)境，安教程安裝MySQL時不要急著做mysql

2、啟動操作。建議把mysql初始化生成的/usr/local/mysql/f刪除，然后把你優(yōu)化好的mysql配置文件f放到/etc下。（b）建議主備兩臺服務(wù)器在同一局域網(wǎng)，主備兩臺數(shù)據(jù)庫網(wǎng)絡(luò)需要互通。（4）我的環(huán)境：主數(shù)據(jù)庫IP：14從數(shù)據(jù)庫IP：23  二、 修改主數(shù)據(jù)庫的的配置文件：mysqldserver-id=1log-bin=mysqlmaster-bin.logsync_binlog=1#注意：下面這個參數(shù)需要修改為服務(wù)器內(nèi)存的70%左右innodb_buffer_pool_size=700Minnodb_flush_log_at

3、_trx_commit=1sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZEROlower_case_table_names=1log_bin_trust_function_creators=1-（1）server-id=1用于配置服務(wù)器ID,是mysql主從集群中的一個唯一區(qū)分號。所有的服務(wù)器都應(yīng)該有自己的唯一ID,如果一個Slave連接到Master，但是和Master有相同的ID,則會產(chǎn)生Master和Slave ID相同的錯誤（2）binlog-do-db

4、 ：指定主從復(fù)制的數(shù)據(jù)庫。（3）log-bin字段給出了二進(jìn)制日志所產(chǎn)生的所有文件的基本名，二進(jìn)制文件可以包含多個文件:（4）log-bin-index.這其實是指定了一個索引文件（純文本，內(nèi)容為文件列表），該索引文件中包含所有二進(jìn)制文件的列表。 如果沒有為log-bin-index設(shè)置默認(rèn)值，則會使用機(jī)器的hostname產(chǎn)生log-bin-index文件，這樣，在服務(wù)器的 hostname改變后，可能會出現(xiàn)無法找到索引文件，從而認(rèn)為二進(jìn)制文件列表為空，導(dǎo)致無法正確的生成二進(jìn)制bin-log。所以，推薦應(yīng)該使用機(jī)器無關(guān)的名字作為bin-log-index的文件名.(可以不填，會自動生成)（5

5、）sync_binlog=1 MySQL服務(wù)器將它的二進(jìn)制日志同步到硬盤上； 默認(rèn)值是0，不與硬盤同步。值為1是最安全的選擇，因為崩潰時，你最多丟掉二進(jìn)制日志中的一個語句/事務(wù)；但是，這是最慢的選擇(除非硬盤有電池備份緩存，從而使同步工作較快)。- 修改之后要重啟mysql：/etc/init.d/mysql restart三、修改從數(shù)據(jù)庫的的配置文件（server-id配置為大于1的數(shù)字即可）：server-id=2log-bin=mysqlslave-bin.logsync_binlog=1#注意：下面這個參數(shù)需要修改為服務(wù)器內(nèi)存的70%左右innodb_buffer_

6、pool_size=700Minnodb_flush_log_at_trx_commit=1sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZEROlower_case_table_names=1log_bin_trust_function_creators=1-以下為參數(shù)解釋：innodb_buffer_pool_size 這個參數(shù)主要作用是這個參數(shù)主要作用是 緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩沖默認(rèn)值：128M； 專用mysql服務(wù)器設(shè)置的

7、大小： 操作系統(tǒng)內(nèi)存的70%-80%最佳。此外，這個參數(shù)是非動態(tài)的，要修改這個值，需要重啟mysqld服務(wù)。所以設(shè)置的時候要非常謹(jǐn)慎。并不是設(shè)置的越大越好。設(shè)置的過大，會導(dǎo)致system的swap空間被占用，導(dǎo)致操作系統(tǒng)變慢，從而減低sql查詢的效率。如 果用Innodb，那么這是一個重要變量。相對于MyISAM來說，Innodb對于buffer size更敏感。MySIAM可能對于大數(shù)據(jù)量使用默認(rèn)的key_buffer_size也還好，但I(xiàn)nnodb在大數(shù)據(jù)量時用默認(rèn)值就感覺在爬了。 Innodb的緩沖池會緩存數(shù)據(jù)和索引，所以不需要給系統(tǒng)的緩存留空間，如果只用Innodb，可以把這個值設(shè)為內(nèi)

8、存的70%-80%。和 key_buffer相同，如果數(shù)據(jù)量比較小也不怎么增加，那么不要把這個值設(shè)太高也可以提高內(nèi)存的使用率。innodb_flush_log_at_trx_commit  （這個很管用） 抱 怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個值。默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設(shè)成2對于很多運用，特別是從MyISAM表轉(zhuǎn)過來的是可以的，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然會每秒flush到硬

9、盤，所以你一般不會丟失超過1-2秒的更新。設(shè)成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務(wù)的數(shù)據(jù)。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。innodb_additional_pool_size 這個的效果不是很明顯，至少是當(dāng)操作系統(tǒng)能合理分配內(nèi)存時。但你可能仍需要設(shè)成20M或更多一點以看Innodb會分配多少內(nèi)存做其他用途。innodb_log_file_size對于寫很多尤其是大數(shù)據(jù)量時非常重要。要注意，大的文件提供更高的性能，但數(shù)據(jù)庫恢復(fù)時會用更多的時間。我一般用64M-512M，具體取決于服務(wù)器的空間。lower_case_table_names=1

10、 這樣MySQL 將在創(chuàng)建與查找時將所有的表名自動轉(zhuǎn)換為小寫字符當(dāng)你更改這個選項時，你必須在啟動 mysqld 前首先將老的表名轉(zhuǎn)換為小寫字母。換句話說，如果你希望在數(shù)據(jù)庫里面創(chuàng)建表的時候保留大小寫字符狀態(tài)，則應(yīng)該把這個參數(shù)置0： lower_case_table_names=0 。否則的話你會發(fā)現(xiàn)同樣的sqldump腳本在不同的操作系統(tǒng)下最終導(dǎo)入的結(jié)果不一樣（在Windows下所有的大寫字符都變成小寫了）。log_bin_trust_function_creators=1 If set to 0 (the default), users are not

11、 permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL

12、characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation.當(dāng)有mysql本地或遠(yuǎn)程建立function或procedure時報上面的錯誤經(jīng)試驗是log_bin_trust_function_creators值為off導(dǎo)致sql_mode 定義了mysql應(yīng)該支持的sql語法，數(shù)據(jù)校驗等！mysql>

13、60;select sql_mode;mysql5.0以上版本支持三種sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。ANSI模式：                              寬松模式，對插入數(shù)據(jù)進(jìn)行校驗，如果不符合定義類型或長度，對數(shù)據(jù)類型調(diào)整或截斷保存，報warning警告。TRADITIONAL模式：          

14、;     嚴(yán)格模式，當(dāng)向mysql數(shù)據(jù)庫插入數(shù)據(jù)時，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗，保證錯誤數(shù)據(jù)不能插入，報error錯誤。用于事物時，會進(jìn)行事物的回滾。STRICT_TRANS_TABLES模式：嚴(yán)格模式，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗，錯誤數(shù)據(jù)不能插入，報error錯誤。NO_AUTO_VALUE_ON_ZERO 影響AUTO_INCREMENT列的處理。 一般情況，你可以向該列插入NULL或0生成下一個序列號。 NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一個序列號。NO_AUTO_CREATE_USEROne of Server SQL Modes,to

15、prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.如果在mysql客戶端中運行：mysql> set sql_mode = 'no_auto_create_user'那么，上述的語句將不能成功，原因是沒有提供用戶密碼，解決的辦法是添加一個indentified 子句，例如：grant CREATE, DROP,INSERT, SELECT, DE

16、LETE, UPDATE,ALTER on xxx.* to xxx identified by 'xxx'另外，要注意的是，空密碼是不認(rèn)的，例如：grant CREATE, DROP,INSERT, SELECT, DELETE, UPDATE,ALTER on xxx.* to xxx identified by '' 就不行。NO_ENGINE_SUBSTITUTION如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設(shè)置此值時，用默認(rèn)的存儲引擎替代，并拋出一個異常+mysql的sql_mode合理設(shè)置sql_mode是個很容易被忽視的變量，默認(rèn)值是空值，

17、在這種設(shè)置下是可以允許一些非法操作的，比如允許一些非法數(shù)據(jù)的插入。在生產(chǎn)環(huán)境必須將這個值設(shè)置為嚴(yán)格模式，所以開發(fā)、測試環(huán)境的數(shù)據(jù)庫也必須要設(shè)置，這樣在開發(fā)測試階段就可以發(fā)現(xiàn)問題 sql_mode常用值如下： ONLY_FULL_GROUP_BY：對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個SQL是不合法的，因為列不在GROUP BY從句中NO_AUTO_VALUE_ON_ZERO：該值影響自增長列的插入。默認(rèn)設(shè)置下，插入0或NULL代表生成下一個自增長值。如果用戶 希望插入的值為0，而該列又是自增長的，那么這個選項就有用了。STRICT_TRA

18、NS_TABLES：在該模式下，如果一個值不能插入到一個事務(wù)表中，則中斷當(dāng)前的操作，對非事務(wù)表不做限制NO_ZERO_IN_DATE：在嚴(yán)格模式下，不允許日期和月份為零NO_ZERO_DATE：設(shè)置該值，mysql數(shù)據(jù)庫不允許插入零日期，插入零日期會拋出錯誤而不是警告。ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數(shù)據(jù)被零除，則產(chǎn)生錯誤而非警告。如 果未給出該模式，那么數(shù)據(jù)被零除時MySQL返回NULLNO_AUTO_CREATE_USER：禁止GRANT創(chuàng)建密碼為空的用戶NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被禁用或未

19、編譯，那么拋出錯誤。不設(shè)置此值時，用默認(rèn)的存儲引擎替代，并拋出一個異常PIPES_AS_CONCAT：將"|"視為字符串的連接操作符而非或運算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似ANSI_QUOTES：啟用ANSI_QUOTES后，不能用雙引號來引用字符串，因為它被解釋為識別符ORACLE的sql_mode設(shè)置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_U

20、SER. 如果使用mysql，為了繼續(xù)保留大家使用oracle的習(xí)慣，可以對mysql的sql_mode設(shè)置如下：在f添加如下配置mysqldsql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'- 修改之后要重啟mysql：/et

21、c/init.d/mysql restart 四、SSH登錄到主數(shù)據(jù)庫：（1）在主數(shù)據(jù)庫上創(chuàng)建用于主從復(fù)制的賬戶（23換成你的從數(shù)據(jù)庫IP）：1# mysql -uroot -p2mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl''23' IDENTIFIED BY 'repl'Create user reply_user;Grant REPLICATION SLAVE On *.* to reply_user IDENTIFIED BY xx

22、xxxxxxx;Flush privileges;-mysql 新設(shè)置用戶或更改密碼后需用flush privileges刷新MySQL的系統(tǒng)權(quán)限相關(guān)表,否則會出現(xiàn)拒絕訪問mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl''192.168.0.%' IDENTIFIED BY 'repl'其中要特別說明一下192.168.0.%，這個配置是指明repl用戶所在服務(wù)器， 這里%是通配符，表示-55的Server都可以以repl用戶登陸主服務(wù)器。如果沒有使用

23、通配符，而訪問 的服務(wù)器又不在上述配制里，那么你將無法使用該賬戶從你的服務(wù)器replicate主服務(wù)器.（2）主數(shù)據(jù)庫鎖表（禁止再插入數(shù)據(jù)以獲取主數(shù)據(jù)庫的的二進(jìn)制日志坐標(biāo)）：1mysql> FLUSH TABLES WITH READ LOCK;（3）然后克隆一個SSH會話窗口，在這個窗口打開MySQL命令行：1# mysql -uroot -p2mysql> SHOW MASTER STATUS;3+-+-+-+-+-+4| File            &#

24、160;      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |5+-+-+-+-+-+6| mysqlmaster-bin.000001 |      330 |              |       &#

25、160;          |                   |7+-+-+-+-+-+81 row in set (0.00 sec)9mysql> exit;在這個例子中，二進(jìn)制日志文件是mysqlmaster-bin.000001，位置是330，記錄下這兩個值，稍后要用到。（4）在主數(shù)據(jù)庫上使用mysqldum

26、p命令創(chuàng)建一個數(shù)據(jù)快照，即備份，導(dǎo)出所有庫：1#mysqldump -uroot -p -h -P3306 -all-databases  -triggers -routines -events >all.sql2# 接下來會提示你輸入mysql數(shù)據(jù)庫的root密碼，輸入完成后，如果當(dāng)前數(shù)據(jù)庫不大，很快就能導(dǎo)出完成。（5）解鎖第（2）步主數(shù)據(jù)的鎖表操作：1mysql> UNLOCK TABLES;五、SSH登錄到從數(shù)據(jù)庫：（1）通過FTP、SFTP或其他方式，將上一步備份的主數(shù)據(jù)庫快照all.sql上傳到從數(shù)據(jù)庫某個路徑，例如我放在了/home/yimi

27、ju/目錄下；（2）導(dǎo)入主的快照到從：1# cd /home/yimiju2# mysql -uroot -p -h -P3306 < all.sql3# 接下來會提示你輸入mysql數(shù)據(jù)庫的root密碼，輸入完成后，如果當(dāng)前數(shù)據(jù)庫不大，很快就能導(dǎo)入完成。 或者mysql命令行mysql>source all.sql;（3）給從數(shù)據(jù)庫設(shè)置復(fù)制的主數(shù)據(jù)庫信息（注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值）：1# mysql -uroot -p2mysql> CHANGE MASTER TO MASTER_HOST='192.

28、168.2.114', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=330;3# 然后啟動從數(shù)據(jù)庫的復(fù)制線程：4mysql> START slave;5# 接著查詢數(shù)據(jù)庫的slave狀態(tài)：6mysql>  SHOW slave STATUS G7# 如果下面兩個參數(shù)都是Yes，則說明主從配置成功！8Slave_IO_Running: Yes9Slave_SQL_R

29、unning: Yes  如果是Slave_IO_State一項顯示：Waiting for master to send event，表示所有工作已經(jīng)就緒。（4）接下來你可以在主數(shù)據(jù)庫上創(chuàng)建數(shù)據(jù)庫、表、插入數(shù)據(jù)，然后看從數(shù)據(jù)庫是否同步了這些操作。mysql> CREATE TABLE web_user2 (      id int(10) unsigned NOT NULL AUTO_INCREMENT,      name varchar(30) NOT NULL DEFAULT '&#

30、39;,     creatime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',     PRIMARY KEY (id)      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (1.98 sec)mysql> show tables;+-+| Tables_in_test |+-+| web_user2      |+-+1 row in set (0.00 sec)mysql> Insert into web_user2 (name