1MYSQLREPLICATE(1) MySQL Utilities MYSQLREPLICATE(1)
2
6 mysqlreplicate - Set Up and Start Replication Between Two Servers
7
9 mysqlreplicate [options]
10
12 This utility permits an administrator to start replication from one
13 server (the master) to another (the slave). The user provides login
14 information for the slave and connection information for connecting to
15 the master. It is also possible to specify a database to be used to
16 test replication.
17 The utility reports conditions where the storage engines on the master
19 and the slave differ. It also reports a warning if the InnoDB storage
20 engine differs on the master and slave. For InnoDB to be the same, both
21 servers must be running the same "type" of InnoDB (built-in or the
22 InnoDB Plugin), and InnoDB on both servers must have the same major and
23 minor version numbers and enabled state.
24 By default, the utility issues warnings for mismatches between the sets
26 of storage engines, the default storage engine, and the InnoDB storage
27 engine. To produce errors instead, use the --pedantic option, which
28 requires storage engines to be the same on the master and slave.
29 The -vv option displays any discrepancies between the storage engines
31 and InnoDB values, with or without the --pedantic option.
32 Replication can be started using one of the following strategies.
34 · Start from the current position (default)
36 Start replication from the current master binary log file and
38 position. The utility uses the SHOW MASTER STATUS statement to
39 retrieve this information.
40 · Start from the beginning
42 Start replication from the first event recorded in the master
44 binary log. To do this, use the --start-from-beginning option.
45 · Start from a binary log file
47 Start replication from the first event in a specific master binary
49 log file. To do this, use the --master-log-file option.
50 · Start from a specific event
52 Start replication from specific event coordinates (specific binary
54 log file and position). To do this, use the --master-log-file and
55 --master-log-pos options.
56 OPTIONS.PP mysqlreplicate accepts the following command-line options:
57 · --help
59 Display a help message and exit.
61 · --license
63 Display license information and exit.
65 · --master=<master>
67 Connection information for the master server.
69 To connect to a server, it is necessary to specify connection
71 parameters such as user name, host name, password, and either a
72 port or socket. MySQL Utilities provides a number of ways to
73 provide this information. All of the methods require specifying
74 your choice via a command-line option such as --server, --master,
75 --slave, etc. The methods include the following in order of most
76 secure to least secure.
77 · Use login-paths from your .mylogin.cnf file (encrypted, not
79 visible). Example : <login-path>[:<port>][:<socket>]
80 · Use a configuration file (unencrypted, not visible) Note:
82 available in release-1.5.0. Example :
83 <configuration-file-path>[:<section>]
84 · Specify the data on the command-line (unencrypted, visible).
86 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
87 · login-path (.mylogin.cnf) : <login-path>[:<port>][:<socket>]
90 · Configuration file : <configuration-file-path>[:<section>]
92 · Command-line : <user>[:<passwd>]@<host>[:<port>][:<socket>]
94 · --master-log-file=<master_log_file>
96 Begin replication from the beginning of this master log file.
98 · --master-log-pos=<master_log_pos>
100 Begin replication from this position in the master log file. This
102 option is not valid unless --master-log-file is given.
103 · --pedantic, -p
105 Fail if both servers do not have the same set of storage engines,
107 the same default storage engine, and the same InnoDB storage
108 engine.
109 · --rpl-user=<replication_user>
111 The user and password for the replication user, in the format:
113 <user>[:<password>] or <login-path>.
114 · --slave=<slave>
116 Connection information for the slave server.
118 To connect to a server, it is necessary to specify connection
120 parameters such as user name, host name, password, and either a
121 port or socket. MySQL Utilities provides a number of ways to
122 provide this information. All of the methods require specifying
123 your choice via a command-line option such as --server, --master,
124 --slave, etc. The methods include the following in order of most
125 secure to least secure.
126 · Use login-paths from your .mylogin.cnf file (encrypted, not
128 visible). Example : <login-path>[:<port>][:<socket>]
129 · Use a configuration file (unencrypted, not visible) Note:
131 available in release-1.5.0. Example :
132 <configuration-file-path>[:<section>]
133 · Specify the data on the command-line (unencrypted, visible).
135 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
136 · --start-from-beginning, -b
139 Start replication at the beginning of events logged in the master
141 binary log. This option is not valid unless both --master-log-file
142 and --master-log-pos are given.
143 · --ssl-ca
145 The path to a file that contains a list of trusted SSL CAs.
147 · --ssl-cert
149 The name of the SSL certificate file to use for establishing a
151 secure connection.
152 · --ssl-cert
154 The name of the SSL key file to use for establishing a secure
156 connection.
157 · --ssl
159 Specifies if the server connection requires use of SSL. If an
161 encrypted connection cannot be established, the connection attempt
162 fails. Default setting is 0 (SSL not required).
163 · --test-db=<test_database>
165 The database name to use for testing the replication setup. If this
167 option is not given, no testing is done, only error checking.
168 · --verbose, -v
170 Specify how much information to display. Use this option multiple
172 times to increase the amount of information. For example, -v =
173 verbose, -vv = more verbose, -vvv = debug.
174 · --version
176 Display version information and exit.
178 NOTES.PP The login user for the master server must have the appropriate
179 permissions to grant access to all databases, and have the ability to
180 create user accounts. For example, the user account used to connect to
181 the master must have the WITH GRANT OPTION privilege.
182 The server IDs on the master and slave must be nonzero and unique. The
184 utility reports an error if the server ID is 0 on either server or the
185 same on the master and slave. Set these values before starting this
186 utility.
187 Mixing IP and hostnames is not recommended. The replication-specific
189 utilities will attempt to compare hostnames and IP addresses as aliases
190 for checking slave connectivity to the master. However, if your
191 installation does not support reverse name lookup, the comparison could
192 fail. Without the ability to do a reverse name lookup, the replication
193 utilities could report a false negative that the slave is (not)
194 connected to the master.
195 For example, if you setup replication using "MASTER_HOST=ubuntu.net" on
197 the slave and later connect to the slave with mysqlrplcheck and have
198 the master specified as "--master=192.168.0.6" using the valid IP
199 address for "ubuntu.net", you must have the ability to do a reverse
200 name lookup to compare the IP (192.168.0.6) and the hostname
201 (ubuntu.net) to determine if they are the same machine.
202 The path to the MySQL client tools should be included in the PATH
204 environment variable in order to use the authentication mechanism with
205 login-paths. This will allow the utility to use the my_print_defaults
206 tools which is required to read the login-path values from the login
207 configuration file (.mylogin.cnf). EXAMPLES.PP To set up replication
208 between two MySQL instances running on different ports of the same host
209 using the default settings, use this command:
210 shell> mysqlreplicate --master=root@localhost:3306 \
212 --slave=root@localhost:3307 --rpl-user=rpl:rpl
213 # master on localhost: ... connected.
214 # slave on localhost: ... connected.
215 # Checking for binary logging on master...
216 # Setting up replication...
217 # ...done.
218 The following command uses --pedantic to ensure that replication
220 between the master and slave is successful if and only if both servers
221 have the same storage engines available, the same default storage
222 engine, and the same InnoDB storage engine:
223 shell> mysqlreplicate --master=root@localhost:3306 \
225 --slave=root@localhost:3307 --rpl-user=rpl:rpl -vv --pedantic
226 # master on localhost: ... connected.
227 # slave on localhost: ... connected.
228 # master id = 2
229 # slave id = 99
230 # Checking InnoDB statistics for type and version conflicts.
231 # Checking storage engines...
232 # Checking for binary logging on master...
233 # Setting up replication...
234 # Flushing tables on master with read lock...
235 # Connecting slave to master...
236 # CHANGE MASTER TO MASTER_HOST = [...omitted...]
237 # Starting slave...
238 # status: Waiting for master to send event
239 # error: 0:
240 # Unlocking tables on master...
241 # ...done.
242 The following command starts replication from the current position of
244 the master (which is the default):
245 shell> mysqlreplicate --master=root@localhost:3306 \
247 --slave=root@localhost:3307 --rpl-user=rpl:rpl
248 # master on localhost: ... connected.
249 # slave on localhost: ... connected.
250 # Checking for binary logging on master...
251 # Setting up replication...
252 # ...done.
253 The following command starts replication from the beginning of recorded
255 events on the master:
256 shell> mysqlreplicate --master=root@localhost:3306 \
258 --slave=root@localhost:3307 --rpl-user=rpl:rpl \
259 --start-from-beginning
260 # master on localhost: ... connected.
261 # slave on localhost: ... connected.
262 # Checking for binary logging on master...
263 # Setting up replication...
264 # ...done.
265 The following command starts replication from the beginning of a
267 specific master binary log file:
268 shell> mysqlreplicate --master=root@localhost:3306 \
270 --slave=root@localhost:3307 --rpl-user=rpl:rpl \
271 --master-log-file=my_log.000003
272 # master on localhost: ... connected.
273 # slave on localhost: ... connected.
274 # Checking for binary logging on master...
275 # Setting up replication...
276 # ...done.
277 The following command starts replication from specific master binary
279 log coordinates (specific log file and position):
280 shell> mysqlreplicate --master=root@localhost:3306 \
282 --slave=root@localhost:3307 --rpl-user=rpl:rpl \
283 --master-log-file=my_log.000001 --master-log-pos=96
284 # master on localhost: ... connected.
285 # slave on localhost: ... connected.
286 # Checking for binary logging on master...
287 # Setting up replication...
288 # ...done.
289 RECOMMENDATIONS.PP You should set read_only=1 in the my.cnf file for
291 the slave to ensure that no accidental data changes, such as INSERT,
292 DELETE, UPDATE, and so forth, are permitted on the slave other than
293 those produced by events read from the master.
294 Use the --pedantic and -vv options for setting up replication on
296 production servers to avoid possible problems with differing storage
297 engines. PERMISSIONS REQUIRED.PP The users on the master need the
298 following privileges: SELECT and INSERT privileges on mysql database,
299 REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. The slave users
300 need the SUPER privilege. The repl user, used as the argument for the
301 --rpl-user option, is either created automatically or if it exists, it
302 needs the REPLICATION SLAVE privilege.
303
305 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
306 reserved.
307 This documentation is free software; you can redistribute it and/or
309 modify it only under the terms of the GNU General Public License as
310 published by the Free Software Foundation; version 2 of the License.
311 This documentation is distributed in the hope that it will be useful,
313 but WITHOUT ANY WARRANTY; without even the implied warranty of
314 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
315 General Public License for more details.
316 You should have received a copy of the GNU General Public License along
318 with the program; if not, write to the Free Software Foundation, Inc.,
319 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
320 http://www.gnu.org/licenses/.
321
324 For more information, please refer to the MySQL Utilities and Fabric
325 documentation, which is available online at
326 http://dev.mysql.com/doc/index-utils-fabric.html
327
329 Oracle Corporation (http://dev.mysql.com/).
330MySQL 1.5.6 09/15/2015 MYSQLREPLICATE(1)