步骤

  1. 锁定主库的表。
  2. 通过mysqldump导出主库的数据。
  3. 清理备库数据，并重新建库和导入数据。
  4. 配置主从同步信息。
  5. 解锁主库的表。

1、锁定主库的表

登录主库并锁表。

1
2
# mysql -u root -p
mysql> flush tables with read lock;

查看主库同步的相关信息，并记录bin-log的名称（mysql-bin.000017）和位置信息（27211813）。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000017
         Position: 27211813
     Binlog_Do_DB: a_product,b_product,c_product,d_product,e_product,f_product
 Binlog_Ignore_DB: information_schema,mysql,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

2、通过mysqldump导出主库的数据

执行脚本导出数据，脚本内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#!/bin/sh
MYSQL_HOST='192.168.1.100'
MYSQL_USER='root'
MYSQL_PWD='123456'

DB_LISTS=(
a_product
b_product
c_product
d_product
e_product
f_product
)

for db in ${DB_LISTS[@]}
do
	mysqldump -u ${MYSQL_USER} -h ${MYSQL_HOST} -p${MYSQL_PWD} -E -R --single-transaction --max-allowed-packet=524288000 ${db} > ${db}.sql
done

3、清理备库数据，并重新建库和导入数据

打开备库可写开关。
set global super_read_only=OFF;

清理备库数据的操作省略。先把上一步骤导出的sql放在备库本地，通过执行脚本重新建库和导入数据，脚本内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/sh

MYSQL_HOST='192.168.1.101'
MYSQL_USER='root'
MYSQL_PWD='123456'

DB_LISTS=(
a_product
b_product
c_product
d_product
e_product
f_product
)

for db in ${DB_LISTS[@]}
do
	mysql -u ${MYSQL_USER} -h ${MYSQL_HOST} -p${MYSQL_PWD} -f -e "create database ${db};"
	mysql -u ${MYSQL_USER} -h ${MYSQL_HOST} -p${MYSQL_PWD} -f -e "use ${db};source ${db}.sql;"
done

4、配置主从同步信息

执行以下命令，Slave_IO_Running和Slave_SQL_Running为yes则成功。

1
2
3
4
5
6
7
8
9
stop slave;
change master to master_host='192.168.1.100',master_user='backup',master_password='123456',master_log_file='mysql-bin.000017',master_log_pos=27211813;
start slave;
show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

关闭备库可写开关。
set global super_read_only=ON;

5、解锁主库的表

登录主库并解锁表。

1
2
# mysql -u root -p
mysql> unlock tables;

6、主库配置信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
[client]
socket=/tmp/mysql.sock

[mysql]
socket = /tmp/mysql.sock
port = 3306
default-character-set=utf8

[mysqld]
user=ampmon
port=3306
explicit_defaults_for_timestamp=true
skip-name-resolve
basedir=/data/mysql/program/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
character-set-server=utf8
lower_case_table_names=1
default-storage-engine=InnoDB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
slow-query-log=OFF
slow_query_log_file=/tmp/mysql_slow_query.log
long_query_time=10
log-error=/tmp/mysqld_error.log
back_log=100
max_connections=1024
wait_timeout=600
max_connect_errors=10
table_open_cache=2048
skip_external_locking
max_allowed_packet=16M
max_heap_table_size=128M
sort_buffer_size=16M
join_buffer_size=16M
thread_cache_size=32
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
ft_min_word_len=1
thread_stack=256K
transaction_isolation=READ-COMMITTED
tmp_table_size=64M
innodb_buffer_pool_size=128M
innodb_thread_concurrency=100
innodb_thread_sleep_delay=3000
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_log_file_size=32M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=30
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_file_format=Barracuda
innodb_write_io_threads=8
innodb_read_io_threads=4
innodb_doublewrite=0
innodb_purge_threads=1
innodb_stats_on_metadata=OFF
innodb_io_capacity=1000
optimizer_switch="index_condition_pushdown=off"
secure_file_priv=
log-bin-trust-function-creators=1
show_compatibility_56=1
event_scheduler=1

# master/slave
log-bin=mysql-bin
server-id=1
expire_logs_days=7

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=a_product
binlog-do-db=b_product
binlog-do-db=c_product
binlog-do-db=d_product
binlog-do-db=e_product
binlog-do-db=f_product

7、备库配置信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
[client]
socket=/tmp/mysql.sock

[mysql]
socket = /tmp/mysql.sock
port = 3306
default-character-set=utf8

[mysqld]
user=ampmon
port=3306
explicit_defaults_for_timestamp=true
skip-name-resolve
basedir=/data/mysql/program/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
character-set-server=utf8
lower_case_table_names=1
default-storage-engine=InnoDB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
slow-query-log=OFF
slow_query_log_file=/tmp/mysql_slow_query.log
long_query_time=10
log-error=/tmp/mysqld_error.log
back_log=100
max_connections=1024
wait_timeout=600
max_connect_errors=10
table_open_cache=2048
skip_external_locking
max_allowed_packet=16M
max_heap_table_size=128M
sort_buffer_size=16M
join_buffer_size=16M
thread_cache_size=32
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
ft_min_word_len=1
thread_stack=256K
transaction_isolation=READ-COMMITTED
tmp_table_size=64M
innodb_buffer_pool_size=128M
innodb_thread_concurrency=100
innodb_thread_sleep_delay=3000
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_log_file_size=32M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=30
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_file_format=Barracuda
innodb_write_io_threads=8
innodb_read_io_threads=4
innodb_doublewrite=0
innodb_purge_threads=1
innodb_stats_on_metadata=OFF
innodb_io_capacity=1000
optimizer_switch="index_condition_pushdown=off"
secure_file_priv=
log-bin-trust-function-creators=1
show_compatibility_56=1
event_scheduler=1

# master/slave
server_id=2
relay-log=relay-bin
read-only=1
super_read_only=1
relay_log_recovery=1
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys