For me, using replication will leverage me from disaster such as server crash or even when database get corrupted.
I use MySQL 5.7 version to setup replication (master/slave) for one of my PowerDNS server because i’m afraid PowerDNS does not work with newest version of MySQL.
Master DB Configuration
- Open
<strong>/etc/my.cnf</strong> and enable Log and Server-ID in mysql configuration
bind-address = 0.0.0.0
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = "mysql"
- Login to MySQL and Create user for replication
# mysql -u root -p
mysql> grant replication slave on *.* TO [replication_username]@'[private IP of second database]' identified by '[some password]';
mysql> flush privileges;
mysql> quit
systemctl restart mysqld
- Please run this command below to make sure there nothing can write master database during a database dump. Also note the filename and position of the binary log because you will need these values to complete the replication configuration on slave database.
# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+--------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 | 10 | | mysql |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)
- Now you can perform database dump by executing this command below
mysqldump -u root -p --databases [database-1] [database-2] ... > /root/db_dump.sql
- After dump process complete, you can unlock the database by running below command
# mysql -u root -p
mysql> UNLOCK TABLES;
- copy the database dump file to the slave db server by using your prefered method. Here, i’m just gonna use scp command (or you can use software like WinSCP to copy the file)
scp /root/db_dump.sql [private-IP-of-slave_db_svr]:/root/
Slave DB Configuration
- Open
<strong>/etc/my.cnf</strong> and enable Log and Server-ID in mysql configuration
bind-address = 0.0.0.0
server-id = 2
master-host = [private-IP-of-Master_DB_Svr]
master-user = [replication-username]
master-password = [replication-password]
master-connect-retry = 60
“You might need to create the database before importing the dump file to the slave database.“
- Now import the database dump from master db to the slave
mysql -u root -p < /root/db_dump.sql
service mysqld restart
- Complete the replication process
# mysql -u root -p
mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='[private-IP-of-Master_DB_Svr]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
Test Replication
To test replication , you can just create new database on the master db or just input new data to it. After that, you can check if the data you inserted to the master database is replicated to the slave database. If the data exist on the slave server, then you replication configuration is successfully configured and ready to do it’s job.