MySQL Replication

MySQL Replication

MySQL is an excellent open source database system. Replication is a great way to keep data redundant in case of a server crash. However, replication should not take the place of backups in case of data corruption or miss-entered data – as this data will also be replicated to the slave.

MySQL replication takes place in a master-slave configuration. Be aware that by using the configuration – only changes made on the master are replicated to the slave. Any changes on the slave will not be replicated to the master.

Following the steps below, you can have MySQL replication setup.

Master Server

1. Open the my.ini (C:\programdata\MySQL) file.

2. Enter somewhere below ‘[MySQLd]‘ on the Master Server.

log-bin=MySQL-bin

server-id=1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

3. Restart MySQL service (services.msc) on the Master Server

4. Create a user on the master with the ‘REPLICATION SLAVE’ privilege. This user needs no other privileges.

Replace X.X.X.X with the IP address of the Slave Server.

CREATE USER ‘user’@ ‘X.X.X.X’ IDENTIFIED BY ‘password’;

GRANT REPLICATION SLAVE ON *.* TO ‘user’@'X.X.X.X’ IDENTIFIED BY ‘password’;

5. Execute ‘FLUSH TABLES WITH READ LOCK;’ on the master to prevent writing to the databases.

6. Execute ‘SHOW MASTER STATUS;’ on the master and record the values for later.

7. Execute ‘UNLOCK TABLES;’ on the master.

Slave Server

1. Open the my.ini on the Slave Server.

2. Enter somewhere below ‘[MySQLd]‘ on the Slave Server:

server-id=2

3. Save the file and restart MySQL service.

4. Execute the following on the Slave Server (adjust values accordingly to user setup in step 4 and values retrieved from step 6 in Master Server):

CHANGE MASTER TO

MASTER_HOST=’X.X.X.X’,

MASTER_USER=’user’,

MASTER_PASSWORD=’password’,

MASTER_PORT=3306,

MASTER_LOG_FILE=’MySQL-bin.000001′,

MASTER_LOG_POS=98,

MASTER_CONNECT_RETRY=10;

5. Execute the following on the Slave Server:

START SLAVE;

6. Check the MySQL log on the slave to ensure that the connection to the master has been successful. You should see a line similar to the following:

080609 8:47:02 [Note] Slave I/O thread: connected to master ‘root@X.X.X.X:3306′, replication started in log ‘MySQL-bin.000001′ at position 98

You should now have a successful MySQL Master-Slave configuration.

If you have any questions please let me know.