sudo apt-get install mysql-server mysql-client
sudo nano /etc/mysql/my.cnfOnce inside that file, we need to make a few changes.
bind-address = 127.0.0.1Replace the standard IP address with the IP address of server.
bind-address = 12.34.56.789The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1. Make sure this line is uncommented.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.logFinally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.
binlog_do_db = newdatabaseAfter you make all of the changes, go ahead and save and exit out of the configuration file.
sudo service mysql restartThe next steps will take place in the MySQL shell, itself.
mysql -u root -pWe need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';Follow up with:
FLUSH PRIVILEGES;The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.
USE newdatabase;Following that, lock the database to prevent any new changes:
FLUSH TABLES WITH READ LOCK;Then type in:
SHOW MASTER STATUS;You will see a table that should look something like this:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | newdatabase | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.
mysqldump -u root -p --opt newdatabase > newdatabase.sqlNow, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.
UNLOCK TABLES;
QUIT;Now you are all done with the configuration of the the master database.
CREATE DATABASE newdatabase;
EXIT;Import the database that you previously exported from the master database.
mysql -u root -p newdatabase < /path/to/newdatabase.sqlNow we need to configure the slave configuration in the same way as we did the master:
sudo nano /etc/mysql/my.cnfWe have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.
server-id = 2Following that, make sure that your have the following three criteria appropriately filled out:
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabaseYou will need to add in the relay-log line: it is not there by default.
sudo service mysql restartThe next step is to enable the replication from within the MySQL shell.
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;This command accomplishes several things at the same time:
START SLAVE;You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.
SHOW SLAVE STATUS\GIf there is an issue in connecting, you can try starting slave with a command to skip over it:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;All done.
Article ID: 181
Created On: Thu, Dec 26, 2013 at 9:58 PM
Last Updated On: Thu, Dec 26, 2013 at 9:58 PM
Authored by: ASPHostServer Administrator [asphostserver@gmail.com]
Online URL: http://faq.asphosthelpdesk.com/article.php?id=181