database

How Do I Set Up Database Replication In MySQL?

Posted on

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.

To set up database replication in MySQL, you need to perform the following steps.

1. Update the /etc/mysql/my.cnf on the master.
a. Comment out these 2 lines if they are uncommented:
#skip-networking
#bind-address = 127.0.0.1

b. log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=testdb
server-id=1

2. Restart MySQL (/etc/init.d/mysql restart)

3. Create an ID with replication privileges.
a. mysql -u root -prootpasswd
b. GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘‘;
FLUSH PRIVILEGES;
c. USE testdb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

+—————+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+—————+———-+————–+——————+
| mysql-bin.006 | 183 | testdb | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)

d. quit;

4. Run a mysqldump and import.
a. mysqldump -u root -p –opt testdb > testdb.sql
b. copy the export to the slave server

5. The next step is to configure the slave.
a. mysql -u root -p
Enter password:
CREATE DATABASE testdb;
quit;

6. Import the database.
a. mysql -u root -p testdb < /path/to/testdb.sql 7. Update the my.cnf on the slave a. Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is testdb. Therefore we add the following lines to /etc/mysql/my.cnf: server-id=2 master-host=192.168.0.100 master-user=slave_user master-password=secret master-connect-retry=60 replicate-do-db=testdb 8. Restart MySQL on the slave (/etc/init.d/mysql restart)

9. mysql -u root -p
Enter password:
SLAVE STOP;

10. Set the pointer.

CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’‘, MASTER_LOG_FILE=’mysql-bin.006’, MASTER_LOG_POS=183;

* MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
* MASTER_USER is the user we granted replication privileges on the master.
* MASTER_PASSWORD is the password of MASTER_USER on the master.
* MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

11. Now all that is left to do is start the slave.

a. START SLAVE;
quit;

That’s it! Now whenever testdb is updated on the master, all changes will be replicated to testdb on the slave. Test it!

Related Items:
Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top