It should be no surprise that MySQL replication is always recommended. It’s like making sure you always wear a motorcycle helmet in the MySQL world. In other words, it’s simply a good idea.
I recommend using your favorite text editor and the MySQL command prompt to get MySQL replication set up. There is something barbaric about a command prompt that nerds love.
1. Set Replication Master
Your master server will need a server ID and binary logging enabled for replication. To do this, you will modify the MySQL configuration file. There are two flavors of the MySQL configuration file. What MySQL configuration file you end up editing is dependent on what server OS you are running. If you are working on a Windows server, you will be editing the my.ini file. If it’s a Linux based server, you will be editing my.cnf. I’m on a Windows server, and I found the my.ini file hiding at C:\Program Files\MySQL\MySQL Server 5.1.
Choose a server ID that is greater than the default of 0. Unless it’s already being used elsewhere, I recommend using 1 for your master server ID. Simple, zen, beautiful. I like it.
To configure the binary log and server ID options, you will need to shut down your master MySQL server. Open up your my.ini or my.cnf file and add these two lines below the [mysqld] section:
Note: It doesn't matter where you put these lines in the [mysqld] section, but my preference is at the very bottom.
After making your changes to the my.ini or my.cnf file, restart your server.
2. Set Replication Slave
A replication slave needs a unique server ID as well. If you are setting up multiple replication slaves, each one needs a unique server ID that is different than the master and other replication slaves.
Open up your my.ini or my.conf on your slave and add the following line below the [mysqld] section:
After making the changes, restart the server using the
--skip-slave-start option to stop replication from starting.
3. Create a User for Replication
A new user on the master is not necessary for replication. However, be aware that the username and password will be stored in plain text inside the master.info file. Therefore, I recommend you create an account with only the privileges needed for replication.
Open up a MySQL command prompt and connect to the master server with your root credentials. Set up a new user, repl, that can connect for replication from any host within your domain (%.yourdomain.com) by running the following commands on the master:
CREATE USER 'repl'@'%.yourdomain.com' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.yourdomain.com';
4. Finding the Replication Master Binary Log Coordinates
Connect to the master server with the MySQL command-line client, and flush all tables and block write statements with the command below:
FLUSH TABLES WITH READ LOCK;
Warning: Leave this command-line client running so that the read lock remains in effect. If you close it, the lock is released.
Open up another MySQL command-line client, and issue the following command to get the file name and position of the binary log. “mysql-bin.000003” is the file name in the example below, while “73” is your binary log position.
SHOW MASTER STATUS;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000003 | 73 | test | manual,mysql |
If the master log file name and position come back empty. The values that you need to use later when setting up the slave’s log file and position are the empty string (”) for the log file and 4 for the position.
5. Create a Data Snapshot Using mysqldump
The master database you are replicating might not be empty. If that’s the case, you will want to dump all the data from the master and import it into the slave.
Run the following command in a standard command-line client on the master server to dump all data to a file named “dbdump.db”:
mysqldump --all-databases --master-data > dbdump.db
Note: This step is not needed if you are setting up replication with a new master and slave without existing data.
6. Import Snapshot Data
We now want to take that backup file and import it into the slave. Go ahead and run the following command in a standard command-line client on the slave to complete the import:
mysql < dbdump.db
7. Setting the Master Configuration on the Slave
To set up the slave to talk to the master, you must tell the slave how to connect. To do this, run the following commands on the slave, replacing the option values with your system specific values:
CHANGE MASTER TO
8. Start the Slave
Back in step 4 we left a MySQL command-line client open on the master that was preventing writing to the database. You can close that client now.
Now we want to start replication on the slave. To do so, run the following command inside of a MySQL command-line client on the slave:
Give Yourself a Pat on the Back
With any luck, you should now have a master MySQL server sending all of its changes to a slave or multiple slave servers. Give it a test by making a small change on the master and seeing if it shows up on the slave. Changes to the master should show up on the slave without significant lag.
If it's simply not working. Or you're getting stuck on a certain step. Give me a holler and I will do my best to get you up and running.