MySQL database replication allows you to have an exact copy of a database on another server which allows you to setup an application to read from, i.e. auth.
All changes to the “master” server is replicated across to the “slave” server instantaneously. This is not a backup technique… any accidental DELETE queries on the master will well… see for yourself. Replication can however help against hardware failure.
On the master server, edit my.cnf and insert the following under the [mysqld] section:
##################################################
## REPLICATION
log-bin
binlog-do-db=pdns
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=1
##################################################
The above will cause the database pdns to be replicated to our slave server and the mysql and test databases will be ignored. If you don’t need this, you can safely remove those lines. Additionally, you can specify to which file mysql should log the binary data. To do that, edit the file and set the log-bin directive to:
log-bin = /var/log/master-mysql-bin.log
Save the file and restart MySQL
/etc/init.d/mysqld restart
Next, connect to the MySQL CLI and add a user to be used for replication. The query below will do just fine for this.
GRANT REPLICATION SLAVE on *.* to 'replication'@172.16.0.30 identified by 'mypassword';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
Take note of of the file name and the log position, you will need to enter this information on the slave later on.
Next you need to grab the data from the master and import it on the slave system. Personally I prefer to just do a dump and import it at the slave, but there are other ways to accomplish this.
mysqldump -u root -p -e pdns > pdns080209.sql
On the slave system, connect to the MySQL CLI and create the database
CREATE DATABASE pdns;
Next, setup the slave settings in my.cnf
##################################################
## REPLICATION
server-id=2
master-host = 172.16.0.29
master-user = replication
master-password = mypassword
master-port = 3306
##################################################
Save and exit the file then import the dump you just created on the master
mysql pdns -u root -p < pdns080209.sql
Note! If the database is BIG, you will get errors, please refer to this post to get a workaround.
Once the database has been imported, restart the slave server, set the log file position and start the slave
/etc/init.d/mysqld restart
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST=’172.16.0.29, MASTER_USER='replication', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='master-mysql-bin.001', MASTER_LOG_POS=73;
START SLAVE;
Next, ensure both Slave_IO_Running and Slave_SQL_Running are showing up as YES, and that’s it… your done.