Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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:

share|improve this answer
    
This is a great answer. I was only asking for (3), but the rest will be very helpful. This answer will probably be a definitive guide for myisam replication. –  Haluk Jul 20 '12 at 18:15
    
A note for other replication beginners like myself: Determining XXXX and YYYY goes into section (3), just before restarting the servers. –  Haluk Jul 26 '12 at 6:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.