...
The process of setting up MySQL replication consists of three primary parts: 1.
- 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 "mysql the following command:
| Code Block |
|---|
mysql -u root -p |
...
|
On each server, as root:
| Code Block |
|---|
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:
| Code Block |
|---|
mysql> grant replication slave, replication client on *.* to 'replicate'@'10.0.0.2' identified by 'replicationpassword'; |
On 10.0.0.2:
| Code Block |
|---|
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:
| Code Block |
|---|
log_bin = mysql-bin |
...
server_id = |
...
1
|
On the slave server, ensure that the following is in the /etc/my.cnf file:
| Code Block |
|---|
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 20 2 for the first slave server. Each server (including the master) should have a different server_id. You don't have to use 10 1 and 202; the point is to choose numbers that are unique.
...
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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.