Setting up replication for MyISAM tables?

Question

Before setting up a replication within MySQL, the data first needs to be transferred to the slave.

Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, to the slave? Would this be enough to start the replication?

Answer 1

Since everything is MyISAM, here is what you need to do

STEP 01) Configure Master to be a Replication Master

If you do not have server-id defined in /etc/my.cnf, add one

[mysqld]
server-id=1

and service mysql restart

STEP 02) Check Binary Logging on the Master

If binary logging is active on the Master, do this

RESET MASTER;

to zap all binary logs and start fresh.

If binary logging is not active, do this

• Add log-bin=mysql-bin under [mysqld] in /etc/my.cnf
• service mysql restart

STEP 03) Copy /var/lib/mysql

You could set rsync to perform a brute force byte-by-byte copy of your MyISAM tables from /var/lib/mysql on one server to /var/lib/mysql on another server. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync. Start mysql on both Master and Slave.

STEP 04) Create Replication User on the Slave

GRANT REPLICATION CLIENT,REPLICATION SLAVE
ON *.* TO replicator@'PrivateIPofSlave' IDENTIFIED BY 'r3pl1cAt0r';

STEP 05) Set up replication

Run this command on the Slave

CHANGE MASTER TO
MASTER_HOST='PrivateIPofMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3pl1cAt0r',
MASTER_LOG_FILE='XXXX',
MASTER_LOG_POS=YYYY;
START SLAVE;

What is XXXX and YYYY ???

• XXXX is the last binary log file at the time you perform final rsync. There is a copy of that file in /var/lib/mysql on the Slave. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and use the last binary log.
• YYYY is the position of the last binary log file at the time you perform final rsync. Interestingly, the position is also the filesize of the binary log. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and take the last binary log's filesize to be the position YYYY.

STEP 06) Check replication

Run this

SHOW SLAVE STATUS\G

If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, Congratulations You are Done !!!

I have written similar posts on using rsync like this:

• MySQL slave replication reset with no Master Downtime (using MyISAM) (Jul 08, 2011 on ServerFault) with Sample Code !!!
• Create a MySQL slave from another slave, but point it at the master (Apr 08, 2011 on ServerFault)
• MySQL Replication - Introduce new Slave to replication (Feb 23, 2011)