Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.2Kb
HTML Download (TGZ) - 289.0Kb
HTML Download (Zip) - 301.1Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you setup and use replication?

3.3.1 How do you setup and use replication?

MySQL has built-in support for several types of replication. Replication is usually employed with the purpose of increasing the performance and/or the fault-tolerance of the server and by extension the application. However, setting up replication can be a somewhat complicated and error prone process. But fear not, MySQL Utilities has tools that can help simplify and even automate several replication related tasks.

Consider a scenario where replication is used to obtain scalability, i.e. to increase the performance. Let us imagine an online shopping service. The shop started out small so a single server was enough to handle all the requests, however now it has become quite popular and as a result that single server is no longer able to handle all the requests. Being an online store, most of the operations are read operations (checking existing products, reviews, stock availability, etc).

Objectives

Our goal is to use replication in order to improve the throughput of the service by adding more servers which will become replicas of the already existing server. These replicas will allow scaling out of the service by taking up all the read requests, leaving the old server (now called the master) in charge of the writes. Rather than doing everything "by hand" with the mysql command line, we are going to setup this replication scenario using a single script, mysqlreplicate which will do most of the hard work for us. We then check the result using the mysqlrpladmin utility.

Let us assume the existing server, Server1, is running on port 13001 on the local machine with IP 192.168.1.1 and that we want to add 2 new servers, Server2 running on 192.168.1.2:13001 and Server3 running on 192.168.1.3:3306.

Example Execution

shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
--slave=slave_acc1@192.168.1.2:13001 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
          --slave=slave_acc2@192.168.1.3:3306 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.3: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

shell> mysqlrplcheck --master=m_account@192.168.1.1:13001 \
          --slave=slave_acc1@192.168.1.2:13001

# master on 192.168.1.1: ... connected.
# slave on 192.168.1.2: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [FAIL]

Slave is NNN seconds behind master.

# ...done.

shell> mysqlrplcheck --master=m_account@192.168.1.1:13001 \
          --slave=slave_acc2@192.168.1.3:3306

# master on 192.168.1.1: ... connected.
# slave on 192.168.1.3: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [FAIL]

Slave is N seconds behind master.

# ...done.

Discussion

In the above example we made use of the mysqlreplicate utility to setup a single tier replication topology, where the existing server is now the master for the two new servers which will act as slaves. Notice how we used the address of the old existing server in the --master option and in the --slave option we used the addresses of the new servers. Also notice the use of the -b flag, this makes replication start from the first event recorded in the master's binary log.

Also notice how we used the mysqlrplcheck utility to check the health of the replication. In this case, the failing test "Check slave delay" is expected, since the slaves are catching up with the master. When the slaves have read and applied all the transactions from the master's binary log the "Check slave delay" test will pass. Also, in case the slave wasn't properly configured and pointing to the master specified the "Is slave connect to master" test would notify us of that with a FAIL or WARN status.

Permissions Required

The m_account user needs the following privileges for the mysqlreplicate: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. As for the slave_acc users, they need the SUPER privilege. The repl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

Also, when using GTIDs, the slave_acc users must also have SELECT privilege over the mysql database in order to run the mysqlrplcheck utility successfully.

Tips and Tricks

In the mysqlreplicate utility we could have also used the --test-db option which creates a dummy database to test the replication setup. However, the mysqlrplcheck provides more detailed information in that regard.

As previously stated, the -b option tells the utility to start replication from the first event recorded in the master's binary log. Omitting this flag, in turn, makes the slaves replicate only what is stored in the master's binary log from the present moment onward.

Furthermore, using the --master-log-file and --master-log-pos options it is possible to specify respectively the master log file and the master log position from which the slave will start its replication process.

The -p flag can be used to ensure that the replication setup is only executed in case the storage engines match in both the master and the slave.

Regarding the mysqlrplcheck utility, we can use the -s option to check the output of the show slave status command. This can be useful for instance to check what might be causing the "Is slave connected" test to fail. We can also use the --master-log-file option to specify the name of the master information file to read.

Lastly, we can use the --verbose option in order to get more information about what is happening "under the hood".


User Comments
Sign Up Login You must be logged in to post a comment.