業(yè)務(wù)場(chǎng)景：
公司幾個(gè)主要的業(yè)務(wù)已經(jīng)獨(dú)立，放在不同的數(shù)據(jù)庫(kù)服務(wù)器上面，但是有一個(gè)業(yè)務(wù)又需要關(guān)聯(lián)多個(gè)業(yè)務(wù)庫(kù)進(jìn)行聯(lián)合查詢統(tǒng)計(jì)。這時(shí)候就需要將不同的業(yè)務(wù)庫(kù)數(shù)據(jù)同步到一臺(tái)從庫(kù)進(jìn)行統(tǒng)計(jì)。根據(jù)Mysql主從同步原理使用多從一主的方案解決。主庫(kù)使用innodb引擎，從庫(kù)開啟多實(shí)例使用myisam引擎并將多個(gè)實(shí)例的數(shù)據(jù)同步到同一個(gè)目錄，并通過flush tables 在一個(gè)實(shí)例里面訪問其他實(shí)例的數(shù)據(jù)。
解決思路：
1、主數(shù)據(jù)庫(kù)使用Innodb引擎，并設(shè)置sql_mode為 NO_AUTO_CREATE_USER
2、從庫(kù)開啟多實(shí)例，將多個(gè)主庫(kù)里面的數(shù)據(jù)通過主從復(fù)制同步到同一個(gè)數(shù)據(jù)目錄。從庫(kù)的每個(gè)實(shí)例對(duì)應(yīng)一個(gè)主庫(kù)。多個(gè)實(shí)例使用同一個(gè)數(shù)據(jù)目錄。
3、從庫(kù)使用Myisam引擎，關(guān)閉從庫(kù)默認(rèn)的innodb引擎,Myisam引擎可以訪問同一個(gè)數(shù)據(jù)目錄里面其他實(shí)例的表。
4、從庫(kù)的每個(gè)實(shí)例需要執(zhí)行flush tables 才能看到其他實(shí)例表的數(shù)據(jù)變化，可以設(shè)置crontab任務(wù)計(jì)劃每分鐘在第一個(gè)實(shí)例刷新表，以便程序連接的默認(rèn)實(shí)例能看到表的實(shí)時(shí)變化。
5、設(shè)置主庫(kù)和從庫(kù)的sql_mode都為NO_AUTO_CREATE_USER，只有這樣主庫(kù)的innodb引擎的sql同步到從庫(kù)的時(shí)候才能執(zhí)行成功。
方案架構(gòu)圖：
環(huán)境說(shuō)明：
主庫(kù)-1：192.168.1.1
主庫(kù)-2：192.168.1.2
從庫(kù)-3：192.168.1.3
從庫(kù)-3：192.168.1.4
從庫(kù)-3：192.168.1.5
實(shí)現(xiàn)步驟：（Mysql安裝步驟這里不在描述）
1、主數(shù)據(jù)庫(kù)配置文件，多個(gè)主庫(kù)配置文件除了server-id不能一樣其他都一樣。
[root@masterdb01 ~]#cat /etc/my.cnf[client]port= 3306socket= /tmp/mysql.sock[mysqld]port = 3306basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4default-storage-engine = InnoDBsocket = /tmp/mysql.sockskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size = 32Mmax-heap-table-size = 32M#query-cache-type = 0query-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1log-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = /data/logs/slow.loglong_query_time = 0.1# BINARY LOGGING #server-id = 1log-bin = /data/binlog/mysql-binlog-bin-index =/data/binlog/mysql-bin.indexexpire-logs-days = 14sync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updates#binlog_format = row binlog_format = MIXED //這里使用的混合模式復(fù)制relay_log_recovery = 1#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的庫(kù)replicate-ignore-db = mysql,information_schema,performance_schemakey_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recovertransaction_isolation = REPEATABLE-READinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 5734Minnodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_data_file_path = ibdata1:1024M:autoextendinnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_max_dirty_pages_pct = 50innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_locks_unsafe_for_binlog = 0[mysqldump]quickmax_allowed_packet = 32M
2、從庫(kù)配置文件。多個(gè)從庫(kù)配置文件除了server-id不能一樣其他都一樣。
[root@slavedb01 ~]# cat /etc/my.cnf[client]port= 3306socket= /tmp/mysql.sock[mysqld_multi]# 指定相關(guān)命令的路徑mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin##復(fù)制主庫(kù)1的數(shù)據(jù)##[mysqld2]port = 3306basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定實(shí)例1的sock文件和pid文件socket = /tmp/mysql.sockpid-file=/data/mysql/mysql.pidskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size = 32Mmax-heap-table-size = 32Mquery-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1#指定第一個(gè)實(shí)例的錯(cuò)誤日志和慢查詢?nèi)罩韭窂絣og-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = /data/logs/slow.loglong_query_time = 0.1# BINARY LOGGING## 指定實(shí)例1的binlog和relaylog路徑為/data/binlog目錄# 每個(gè)從庫(kù)和每個(gè)實(shí)例的server_id不能一樣。server-id = 2log-bin = /data/binlog/mysql-binlog-bin-index =/data/binlog/mysql-bin.indexrelay_log = /data/binlog/mysql-relay-binrelay_log_index = /data/binlog/mysql-relay.indexmaster-info-file = /data/mysql/master.inforelay_log_info_file = /data/mysql/relay-log.inforead_only = 1expire-logs-days = 14sync_binlog = 1#需要同步的庫(kù)，如果不設(shè)置，默認(rèn)同步所有庫(kù)。#replicate-do-db = xxx#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的庫(kù)replicate-ignore-db = mysql,information_schema,performance_schemabinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updates =1#binlog_format = row binlog_format = MIXEDrelay_log_recovery = 1key_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recover#設(shè)置默認(rèn)引擎為Myisam，下面這些參數(shù)一定要加上。default-storage-engine=MyISAMdefault-tmp-storage-engine=MYISAM#關(guān)閉innodb引擎skip-innodbinnodb = OFFdisable-innodb#設(shè)置sql_mode模式為NO_AUTO_CREATE_USERsql_mode = NO_AUTO_CREATE_USER#關(guān)閉innodb引擎loose-skip-innodbloose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0loose-innodb-cmp-per-index-reset=0loose-innodb-cmp-reset=0 loose-innodb-cmpmem=0 loose-innodb-cmpmem-reset=0 loose-innodb-buffer-page=0 loose-innodb-buffer-page-lru=0 loose-innodb-buffer-pool-stats=0 loose-innodb-metrics=0 loose-innodb-ft-default-stopword=0 loose-innodb-ft-inserted=0 loose-innodb-ft-deleted=0 loose-innodb-ft-being-deleted=0 loose-innodb-ft-config=0 loose-innodb-ft-index-cache=0 loose-innodb-ft-index-table=0 loose-innodb-sys-tables=0 loose-innodb-sys-tablestats=0 loose-innodb-sys-indexes=0 loose-innodb-sys-columns=0 loose-innodb-sys-fields=0 loose-innodb-sys-foreign=0 loose-innodb-sys-foreign-cols=0 ##復(fù)制主庫(kù)2的數(shù)據(jù)##[mysqld3]port = 3307basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定實(shí)例2的sock文件和pid文件socket = /tmp/mysql3.sockpid-file=/data/mysql/mysql3.pidskip-name-resolv = 1open_files_limit = 65535 back_log = 103max_connections = 512max_connect_errors = 100000table_open_cache = 2048tmp-table-size = 32Mmax-heap-table-size = 32Mquery-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mmax_heap_table_size = 96Mquery_cache_type=1log-error=/data/logs/mysqld3.logslow_query_log = 1slow_query_log_file = /data/logs/slow3.loglong_query_time = 0.1# BINARY LOGGING ## 這里一定要注意，不能把兩個(gè)實(shí)例的binlog和relaylog放到同一個(gè)目錄，# 這里指定實(shí)例2的binlog日志為/data/binlog2目錄# 每個(gè)從庫(kù)和每個(gè)實(shí)例的server_id不能一樣。server-id = 22log-bin = /data/binlog2/mysql-binlog-bin-index =/data/binlog2/mysql-bin.indexrelay_log = /data/binlog2/mysql-relay-binrelay_log_index = /data/binlog2/mysql-relay.indexmaster-info-file = /data/mysql/master3.inforelay_log_info_file = /data/mysql/relay-log3.inforead_only = 1expire-logs-days = 14sync_binlog = 1#不需要復(fù)制的庫(kù)replicate-ignore-db = mysql,information_schema,performance_schemabinlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updates =1#binlog_format = row binlog_format = MIXEDrelay_log_recovery = 1key_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recover#設(shè)置默認(rèn)引擎為Myisamdefault-storage-engine=MyISAMdefault-tmp-storage-engine=MYISAM#關(guān)閉innodb引擎skip-innodbinnodb = OFFdisable-innodb#設(shè)置sql_mode模式為NO_AUTO_CREATE_USERsql_mode = NO_AUTO_CREATE_USER#關(guān)閉innodb引擎，下面這些參數(shù)一定要加上。loose-skip-innodbloose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0loose-innodb-cmp-per-index-reset=0loose-innodb-cmp-reset=0 loose-innodb-cmpmem=0 loose-innodb-cmpmem-reset=0 loose-innodb-buffer-page=0 loose-innodb-buffer-page-lru=0 loose-innodb-buffer-pool-stats=0 loose-innodb-metrics=0 loose-innodb-ft-default-stopword=0 loose-innodb-ft-inserted=0 loose-innodb-ft-deleted=0 loose-innodb-ft-being-deleted=0 loose-innodb-ft-config=0 loose-innodb-ft-index-cache=0 loose-innodb-ft-index-table=0 loose-innodb-sys-tables=0 loose-innodb-sys-tablestats=0 loose-innodb-sys-indexes=0 loose-innodb-sys-columns=0 loose-innodb-sys-fields=0 loose-innodb-sys-foreign=0 loose-innodb-sys-foreign-cols=0[mysqldump]quickmax_allowed_packet = 32M```
3、設(shè)置主庫(kù)sql_mode,Mysql5.6默認(rèn)需要在啟動(dòng)文件文件里面設(shè)置sql_mode才可以生效。
# cat /etc/init.d/mysqld#other_args="$*" # uncommon, but needed when called from an RPM upgrade action # Expected: "--skip-networking --skip-grant-tables" # They are not checked here, intentionally, as it is the resposibility # of the "spec" file author to give correct arguments only.#將上面默認(rèn)的#other_args開啟后改為other_args="--sql-mode=NO_AUTO_CREATE_USER"
4、開啟主庫(kù)和從庫(kù)
#主庫(kù)service mysqld start#開啟從庫(kù)的二個(gè)實(shí)例/usr/local/mysql/bin/mysqld_multi start 2/usr/local/mysql/bin/mysqld_multi start 3
5、在兩臺(tái)主庫(kù)上面分別授權(quán)復(fù)制賬號(hào)
#需要授權(quán)三個(gè)從庫(kù)的ip可以同步mysql> GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.1.3' IDENTIFIED BY 'rep123';mysql> GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.1.4' IDENTIFIED BY 'rep123';mysql> GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.1.5' IDENTIFIED BY 'rep123';mysql> flush privileges;
6、在三個(gè)從庫(kù)分別開啟同步。
#進(jìn)入第一個(gè)實(shí)例執(zhí)行$ mysql -S /tmp/mysql.sockmysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='rep',MASTER_PASSWORD='rep123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=112; #進(jìn)入第二個(gè)實(shí)例執(zhí)行$ mysql -S /tmp/mysql3.sockmysql> CHANGE MASTER TO MASTER_HOST='192.168.1.2',MASTER_USER='rep',MASTER_PASSWORD='rep123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=112;
7、測(cè)試數(shù)據(jù)同步
在二個(gè)主數(shù)據(jù)庫(kù)分別建表和插入數(shù)據(jù)，到從庫(kù)查看可以看到二個(gè)主庫(kù)同步到同一個(gè)從庫(kù)上面的所有數(shù)據(jù)。
8、在每臺(tái)從庫(kù)服務(wù)器上設(shè)置任務(wù)計(jì)劃每分鐘刷新第一個(gè)實(shí)例的表
# crontab -l*/1 * * * * mysql -S /tmp/mysql.sock -e 'flush tables;'
Mysql5.6多主一從的坑
1、Mysql5.6默認(rèn)的引擎是innodb默認(rèn)同步的時(shí)候一定要把主和從的sql_mode模式里面的NO_ENGINE_SUBSTITUTION這個(gè)參數(shù)關(guān)閉。如果不關(guān)閉innodb同步到從庫(kù)上面的sql將會(huì)找不到innodb引擎導(dǎo)致同步失敗。
2、在mysql5.6開啟多實(shí)例的時(shí)候第一次啟動(dòng)的時(shí)候在你數(shù)據(jù)庫(kù)的安裝目錄里面（/usr/local/mysql/)會(huì)生成my.cnf配置文件，默認(rèn)會(huì)優(yōu)先讀取數(shù)據(jù)庫(kù)安裝目錄里面的配置文件。導(dǎo)致多實(shí)例不生效。
