How To Setup MySQL Replication in 10 minutes

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 mis-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 in no time at all.

Source: MySQL Dev Site

1. Open the my.cnf or my.ini (depending on linux or windows).

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 mysqld 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.

8. Open the my.cnf or my.ini on the slave server.

9. Enter somewhere below ‘[mysqld]‘ on the slave server:

server-id=2

10. Save the file and restart mysqld.

11. Execute the following on the slave server (adjust values accordingly to user setup in step 4 and values retrieved from step 6):

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;

12. Execute the following on the slave server:

START SLAVE;

13. 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.

Via Hackosis.com