3. Copy Master Database Into Slave Server

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.