Innobackup mysql 多实例环境搭建主从同步

Innobackup mysql 多实例环境搭建主从同步

该实验是在MySQL多实例环境下做的；如果需要部署 mysql 多实例环境，则移步：

mysql 多实例案例实战： http://blog.csdn.net/wanglei_storage/article/details/49305239

---

mysql 的主从搭建大家有很多种方式，传统的 mysqldump 方式是很多人的选择之一。但对于较大的数据库则该方式并非理想的选择。使用 Xtrabackup 可以快速轻松的构建 mysql 主从架构。本文描述了使用innobackupex快速来搭建主从架构。

需求：

（1）用户通过 innobackupex 从 mysql-master备份数据 
（2）将主库备份的数据在 mysql-slave 还原数据 
（3）设置 mysql-master 和 mysql-slave 数据实时同步

环境： 
mysql-master：10.1.0.50 
mysql-slave：10.1.0.60 
mysql 版本：5.6.14

准备： 
percona-xtrabackup 安装包，提供 innobackupex 工具

下面开始：

一、安装 percona-xtrabackup（主从都需要，主用来备份，从用来还原）

下载地址：http://www.percona.com/downloads/XtraBackup/

[root@master ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev-devel 
[root@master ~]# rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

---

二、主库备份数据（mysql-master：10.1.0.50）

[root@master ~]# mkdir /tmp/backup 
[root@master ~]# time innobackupex –defaults-file=/etc/my.cnf –defaults-group=mysqld1 –user=root –password=$(cat /data/save/mysql_root) –socket=/tmp/mysql3306.sock /tmp/backup/

---

二、打包，传递到从库（mysql-slave：10.1.0.60）

[root@master tmp]# tar zcf backup.tgz backup/ 
[root@master tmp]# scp backup.tgz 10.1.0.60:/tmp

---

三、还原从主库备份的数据

解包： 
[root@slave tmp]# tar zxf backup.tgz

停止数据库，备份旧的数据目录，并且新建一个数据目录，进行还原从主库备份的数据： 
[root@slave data]# /usr/local/mysql/bin/mysqld_multi stop 1 
[root@slave tmp]# mv /usr/local/mysql/data/dbdata_3306/ /usr/local/mysql/data/dbdata_3306_bak 
[root@slave tmp]# mkdir -p /usr/local/mysql/data/dbdata_3306

还原数据： 
[root@slave data]# time innobackupex –defaults-file=/etc/my.cnf –defaults-group=mysqld1 –user=root –password=$(cat /data/save/mysql_root) –apply-log /tmp/backup/2016-04-19_16-57-37/

[root@slave data]# time innobackupex –defaults-file=/etc/my.cnf –defaults-group=mysqld1 –user=root –password=$(cat /data/save/mysql_root) –copy-back /tmp/backup/2016-04-19_16-57-37/

注意： 
–apply-log 选项的命令是准备在一个备份上启动mysql服务 
–copy-back 选项的命令从备份目录拷贝数据,索引,日志到my.cnf文件里规定的初始位置

授权，并且启动 mysql 服务： 
[root@slave data]# chown mysql:mysql -R /usr/local/mysql/data/dbdata_3306/ 
[root@slave data]# /usr/local/mysql/bin/mysqld_multi start 1

---

四、从库设置并启动主从同步

我们先来看看 innobackupex 备份后生成的一份文件，里面记载了主从所需要的 MASTER_LOG_FILE 和 MASTER_LOG_POS

登录数据库

输入 mysql-master 的 user，host，port，password，log_file，log_pos 等信息，接着启动主从命令：start slave；

查看主从情况（发现是正常的）：

root@(none) 17:15>root@(none) 17:15>root@(none) 17:15>show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 10.1.0.50                  Master_User: backup                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 151               Relay_Log_File: mysql-bin.000002                Relay_Log_Pos: 314        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           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: 151              Relay_Log_Space: 512              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: 0Master_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: 7018e49e-060c-11e6-ba4c-000c2943a57f             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           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: 01 row in set (0.00 sec)ERROR: No query specifiedroot@(none) 17:15>
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
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

---

五、mysql-master 和 mysql-slave 配置文件

mysql-master 配置档：

#!/bin/bash[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = rootpassword = yunjee0515ueopro1234[mysqld1]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3306socket = /tmp/mysql_3306.sockdatadir = /usr/local/mysql/data/dbdata_3306pid-file = /usr/local/mysql/data/dbdata_3306/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3306/mysql.errserver-id = 1skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名##MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           ##report-port=port                           #从属服务器的端口#report-host=host                           #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                           #超过5天的binlog删除  max_binlog_size=104857600#replicate-ignore-db = mysql                        #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3306##slave-skip-errors=0                           #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld2]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3307socket = /tmp/mysql_3307.sockdatadir = /usr/local/mysql/data/dbdata_3307pid-file = /usr/local/mysql/data/dbdata_3307/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3307/mysql.errserver-id = 1skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           ##report-port=port                                                       #从属服务器的端口#report-host=host                                                       #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                                                       #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql                                             #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3307##slave-skip-errors=0                                                   #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld3]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3308socket = /tmp/mysql_3308.sockdatadir = /usr/local/mysql/data/dbdata_3308pid-file = /usr/local/mysql/data/dbdata_3308/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3308/mysql.errserver-id = 1skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           ##report-port=port                                                       #从属服务器的端口#report-host=host                                                       #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                                                       #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql                                             #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3308##slave-skip-errors=0                                                   #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m># Remove the next comment character if you are not familiar with SQL#safe-updates[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243

mysql-slave 配置档：

#!/bin/bash[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = rootpassword = yunjee0515ueopro1234[mysqld1]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3306socket = /tmp/mysql_3306.sockdatadir = /usr/local/mysql/data/dbdata_3306pid-file = /usr/local/mysql/data/dbdata_3306/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3306/mysql.errserver-id = 2skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名##MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           #report-port=3306                            #从属服务器的端口report-host=10.1.0.60                           #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                           #超过5天的binlog删除  max_binlog_size=104857600#replicate-ignore-db = mysql                        #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3306##slave-skip-errors=0                           #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld2]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3307socket = /tmp/mysql_3307.sockdatadir = /usr/local/mysql/data/dbdata_3307pid-file = /usr/local/mysql/data/dbdata_3307/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3307/mysql.errserver-id = 2skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           #report-port=3307                                                       #从属服务器的端口report-host=10.1.0.60                                                       #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                                                       #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql                                             #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3307##slave-skip-errors=0                                                   #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld3]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3308socket = /tmp/mysql_3308.sockdatadir = /usr/local/mysql/data/dbdata_3308pid-file = /usr/local/mysql/data/dbdata_3308/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3308/mysql.errserver-id = 2skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8'                                           #连接时执行的SQLcharacter-set-server=utf8                                               #服务端默认字符集wait_timeout=1800                                                       #请求的最大连接时间interactive_timeout=1800                                                #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式log-bin = mysql-bin                                                     #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin                                                   #文件名格式relay-log-index = mysql-bin.index                                       #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW                                                       #binlog格式log-slave-updates=true                                                  #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on                                                            #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true                                           #report-port=3308                                                       #从属服务器的端口report-host=10.1.0.60                                                       #从属服务器的主机名master-info-repository=TABLE                                            #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE                                         #sync-master-info=1                                                      #启用之可确保无信息丢失slave-parallel-workers=2                                                #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32                                                   #master-verify-checksum=1                                                #slave-sql-verify-checksum=1                                             #启用复制有关的所有校验功能binlog-rows-query-log_events=1                                          ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5                                                       #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql                                             #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3308##slave-skip-errors=0                                                   #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m># Remove the next comment character if you are not familiar with SQL#safe-updates[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243