MySQL主備雙活安裝說明

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

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

3、cation slave on *.* to 'repl''10.68.19.183' identified by 'replpwd'mysql> flush privileges;在B機上執(zhí)行mysql> grant replication slave on *.* to 'repl''10.68.19.182' identified by 'replpwd'mysql> flush privileges;2.4. 修改MySQL配置在A機上修改配置文件$ 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備注說明：1、 bind-address注釋掉或者修改為本地IP地址，否則外部機器無法連接到MySQL服務器；2、 server-id為服務器的ID值，兩臺不同的MySQL服務器，必須配置成不同；3、 binlog_do_db為需要同步的數(shù)據庫，當前部署采用采用排除模式，不進行設置；4、 binlog_ignore_db設定忽略哪些數(shù)據庫的日志，當前的配置排除了系統(tǒng)自帶的數(shù)據庫；5、 log-slave-updates這個參數(shù)用來配置從服務器的更新是否寫入二進制日志，例如A->B B->A

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

7、ent、auto-increment-offset用于控制自增長主鍵生成策略，避免不同服務器之間產生的id發(fā)生沖突。auto-increment-increment用于設定自動增長字段的數(shù)值間隔，auto-increment-offset用于設定自動增長的偏移量（每臺MySQL需配置不同值）。在本應用設置中，A機自動增長的數(shù)值為1、3、5，B機自動增長的數(shù)值為2、4、6。在B機上修改配置文件$ 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分別在兩臺服務器上重啟MySQL$ sudo /etc/init.d/mysql r

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

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

11、lt; /home/whcyit/testdb.sql-設定初始日志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;-啟動同步start slave;-鎖定表FLUSH TABLES WITH READ LOCK;-顯示表日志情況show master statusG在A機上執(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;-啟動同步start slave;在B機上執(zhí)行-解除只讀unlock tables;分別在兩臺機器上執(zhí)行以下命令查看從服務器狀態(tài)mysql> show slave statusG;確認Slave_IO_Runni

13、ng、Slave_SQL_Running為Yes狀態(tài)。2.6. 驗證分別在兩個數(shù)據庫，分別插入數(shù)據，看另一數(shù)據庫是否正常顯示數(shù)據。停止一個數(shù)據庫，操作另一數(shù)據庫后，再次啟動數(shù)據庫，查看數(shù)據是否同步完成。三、 MySQL常用配置項說明使用MySQL的命令show variables like 'max_connections' 、show status like 'max_connections'可以查詢配置內容，修改配置需直接修改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ù)據和索引緩存的地方：這個值越大越好，這能保證你在

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

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

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

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