When you have a MySQL database and want to spread the load across multiple servers, you can setup replication. What this does is in almost-realtime copy all commands from the master server to the slave server, keeping the data the same on both servers so both servers can serve requests simultaneously, sharing the load.
This is NOT a good system for backups, as any accident on the Master server will be replicated to the Slave server, such as dropping all your tables.
To setup Master-Slave Replication the first thing you need to do is create a user on the Master server that allows replication.
# mysql -u root -p mysql> grant replication slave on *.* TO repl@"%" identified by '[repl password]'; mysql> quit
Be sure to replace [repl password] with the actual password you want to use. Also, you must ensure that your firewall has port 3306:tcp open, the default port for the mysql server service.
Next, exit your ini file (typically /etc/my.cnf on Linux servers) to start binary logging of the Master server. You may or may not want to use the last line to ignore changes to the mysql database since that is the database used for mysql configuration and permissions.
Under the [mysqld] heading add the following lines:
log-bin=mysql-bin server-id=1 binlog-ignore-db="mysql"
Restart your mysql server service.
Before we start copying changes, we want to make sure the data on each server is the same, so dump the data from the Master
server and add it to the Slave server. This can easily be performed using mysqldump as follows:
# mysqldump -u root -p[password] [database]>/home/[user]/[database].sql
Be sure again to replace [password] with the actual password, [database] with the actual name of each database, one at a time.
Now that you have a snapshot of the data, get the binary position of the log file.
# mysql -u root -p[plain-text password] mysql> SHOW MASTER STATUS;
The output should look something like this:
+------------------+--------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+--------------------------+------------------+ | mysql-bin.000112 | 79 | | mysql | +------------------+--------------------------+------------------+ 1 row in set (0.00 sec)
Write down the filename and log position for use on the Slave server.
Next, copy the databases to your Slave server.
# scp /home/[user]/*.sql [Slave IP]:/home/[user]/
At this point we are done with the Master server. Now for the Slave.
Edit the Slave server's mysql configuation file (typically /etc/my.cnf on Linux servers) to identify its server number, master host and user.
Under the [mysqld] heading add the following lines:
server-id=2 master-host = [IP of Master Server] master-user = repl master-password = [repl password] master-port = 3306
Again, ensure you replace [repl password] with the actual repl user password, and [IP of Master Server] with the IP address of the Master server.
Insert the data from the Master server into the Slave server databases for each of the databases.
# mysql -p[password] [database] < /home/[user]/[database].sql
Be sure to replace [password] with the root users password, and [database] with each database's name, one at a time.
Restart the mysql server service.
Now log in to Mysql and configure the Slave replication.
# mysql -u root -p mysql> CHANGE MASTER TO MASTER_LOG_FILE='[Filename written down]', MASTER_LOG_POS=[Position written down]; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G
Slave_IO_State status information should identify "Waiting for master to send event". If it stops at "Connecting to Master" check your log file. By default it is located in /var/log/mysqld.log but may be different on your system. Check your my.cnf file for the exact location of your log file.
Note: I've noticed an issue with connecting when using "%" wildcard and have had to specify the connecting server in many instances. MySQL doesn't seem to like the wildcard in quite a few cases.
And that's all there is to it. Any changes made to the Master server will be replicated to the Slave server. While this is good for balancing a load across multiple servers, it only works for data reads. Any writes must be made to the Master server, otherwise the data will not be synchronized.
To syncronize data writes across mulitple servers, you have to set up Master-Master Replication.
