If you need to have a master database server and a slave database server, you can use the following methods to do it. In this set up, all the requests from the servers are sent to your master server and then replicated to your slave server as the process occurs. The slave will serve as a backup system. This way, you can organise the database server using a VIP address. If and when the master database server is offline, automatic failover to the slave database server will be carried out, promoting the slave server to master.
Steps to Set Up Master/Slave Server Relationship
- Install your MySQL server and client on the master server.
After installation, follow the directions and select a password that you would be using for the root user. Do not apply this setting in the /etc/network/interfaces as static. Doing so means that the slave server, once nominated to master will be reconfiguring the VIP file. Go to the MySQL shell and build a new “dbuser” and “website” – your new user and database, respectively. Next, do a database dump. Enable remote connections to the master server in the configuration file. Then you should comment the bind-address = 127.0.0.1 line. Then enable your Web server to link to the DB server. In your site settings, apply the virtual IP address for the MySQL settings.
- On the slave server, install MySQL server and client.
- MySQL replication on master.
You should allow master DB replication by editing the MySQL configuration.
Find the server-id line and then fixed it to 1.
Find the log_bin line and then uncomment it. Then add the expire_logs_days = 7. This will help you avoid getting hefty log files that will eat up your disk space. Then you would be assigning the database, which will be duplicated using the binlog_do_db = function.
At this point, you would need to save and then exit the config file and restart MySQL.
You will need to give rights to a new slave user for the duplication to take place.
mysql -u root -p
mysql> grant REPLICATION SLAVE on *.* to ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
mysql> flush privileges;
You would then need to initiate a SSH connection to the master server using a new shell window. Lock the website database so that no changes will happen.
Type in mysql> show master status;
This command will show a tab, which displays a variety of information. You would need to take note of the position, which refers to the place that the slave DB would commence replicating.
Next, transfer your database by using the mysqldump in the new window. Then go back to your original SSH window to unlock the databases and allow it to be writeable.
Exit the shell.
- MySQL replication on slave
Now that you have replication set up on master, you would need to set up replication on the slave server.
You should be logged into your slave server. After logging in, go to MySQL shell. You would be creating a new database + new user just as you did before. Next, allow your Web server to connect to your database server. You would need to import the DB, which you have exported from the master and set the slave just like how you’ve configured the master. Comment the bind-address line and set your server-id to 2. Then add in these four lines:
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = website
relay-log = /var/log/mysql/mysql-relay-bin.log
relay_log_space_limit=2G
Save your configuration and restart your MySQL. After that, you would need to allow the replication using the MySQL shell.
After these, you would need to manage master binary logs and set up your Master High Availability.
Still confused? Four Cornerstone can help you get your master and slave replication and other backup strategies in place. Just contact us using our contact form.
For more information regarding the basics of setting MySQL master and slave replication, you can read our source here.
Photo courtesy of MySQL.
