This document will detail how to configure a generic MySQL instance using replication. This document does not detail a specific installation case, but is rather intended to be used as a guide.
You should make sure you start with a reasonably good copy of the master instance installed on the slave machine. You can do this by shutting both the master and slave instances down, and taking a cold copy of the MySQL database files from the master server and placing them onto the slave server. The exact location of these files will vary depending on where MySQL was installed.
Setting up Replication:
The process of setting up MySQL replication consists of three primary parts:
- Setting up replication accounts on each machine
- Configuring the master and slave machines
- Starting the replication
To access a MySQL instance as the root user, use the following command:
mysql -u root -p
On each server, as root:
mysql> grant replication slave, replication client on *.* to 'replicate'@'<IP Address of slave server>' identified by '<Replication password>';
In this case, we want the "IP Address of slave server", for each machine, to be the IP address of the slave server that will connect to it. For instance, if your master server is 10.0.0.1 and your slave server is 10.0.0.2, you would do the following:
On 10.0.0.1:
mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.2' identified by 'replicationpassword';
On 10.0.0.2:
mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.1' identified by 'replicationpassword';
On the master server, ensure the following is in the /etc/my.cnf file:
log_bin = mysql-bin server_id = 1
On the slave server, ensure that the following is in the /etc/my.cnf file:
log_bin = mysql-bin server_id = 2 relay_log = mysql-relay-bin log_slave_updates = 1 read_only = 1
Please note that the server_id should only be 2 for the first slave server. Each server (including the master) should have a different server_id. You don't have to use 1 and 2; the point is to choose numbers that are unique.
Once the my.cnf files are updated on both machines, you should restart the MySQL instances on both machines in order for them to pick up the changes.
Please do not place replication configuration options such as "master_host" and "master_port" into the slave my.cnf file, as this can cause problems.
Also note that the slave must be able to connect to the master server, so adjust any firewall rules for port 3306 accordingly.
On the master server:
mysql> show master status;
You should see a binary log file name returned in the output of the command. That file name is needed for the next step, which is performed on the slave server.
On the slave server:
mysql> change master to master_host='<master host name>', master_user='replicate', master_password='<replication password>', master_log_file='<log file name from "show master status" command on master server>', master_log_pos=0;
So, for example, if "show master status" on your master server returns the filename "mysql-bin.000001", your master server is named "masterserver", and your replication password is "password", you would use the following command on your slave server:
mysql> change master to master_host='masterserver', master_user='replicate', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=0;
At this point, your slave server is configured to connect to the master server. The next step is telling the slave server to actually start the replication process. You can do this by running the following command on the slave server:
mysql> start slave;
Replication should now be fully operational on your MySQL installation. To verify that the replication is actually, occurring, you could issue the following command on the slave server:
mysql> show slave status\G
Your output from the "show slave status\G" command should be something similar to the following, although it probably will not be identical:
Slave_IO_State: Waiting for master to send event Master_Host: masterserver.mit.edu Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 298070 Relay_Log_File: mysql-relay-bin.000123 Relay_Log_Pos: 5250 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: 298070 Relay_Log_Space: 5250 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)
You should now have a fully functional MySQL replication setup.