This blog describes how to configure systemd for multiple instances of MySQL. With package installations of MySQL using YUM or APT, it’s easy to manage MySQL with systemctl, but how will you manage it when you install from the generic binaries?

Here, we will configure multiple MySQL instances from the generic binaries and manage them using systemd.

Why do you need multiple instances on the same server?

We will do that, but why would you need multiple instances on the same host in the first place? Why not just create another database on the same instance? In some cases, you will need multiple instances on the host. 

  1. You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs.
  2. Backup testing. You can run multiple instances on a server to test your backups with the correct version and configs.
  3. We split databases by function/team to give each team full autonomy over their schema, And if someone screws up, it breaks their cluster, not all databases. However, larger instances are more economical as not all MySQL servers will always need maximum resources. So you put multiple MySQL servers on a single machine instead of multiple databases inside one MySQL instance. Better failure handling, similar cost. But yes, do not put all nodes of the same cluster on the same host, but you have multiple nodes on the same host of different clusters. 
  4. Cases where (in very large sharded deployments) a user will install multiple mysqlds per server to reduce contention, i.e., they get more performance per 2-socket server with four or eight mysqlds than one.  AFAIK Facebook does this.

The original motivation for FB was due to different hardware generations, especially between regions/data centers. For example, an older data center may have smaller/less powerful machines, so they run fewer mysqld per host there to compensate. There were other exceptions, too, like abnormally large special-case-shard needing dedicated machines.

That said, other performance motivations mentioned above did play into it, especially before the days of multi-threaded replication. And I agree that in the modern age of cloud and huge flash storage, the vast majority of companies will never need to consider doing this in prod, but there is always a chance of its need. 

Install MySQL

To install and use a MySQL binary distribution, the command sequence looks like this:

Create MySQL configuration for each instance

Below is an example of the first instance I placed in /etc/prod3306.cnf. My naming convention is prod3306 and prod3307. I then place that naming convention in the configuration filename  /etc/prod3306.cnf. I could have done my.cnf.instance or instance.my.cnf.

The directory lc_messages_dir=/usr/local/mysql/share  is required when your MySQL binaries base directory is not the default one, so I had to pass the path for it — otherwise, MySQL won’t start. 

Initialize instance

Initialize your database and get the temporary password for the database from the error log file so you can log in and update the passwords after the MySQL instances are started.

Configured the systemd service

Create the SYSTEMD base configuration at /etc/systemd/system/[email protected] and place the following contents inside. This is where the naming convention of the MySQL instances comes into effect. In the SYSTEMD configuration file, %I will be replaced with the naming convention that you use. 

Reload daemon

Start MySQL

Enable MySQL service

Error log for each instance

Conclusion

Utilizing systemctl to control MySQL significantly simplifies the management of MySQL instances. This approach facilitates the easy configuration of multiple instances, extending beyond two, and streamlines the overall administration process. However, it is essential to be mindful of memory allocation when setting up multiple MySQL instances on a single server. Allocating memory appropriately for each MySQL instance ensures sufficient overhead and optimal performance.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Simon Mudd

Can any cgroup or similar memory controls be added to hard limit memory consumption as MySQL memory use can be unpredictable. Having a way to hard limit maximum usage per instance would make a lot of sense here and perhaps similar considerations go for Cpu limiting too.

0
Reply