業務場景：
公司幾個主要的業務已經獨立，放在不同的資料庫伺服器上面，但是有一個業務又需要關聯多個業務庫進行聯合查詢統計。這時候就需要將不同的業務庫資料同步到一台從庫進行統計。根據mysql主從同步原理使用多從一主的方案解決。主庫使用innodb引擎，從庫開啟多實例使用myisam引擎並將多個實例的資料同步到同一個目錄，並透過flush tables 在一個實例裡面存取其他實例的資料。
解決想法：
1、主資料庫使用innodb引擎，並設定sql_mode為no_auto_create_user
2、從函式庫開啟多實例，將多個主函式庫裡面的資料透過主從複製同步到同一個資料目錄。從庫的每個實例對應一個主庫。多個實例使用同一個資料目錄。
3、從函式庫使用myisam引擎，關閉從函式庫預設的innodb引擎,myisam引擎可以存取同一個資料目錄裡面其他實例的表。
4、從庫的每個實例需要執行flush tables 才能看到其他實例表的資料變化，可以設定crontab任務計劃每分鐘在第一個實例刷新表，以便程式連接的預設實例能看到表的即時變化。
5、設定主函式庫和從函式庫的sql_mode都為no_auto_create_user，只有這樣主函式庫的innodb引擎的sql同步到從函式庫的時候才能執行成功。
方案架構圖：
#環境說明：
主庫-1：192.168.1.1
主庫-2：192.168.1.2
從庫-3：192.168.1.3
從庫-3：192.168.1.4
從庫-3：192.168. 1.5
實作步驟：（mysql安裝步驟這裡不在說明）
1、主資料庫設定文件，多個主庫設定檔除了server-id不能一樣其他都一樣。
[root@masterdb01 ~]#cat /etc/my.cnf [client] port= 3306 socket= /tmp/mysql.sock [mysqld] port = 3306 basedir = /usr/local/mysql datadir = /data/mysql character-set-server = utf8mb4 default-storage-engine = innodb socket = /tmp/mysql.sock skip-name-resolv = 1 open_files_limit = 65535 back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size = 32m max-heap-table-size = 32m #query-cache-type = 0 query-cache-size = 0 external-locking = false max_allowed_packet = 32m sort_buffer_size = 2m join_buffer_size = 2m thread_cache_size = 51 query_cache_size = 32m tmp_table_size = 96m max_heap_table_size = 96m query_cache_type=1 log-error=/data/logs/mysqld.log slow_query_log = 1 slow_query_log_file = /data/logs/slow.log long_query_time = 0.1 # binary logging # server-id = 1 log-bin = /data/binlog/mysql-bin log-bin-index =/data/binlog/mysql-bin.index expire-logs-days = 14 sync_binlog = 1 binlog_cache_size = 4m max_binlog_cache_size = 8m max_binlog_size = 1024m log_slave_updates #binlog_format = row binlog_format = mixed //这里使用的混合模式复制 relay_log_recovery = 1 #不需要同步的表 replicate-wild-ignore-table=mydb.sp_counter #不需要同步的库 replicate-ignore-db = mysql,information_schema,performance_schema key_buffer_size = 32m read_buffer_size = 1m read_rnd_buffer_size = 16m bulk_insert_buffer_size = 64m myisam_sort_buffer_size = 128m myisam_max_sort_file_size = 10g myisam_repair_threads = 1 myisam_recover transaction_isolation = repeatable-read innodb_additional_mem_pool_size = 16m innodb_buffer_pool_size = 5734m innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1024m:autoextend innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 32m innodb_log_file_size = 2g innodb_log_files_in_group = 2 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_max_dirty_pages_pct = 50 innodb_flush_method = o_direct innodb_file_format = barracuda innodb_file_format_max = barracuda innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_locks_unsafe_for_binlog = 0 [mysqldump] quick max_allowed_packet = 32m
2、從庫設定檔。多個從庫設定檔除了server-id不能一樣其他都一樣。
[root@slavedb01 ~]# cat /etc/my.cnf [client] port= 3306 socket= /tmp/mysql.sock [mysqld_multi] # 指定相关命令的路径 mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin ##复制主库1的数据## [mysqld2] port = 3306 basedir = /usr/local/mysql datadir = /data/mysql character-set-server = utf8mb4 #指定实例1的sock文件和pid文件 socket = /tmp/mysql.sock pid-file=/data/mysql/mysql.pid skip-name-resolv = 1 open_files_limit = 65535 back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size = 32m max-heap-table-size = 32m query-cache-size = 0 external-locking = false max_allowed_packet = 32m sort_buffer_size = 2m join_buffer_size = 2m thread_cache_size = 51 query_cache_size = 32m tmp_table_size = 96m max_heap_table_size = 96m query_cache_type=1 #指定第一个实例的错误日志和慢查询日志路径 log-error=/data/logs/mysqld.log slow_query_log = 1 slow_query_log_file = /data/logs/slow.log long_query_time = 0.1 # binary logging# # 指定实例1的binlog和relaylog路径为/data/binlog目录 # 每个从库和每个实例的server_id不能一样。 server-id = 2 log-bin = /data/binlog/mysql-bin log-bin-index =/data/binlog/mysql-bin.index relay_log = /data/binlog/mysql-relay-bin relay_log_index = /data/binlog/mysql-relay.index master-info-file = /data/mysql/master.info relay_log_info_file = /data/mysql/relay-log.info read_only = 1 expire-logs-days = 14 sync_binlog = 1 #需要同步的库，如果不设置，默认同步所有库。 #replicate-do-db = xxx #不需要同步的表 replicate-wild-ignore-table=mydb.sp_counter #不需要同步的库 replicate-ignore-db = mysql,information_schema,performance_schema binlog_cache_size = 4m max_binlog_cache_size = 8m max_binlog_size = 1024m log_slave_updates =1 #binlog_format = row binlog_format = mixed relay_log_recovery = 1 key_buffer_size = 32m read_buffer_size = 1m read_rnd_buffer_size = 16m bulk_insert_buffer_size = 64m myisam_sort_buffer_size = 128m myisam_max_sort_file_size = 10g myisam_repair_threads = 1 myisam_recover #设置默认引擎为myisam，下面这些参数一定要加上。 default-storage-engine=myisam default-tmp-storage-engine=myisam #关闭innodb引擎 skip-innodb innodb = off disable-innodb #设置sql_mode模式为no_auto_create_user sql_mode = no_auto_create_user #关闭innodb引擎 loose-skip-innodb loose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0 loose-innodb-cmp-per-index-reset=0 loose-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 ##复制主库2的数据## [mysqld3] port = 3307 basedir = /usr/local/mysql datadir = /data/mysql character-set-server = utf8mb4 #指定实例2的sock文件和pid文件 socket = /tmp/mysql3.sock pid-file=/data/mysql/mysql3.pid skip-name-resolv = 1 open_files_limit = 65535 back_log = 103 max_connections = 512 max_connect_errors = 100000 table_open_cache = 2048 tmp-table-size = 32m max-heap-table-size = 32m query-cache-size = 0 external-locking = false max_allowed_packet = 32m sort_buffer_size = 2m join_buffer_size = 2m thread_cache_size = 51 query_cache_size = 32m tmp_table_size = 96m max_heap_table_size = 96m query_cache_type=1 log-error=/data/logs/mysqld3.log slow_query_log = 1 slow_query_log_file = /data/logs/slow3.log long_query_time = 0.1 # binary logging # # 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录， # 这里指定实例2的binlog日志为/data/binlog2目录 # 每个从库和每个实例的server_id不能一样。 server-id = 22 log-bin = /data/binlog2/mysql-bin log-bin-index =/data/binlog2/mysql-bin.index relay_log = /data/binlog2/mysql-relay-bin relay_log_index = /data/binlog2/mysql-relay.index master-info-file = /data/mysql/master3.info relay_log_info_file = /data/mysql/relay-log3.info read_only = 1 expire-logs-days = 14 sync_binlog = 1 #不需要复制的库 replicate-ignore-db = mysql,information_schema,performance_schema binlog_cache_size = 4m max_binlog_cache_size = 8m max_binlog_size = 1024m log_slave_updates =1 #binlog_format = row binlog_format = mixed relay_log_recovery = 1 key_buffer_size = 32m read_buffer_size = 1m read_rnd_buffer_size = 16m bulk_insert_buffer_size = 64m myisam_sort_buffer_size = 128m myisam_max_sort_file_size = 10g myisam_repair_threads = 1 myisam_recover #设置默认引擎为myisam default-storage-engine=myisam default-tmp-storage-engine=myisam #关闭innodb引擎 skip-innodb innodb = off disable-innodb #设置sql_mode模式为no_auto_create_user sql_mode = no_auto_create_user #关闭innodb引擎，下面这些参数一定要加上。 loose-skip-innodb loose-innodb-trx=0 loose-innodb-locks=0 loose-innodb-lock-waits=0 loose-innodb-cmp=0 loose-innodb-cmp-per-index=0 loose-innodb-cmp-per-index-reset=0 loose-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] quick max_allowed_packet = 32m ```
3、設定主庫sql_mode,mysql5.6預設需要在啟動檔檔裡面設定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.
#将上面默认的#other_args开启后改为
other_args="--sql-mode=no_auto_create_user"4、開啟主庫和從庫
#主库 service mysqld start #开启从库的二个实例 /usr/local/mysql/bin/mysqld_multi start 2 /usr/local/mysql/bin/mysqld_multi start 3
5、在兩台主庫上面分別授權複製帳號
#需要授权三个从库的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、在三個從庫分別開啟同步。
#进入第一个实例执行 $ mysql -s /tmp/mysql.sock mysql> 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; #进入第二个实例执行 $ mysql -s /tmp/mysql3.sock mysql> 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、測試數據同步
在二個主資料庫分別建表和插入數據，到從庫查看可以看到二個主庫同步到同一個從庫上面的所有數據。
8、在每台從庫伺服器上設定任務計劃每分鐘刷新第一個實例的表
# crontab -l */1 * * * * mysql -s /tmp/mysql.sock -e 'flush tables;'
mysql5.6多主一從的坑
1、mysql5.6預設的引擎是innodb預設同步的時候一定要把主和從的sql_mode模式裡面的no_engine_substitution這個參數關掉。如果不關閉innodb同步到從函式庫上面的sql將會找不到innodb引擎導致同步失敗。
2、在mysql5.6開啟多實例的時候第一次啟動的時候在你資料庫的安裝目錄裡面（/usr/local/mysql/)會產生my.cnf設定文件，預設會優先讀取資料庫安裝目錄裡面的設定檔。導致多實例不生效。
以上是Centos7怎麼配置Mysql多主一從的詳細內容。更多資訊請關注PHP中文網其他相關文章！
