So, you’ve got your MySQL database all up and running. Life is good. The problem? You get a ton of hits a day and your tables are too big for you to dump using mysqldump. Some of you non-techies might be wondering why this is an issue. Well, mysqldump uses a table level lock while dumping a table, making it inaccessible to your website while the dumping is taking place.

As a way to both have a hot spare to run heavy SELECT statements against and a way to run mysqldump without worries of table locks I recently set up MySQL Replication. It was almost too easy. The steps I used to set this all up where as follows.

1. Create a user on your master server that your slave can use to connect remotely with. This user MUST have at least REPLICASTION privileges (I just set up a user with all privileges).
2. Make sure you put log-bin and server-id=1 in the [mysqld] section of your my.cnf file on your master server.
3. Put log-bin and server-id=2 in the [mysqld] section of your my.cnf file on your slave server.
4. Add master-host, master-user, master-password, master-port to your /etc/my.cnf on your slave with the account information you created in step 1.
5. Shut down your slave database
6. Bring down your master database and tar up its data directory and then copy it down and untar it into your slave’s data directory. Make sure all of the permissions are set correctly on the slave’s data directory (mysql.mysql and 700 on DB directories).
7. Bring up your master database and then bring up your slave database. You’ll note that you now connect to your slave in the same manner that you connect to your master database (ie. if you have a root password set on your master you’ll use that login information on your slave as well).
8. I had to run a CHANGE MASTER TO and then a START SLAVE command before everything was replication perfectly, but after that it worked fine.

Now, when you create tables, alter tables, INSERT, UPDATE, DELETE, etc. those queries will be sent to your slave as well. This was just meant for archival purposes only, you’ll definitely want to read the manual before attempting to do this, but it is quite easy. You’ll also want to note a few things once you do have it working.

1. If you run a bad query (ie. DELETE FROM users) it will bone your slave database as well. The whole point for me was to set up a slave that I could run mysqldump against without bringing down my live server.
2. Running queries that alter the slave will cause huge problems with keeping your data in sync between the two. You might want to look into looping your master and slave (ie. A is B’s master and B is A’s master, which is possible).

Hope this helps someone else out there.