MySQL Replication : Replicating an existing database

Replication of Existing DBs

If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, then you must stop processing statements on the master, obtain the current position, and then dump the data, before allowing the master to continue executing statements.

If you do not stop the execution of statements, the data dump and the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

PREPARATION OF MASTER SERVER

1. Select a master server. It can be either one.

2. Make sure all databases that you want to replicate to the slave already exist! The easist way is to just copy the database dirs inside your MySQL data directory intact over to your slave, and then recursively chown them to “mysql:mysql”. Remember, the binary structures are file-system dependant, so you can’t do this between MySQL servers on different OS’s. In this instance you will want to use mysqldump most likely.

3. Create /etc/my.cnf if you do not already have one:

[mysqld]
socket=/tmp/mysql.sock [enter YOUR path to mysql.sock here]
server-id=1
log-bin=mysql-bin
binlog-do-db=bossdb     # input the database which should be replicated
binlog-ignore-db=mysql1 # input the database that should be ignored for replication
binlog-ignore-db=mysql2  # input the database that should be ignored for replication

4. Permit your slave server to replicate by issuing the following SQL command (substituting your slave’s IP and preferred password):

mysql> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO 'replicate'@'192.168.1.1' IDENTIFIED BY 'somepass';

5. Flush all talbes and block write statements :

mysql> FLUSH TABLES WITH READ LOCK;

6. Use the SHOW MASTER STATUS statement to determine the current binary log file name and offset on the master:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

Copy the file + position for use in Step 4 of the slave configuration.

7. Create data snapshot to import into slave with mysqldump :

shell> mysqldump -u root -p db_name --lock-all-tables >dbdump.sql

8. Unlock the tables of the database :

mysql> UNLOCK TABLES;

9. Transfer & import the db into the slave

10. Shut down and restart MySQL daemon and verify that all is functional.

PREPARATION OF SLAVE

1. Create /etc/my.cnf if you do not already have one:

[mysqld]
socket=/tmp/mysql.sock [enter YOUR path to mysql.sock here]
server-id=2 [MUST be different to master]
master-host=192.168.1.1
master-user=replicate
master-password=somepass

2. Shut down and restart MySQL on slave.

3. Log into mysql and stop slave :

mysql> stop slave;

4. Set the master configuration on the slave :

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

3. Issue the following SQL command to check status:

mysql> show slave status\G;

Ensure that the following two fields are showing this :

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If not, try to issue the following command :

mysql> start slave;

This will manually start the slave process. Note that only updated tables and entries after the slave process has started will be sent from the master to the slave — it is not a differential replication.

TESTING

Just update some data on the master, and query that record on the slave. The update should be instantaneous.

Test creating a table on the master MySQL server database :

mysql> use replicateddb;
Database changed

mysql> CREATE TABLE example4( id INT NOT NULL AUTO_INCREMENT,  PRIMARY KEY(id),  name VARCHAR(30),   age INT);
Query OK, 0 rows affected (0.04 sec)