Setting up replication for MyISAM tables?
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?
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
service mysql restart
STEP 02) Check Binary Logging on the Master
If binary logging is active on the Master, do this
to zap all binary logs and start fresh.
If binary logging is not active, do this
- service mysql restart
STEP 03) Copy
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 ???
XXXXis 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.
YYYYis 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
STEP 06) Check replication
SHOW SLAVE STATUS\G
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)
Our Awesome Free Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor
- Online MD5 Hash Generator
- Online SHA-1, SHA-256, SHA-512 Generator
- Online Base64 Encoder/Decoder
- Online CRC-32 Calculator
- Online Triple DES Encryptor/Decryptor
- Best World Clocks