Infrastructure blog

The Basics of Setting MySQL Master/Slave Replication

 

Using replication to improve performance during scale-out.

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

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

  1. On the slave server, install MySQL server and client.
  1. MySQL replication on master.

You should allow master DB replication by editing the MySQL configuration.

[expand title=”Click here to read more about this article”]

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.

  1. 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.[/expand]

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading

Cloud

Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Business Intelligence Blog

The Top 6 Success Stories…

Nearly nine out of 10 digital transformation initiatives fail. Digital transformation projects fail for a variety of reasons. It can be because of a lack...

Keep Reading

Live Chat | Emergency