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.

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.

Uncategorized

Performance Boost in MySQL 8.0.35…

Oracle has recently published interesting performance numbers that show MySQL 8.0.35 Enterprise Edition about 25%, or higher, faster than MySQL Community Edition.   A great...

Keep Reading

Uncategorized

Security Trends For 2024: SBOMs

A recent article on Forbes, linked below, lists five security trends that are likely to be important in 2024. Of course, AI-related security topics top...

Keep Reading

Uncategorized

Getting started with your first…

Anyone looking at writing their first Generative AI application should read this to save time. In June 2023, Google Cloud published a useful blog post,...

Keep Reading

Uncategorized

Read-Write and Read-Only query splitting…

Optimize the usage of the standby (secondary) read-only MySQL InnoDB Cluster servers by automatically and transparently distributing read-queries to them. A great feature in MySQL...

Keep Reading

Uncategorized

Considering Cloud Diversification?

Moving an enterprise to a Public Cloud can quickly feel like moving into a locked-in relationship with the Cloud vendor. One solution to diminish this...

Keep Reading

Uncategorized

Gartner: Global cloud consumption will…

Gartner forecasts that 2024 will see a 20% global cloud consumption increase from 2023 to $679B, while 2023 has seen an 18% increase from 2022...

Keep Reading

Uncategorized

ByteDance: Use AI for tuning…

ZDNet is reporting that a ByteDance (the maker of TikTok) Linux kernel developer has proposed to implement AI to tune the performance of Linux systems...

Keep Reading

AI Governance

What is AI TRiSM?

If reading now about AI TRiSM for the first time, it might be good read on and learn what it’s about because it leads the...

Keep Reading

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

Live Chat | Emergency