Shulou(Shulou.com)05/31 Report--

This article mainly introduces Centos7 how to configure Mysql multi-master one-slave related knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe you will have something to gain after reading this Centos7 how to configure Mysql multi-master one-slave article, let's take a look.

Business scenario:

Several major businesses of the company have been independent and placed on different database servers, but one business needs to be associated with multiple business libraries for joint query and statistics. At this time, it is necessary to synchronize the data of different business libraries to a slave database for statistics. According to the principle of mysql master-slave synchronization, a multi-slave master solution is used. The main library uses the innodb engine, opens multiple instances from the library and synchronizes the data of multiple instances to the same directory, and accesses the data of other instances in one instance through flush tables.

The solution is as follows:

1. The main database uses the innodb engine and sets sql_mode to no_auto_create_user

2. Open multiple instances from the slave database and synchronize the data in multiple master libraries to the same data directory through master-slave replication. Each instance of the slave library corresponds to a master library. Multiple instances use the same data directory.

3. Use the myisam engine in the slave library, disable the default innodb engine in the slave library, and the myisam engine can access the tables of other instances in the same data directory.

4. Each instance of the library needs to perform flush tables to see the data changes of other instance tables. You can set the crontab task schedule to refresh the table in the first instance every minute, so that the default instance connected by the program can see the real-time changes of the table.

5. Set the sql_mode of both the master library and the slave library to no_auto_create_user. Only when the sql of the innodb engine of the master library is synchronized to the slave library can it be executed successfully.

Solution architecture diagram:

Environment description:

Main library-1: 192.168.1.1

Main library-2VRV 192.168.1.2

From the library-3vvl 192.168.1.3

From the library-3vvl 192.168.1.4

From the library-3vvl 192.168.1.5

Implementation steps: (mysql installation steps are not described here)

1. Master database configuration file, multiple master database configuration files cannot be the same except 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 = / 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 = 0queryML 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 / / the mixed mode replication relay_log_recovery used here = replicate tables that do not require synchronization-wild-ignore-table=mydb.sp_counter# libraries that do not require synchronization 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 = 1innodbstores locksfor _ Binlog = 0 [mysqldump] quickmax_allowed_packet = 32m

2. From the library configuration file. Multiple slave configuration files cannot be the same except server-id.

[root@slavedb01 ~] # cat / etc/ my.cnf [client] port= 3306socket= / tmp/ mysql.sock [mysqld _ multi] # specify the path to the related command mysqld = / usr/local/mysql/bin/mysqld_safemysqladmin = / usr/local/mysql/bin/mysqladmin## copy the data of the main library # # [mysqld2] port= 3306basedir = / usr/local/mysqldatadir = / data/mysqlcharacter-set-server = utf8mb4# specify the sock file and pid file socket= / of instance 1 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 # specify the error log and slow log path of the first instance log-error=/data/logs/mysqld.logslow_query_log = 1slow_query_log_file = / data/logs/slow.loglong_query_time = 0.2 binary logging## specify the binlog and relaylog path of instance 1 as / data/binlog directory # the server_id of each slave library and each instance cannot be the same. 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 = libraries that need to be synchronized. If not set, all libraries will be synchronized by default. # replicate-do-db = xxx# tables that do not require synchronization-wild-ignore-table=mydb.sp_counter# libraries replicate-ignore-db = mysql,information_schema that do not require synchronization 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# set the default engine to myisam The following parameters must be added. Default-storage-engine=myisamdefault-tmp-storage-engine=myisam# shuts down innodb engine skip-innodbinnodb = offdisable-innodb# sets sql_mode mode to no_auto_create_usersql_mode = no_auto_create_user# shuts down innodb engine 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 # # copy data from main Library 2 # # [mysqld3] port = 3307basedir = / usr/local/mysqldatadir = / data/mysqlcharacter-set-server = utf8mb4# specify the sock file and pid file of instance 2 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 # # it must be noted here You cannot put the binlog and relaylog of two instances in the same directory. # specify the binlog log of instance 2 as / data/binlog2 directory # the server_id of each slave library and each instance cannot be the same. 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 = libraries that do not need to be copied 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# set the default engine to myisamdefault-storage-engine=myisamdefault-tmp-storage-engine=myisam# off Turn off innodb engine skip-innodbinnodb = offdisable-innodb# set sql_mode mode to no_auto_create_usersql_mode = no_auto_create_user# turn off innodb engine The following parameters must be added. 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. To set the main library sql_mode,mysql5.6 by default, you need to set sql_mode in the startup file to take effect.

# 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.# change the above default # other_args to other_args= "--sql-mode=no_auto_create_user"

4. Open the master library and slave library

# two instances of slave library enabled by master library service mysqld start# / usr/local/mysql/bin/mysqld_multi start 2/usr/local/mysql/bin/mysqld_multi start 3

5. Authorize the replication of accounts on the two main databases respectively

# the ip that needs to authorize three slave libraries can synchronize 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. Enable synchronization in the three slave libraries respectively.

# enter the first instance and execute $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 # enter the second instance and execute $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. Test data synchronization

Create tables and insert data in the two master databases, respectively. To view the slave library, you can see that the two master libraries are synchronized to all the data on the same slave database.

8. Set up a task schedule on each slave server to refresh the table of the first instance every minute

# crontab-lump flush tables;' 1 * mysql-s / tmp/mysql.sock-e 'flush tables;'

The pit of multi-master and one-slave mysql5.6

1. The default engine of mysql5.6 is innodb. When synchronizing by default, be sure to turn off the parameter no_engine_substitution in the master and slave sql_mode mode. If you do not turn off innodb synchronization to the sql above the slave library, the innodb engine will not be found and the synchronization will fail.

2. When mysql5.6 starts multiple instances for the first time, it will generate my.cnf configuration files in the installation directory of your database (/ usr/local/mysql/). By default, the configuration files in the database installation directory will be read first. Causes multiple instances not to take effect.

This is the end of the article on "how to configure Mysql Multi-Master and one Slave in Centos7". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to configure Mysql in Centos7". If you still want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.