MySQL 5.7.15 stand-alone master and slave to quickly build and configure replicated tables to different libraries

As a result of the online system disk stroke control too much resource consumption, resulting in server load is too high, often affect the stability of the intraday trading, recently decided to split the wind control into a separate library for calculation, and backfill operations.

In general, some of the tables related to wind control calculations are synchronized to the repository, but the synchronized tables need to be from Db_act->db_rsk;db_rsk->db_rsk;db_sys->db_rsk.

For a variety of reasons, we need to build a development and test environment on a single server. Long time no stand-alone building, in order to find the next convenient, special record as follows (in order to save trouble, the use of the method of stopping the Copy data folder):

1, assuming that the main library is in normal operation, Mysql_home:/usr/local/mysql,datadir:/usr/local/mysql/data;

2, the main library mysqladmin shutdown;

3, Cd/usr/local/mysql

Cp-r Data data2

4, edit the/ETC/MY.CNF, modify as follows:

[MYSQLD_MULTI]MYSQLD =/usr/local/mysql/bin/mysqld_safemysqladmin =/usr/local/mysql/bin/mysqladminlog =/usr/local/ Mysql/mysqld_multi.log[Mysqld1]Log_bin_trust_function_creators = Truequery_cache_size = 0query_cache_wlock_invalidate=offquery_cache_type=0innodb _thread_concurrency=4innodb_strict_mode=truetmpdir=/tmpautocommit=1binlog_rows_query_log_events=onlog_slave_ updates=onsync_relay_log=1innodb_buffer_pool_size=1024minnodb_flush_log_at_trx_commit=0transaction-isolation= Read-committedinnodb_autoinc_lock_mode = 2skip-name-resolvelower_case_table_names=1back-log= 500default-storage-engine=innodbsync_binlog=1log-bin=mysql-binbinlog_format=rowbinlog_checksum=nonebinlog_row_ Image=fullbinlog_cache_size = 8M Max_binlog_size = 500M max_binlog_cache_size = 16mexpire_logs_days = 3innodb_log_file_ Size=512minnodb-log-buffer-size=8minnodb-log-files-in-group=3innodb_data_home_dir=/usr/local/mysql/datapid-file =/usr/local/mysql/data/mysqld.piddatadir =/usr/local/ Mysql/datasocket =/usr/local/mysql/data/mysqld.sockInnodb_lock_wait_timeout=15lock_wait_timeout=60default-tmp-storage-engine=memoryinnodb-autoextend-increment= 16minnodb-buffer-pool-instances=1# innodb_read_io_threads=2# innodb_write_io_threads=2innodb-file-per-table= trueinnodb_rollback_on_timeout=true# innodb_flush_method=all_o_directopen-files-limit=32767innodb_open_files= 32767slow-query-log=truelong_query_time=0.02server-id=1port=3306thread_handling=pool-of-threadsthread_pool_size=4thread_cache_size=5max-connections=200max_allowed_packet= 10485760event_scheduler=onuserstat=oninnodb_use_global_flush_log_at_trx_commit=0sql_mode=strict_trans_tables, No_engine_substitution[Mysqld2]Log_bin_trust_function_creators = Truequery_cache_size = 0query_cache_wlock_invalidate=offquery_cache_type=0innodb _thread_concurrency=4innodb_strict_mode=truetmpdir=/tmpautocommit=1binlog_rows_query_log_events=onlog_slave_ updates=onsync_relay_log=1innodb_buffer_pool_size=1024minnodb_flush_log_at_trx_commit=0transaction-isolation= Read-committedinnodb_autoinc_lock_mode = 2skip-name-resolvelower_case_table_names=1back-log= 500default-storage-engine=innodbsync_binlog=1log-bin=mysql-binbinlog_format=rowbinlog_checksum=nonebinlog_row_ Image=fullbinlog_cache_size = 8M Max_binlog_size = 500M max_binlog_cache_size = 16mexpire_logs_days = 3innodb_log_file_ Size=512minnodb-log-buffer-size=8minnodb-log-files-in-group=3innodb_data_home_dir=/usr/local/mysql/data2pid-file =/usr/local/mysql/data2/mysqld.piddatadir =/usr/local/ Mysql/data2socket =/usr/local/mysql/data2/mysqld.sockInnodb_lock_wait_timeout=15lock_wait_timeout=60default-tmp-storage-engine=memoryinnodb-autoextend-increment= 16minnodb-buffer-pool-instances=1# innodb_read_io_threads=2# innodb_write_io_threads=2innodb-file-per-table= trueinnodb_rollback_on_timeout=true# innodb_flush_method=all_o_directopen-files-limit=32767innodb_open_files= 32767slow-query-log=truelong_query_time=0.02server-id=2port=3307thread_handling=pool-of-threadsthread_pool_size=4thread_cache_size=5max-connections=200max_allowed_packet= 10485760event_scheduler=onuserstat=oninnodb_use_global_flush_log_at_trx_commit=0sql_mode=strict_trans_tables, No_engine_substitution

5, because Data2 is a copy of data, and MySQL 5.6 began, there is a concept of server_uuid, it is launched mysqld automatically generated, Saved in the DataDir auto.cnf file, so you need to change the server_uuid of auto.cnf under DATA2, otherwise it will be slave Fatal error:the I/o thread slave Because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work. " and stop.

[email protected] data2]# cat AUTO.CNF
[Auto]
server-uuid=96b464e1-b68f-11e6-8321-00163f003609 #随便改什么都可以, the length and format are just as good.

6. Use Mysqld_multi to start the MySQL process as follows:

Mysqld_multi--defaults-file=/etc/mysql.cnf Start

To view the MySQL process that has started:

[Email protected] ~]# Mysqld_multi--DEFAULTS-FILE=/ETC/MY.CNF Report
Reporting MySQL (Percona Server) servers
MySQL (Percona Server) from Group:mysqld1 is running
MySQL (Percona Server) from Group:mysqld2 is running

7. View the current Binlog location of the main library

Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 6857082 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

8, set copy, library name rewrite, filter specific table (repository execution, mysqld2)

Note: The change REPLICATION filter is a new feature introduced in 5.7 that supports runtime modification without restarting the MySQL process and restarting the initialization parameters in 5.6 and earlier versions.

mysql> change REPLICATION FILTER replicate_rewrite_db = ((Db_sys,db_rsk), (Db_act,db_rsk));

mysql> change REPLICATION FILTER replicate_wild_do_table = (' Db_rsk.tb_act_operationconfig ', ' Db_rsk.tb_act_stock_ Quotation ', ' db_rsk.tb_sys_stock_info ', ' db_rsk.tb_act_unitequitiesposition ', ' db_rsk.tb_act_unitaccount ', ' db_ Rsk.tb_act_productunitasset ', ' db_rsk.tb_act_productaccount ', ' db_rsk.tb_act_productposition ', ' db_rsk.tb_rsk% ', ' db_rsk.tbjour_rsk% ');

Mysql> Change MASTER to
Master_host= ' 127.0.0.7 ',
Master_user= ' Root ',

master_password= ' MySQL ',
master_port=3306,
Master_log_file= ' mysql-bin.000006 ',
master_log_pos=6857082;

9, start slave;

Mysql>start slave; Query OK,0Rows Affected (0.00sec) MySQL>show slave status\g;*************************** 1. Row***************************slave_io_state:queueing Master Event toThe RelayLogMaster_host:127.0.0.1master_user:root Master_port:3306Connect_retry: -Master_log_file:mysql-Bin.000018Read_master_log_pos:16175239RELAY_LOG_FILE:IZ23NN1P4MJZ-Relay-Bin.000007Relay_log_pos:84333Relay_master_log_file:mysql-Bin.000018Slave_io_running:yes Slave_sql_running:yes replicate_do_db:replicate _ignore_db:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:db_rsk.tb_act_ope Rationconfig,db_rsk.tb_act_stock_quotation,db_rsk.tb_sys_stock_info,db_rsk.tb_act_unitequitiesposition,db_ Rsk.tb_act_unitaccount,db_rsk.tb_act_productunitasset,db_rsk.tb_act_productaccount,db_rsk.tb_act_ Productposition,db_rsk.tb_rsk%, Db_rsk.tbjour_rsk%Replicate_wild_ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:16144064Relay_log_space:287132until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: $master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1master_uuid:96b464e1-b68f-11e6-8321-00163f00368a master_info_file:/Usr/Local/Percona-Server-5.7. --Ten-Linux.x86_64.ssl101/Data2/master.info Sql_delay:0Sql_remaining_delay:NULLslave_sql_running_state:reading Event fromThe RelayLogMaster_retry_count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL _crl:master_ssl_crlpath:retrieved_gtid_set:executed_gtid_set:auto_po Sition:0replicate_rewrite_db: (Db_sys,db_rsk), (Db_act,db_rsk) channel_name:master_tls_ver Sion:1Rowinch Set(0.01sec) Error:no query specified

Test, Main Library insert

mysql> INSERT into Tb_rsk_rowid values (954256,954256);
Query OK, 1 row affected (0.01 sec)

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

Standby query

Mysql> SELECT * from Tb_rsk_rowid f where f.table_enname= ' 954256 ';
+--------------+------------+
| Table_enname | Curr_rowid |
+--------------+------------+
| 954256 | 954256 |
+--------------+------------+
1 row in Set (0.00 sec)

The configuration filtering needs to be noted:

If you apply replicate_[wild_]do_[table| at the same time DB] and replicate_rewrite_db, the replicate_rewrite_db is applied first, and then the replicate_[wild_]do_[table| is executed. DB].

Other:

If the replicate_rewrite_db or replicate_wild_do_table configuration is wrong and needs to be reconfigured, you can do the following:

Stop slave;

mysql> change REPLICATION FILTER replicate_wild_do_table = (' Db_rsk.tb_act_operationconfig ', ' Db_rsk.tb_act_stock_ Quotation ', ' db_rsk.tb_sys_stock_info ', ' db_rsk.tb_act_unitequitiesposition ', ' db_rsk.tb_act_unitaccount ', ' db_ Rsk.tb_act_productunitasset ', ' db_rsk.tb_act_productaccount ', ' db_rsk.tb_act_productposition ');
Query OK, 0 rows Affected (0.00 sec)

Start slave;

If you want to remove the settings, you can change REPLICATION FILTER replicate_wild_do_table = ();

Some other rules that rewrite the DB and filter specific DB details can refer to http://dev.mysql.com/doc/refman/5.7/en/replication-rules.html.

As with other HA mechanisms, the HA mechanism must have an automated alert triggering mechanism, especially an important online system, compared to a single machine.

MySQL 5.7.15 stand-alone master and slave to quickly build and configure replicated tables to different libraries