MySQL Replication Setting manual
April 3, 2007
Jun Sakai
This manual describe how to set up replication servers, and serves as a reference to the available replication options.
1. Set up an account
2. Create Snapshot
3. Copy Master database into Slave Server
4. Configure my.cnf on Master
5. Configure my.cnf on Slave
1. Set up an account
Set up an account on the master server that the slave server can use to connect. Suppose that your domain is mydomain.com and that you want to create an account with a usernameof repl such that slave servers can use the account to access the master server from any hostin your domain using a password of slavepass. To create the account, use this GRANT statement:
mysql> GRANT REPLICATION SLAVEON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
2. Create Snapshot
Change location into the master server’s data directory, and then create a snapshot is to use an archiving program to make a binary backup of the databases in your master’s data directory.
shell> cd /usr/local/mysql/var/
shell> tar cpf /var/tmp/mysql.tar .
3. Copy Master database into Slave Server
Then copy the archive file to the/var/tmp directory on the slave server host.
shell> scp /var/tmp/mysql.tar salave:/var/tmp/mysql.tar
On that machine, change location into the slave’s data directory, and unpack the archive file .
shell> cd /usr/local/mysql/var
shell> rm -rf *
shell> tar xpf /var/tmp/mysql.tar
!! You may not want to replicate the mysql database if the slave server has a different set of user accountsfrom those that exist on the master. In this case, you should exclude it from the archive. Youalso need not include any log files in the archive, or the master.infoor relay-log.infofiles.
4. Configure my.cnf on Master
Make sure that the [mysqld] section of the my.cnf file on the master host includes a log-binoption. The section should also have a server-id=master_id option, where master_idmust be a positive integer value from 1 to 232 – 1. For example:
shell> vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
run mysql
shell> /usr/local/mysql/bin/mysqld_safe --user=mysql &
5. Configure my.cnf on Slave
Stop the server that is to be used as a slave and add the following lines to its my.cnf file:
shell> vi /etc/my.cnf
[mysqld]
server-id=2
master-host=192.168.20.24 <- Master’s IP address
master-user=repl
master-password= slavepass
rum mysql
shell> /usr/local/mysql/bin/mysqld_safe --user=mysql &
6 Check slave status
Check Slave status on Slave Server using show command:
mysql> show slave status¥G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.144
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 179
Relay_Log_File: localhost-relay-bin.000025
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000002
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: 179
Relay_Log_Space: 235
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)
ERROR:
No query specified
If “Slave_IO_Running” and “Slave_IO_Running” status is YES, replication setting is successful.