1MYSQLREPLICATE(1) MySQL Utilities MYSQLREPLICATE(1)
2
6 mysqlreplicate - Setup replication among two MySQL servers
7
9 mysqlreplicate --master=<user>[<passwd>]@<host>:[<port>][:<socket>]
10 --slave=<user>[<passwd>]@<host>:[<port>][:<socket>]
11 [[--help | --version] | --quiet |
12 --verbose | --testdb=<test database> | --pedantic
13 --rpl_user=<uid:passwd>]
14
16 This utility permits an administrator to start replication among two
17 servers. The user provides login information to the slave and provides
18 connection information for connecting to the master.
19 You can also specify a database to be used to test replication as well
21 as a unix socket file for connecting to a master running on a local
22 host.
23 The utility will report conditions where the storage engines on the
25 master and the slave differ. Warnings are issued by default or you can
26 use the --pedantic option to require the storage engines to be the same
27 on both the master and slave. This would include not only that both
28 servers have the same storage engines enabled but also that the default
29 storage engine is the same.
30 Furthermore, the utility will also report a warning if the InnoDB stor‐
32 age engine differs from the master and slave. Similarly, --pedantic
33 requires the InnoDB storage engine to the be the same on the master and
34 slave.
35 The -vv option will also display any discrepancies among the storage
37 engines and InnoDB values with or without the --pedantic option.
38
40 --version
41 show version number and exit
42 --help show help page
44 --master <master>
46 connection information for master server in the form:
48 <user>:<password>@<host>:<port>:<socket>
49 --slave <slave>
51 connection information for slave server in the form:
53 <user>:<password>@<host>:<port>:<socket>
54 --rpl-user <replication-user>
56 the user and password for the replication user requirement - For
58 example, rpl:passwd - default = rpl:rpl
59 --test-db <test database>
61 database name to use in testing replication setup (optional)
63 --verbose, -v
65 control how much information is displayed. For example, -v =
67 verbose, -vv = more verbose, -vvv = debug
68 --pedantic, -p
70 fail if storage engines differ among master and slave (optional)
72
74 The login user must have the appropriate permissions to grant access to
75 all databases and the ability to create a user account. For example,
76 the user account used to connect to the master must have the WITH GRANT
77 OPTION privilege.
78 The server ID on the master and slave must be unique. The utility will
80 report an error if the server ID is 0 or is the same on the master and
81 slave. Set these values before starting this utility.
82
84 To setup replication between a MySQL instance on two different hosts
85 using the default settings, use this command:
86 $ mysqlreplicate --master=root@localhost:3306 \\
88 --slave=root@localhost:3307 --rpl-user=rpl:rpl
89 # master on localhost: ... connected.
90 # slave on localhost: ... connected.
91 # Checking for binary logging on master...
92 # Setting up replication...
93 # ...done.
94 The following command ensures the replication between the master and
96 slave is successful if and only if the InnoDB storage engines are the
97 same and both servers have the same storage engines with the same
98 default specified.:
99 $ mysqlreplicate --master=root@localhost:3306 \\
101 --slave=root@localhost:3307 --rpl-user=rpl:rpl -vv --pedantic
102 # master on localhost: ... connected.
103 # slave on localhost: ... connected.
104 # master id = 2
105 # slave id = 99
106 # Checking InnoDB statistics for type and version conflicts.
107 # Checking storage engines...
108 # Checking for binary logging on master...
109 # Setting up replication...
110 # Flushing tables on master with read lock...
111 # Connecting slave to master...
112 # CHANGE MASTER TO MASTER_HOST = [...omitted...]
113 # Starting slave...
114 # status: Waiting for master to send event
115 # error: 0:
116 # Unlocking tables on master...
117 # ...done.
118
120 You should use read_only = True in the my.cnf file for the slave to
121 ensure no accidental data changes such as INSERT, DELETE, UPDATE, etc.
122 are permitted on the slave.
123 Use the --pedantic and -vv options for setting up replication on pro‐
125 duction servers to avoid possible problems with differing storage
126 engines.
127
129 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
130 This program is free software; you can redistribute it and/or modify it
132 under the terms of the GNU General Public License as published by the
133 Free Software Foundation; version 2 of the License.
134 This program is distributed in the hope that it will be useful, but
136 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
137 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
138 Public License for more details.
139 You should have received a copy of the GNU General Public License along
141 with this program; if not, write to the Free Software Foundation, Inc.,
142 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
143
145 MySQL Utilities Team
146
148 2010, Oracle and/or its affiliates. All rights reserved.
1491.0.1 September 23, 2011 MYSQLREPLICATE(1)