mysql 5.7.15单机主从快速搭建并配置复制表到不同库

一直以来因为线上系统盘中风控计算过于消耗资源,导致服务器负载太高,时常影响盘中交易的稳定性,最近决定了将风控拆分到独立的库进行计算,并进行回填操作。

总体来说,是将部分风控计算相关的表同步到备库,但是同步的表需要从db_act->db_rsk;db_rsk->db_rsk;db_sys->db_rsk。

由于各种原因,我们需要在一台服务器上搭建开发和测试环境。好久没有单机搭建了,为了下次查找方便,特记录如下(为了省事,采用停机拷贝data文件夹的方式):

1、假设主库已经在正常运行,mysql_home:/usr/local/mysql,datadir:/usr/local/mysql/data;

2、主库mysqladmin shutdown;

3、cd /usr/local/mysql

cp -R data data2

4、编辑/etc/my.cnf,修改为如下:

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/mysqld_multi.log [mysqld1] log_bin_trust_function_creators = true
query_cache_size = 0
query_cache_wlock_invalidate=OFF
query_cache_type=0
innodb_thread_concurrency=4
innodb_strict_mode=true
tmpdir=/tmp
autocommit=1
binlog_rows_query_log_events=on
log_slave_updates=ON
sync_relay_log=1
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=0
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2
skip-name-resolve
lower_case_table_names=1
back-log=500
default-storage-engine=InnoDB
sync_binlog=1
log-bin=mysql-bin
binlog_format=row
binlog_checksum=NONE
binlog_row_image=full
binlog_cache_size = 8M
max_binlog_size = 500M
max_binlog_cache_size = 16M
expire_logs_days = 3
innodb_log_file_size=512m
innodb-log-buffer-size=8M
innodb-log-files-in-group=3
innodb_data_home_dir=/usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysqld.pid
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysqld.sock
innodb_lock_wait_timeout=15
lock_wait_timeout=60
default-tmp-storage-engine=MEMORY
innodb-autoextend-increment=16M
innodb-buffer-pool-instances=1
# innodb_read_io_threads=2
# innodb_write_io_threads=2
innodb-file-per-table=true
innodb_rollback_on_timeout=true
# innodb_flush_method=ALL_O_DIRECT
open-files-limit=32767
innodb_open_files=32767
slow-query-log=true
long_query_time=0.02
server-id=1
port=3306
thread_handling=pool-of-threads
thread_pool_size=4
thread_cache_size=5
max-connections=200
max_allowed_packet=10485760
event_scheduler=ON
userstat=ON
innodb_use_global_flush_log_at_trx_commit=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION [mysqld2]
log_bin_trust_function_creators = true
query_cache_size = 0
query_cache_wlock_invalidate=OFF
query_cache_type=0
innodb_thread_concurrency=4
innodb_strict_mode=true
tmpdir=/tmp
autocommit=1
binlog_rows_query_log_events=on
log_slave_updates=ON
sync_relay_log=1
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=0
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2
skip-name-resolve
lower_case_table_names=1
back-log=500
default-storage-engine=InnoDB
sync_binlog=1
log-bin=mysql-bin
binlog_format=row
binlog_checksum=NONE
binlog_row_image=full
binlog_cache_size = 8M
max_binlog_size = 500M
max_binlog_cache_size = 16M
expire_logs_days = 3
innodb_log_file_size=512m
innodb-log-buffer-size=8M
innodb-log-files-in-group=3
innodb_data_home_dir=/usr/local/mysql/data2
pid-file = /usr/local/mysql/data2/mysqld.pid
datadir = /usr/local/mysql/data2
socket = /usr/local/mysql/data2/mysqld.sock innodb_lock_wait_timeout=15
lock_wait_timeout=60
default-tmp-storage-engine=MEMORY
innodb-autoextend-increment=16M
innodb-buffer-pool-instances=1
# innodb_read_io_threads=2
# innodb_write_io_threads=2
innodb-file-per-table=true
innodb_rollback_on_timeout=true
# innodb_flush_method=ALL_O_DIRECT
open-files-limit=32767
innodb_open_files=32767
slow-query-log=true
long_query_time=0.02
server-id=2
port=3307
thread_handling=pool-of-threads
thread_pool_size=4
thread_cache_size=5
max-connections=200
max_allowed_packet=10485760 event_scheduler=ON
userstat=ON
innodb_use_global_flush_log_at_trx_commit=0
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

5、因为data2是拷贝data的,而mysql 5.6开始,有个server_uuid的概念,它是启动时mysqld自动生成的,保存在datadir的auto.cnf文件中,所以需要更改data2下auto.cnf的server_uuid,否则在slave复制的时候就会因为“Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.”而停止。

[root@iZ23nn1p4mjZ data2]# cat auto.cnf
[auto]
server-uuid=96b464e1-b68f-11e6-8321-00163f003609  #随便改什么都可以,长度和格式一样就好了

6、使用mysqld_multi启动mysql进程,如下:

mysqld_multi --defaults-file=/etc/mysql.cnf start 1,2

查看已经启动的mysql进程:

[root@iZ23nn1p4mjZ ~]# 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、查看主库当前binlog位置

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、设置复制、库名重写、过滤特定表(备库执行,mysqld2)

注:CHANGE REPLICATION FILTER是5.7新引入的特性,支持运行时修改而无需重新启动mysql进程,在5.6以及之前的版本则更改初始化参数而重启。

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、启动slave;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 16175239
Relay_Log_File: iZ23nn1p4mjZ-relay-bin.000007
Relay_Log_Pos: 84333
Relay_Master_Log_File: mysql-bin.000018
Slave_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_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%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 16144064
Relay_Log_Space: 287132
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 36
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 96b464e1-b68f-11e6-8321-00163f00368a
Master_Info_File: /usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/data2/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB: (db_sys,db_rsk),(db_act,db_rsk)
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec) ERROR:
No query specified

测试,主库插入

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)

备库查询

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)

配置过滤需要注意的是:

如果同时应用Replicate_[Wild_]Do_[Table|Db]和Replicate_Rewrite_DB,则会先应用Replicate_Rewrite_DB,后执行Replicate_[Wild_]Do_[Table|Db]。

其他:

如果Replicate_Rewrite_DB或Replicate_Wild_Do_Table配置错了,需要重新配置,可以执行如下操作:

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;

如果要去掉设置,则可以CHANGE REPLICATION FILTER Replicate_Wild_Do_Table =();

其他一些重写db和过滤特定db的规则具体细节可参考http://dev.mysql.com/doc/refman/5.7/en/replication-rules.html。

跟其他HA机制一样,HA机制相对于单机来说,一定要有自动化的预警触发机制,尤其是重要的线上系统。

PS:发现一个奇怪的现象,第二天再去show slave status查看状态的时候,发现Replicate_Wild_Do_Table被清空了,如下:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000020
Read_Master_Log_Pos: 79613721
Relay_Log_File: iZ23nn1p4mjZ-relay-bin.000016
Relay_Log_Pos: 79613926
Relay_Master_Log_File: mysql-bin.000020
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db_sys,db_act
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 79613721
Relay_Log_Space: 79614175
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 96b464e1-b68f-11e6-8321-00163f00368a
Master_Info_File: /usr/local/Percona-Server-5.7.16-10-Linux.x86_64.ssl101/data2/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB: (db_sys,db_rsk),(db_act,db_rsk)
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

然后Replicate_Do_DB不为空了,奇怪了。。。因为刚刚已经调整了,明天再检查下对结果有没有影响。

如果不希望使用replication filter,可在my.cnf中配置上述过滤条件,如下所示:

mysql 5.7.15单机主从快速搭建并配置复制表到不同库

每个都需要单列,这个设计真是比较奇葩。。。。

在rewrite-db中,存储过程/函数同样会复制到slave(包括create procedure),并且自动rewrite所属db。但是table create ddl则完全根据wild-do_table的规则进行。这一点要尤其注意,不然会导致看起来很奇怪的现象。

相关典型错误及处理方法可见http://www.360doc.com/content/14/1128/13/12904276_428730593.shtml。

上一篇:SharePoint 2013 创建搜索中心及搜索设置


下一篇:blade 学习