MySQL Replication
There are several ways to implement replication under MySQL (Master -> Slave, Master -> Master, Master -> Relay -> Slave) in addition to just replicating a single, several or all databases between the hosts.
This how-to will walk you through the process of setting up MySQL database replication in a Master -> Slave environment that will replicate ALL databases (and their respective entries) from the Master to the Slave.
In an upcoming how-to, I'll walk you through getting everything back to normal after a server crash, but this how-to only focuses on getting MySQL replication working between the Master and Slave servers. Additionally, while this how-to is written specifically for MySQL 5.0.x running on CentOS 5.2 on a PBX In A Flash (PIAF) server, you could probably make this work on a server that isn't running PIAF. But there are no guarantees. With most things technical, there is no warranty expressed or implied. Use at your own risk. You have been warned.
This document makes several assumptions, so let me get those out of the way first. To start, it is assumed that you already have two (2) functional PIAF boxes configured, running and communicating with each other (whether they are on the same subnet, or via hamachi - it makes no difference, so long as they can see each other). It also assumes that you know how to log into the MySQL client, use ssh as well as use some sort of editor (joe/vim/vi). If you don't have all of those prerequisites, stop right here until you have all the parts. I'll wait.
Ok, since we have 2 servers, you need to decide which server is the main (or Master) server and which server will be the backup (or Slave) server. The Master server will keep a log of each and every transaction (add, delete, modify) that happens in that database. The Slave will look at the log on the Master server and whenever any changes happen on the Master, it will also make those changes happen on the Slave.
Since the Slave connects to the Master using a standard MySQL username/password, there must be an account on the Master server that the Slave can use to connect with. Any account can be used for replication, just be aware that the username/password used for replication will be stored in plain text within either the my.cnf or master.info file(s). Personally, I find it's easier (not to mention cleaner and safer) to create an account specifically for replication; that way if the account is compromised, the account only has the privilege of performing replication.
Given that, we're going to create a user (called "repl" with a password of "passw0rd") and grant the privileges required for replication, using the GRANT statement.
Please feel free to change these to whatever you want.
Log into your MySQL client as the root user and issue the following commands:
Code:
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' IDENTIFIED BY 'passw0rd';
FLUSH PRIVILEGES;
QUIT
Now that the "repl" user has been created, let's configure the Master MySQL server to create/keep the log to store the changes that Slave server will "feed" from.
Edit the /etc/my.cnf file and in the [mysqld] section add these two lines:
Code:
log-bin=mysql-bin
server-id=1
The first line is the "base name" of the log file that MySQL will use (mysql-bin.000001, mysql-bin.000002, etc)
The second line assigns the Master server an ID (used by replication) to distinguish it from the Slave server.
Now we need to dump the databases that we have on the Master server to import onto the Slave server. We dump the databases by doing:
Code:
cd /tmp
mysqldump -u root -p --all-databases --lock-all-tables > dbdump.sql
We now need to scp the dump file to the Slave server:
Once you've got the mysql dump is done, let's restart the mysqld service on the Master server:
Code:
/etc/init.d/mysqld restart
SSH into the Slave server and import the database with this command:
Code:
mysql -u root -p < /tmp/dbdump.sql
On the Slave server, edit the /etc/my.cnf file and in the [mysqld] section add these four lines:
Code:
master-host=111.222.333.444 (replace this with the IP address of the Master server)
master-user=repl
master-password=passw0rd
server-id=2
Once you're done, let's restart the mysqld service on the Slave server:
/etc/init.d/mysqld restart
Log into your MySQL client on the Slave server and issue the following commands:
Code:
SLAVE START;
SHOW SLAVE STATUS\G
You should see something similar to this:
Code:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 111.222.333.444
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: pbx-relay-bin.000010
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
There are three fields that we are concerned about: the
Slave_IO_Running,
Slave_SQL_Running and the
Read_Master_Log_Pos fields.
The Slave_IO_Running service is the service that maintains communications, as well as moving the log file entries between the Master and Slave. If the Slave_IO_Running service isn't running (or says "No"), the Slave can't see/communicate with the Master.
The Slave_SQL_Running service is the actual service that takes the log file entries that have been copied over to the Slave and makes the changes to the MySQL database. If this service isn't running, check the /var/log/mysel.err log for more information.
The Read_Master_Log_Pos field is really nothing more than the position in the file that the Slave is current with. To see if the slave has caught up with the Master, do the following:
Log into your MySQL client on the Master server and issue the following commands:
You should see something very similar to this:
Code:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The Read_Master_Log_Pos on the Slave server should match the Position field on the Master server. If they differ, it means that the Slave has some catching up to do. Repeat the steps again in a minute or two; if you have a LARGE amount of data, it could take up to 5 minutes (or more) to make the Slave in sync with the Master.
Contratulations, you have just set up database replication between the Master and the Slave.
To really see the replication in action, log into your MySQL client on the Master server and issue the following commands:
Code:
CREATE DATABASE DELETE_ME1;
SHOW DATABASES;
Now, log into your MySQL client on the Slave server and issue the following commands:
You should see the database called DELETE_ME1 listed as one of the databases on the Slave server.
Back in the MySQL client on the Master server issue the following command:
Code:
DROP DATABASE DELETE_ME1;
Back in the MySQL client on the Slave server issue the following command:
The DELETE_ME1 database should be gone.
I hope this tutorial was helpful. If you have questions, please post them below and I will do my best to answer them.
-Rick
Orgasmatron 5.x / Incredible PBX UPDATE: Chances are, you will will need to add these two lines to your /etc/sysconfig/iptables file on both the master and the slave (this will allow the two boxes to see each other via the MySQL replication port):
Code:
# Allow connections to our MySQL server
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
Once those lines are added, you'll need to restart iptables:
Code:
/etc/init.d/iptables restart
but I'll publish it on Nerd Vittles in a few weeks just to be sure the donations come pouring in. 
Phone System 