When working with replication, one of the most frequent maintenance tasks is adding a new slave for scale out. Although adding a new slave has been simplified with utilities like mysqlreplicate, provisioning the slave (copying data and getting replication started properly) can be a challenge (or at least tedious) if done manually.
Fortunately, we have two utilities - mysqldbexport and mysqldbimport - that have been designed to work with replication so that when the export is generated, you can include the proper replication control statements in the output stream.
mysqldbexport --server=root:root@localhost:13001 --all --export=both --rpl=master --rpl-user=rpl:rpl > data.sqlshell>
mysqldbimport --server=root:root@localhost:13002 data.sql# Source on localhost: ... connected. # Importing definitions from data.sql. ERROR: The import operation contains GTID statements that require the global gtid_executed system variable on the target to be empty (no value). The gtid_executed value must be reset by issuing a RESET MASTER command on the target prior to attempting the import operation. Once the global gtid_executed value is cleared, you may retry the import. shell>
mysql -uroot -proot -h 127.0.0.1 --port=13002 -e "RESET MASTER"shell>
mysqldbimport --server=root:root@localhost:13002 data.sql# Source on localhost: ... connected. # Importing definitions from data.sql. CAUTION: The following 1 warning messages were included in the import file: # WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options. #...done.
There are several operations listed here. The first one we see
is the execution of the mysqldbexport
utility to create a file that includes an export of all
databases as designated with the
--all option. We add the
'--export=both' option to ensure we include the definitions as
well as the data.
We also add the
which instructs mysqldbexport to generate
the replication commands with respect to the source server
being the master. Lastly, we include the replication user and
password to be included in the CHANGE MASTER command.
Next, we see an attempt to run the import using mysqldbimport but we see there is an error. The reason for the error is the mysqldbimport utility detected a possible problem on the slave whereby there were global transaction identifiers (GTIDs) recorded from the master. You can see this situation if you setup replication prior to running the import. The way to resolve the problem is to run the RESET MASTER command on the slave as shown in the next operation.
We then see a rerun of the import and in this case it succeeds. We see a warning that is issued any time there are replication commands detected in the input stream whenever GTIDs are enabled.
The user used to read data from the master must have the SELECT privilege on all databases exported. The user on the slave must have the SUPER privilege to start replication.
The warning issued during the import concerning GTIDs is to
ensure you are aware that the process for gathering the proper
GTIDs to execute on the slave include transactions from all
databases. Thus, if you ran a partial export that includes the
replication commands and you have GTIDs enabled, you should
option to skip the replication commands and restart
Should your data be large enough to make the use of
mysqldbexport impractical, you can use
mysqldbexport to generate the correct
replication commands anyway by using the
option. This will generate the SQL statements for the objects
but not the data. You can then use the replication commands
generated with your own backup and restore tools.
You can use the option
--rpl=slave to generate
a output stream that considers the source server a slave and
uses the source servers master settings for generating the
CHANGE MASTER command.
If you need to copy users, we can use the mysqluserclone to copy user accounts.