I clearly don\u2019t need to expound on the benefits of master-slave replication for your MySQL database. It\u2019s simply a good idea; one nicety I looked forward to was the ability to run backups from the slave without impacting the performance of our production database. But the benefits abound.
\nMost tutorials on master-slave replication use a read lock to accomplish a consistent copy during initial setup. Barbaric! With our users sending thousands of cards and gifts at all hours of the night, I wanted to find a way to accomplish the migration without any downtime.<\/p>\n
enabling bin-logging and taking a non- locking dump with the binlog position included. In effect, you\u2019re creating a copy of the db marked with a timestamp, which allows the slave to catch up once you\u2019ve migrated the data over. This seems like the best way to set up a MySQL slave with no downtime, so I figured I\u2019d document the step-by-step here, in case it proves helpful for others.
\nFirst, you\u2019ll need to configure the master\u2019s \/etc\/mysql\/my.cnf by adding these lines in the [mysqld] section:<\/p>\n
Restart the master mysql server and create a replication user that your slave server will use to connect to the master:<\/p>\n
Note: Mysql allows for passwords up to 32 characters for replication users.<\/p>\n
Next, create the backup file with the binlog position. It will affect the performance of your database server, but won\u2019t lock your tables:<\/p>\n
Now, examine the head of the file and jot down the values for MASTER_LOG_FILE and MASTER_LOG_POS. You will need them later:<\/p>\n
Because this file for me was huge, I gzip’ed it before transferring it to the slave, but that\u2019s optional:<\/p>\n
Now we need to transfer the dump file to our slave server (if you didn\u2019t gzip first, remove the .gz bit):<\/p>\n
While that\u2019s running, you should log into your slave server, and edit your \/etc\/mysql\/my.cnf file to add the following lines:<\/p>\n
Restart the mysql slave, and then import your dump file:<\/p>\n
Log into your mysql console on your slave server and run the following commands to set up and start replication:<\/p>\n
To check the progress of your slave:<\/p>\n
If all is well, Last_Error will be blank, and Slave_IO_State will report \u201cWaiting for master to send event\u201d. Look for Seconds_Behind_Master which indicates how far behind it is. It took me a few hours to accomplish all of the above, but the slave caught up in a matter of minutes. YMMV.
\nAnd now you have a newly minted mysql slave server without experiencing any downtime!<\/p>\n
A parting tip: Sometimes errors occur in replication. For example, if you accidentally change a row<\/p>\n
\u201cMySQL server has gone away\u201d error while running the initial dump. The solution he found was to add the following during the import on slave:<\/p>\n