How To Replicate MySQL Database - Step 2

Go Back Step 1

Getting the data to the Slave.


On the Master Server
I'm assuming you have a live Master server, and an as yet empty Slave server. This stage depends on whether data is constantly being added to the Master. If so, we will have to prevent all database access on the Master so nothing can be added. This means your server will hang during the next step. If no data is being added to the server, you can skip this step. On the Master server, log into MySQL and do the following:

# mysql -u root -p
   Enter password:

   FLUSH TABLES WITH READ LOCK;

   exit;

Now we will use mysqldump to get the data out. So, still on the Master server:

# mysqldump my_database -u root -p > /tmp/database.sql;
# gzip /tmp/database.sql;

Make sure you change my_database to your database name. You will now have a file called database.sql.gz in your temp directory. This is a gziped copy of your database.

On the Slave Server
Now we need to copy over the gzipped file. On the Slave run the following:
# scp root@192.168.1.100:/tmp/database.sql.gz /tmp/

Make sure 192.168.1.100 is the IP of the Master. This will copy the file from the Master and put it in your temp directory on the Slave. Now we just need to import into MySQL:

# mysql -u root -p
   Enter password:

   CREATE DATABASE `my_database`;

   exit;

# gunzip /tmp/database.sql.gz
# mysql -u root -p my_database  


Finishing

On the Master Server
 Now we need to find the position the Master is at in the logs. So, log into MySQL and run the following:
# mysql -u root -p
   Enter password:
   SHOW MASTER STATUS;

This should give you an output along these lines:

+--------------------------+-------------+---------------------------+------------------+
| File                     | Position    | Binlog_Do_DB              | Binlog_Ignore_DB |
+--------------------------+-------------+---------------------------+------------------+
| mysql-bin.000001         | 21197930    | my_database,my_database   |                  |
+--------------------------+-------------+---------------------------+------------------+

Keep that on-screen.

On the Slave Server
Log into MySQL and do the following:
# mysql -u root -p

   Enter password:

   slave stop;
   CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='slave_user',  

   MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000001',   

   MASTER_LOG_POS=21197930;

   slave start;

   exit;

The Slave will now be waiting. So all that's left is to...

Back to the Master Server
To release the tables from lock, Note you only have to do this if you previously run

   FLUSH TABLES WITH READ LOCK;

We shoud already be logged into MySQL, so all you have to do is:

   unlock tables;

   exit;