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.
- Create a user on your master server that your slave can use to connect remotely with. This user MUST have at least
REPLICASTIONprivileges (I just set up a user with all privileges).
- Make sure you put
[mysqld]section of your
my.cnffile on your master server.
[mysqld]section of your
my.cnffile on your slave server.
/etc/my.cnfon your slave with the account information you created in step 1.
- Shut down your slave database
- Bring down your master database and
datadirectory and then copy it down and untar it into your slave’s
datadirectory. Make sure all of the permissions are set correctly on the slave’s data directory (mysql.mysql and 700 on DB directories).
- 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).
- I had to run a
CHANGE MASTER TOand then a
START SLAVEcommand before everything was replication perfectly, but after that it worked fine.
Now, when you create tables, alter tables,
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.
- 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
mysqldumpagainst without bringing down my live server.
- 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.