Posted by: kezhong | May 28, 2011

Setting Up Database Replication with Existing Data in MySQL on Fedora 14

On the master (192.168.26.6)

Creating a User for Replication
mysql> create user ‘repl’ identified by ‘slavepass’;
mysql> grant replication slave on *.* to ‘repl’;

Setting the Replication Master Configuration
Stop the MySQL servive and add the following two lines into the mysqld section of the /etc/my.cnf file.

[mysqld]
log-bin=mysql-bin
server-id=6

After changing, start the mysql service.

Obtaining the Replication Master Binary Log Coordinates
Flush all tables and block write statements

mysql> FLUSH TABLES WITH READ LOCK;

Open another terminal, and check the current binary log file name and position

 

 

 

The File column shows the name of the log file and Position shows the position within the file. Record these values. We need them later when we are setting up the slave.

Creating a Data Snapshot Using mysqldump
# mysqldump -uroot -ppassword –all-databases –lock-all-tables > dbdump.db

After creating the snapshot, unlock the tables in the terminal running “FLUSH” command
mysql> UNLOCK TABLES;

On the slave (192.168.26.67)

Setting the Replication Slave Configuration
Stop the MySQL servive and add the following line into the mysqld section of the /etc/my.cnf file.

[mysqld]
server-id=67

Then start the server using the –skip-slave-start option so that replication does not start.
# service mysqld start –skip-slave-start

Import the dump file
Copy the dbdump.db file from the master server to local, and then import the dump file
# mysql -u root -p < dbdump.db

Setting the Master Configuration on the Slave

mysql> change master to
-> master_host=’192.168.26.6′,
-> master_user=’repl’,
-> master_password=’slavepass’,
-> master_log_file=‘mysql-bin.000001’,
-> master_log_pos=1330851;

Start the slave
mysql> start slave;

Check the slave status

 

 

 

 

 

 

 

 

 

From the image, we can see “Slave_IO_Running: Yes” and “Slave_SQL_Running: Yes”. It indicates that the replication is normal.

We also can run “show master status” at the master server and “show slave status\G” at the slave server repeatedly to compare “Position” with “Read_Master_Log_Pos”, we will find they are generally approaching.

 

Reference

http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: