1MYSQLRPLCHECK(1) MySQL Utilities MYSQLRPLCHECK(1)
2
3
4
6 mysqlrplcheck - Check Replication Prerequisitiess
7
9 mysqlrplcheck [options]
10
12 This utility checks the prerequisites for replication between a master
13 and a slave. These checks (called tests) are designed to ensure a
14 healthy replication setup. The utility performs the following tests:
15
16 1. Is the binary log enabled on the master?
17
18 2. Are there binary logging exceptions (such as *_do_db or *_ignore_db
19 settings)? If so, display them.
20
21 3. Does the replication user exist on the master with the correct
22 privileges?
23
24 4. Are there server_id conflicts?
25
26 5. Is the slave connected to this master? If not, display the master
27 host and port.
28
29 6. Are there conflicts between the master.info file on the slave and
30 the values shown in SHOW SLAVE STATUS on the master?
31
32 7. Are the InnoDB configurations compatible (plugin vs. native)?
33
34 8. Are the storage engines compatible (have same on slave as master)?
35
36 9. Are the lower_case_tables_names settings compatible? Warn if there
37 are settings for lowercase/uppercase table names that can cause
38 problems. See Bug #59240.
39
40 10. Is the slave behind the master?
41
42 The utility runs each test in turn unless there is a fatal error
43 preventing further testing, such as a loss of connection to the
44 servers.
45
46 Each test can complete with one of the following states: pass (the
47 prerequisites are met), fail (the prerequisites were met but one or
48 more errors occurred or there are exceptions to consider), or warn (the
49 test found some unusual settings that should be examined further but
50 may not be in error).
51
52 Use the --verbose option to see additional information such as server
53 IDs, lower_case_table_name settings, and the contents of the master
54 information file on the slave.
55
56 To see the values from the SHOW SLAVE STATUS statement, use the
57 --show-slave-status option. OPTIONS.PP mysqlrplcheck accepts the
58 following command-line options:
59
60 · --help
61
62 Display a help message and exit.
63
64 · --license
65
66 Display license information and exit.
67
68 · --master=<source>
69
70 Connection information for the master server.
71
72 To connect to a server, it is necessary to specify connection
73 parameters such as user name, host name, password, and either a
74 port or socket. MySQL Utilities provides a number of ways to
75 provide this information. All of the methods require specifying
76 your choice via a command-line option such as --server, --master,
77 --slave, etc. The methods include the following in order of most
78 secure to least secure.
79
80 · Use login-paths from your .mylogin.cnf file (encrypted, not
81 visible). Example : <login-path>[:<port>][:<socket>]
82
83 · Use a configuration file (unencrypted, not visible) Note:
84 available in release-1.5.0. Example :
85 <configuration-file-path>[:<section>]
86
87 · Specify the data on the command-line (unencrypted, visible).
88 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
89
90
91 · --master-info-file=<file>
92
93 The name of the master information file on the slave. The default
94 is master.info read from the data directory. Note: This option
95 requires that you run the utility on the slave and that you have
96 appropriate read access for the file.
97
98 · --quiet, -q
99
100 Turn off all messages for quiet execution. Note: Errors and
101 warnings are not suppressed.
102
103 · --show-slave-status, -s
104
105 Display the values from SHOW SLAVE STATUS on the master.
106
107 · --slave=<source>
108
109 Connection information for the slave server.
110
111 To connect to a server, it is necessary to specify connection
112 parameters such as user name, host name, password, and either a
113 port or socket. MySQL Utilities provides a number of ways to
114 provide this information. All of the methods require specifying
115 your choice via a command-line option such as --server, --master,
116 --slave, etc. The methods include the following in order of most
117 secure to least secure.
118
119 · Use login-paths from your .mylogin.cnf file (encrypted, not
120 visible). Example : <login-path>[:<port>][:<socket>]
121
122 · Use a configuration file (unencrypted, not visible) Note:
123 available in release-1.5.0. Example :
124 <configuration-file-path>[:<section>]
125
126 · Specify the data on the command-line (unencrypted, visible).
127 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
128
129
130 · --suppress
131
132 Suppress warning messages.
133
134 · --ssl-ca
135
136 The path to a file that contains a list of trusted SSL CAs.
137
138 · --ssl-cert
139
140 The name of the SSL certificate file to use for establishing a
141 secure connection.
142
143 · --ssl-cert
144
145 The name of the SSL key file to use for establishing a secure
146 connection.
147
148 · --ssl
149
150 Specifies if the server connection requires use of SSL. If an
151 encrypted connection cannot be established, the connection attempt
152 fails. Default setting is 0 (SSL not required).
153
154 · --verbose, -v
155
156 Specify how much information to display. Use this option multiple
157 times to increase the amount of information. For example, -v =
158 verbose, -vv = more verbose, -vvv = debug.
159
160 · --version
161
162 Display version information and exit.
163
164 · --width=<number>
165
166 Change the display width of the test report. The default is 75
167 characters.
168 NOTES.PP The login user must have the appropriate permissions to
169 execute SHOW SLAVE STATUS, SHOW MASTER STATUS, and SHOW VARIABLES on
170 the appropriate servers.
171
172 Mixing IP and hostnames is not recommended. The replication-specific
173 utilities will attempt to compare hostnames and IP addresses as aliases
174 for checking slave connectivity to the master. However, if your
175 installation does not support reverse name lookup, the comparison could
176 fail. Without the ability to do a reverse name lookup, the replication
177 utilities could report a false negative that the slave is (not)
178 connected to the master.
179
180 For example, if you setup replication using MASTER_HOST=ubuntu.net on
181 the slave and later connect to the slave with mysqlrplcheck and have
182 the master specified as --master=192.168.0.6 using the valid IP address
183 for ubuntu.net, you must have the ability to do a reverse name lookup
184 to compare the IP (192.168.0.6) and the hostname (ubuntu.net) to
185 determine if they are the same machine.
186
187 The path to the MySQL client tools should be included in the PATH
188 environment variable in order to use the authentication mechanism with
189 login-paths. This will allow the utility to use the my_print_defaults
190 tools which is required to read the login-path values from the login
191 configuration file (.mylogin.cnf). EXAMPLES.PP To check the
192 prerequisites of a master and slave that currently are actively
193 performing replication, use the following command:
194
195 shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311
196 # master on host1: ... connected.
197 # slave on host2: ... connected.
198 Test Description Status
199 ------------------------------------------------------------------------
200 Checking for binary logging on master [pass]
201 Are there binlog exceptions? [pass]
202 Replication user exists? [pass]
203 Checking server_id values [pass]
204 Is slave connected to master? [pass]
205 Check master information file [pass]
206 Checking InnoDB compatibility [pass]
207 Checking storage engines compatibility [pass]
208 Checking lower_case_table_names settings [pass]
209 Checking slave delay (seconds behind master) [pass]
210 # ...done.
211
212 As shown in the example, you must provide valid login information for
213 both the master and the slave.
214
215 To perform the same command but also display the contents of the master
216 information file on the slave and the values of SHOW SLAVE STATUS as
217 well as additional details, use this command:
218
219 shell> mysqlrplcheck --master=root@host1:3310 --slave=root@host2:3311 \
220 --show-slave-status -vv
221 # master on host1: ... connected.
222 # slave on host2: ... connected.
223 Test Description Status
224 ------------------------------------------------------------------------
225 Checking for binary logging on master [pass]
226 Are there binlog exceptions? [pass]
227 Replication user exists? [pass]
228 Checking server_id values [pass]
229 master id = 10
230 slave id = 11
231 Is slave connected to master? [pass]
232 Check master information file [pass]
233 #
234 # Master information file:
235 #
236 Master_Log_File : clone-bin.000001
237 Read_Master_Log_Pos : 482
238 Master_Host : host1
239 Master_User : rpl
240 Master_Password : XXXX
241 Master_Port : 3310
242 Connect_Retry : 60
243 Master_SSL_Allowed : 0
244 Master_SSL_CA_File :
245 Master_SSL_CA_Path :
246 Master_SSL_Cert :
247 Master_SSL_Cipher :
248 Master_SSL_Key :
249 Master_SSL_Verify_Server_Cert : 0
250 Checking InnoDB compatibility [pass]
251 Checking storage engines compatibility [pass]
252 Checking lower_case_table_names settings [pass]
253 Master lower_case_table_names: 2
254 Slave lower_case_table_names: 2
255 Checking slave delay (seconds behind master) [pass]
256 #
257 # Slave status:
258 #
259 Slave_IO_State : Waiting for master to send event
260 Master_Host : host1
261 Master_User : rpl
262 Master_Port : 3310
263 Connect_Retry : 60
264 Master_Log_File : clone-bin.000001
265 Read_Master_Log_Pos : 482
266 Relay_Log_File : clone-relay-bin.000006
267 Relay_Log_Pos : 251
268 Relay_Master_Log_File : clone-bin.000001
269 Slave_IO_Running : Yes
270 Slave_SQL_Running : Yes
271 Replicate_Do_DB :
272 Replicate_Ignore_DB :
273 Replicate_Do_Table :
274 Replicate_Ignore_Table :
275 Replicate_Wild_Do_Table :
276 Replicate_Wild_Ignore_Table :
277 Last_Errno : 0
278 Last_Error :
279 Skip_Counter : 0
280 Exec_Master_Log_Pos : 482
281 Relay_Log_Space : 551
282 Until_Condition : None
283 Until_Log_File :
284 Until_Log_Pos : 0
285 Master_SSL_Allowed : No
286 Master_SSL_CA_File :
287 Master_SSL_CA_Path :
288 Master_SSL_Cert :
289 Master_SSL_Cipher :
290 Master_SSL_Key :
291 Seconds_Behind_Master : 0
292 Master_SSL_Verify_Server_Cert : No
293 Last_IO_Errno : 0
294 Last_IO_Error :
295 Last_SQL_Errno : 0
296 Last_SQL_Error :
297 # ...done.
298
299 PERMISSIONS REQUIRED.PP The users on the master need the following
300 privileges: SELECT and INSERT privileges on mysql database, REPLICATION
301 SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users need the
302 SUPER privilege.
303
304 Also, when using GTIDs, the slave users must also have SELECT privilege
305 over the mysql database.
306
308 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
309 reserved.
310
311 This documentation is free software; you can redistribute it and/or
312 modify it only under the terms of the GNU General Public License as
313 published by the Free Software Foundation; version 2 of the License.
314
315 This documentation is distributed in the hope that it will be useful,
316 but WITHOUT ANY WARRANTY; without even the implied warranty of
317 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
318 General Public License for more details.
319
320 You should have received a copy of the GNU General Public License along
321 with the program; if not, write to the Free Software Foundation, Inc.,
322 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
323 http://www.gnu.org/licenses/.
324
325
327 For more information, please refer to the MySQL Utilities and Fabric
328 documentation, which is available online at
329 http://dev.mysql.com/doc/index-utils-fabric.html
330
332 Oracle Corporation (http://dev.mysql.com/).
333
334
335
336MySQL 1.5.6 09/15/2015 MYSQLRPLCHECK(1)