MySQL Replication setup the easy way
The MySQL replication setup requires three basic steps, we need to setup the master, then setup the client and finally start the replication process.
1. Configure MySQL Master
We will need to modify a file called my.cnf, which is the main configuration file for mysql. On most systems it’s located in /etc/ or /etc/mysql/ and it contains all important configuration data.
First, let’s ensure that networking is enabled and mysql listens on all, or at least the client’s IP addresses. We also have to tell mysql what file to write the logs to and from which databases to keep logging, so that the Slave can pick up the changes. And finally we need to assign a unique ID to the Master.
All this info is contained within the following lines in your my.cnf file. Please note that the position of these entries can be spread throughout the file, so you might have to search for each of them.
#skip-networking # bind-address = xxx.xxx.xxx.xxx (this can be the Slave's IP address. if you're not sure, leave it commented out) log-bin = /var/log/mysql/mydatabase-bin.log server-id = 1
Restart the server. Then log into mysql and create a user with replication privileges:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<password>'; FLUSH PRIVILEGES; USE mydatabase;
The next 3 steps is to lock all tables on the database, take a backup and get the replication sequence ID. We’ll use the backup later on the Slave to establish the baseline, and tell it to start replication starting from the sequence ID.
If you’re performing this action on a high volume production system, I’d recommend to plan these next steps carefully, as the db will be locked for writing during the backup process.
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Write down the “Position” number, it’s the sequence ID and the bin log file. Since the tables are locked, you’ll need to open a second terminal and log into mysql and perform the backup:
mysqldump -u root -p<password> --opt mydatabase | gzip > mydatabasedump.sql.gz
Once the backup is finished, unlock the tables in the first terminal.
UNLOCK TABLES;Whew, we’re done with the Master. Wasn’t that hard, was it? Now on to the Slave.
2. Configure MySQL Slave
You’ll have to copy the backup file to the Slave server and extract it (gzip -d). On the Slave, let’s first create the database and restore the dump we took from the Master.
After loging into mysql from the terminal:
CREATE DATABASE mydatabase; exit; mysql -u -p mydatabase < mydatabasedump.sql;
Then we’ll go on to configure my.cnf on the Slave. As I stated earlier, the position of these entries can be spread throughout the file, so you might have to search for each of them or they might not even be present, so you have to add them.
server-id=2 replicate-do-db=mydatabase
3. Setup parameters and start replication
Restart MySQL, login to mysql and stop the Slave. You now will need the info you wrote down during the “SHOW MASTER STATUS” command.
SLAVE STOP; CHANGE MASTER TO MASTER_HOST='IP address of Master', MASTER_USER='<user>', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='<the log bin file>', MASTER_LOG_POS=<the sequence ID>;
Example: CHANGE MASTER TO MASTER_HOST=’192.168.1.1′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’1234′, MASTER_LOG_FILE=’mysql-bin.001′, MASTER_LOG_POS=256;
Finally, we start the Slave with the new settings and get replication started. Still from the terminal, logged in to mysql:
START SLAVE;
That’s all there is to MySQL Replication setup. There are commands like “SHOW SLAVE STATUS” that let’s you check if replication is working.
Update:
If you have problems getting “Slave_IO_Running” to “Yes” state, you might have to flush the tables on the slave, like this:
STOP SLAVE; FLUSH TABLES WITH READ LOCK; UNLOCK TABLES; START SLAVE;
Awesome, thanks much for the instructions. This worked flawlessly.
There is also a method “Load Data From Master” that could be used, but yours works in all cases.
Sweet, worked like a charm. Thanks!