Centos7怎么配置Mysql多主一从

Centos7怎么配置Mysql多主一从

业务场景：

公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据mysql主从同步原理使用多从一主的方案解决。主库使用innodb引擎，从库开启多实例使用myisam引擎并将多个实例的数据同步到同一个目录，并通过flush tables 在一个实例里面访问其他实例的数据。

解决思路：

1、主数据库使用innodb引擎，并设置sql_mode为 no_auto_create_user2、从库开启多实例，将多个主库里面的数据通过主从复制同步到同一个数据目录。从库的每个实例对应一个主库。多个实例使用同一个数据目录。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= 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 //这里使用的混合模式复制relay_log_recovery = 1#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的库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、从库配置文件。多个从库配置文件除了server-id不能一样其他都一样。

[root@slavedb01 ~]# cat /etc/my.cnf[client]port= 3306socket= /tmp/mysql.sock[mysqld_multi]# 指定相关命令的路径mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin##复制主库1的数据##[mysqld2]port = 3306basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定实例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#指定第一个实例的错误日志和慢查询日志路径log-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = /data/logs/slow.loglong_query_time = 0.1# binary logging## 指定实例1的binlog和relaylog路径为/data/binlog目录# 每个从库和每个实例的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#需要同步的库，如果不设置，默认同步所有库。#replicate-do-db = xxx#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的库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#设置默认引擎为myisam，下面这些参数一定要加上。default-storage-engine=myisamdefault-tmp-storage-engine=myisam#关闭innodb引擎skip-innodbinnodb = offdisable-innodb#设置sql_mode模式为no_auto_create_usersql_mode = no_auto_create_user#关闭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 ##复制主库2的数据##[mysqld3]port = 3307basedir = /usr/local/mysqldatadir = /data/mysqlcharacter-set-server = utf8mb4#指定实例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 ## 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，# 这里指定实例2的binlog日志为/data/binlog2目录# 每个从库和每个实例的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#不需要复制的库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#设置默认引擎为myisamdefault-storage-engine=myisamdefault-tmp-storage-engine=myisam#关闭innodb引擎skip-innodbinnodb = offdisable-innodb#设置sql_mode模式为no_auto_create_usersql_mode = no_auto_create_user#关闭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[mysqldump]quickmax_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.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; #进入第二个实例执行$ 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、测试数据同步

在二个主数据库分别建表和插入数据，到从库查看可以看到二个主库同步到同一个从库上面的所有数据。

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配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。