How to Setup MySQL Replication

Configuring the Master

Create a MySQL user on the master which the slave will connect as. The user requires only SLAVE REPLICATION permissions. I used phpmyadmin for this and named my user slave.

Configure a server-id and binary logging on the MASTER in /etc/mysql/my.cnf:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Configure the server to flush writes to disk in real time for greater reliability:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Restart MySQL:

/etc/init.d/mysql restart

Connect to MySQL on the MASTER and issue the following commands to delete any existing binary logs and then lock your tables:

mysql -h localhost -u username -p
reset master;
FLUSH TABLES WITH READ LOCK;

Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect.

Now use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master:

SHOW MASTER STATUS;

You should see output something like below:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. 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 an empty string and 4.

If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place and then proceed to transfer a dump of your databases as described below.

Connect to MySQL on the MASTER with a new shell and use mysqldump to create a dump of all databases:

mysqldump -h localhost -u username -p --all-databases --lock-all-tables > ~/dump.db

Configure the Slave(s)

Configure a server-id and binary logging on the slave in /etc/mysql/my.cnf:

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

Configure the server to flush writes to disk in real time for greater reliability:

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Note that while binary logging is not required on the slave it may be useful for backup or in the event you need to recover from a failure.

Restart MySQL:

/etc/init.d/mysql restart

Import your data dump:

mysql -h localhost -u username -p < ~/dump.db

To set up the slave to communicate with the master for replication, you must tell the slave the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

reset master;
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;

PS) I added reset master in case binary logging had been enabled on the slave previously. This will clear any existing binary logs.

Now start the slave:

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. You also find error messages in the slave's error log if it is not able to replicate for any reason.

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.

Administration

To check the status of replication connect to MySQL on the SLAVE and issue the following command:

mysql -h localhost -u username -p
SHOW SLAVE STATUS\G

To pause and then restart replication in a slave:

STOP SLAVE;
START SLAVE;

Disable MySQL Replication

To completely disable MySQL replication on a slave, first login to the slave as your mysql root user:

mysql -h localhost -u username -p

Then issue the following commands:

STOP SLAVE;
RESET SLAVE;
change master to master_host='', master_user='',  master_password='';

The first command suspends replication. The second command tells the slave to forget it's position in the relay log. At this point any obsolete relay logs that have built up since replication was last running will be automatically be removed from the slave.

The third command tells the slave to forget it's configuration for the master server, otherwise replication would automatically start up again when MySQL is next restarted.

Replication should now be completely disabled, which you can check with:

SHOW SLAVE STATUS\G

You might also want to add the following to the [mysqld] section of your my.cnf to ensure replication wouldn't start even if some configuration were left in place:

skip-slave-start

And finally, although you probably configured replication dynamically with SQL commands such as “change maser to” and so won't necessarily have any configuration in your my.cnf relating to replication, you might still want to check for and comment out anything beginning with “master-”.


Subscribe to the RSS feed for Andy's Debian HOWTOs

Article from Andy's Debian HOWTOs (http://www.besy.co.uk/debian/debian)

 
debian/how_to_setup_mysql_replication.txt · Last modified: 2010/10/06 12:23 (external edit) · [Old revisions]
Recent changes RSS feed Powered by Debian Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki