MySQL Master-Slave Databases - Kayako SupportSuite Help Desk Software

Support Center » Knowledgebase » MySQL Master-Slave Databases

	MySQL Master-Slave Databases

Article Assuming we have Master database where all cards sales are dumped (in Magento) and another Slave database, where A2Billing manages times and calls made with the Asterisk server.

We configure the server:
- Edit /etc/mysql/my.cnf and comment these lines:
#skip-networking
#bin-address = 127.0.0.1
This way, the database will listen to any IP address who happens to be sending SQL commands. We also need to create a log file in order to keep track of the changes made in the master database, adding the following commands to the same file mentioned above:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = mastersalesdb
server-id=1
Once the my.cnf file is saved, we restart the database server in order to get the changes running. The command to do this is:
/etc/init.d/mysql restart
Next, we need to log in as root to do some magic:
mysql -u root -p
Enter password:
Now, let's grant replication privileges:
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;
This is were we setp the master table:
USE master_table;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
We need to write down the output because we are going to use it later... But for now we may simply unlock the tables used and quit:
UNLOCK TABLES;
QUIT;

Now it's time to create the slave database in another server.
mysql -u root -p
Enter password:
CREATE DATABASE mastersalesdb
QUIT;

We also need to configure the configuration file in the slave server, so open:
/etc/mysql/my.cnf
and fill the following info:
server-id = 2
master-host =
master-user = slave-user
master-password =
master-connect-retry = 60
replicate-do-db = mastersalesdb
We save the file and restart MySQL with: /etc/init.d/mysql restart and run the following commands:
mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
QUIT;

Now comes the tricky part, so keep sharp!
mysql -u root - p
Enter password:
SLAVE STOP;

CHANGE MASTER TO MASTER_HOST = '', MASTER_USER = 'slave_user', MASTER_PASSWORD = '´', MASTER_LOG_FILE = '', MASTER_LOG_POS = ;

Finally, we start the slave database and everyting should be working!

Article Details

Article ID:

Created On:

12 Jul 2010 10:25 PM