Setting up database replication on MySQL
Just replicate the database onto another server, and you have two atabase servers for SELECTs and you can leave the master for INSERTs, DELETEs and UPDATEs. The only bad thing about this is that you will need different connections for read and write (nothing that Dr. Nic's magic can't resolve), and you must have a fast connection between the two servers or you may have records that don't show up instantly when you store them (that's bad for a web application).
First of all, I'm supposing you already have a production database running in a server. If you start from scratch with several database servers, then it's much easier, as you don't have to dump/restore databases.
Go to your master server (let's say her name is scherie), and edit /etc/mysql/my.cnf:
Make sure you have set server-id to 1:
You need to uncomment the bind-address line, as we need port 3306 open for our slave:
By default, Debian for instance, already saves the transaction log. Uncomment or add it if you don't have it:
Add a line for the database that will be dumped to the binary log:
You can now restart the database:
Open a mysql client, and create a new user called replication_user (change this to whatever you want):
Now we have to lock temporarily the database while we proceed to dump it, so we have the correct binary log offset (Position).
Don't close the mysql client. Leave the terminal open, otherwise, the database will be unlocked. Write down the File and Position values, we will need them later.
Dump the database (you could use load data from master, but it's deprecated and will be removed in the future).
Once dumped return to the open terminal, and unlock tables:
It would be a good idea to compress the database dump before transferring it:
You should also allow access from the slave to the master server, but block it otherwise (basic iptables rules):
You're done with the master configuration. Let's open a terminal to our slave server (let's say she is called athena):
Check whether we have access to the master:
We can restore now the database dump from the master:
Add the following data to the slave's my.cnf, under the [mysqld] section:
Connect as root to our slave server:
Stop the slave:
Now, we need those File and Position parameters we got from the master, change the values on master_log_file and master_log_pos for those you wrote down previously:
We start the slave:
Now, if we issue a show slave status, you should see the following variables to 'Yes':
If you have Slave_IO_Running set to No, you haven't probably locked correctly the database prior to dumping it. You will have to repeat the dumping process locking correctly the database.
If everything is correct, your database will be automatically replicated. Doing a simple show processlist; in either the master or the slave will show whether the communication is working.
You can also take a look at the full guide to replication at the MySQL official site.