MySQL Replication Notes

23 Oct 2007

dev.mysql.com replication documentation. Replication on MySQL is similar to Log Shipping on MS SQL; the default is statement level replication, but newer versions also offer row-based replication.

Setting Up

On the Master:

create the account that will be used by each slave for replication:

> create user ‘repl’@‘%’ identified by ‘secret’;
> grant replication slave on . to ‘repl’@‘%’;
> flush privileges;

enable for replication by editing modify /etc/my.cnf (fuller example below):

[mysqld]
server-id=1               # number needs to be unique amongst all servers - can be any number
log-bin=mysql-bin         # can be left empty, but convention is to call mysql-bin
binlog-ignore-db=mysql    # any databases not to replicate
# if using any InnoDB tables, for the greatest possible durability and consistency set:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
.
$ /etc/init.d/mysql restart

stop updates, record the log file name and position, copy data:

mysql> flush tables with read lock;
mysql> show master statusG

the following step must be done in another terminal:

$ mysqlhotcopy –method=scp –user=root –password=secret database root@dest_host:/var/lib/mysql
mysql> unlock tables; # release locks in the original terminal

other methods of copying data:
- if only using MyISAM the LOAD DATA FROM MASTER command can be used without having to stop the master; even though it’s usage is deprecated there’s no equivalent replacement. Additional permissions have to be assigned to the slave repl account (xx) and some network settings need to be temporarily changed (??), and a global read lock needs to be held on the master (thus preventing updates while transferring data)
- data can also be copied using mysqldump or by dumping the raw files. If using InnoDB there’s also a commercial product called Hot Backup.
if using different storage engines between the master and slave, don’t put the engine statements in the CREATE or ALTER TABLE statements (as they’ll be replicated). Either use SET storage_engine followed by CREATE TABLE, or stop the slave and issue ALTER TABLE statements on the slave; see documentation.
Binary log files must be removed when they’re no longer needed because MySQL doesn’t do so automatically. Use purge master logs to ‘mysql-bin.1234’; O’Reilly’s High Performance MySQL has a log purge script (section 7.5), as well as other useful scripts for managing replication

On the Slave:

enable for replication by editing modify /etc/my.cnf – only need to set a unique server-id, unless this slave will also act as a master for other slaves
restart mysql, check that data copied ok with mysqlhotcopy by doing a show tables; (etc.) in the new database

start replication:

mysql> change master to master host = ‘master host’, master user = ‘repl’, master_password = ‘secret’, master_log_file = ‘master_log_file’, master_log_pos = ‘master_log_pos’;
mysql> start slave;

Note: this information is stored in the file data_dir/master.info, so the password isn’t very secure

to quickly add another slave, see this topic (shutdown an existing slave, copy the data directory with logs and info files, set a unique server-id, start existing and new slave)

Master .my.cnf should look like:

[mysqld]
server-id = 71
log-bin=mysql-bin # use a standard name
relay-log=relay-bin # use a standard name
log=/var/log/mysql/mysql.log # general logging
log-slow-queries=/var/log/mysql/mysql-slow.log # log slow queries
log-error=/var/log/mysql/mysql-error.log # log errors
binlog-ignore-db=mysql # as master, don't send this to my slaves
log-slave-updates # this master is also a slave
replicate-do-db=foodb # as slave, only pull this db from *my* master

Slave .my.cnf should look like:

[mysqld]
server-id=72
log-bin=mysql-bin # use a standard name
relay-log=relay-bin # use a standard name
log=/var/log/mysql/mysql.log # general logging
log-slow-queries=/var/log/mysql/mysql-slow.log # log slow queries
log-error=/var/log/mysql/mysql-error.log # log errors

**Monitoring/Troubleshooting **

See Troubleshooting Replication.

On the Master:

show master statusG – shows the binary log file name and position. O’Reilly’s High Performance MySQL has a heartbeat script (section 7.5)
show processlistG – the master uses one thread to provide replication to slaves (Command: Binlog Dump). No state means replication hasn’t been setup correctly. Good states are Sending binlog event to slave, Finished reading one binlog; switching to next binlog, Has sent all binlog to slave; waiting for binlog to be updated.
show master logsG – shows names of existing binary log files
$ mysqlbinlog mysql-bin.1234 – allows you to read through binary and relay logs on both Master and Slave. Has the standard -u -h -p options, as well as -o to specify offset to start at

On the Slave:

show slave statusG (documentation) – key things to watch for are:
- Seconds_Behind_Master should be close to zero
- Slave_IO_Running and Slave_SQL_Running should both be ‘Yes’
- Read_Master_Log_Pos should only be slightly behind the position given by show master status on the master.
- Exec_Master_Log_Pos should only be slightly behind Read_Master_Log_Pos
- watch Last_Errno and Last_Error for errors and all the Replicate/Ignore DB/Table fields for data not being replicated. In an emergency, troublesome statements can be skipped over using TODO.
show process listG – the slave uses two threads to implement replication – the I/O thread reads events from the master and stores them in the relay log; and the SQL thread reads statements from the relay log and applies them. A good state for the I/O thread is Waiting for master to send event; see doco for many other states. Good states for the SQL thread are Reading event from the relay log; and Has read all relay log; waiting for the slave I/O thread to update it
start slave; see documentation.
stop slave; or even better stop slave sql_thread; Statements continue to be read and written to relay log, but they aren’t applied, allowing a backup of the slave to be done then allowing the slave to catch more rapidly when restarted – see documentation.
to prevent replication automatically occurring on a restart, start up server with –skip-slave-start to skip the saved .info files (or delete those files beforehand to wipe replication completely).

Sonia Hamilton - Blog

MySQL Replication Notes

23 Oct 2007

the following step must be done in another terminal: