MySQL Database Replication

Database replication is another means of taking backup of a database. Database will be synchronized with master and slave.

We need two servers one for configuring Master and other for Slave.

Replication, how it works:

  • Master has Binary Logging enabled
  • Master records completed SQL Statements
  • Master will inject ‘USE dbname;’ among the SQL Statements it records
  • Slave connects to Master (IO Thread)
  • Slave IO Thread requests next SQL statement from the Binary Logs
  • Slave IO Thread copies it to its Relay Logs
  • Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs, multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:


SHOW BINARY LOGS;
SHOW MASTER STATUS;

By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

This Article describes how to set up complete replication of a MySQL server. It assumes that you want to replicate all databases on the master and have not previously configured replication. You must shut down your master server briefly to complete the steps outlined here.

This procedure is written in terms of setting up a single slave, but you can repeat it to set up multiple slaves.

Make sure that you have a recent version of MySQL installed on the master and slaves, and that these versions are compatible.

If you encounter a problem, please do not report it as a bug until you have verified that the problem is present in the latest MySQL release.

Configuring Master Server:
Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege. If the account is used only for replication (which is recommended), you need not grant any additional privileges.

Suppose that your domain is mydomain.com and that you want to create an account with a user name of repl such that slave servers can use the account to access the master server from any host in your domain using a password of slavepass. To create the account, use this GRANT statement:

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

For MySQL versions older than 4.0.2, the REPLICATION SLAVE privilege does not exist. Grant the FILE privilege instead:

mysql> GRANT FILE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Flush all the tables and block write statements by executing a

mysql> FLUSH TABLES WITH READ LOCK;

* You can obtain a reliable binary snapshot of InnoDB tables only after shutting down the MySQL Server.

A way that works for both MyISAM and InnoDB tables is to take an SQL dump of the master. For this, you can use mysqldump –master-data on your master and later load the SQL dump file into your slave. However, this is slower than doing a binary copy.

Leave running the client from which you issue the FLUSH TABLES statement so that the read lock remains in effect. (If you exit the client, the lock is released.) Then take a snapshot of the data on your master server.

The easiest way to create a snapshot is to use an archiving program to make a binary backup of the databases in your master’s data directory. For example, use tar on Unix, or PowerArchiver, WinRAR, WinZip, or any similar software on Windows. To use tar to create an archive that includes all databases, change location into the master server’s data directory, then execute this command:

shell> tar -cvf /tmp/mysql-snapshot.tar .

If you want the archive to include only a database called this_db, use this command instead:

shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db

Then copy the archive file to the /tmp directory on the slave server host. On that machine, change location into the slave’s data directory, and unpack the archive file using this command:

shell> tar -xvf /tmp/mysql-snapshot.tar

You may not want to replicate the mysql database if the slave server has a different set of user accounts from those that exist on the master. In this case, you should exclude it from the archive. You also need not include any log files in the archive, or the master.info or relay-log.info files.

While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

If the master has been running previously without binary logging enabled, the log name and position values displayed by SHOW MASTER STATUS or mysqldump –master-data will be empty. In that case, the values that you need to use later when specifying the slave’s log file and position are the empty string (”) and 4.

After you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

mysql> UNLOCK TABLES;

Make sure that the [mysqld] section of the my.cnf file on the master host includes a log-bin option.
The section should also have a server-id=master_id option, where master_id must be a positive integer value from 1 to 232 – 1.
If those options are not present, add them and restart the server. The server cannot act as a replication master unless binary logging is enabled.

[mysqld]
log-bin=mysql-bin
server-id=1

We have to enable networking for MySQL. Open the MySQL configuration file and comment out the following lines.

[mysqld]
#skip-networking
#bind-address            = 127.0.0.1

Note
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use in your master my.cnf file.

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb-safe-binlog

Stop the server that is to be used as a slave and add the following lines to its my.cnf file:

[mysqld]
server-id=slave_id

The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is necessary that the ID of the slave be different from the ID of the master. For example:

[mysqld]
server-id=2

If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

If you do not specify a server-id value, it defaults to 0.

Note
If you omit server-id (or set it explicitly to 0), a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

If you made a binary backup of the master server’s data, copy it to the slave server’s data directory before starting the slave. Make sure that the privileges on the files and directories are correct. The system account that you use to run the slave server must be able to read and write the files, just as on the master.

If you made a backup using mysqldump, start the slave first. The dump file is loaded in a later step.

Configure Slave Server
Start the slave server. If it has been replicating previously, start the slave server with the –skip-slave-start option so that it does not immediately try to connect to its master. You also may want to start the slave server with the –log-warnings option to get more messages in the error log about problems (for example, network or connection problems). The option is enabled by default as of MySQL 4.0.19 and 4.1.2, but as of MySQL 4.0.21 and 4.1.3, aborted connections are not logged to the error log unless the value is greater than 1.

If you made a backup of the master server’s data using mysqldump, load the dump file into the slave server:

shell> mysql -u root -p < dump_file.sql 

Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;

Note
Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

The following table shows the maximum permissible length for the string-valued options.

Option Maximum Length

MASTER_HOST 60
MASTER_USER 16
MASTER_PASSWORD 32
MASTER_LOG_FILE 255

Start the slave threads:

mysql> START SLAVE;

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.

If you have forgotten to set the server-id option for the master, slaves cannot connect to it.

If you have forgotten to set the server-id option for the slave, you get the following error in the slave’s error log:

Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.
You also find error messages in the slave’s error log if it is not able to replicate for any other reason.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master’s binary log it has processed. Do not remove or edit these files unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave will use the values specified in the statement to update the status files automatically.

Note
The content of master.info overrides some of the server options specified on the command line or in my.cnf. See Section 14.8, “Replication and Binary Logging Options and Variables”, for more details.

Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.

About This Author

I'm a software engineer with many years of experience, open source enthusiast, now I'm creating and contributing to awesome PHP web projects. I love coding as much as learning, and I enjoy trying new languages and patterns. My passion revolves around (but is not limited to) back-end development.

You are not signed in. Sign in to post comments.