MySQL主備雙活安裝說(shuō)明

1、MySQL主備雙活安裝說(shuō)明一、 MySQL主備工作原理MySQL主備集群模式采用日志回放的模式，將主服務(wù)器上執(zhí)行過(guò)的日志信息，在備機(jī)上重新進(jìn)行一次回放，確保主備服務(wù)器數(shù)據(jù)的一致性。簡(jiǎn)單而言就是把一個(gè)服務(wù)器上執(zhí)行過(guò)的sql語(yǔ)句在別的服務(wù)器上也重復(fù)執(zhí)行一遍，只要兩個(gè)數(shù)據(jù)庫(kù)的初態(tài)是一樣的，那么它們就能一直同步。對(duì)于一個(gè)mysql服務(wù)器，一般有兩個(gè)線程來(lái)負(fù)責(zé)復(fù)制和被復(fù)制。1. 作為主服務(wù)器Master，會(huì)把自己的每一次改動(dòng)都記錄到 二進(jìn)制日志 Binarylog 中；2. 作為從服務(wù)器Slave，會(huì)用master上的賬號(hào)登陸到 master上， 讀取master的Binarylog，寫(xiě)入到自己的中繼日

2、志 Relaylog，然后自己的sql線程會(huì)負(fù)責(zé)讀取這個(gè)中繼日志，并執(zhí)行一遍。MySQL主備雙活即主服務(wù)器負(fù)責(zé)寫(xiě)入讀取，從服務(wù)器則只能進(jìn)行讀取操作。MySQL主主雙活則在主備的基礎(chǔ)上，采用互為備份的方式。二、 MySQL雙活配置2.1. 環(huán)境說(shuō)明兩臺(tái)服務(wù)器，IP地址分別為10.68.19.182(以下簡(jiǎn)稱(chēng)A機(jī))、10.68.19.183（以下簡(jiǎn)稱(chēng)B機(jī)），操作系統(tǒng)Ubuntu 14.04。2.2. MySQL安裝在兩臺(tái)服務(wù)器上分別安裝MySQL服務(wù)器。$ sudo apt-get install mysql-server2.3. 創(chuàng)建備份用戶(hù)在A機(jī)上執(zhí)行mysql> grant repli

3、cation slave on *.* to 'repl''10.68.19.183' identified by 'replpwd'mysql> flush privileges;在B機(jī)上執(zhí)行mysql> grant replication slave on *.* to 'repl''10.68.19.182' identified by 'replpwd'mysql> flush privileges;2.4. 修改MySQL配置在A機(jī)上修改配置文件$ sudo vi /et

4、c/mysql/f#bind-address = 127.0.0.1server-id = 101log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_namebinlog_ignore_db = mysqlbinlog_ignore_db = information_schemabinlog_ignore_db = performance_schemalog-slave-updatessync_binlog=0auto-

5、increment-increment= 2auto-increment-offset = 1備注說(shuō)明：1、 bind-address注釋掉或者修改為本地IP地址，否則外部機(jī)器無(wú)法連接到MySQL服務(wù)器；2、 server-id為服務(wù)器的ID值，兩臺(tái)不同的MySQL服務(wù)器，必須配置成不同；3、 binlog_do_db為需要同步的數(shù)據(jù)庫(kù)，當(dāng)前部署采用采用排除模式，不進(jìn)行設(shè)置；4、 binlog_ignore_db設(shè)定忽略哪些數(shù)據(jù)庫(kù)的日志，當(dāng)前的配置排除了系統(tǒng)自帶的數(shù)據(jù)庫(kù)；5、 log-slave-updates這個(gè)參數(shù)用來(lái)配置從服務(wù)器的更新是否寫(xiě)入二進(jìn)制日志，例如A->B B->A

6、，在 B中設(shè)置log_slave_updates后還可以B->C. 這樣A,C中的數(shù)據(jù)也是一致的，在主主互備的方式中，日志會(huì)自動(dòng)過(guò)濾自己發(fā)送給其它服務(wù)器的日志；6、 sync_binlog對(duì)應(yīng)的數(shù)值為0或任意整形數(shù)據(jù)，如果大于0，當(dāng)每個(gè)sync_binlog寫(xiě)入該二進(jìn)制日志后，MySQL服務(wù)器將它的二進(jìn)制日志同步到硬盤(pán)上(fdatasync()。請(qǐng)注意如果在autocommit模式，每執(zhí)行一個(gè)語(yǔ)句向二進(jìn)制日志寫(xiě)入一次，否則每個(gè)事務(wù)寫(xiě)入一次。 默認(rèn)值是0，不與硬盤(pán)同步。值為1是最安全的選擇，因?yàn)楸罎r(shí)，你最多丟掉二進(jìn)制日志中的一個(gè)語(yǔ)句/事務(wù)；7、 auto-increment-increm

7、ent、auto-increment-offset用于控制自增長(zhǎng)主鍵生成策略，避免不同服務(wù)器之間產(chǎn)生的id發(fā)生沖突。auto-increment-increment用于設(shè)定自動(dòng)增長(zhǎng)字段的數(shù)值間隔，auto-increment-offset用于設(shè)定自動(dòng)增長(zhǎng)的偏移量（每臺(tái)MySQL需配置不同值）。在本應(yīng)用設(shè)置中，A機(jī)自動(dòng)增長(zhǎng)的數(shù)值為1、3、5，B機(jī)自動(dòng)增長(zhǎng)的數(shù)值為2、4、6。在B機(jī)上修改配置文件$ sudo vi /etc/mysql/f#bind-address = 127.0.0.1server-id = 102log_bin = /var/log/mysql/mysql-bin.logexp

8、ire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_namebinlog_ignore_db = mysqlbinlog_ignore_db = information_schemabinlog_ignore_db = performance_schemalog-slave-updatessync_binlog=0auto-increment-increment = 2auto-increment-offset = 2分別在兩臺(tái)服務(wù)器上重啟MySQL$ sudo /etc/init.d/mysql r

9、estart或者$ sudo service mysql restart備注：注意啟動(dòng)提示，如未正常提示數(shù)據(jù)庫(kù)啟動(dòng)成功，請(qǐng)查看服務(wù)器/var/log/mysql目錄中的日志內(nèi)容。2.5. 同步數(shù)據(jù)庫(kù)在A機(jī)上執(zhí)行-創(chuàng)建數(shù)據(jù)庫(kù)create database testdb default charset utf8;-切換數(shù)據(jù)庫(kù)use testdb;-創(chuàng)建測(cè)試表create table DM_TEST( ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100);-插入測(cè)試數(shù)據(jù)insert DM_TEST(NAME) values('user1&#

10、39;);insert DM_TEST(NAME) values('user2');-將表設(shè)定為只讀FLUSH TABLES WITH READ LOCK;-顯示表日志情況show master statusG-備份數(shù)據(jù)庫(kù)mysqldump -master-data -uroot -p testdb > testdb.sqlscp testdb.sql 10.68.19.183:/home/whcyit在B機(jī)上執(zhí)行-創(chuàng)建數(shù)據(jù)庫(kù)create database testdb default charset utf8;-恢復(fù)數(shù)據(jù)庫(kù)mysql -uroot -p testdb &

11、lt; /home/whcyit/testdb.sql-設(shè)定初始日志change master to master_host='10.68.19.182',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000001',master_log_pos=94382;-啟動(dòng)同步start slave;-鎖定表FLUSH TABLES WITH READ LOCK;-顯示表日志情況show master statusG在A機(jī)上執(zhí)行-解除只讀un

12、lock tables;-鎖定表change master to master_host='10.68.19.183',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000001',master_log_pos=107;-啟動(dòng)同步start slave;在B機(jī)上執(zhí)行-解除只讀unlock tables;分別在兩臺(tái)機(jī)器上執(zhí)行以下命令查看從服務(wù)器狀態(tài)mysql> show slave statusG;確認(rèn)Slave_IO_Runni

13、ng、Slave_SQL_Running為Yes狀態(tài)。2.6. 驗(yàn)證分別在兩個(gè)數(shù)據(jù)庫(kù)，分別插入數(shù)據(jù)，看另一數(shù)據(jù)庫(kù)是否正常顯示數(shù)據(jù)。停止一個(gè)數(shù)據(jù)庫(kù)，操作另一數(shù)據(jù)庫(kù)后，再次啟動(dòng)數(shù)據(jù)庫(kù)，查看數(shù)據(jù)是否同步完成。三、 MySQL常用配置項(xiàng)說(shuō)明使用MySQL的命令show variables like 'max_connections' 、show status like 'max_connections'可以查詢(xún)配置內(nèi)容，修改配置需直接修改f文件。參考配置：innodb_buffer_pool_size=4Ginnodb_log_file_size=1024Minnodb

14、_log_buffer_size = 8Minnodb_flush_log_at_trx_commit=2innodb_file_per_table=1innodb_file_io_threads=4innodb_flush_method=O_DIRECTinnodb_io_capacity=2000innodb_thread_concurrency = 0innodb_additional_mem_pool_size=16Minnodb_autoinc_lock_mode = 23.1. innodb_buffer_pool_size緩沖池是數(shù)據(jù)和索引緩存的地方：這個(gè)值越大越好，這能保證你在

15、大多數(shù)的讀取操作時(shí)使用的是內(nèi)存而不是硬盤(pán)。典型的值是5-6GB(8GB內(nèi)存)，20-25GB(32GB內(nèi)存)，100-120GB(128GB內(nèi)存)。3.2. innodb_log_file_sizeRedo日志的大小設(shè)置，redo日志被用于確保寫(xiě)操作快速而可靠并且在崩潰時(shí)恢復(fù)。設(shè)置較大的值，可以提高數(shù)據(jù)庫(kù)性能（避免日志頻繁切換），設(shè)置較小的值，可以使得來(lái)MySQL崩潰后能夠更快恢復(fù)。可以考慮將把innodb_log_file_size設(shè)置成512M(默認(rèn)2個(gè)日志文件，這樣有1GB的redo日志)會(huì)使你有充裕的寫(xiě)操作空間。如果應(yīng)用程序需要頻繁的寫(xiě)入數(shù)據(jù)并且你使用的時(shí)MySQL 5.6，你可以一開(kāi)

16、始就設(shè)置為4G。3.3. max_connections最大用戶(hù)連接數(shù)，默認(rèn)值為151。設(shè)置過(guò)大的值（例如1000或更高）后，服務(wù)器運(yùn)行1000個(gè)或更高的活動(dòng)事務(wù)時(shí)會(huì)變的沒(méi)有響應(yīng)。3.4. innodb_thread_concurrency并發(fā)線程數(shù)，推薦設(shè)置為 2*(NumCPUs+NumDisks)，默認(rèn)一般為8。3.5. innodb_file_per_table設(shè)置InnoDB是否需要將所有表的數(shù)據(jù)和索引存放在共享表空間里（innodb_file_per_table = OFF） 或者為每張表的數(shù)據(jù)單獨(dú)放在一個(gè).ibd文件（innodb_file_per_table = ON）。每張表

17、一個(gè)文件允許你在drop、truncate或者rebuild表時(shí)回收磁盤(pán)空間。這對(duì)于一些高級(jí)特性也是有必要的，比如數(shù)據(jù)壓縮。但是它不會(huì)帶來(lái)任何性能收益。你不想讓每張表一個(gè)文件的主要場(chǎng)景是：有非常多的表（比如10k+）。MySQL 5.6中，這個(gè)屬性默認(rèn)值是ON，因此大部分情況下你什么都不需要做。對(duì)于之前的版本你必需在加載數(shù)據(jù)之前將這個(gè)屬性設(shè)置為ON，因?yàn)樗粚?duì)新創(chuàng)建的表有影響。3.6. innodb_log_buffer_size該配置決定了為尚未執(zhí)行的事務(wù)分配的緩存。其默認(rèn)值（1MB）一般來(lái)說(shuō)已經(jīng)夠用了，但是如果你的事務(wù)中包含有二進(jìn)制對(duì)象或者大文本字段的話(huà)，這點(diǎn)緩存很快就會(huì)被填滿(mǎn)并觸發(fā)額外的

18、I/O操作。3.7. query_cache_sizeQuery cache（查詢(xún)緩存）是一個(gè)眾所周知的瓶頸，甚至在并發(fā)并不多的時(shí)候也是如此。 最佳選項(xiàng)是將其從一開(kāi)始就停用，設(shè)置query_cache_size = 0（現(xiàn)在MySQL 5.6的默認(rèn)值）并利用其他方法加速查詢(xún)：優(yōu)化索引、增加拷貝分散負(fù)載或者啟用額外的緩存（比如memcache或redis）。3.8. log_bin日志文件存放路徑，對(duì)于主備雙活、主主雙活的方案，主機(jī)上必須開(kāi)啟日志文件，單純的從服務(wù)器可以不開(kāi)啟日志服務(wù)。在設(shè)置log_bin的同時(shí)，需server_id作為服務(wù)器的唯一標(biāo)識(shí)。在對(duì)于基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)的場(chǎng)景，日志文件也必須開(kāi)啟，即采用最新的全備數(shù)據(jù)恢復(fù)，然后用日志文件進(jìn)行前滾。二進(jìn)制日