https://www.percona.com/blog/wp-content/uploads/2023/08/Use-systemd-in-Linux-to-Configure-and-Manage-Multiple-MySQL-Instances-200×115.jpeg
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.
- 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.
- Backup testing. You can run multiple instances on a server to test your backups with the correct version and configs.
- 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.
- 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:
yum install libaio1 libaio-dev numactl
useradd -r -g mysql -s /bin/false mysql
groupadd mysql
cd /usr/local/
tar xvfz /root/Percona-Server-8.0.19-10-Linux.x86_64.ssl101.tar.gz
ln -s /usr/local/Percona-Server-8.0.19-10-Linux.x86_64.ssl101/ mysql
cd /data/
mkdir -p /data/mysql/{3306,3307}/data
chown -R mysql:mysql /data
chmod 750 -R /data/mysql/{3306,3307}/data
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.
[root@ip-172-31-128-38 share]# cat /etc/prod3306.cnf [mysqld@prod3306] datadir=/data/mysql/3306 socket=/data/mysql/3306/prod3306.sock mysqlx_socket=/data/mysql/3306/prod3306x.sock log-error=/data/mysql/prod3306.err port=3306 mysqlx_port=33060 server-id=1336 slow_query_log_file=/data/mysql/3306/slowqueries.log innodb_buffer_pool_size = 50G lower_case_table_names=0 tmpdir=/data/mysql/3306/tmp/ log_bin=/data/mysql/3306/prod3306-bin relay_log=/data/mysql/3306/prod3306-relay-bin lc_messages_dir=/usr/local/mysql/share [mysqld@prod3307] datadir=/data/mysql/3307 socket=/data/mysql/3307/prod3307.sock mysqlx_socket=/data/mysql/3307/prod3307x.sock log-error=/data/mysql/prod3307.err port=3307 mysqlx_port=33070 server-id=2337 slow_query_log_file=/data/mysql/3307/slowqueries.log innodb_buffer_pool_size = 50G lower_case_table_names=0 lc_messages_dir=/usr/local/mysql/share tmpdir=/data/mysql/3307/tmp/ log_bin=/data/mysql/3307/prod3307-bin relay_log=/data/mysql/3307/prod3307-relay-bin
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.
ln -s /usr/local/mysql/bin/mysqld /usr/bin mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/data/mysql/3307 --lower_case_table_names=0 mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/data/mysql/3306 --lower_case_table_names=0
Configured the systemd service
Create the SYSTEMD base configuration at /etc/systemd/system/mysql@.service 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.
[root@ip-172-31-128-38 share]# cat /usr/lib/systemd/system/mysqld@.service # Copyright (c) 2016, 2021, Oracle and/or its affiliates. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License, version 2.0, # as published by the Free Software Foundation. # # This program is also distributed with certain software (including # but not limited to OpenSSL) that is licensed under separate terms, # as designated in a particular file or component or in included license # documentation. The authors of MySQL hereby grant you an additional # permission to link the program and your derivative works with the # separately licensed software that they have included with MySQL. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License, version 2.0, for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # systemd service file for MySQL forking server # [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql Type=forking PIDFile=/data/mysql/mysqld-%i.pid # Disable service start and stop timeout logic of systemd for mysqld service. TimeoutSec=0 # Execute pre and post scripts as root PermissionsStartOnly=true # Needed to create system tables #ExecStartPre=/usr/bin/mysqld_pre_systemd %I # Start main service ExecStart=/usr/bin/mysqld --defaults-file=/etc/prod3306.cnf --defaults-group-suffix=@%I --daemonize --pid-file=/data/mysql/mysqld-%i.pid $MYSQLD_OPTS # Use this to switch malloc implementation EnvironmentFile=-/etc/sysconfig/mysql # Sets open_files_limit LimitNOFILE = 65536 Restart=on-failure RestartPreventExitStatus=1 Environment=MYSQLD_PARENT_PID=1 PrivateTmp=false [root@ip-172-31-128-38 share]#
Reload daemon
systemctl daemon-reload
Start MySQL
systemctl start mysqld@prod3307 systemctl start mysqld@prod3306
Enable MySQL service
systemctl enable mysqld@prod3307 systemctl enable mysqld@prod3306
Error log for each instance
[root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3306.er tail: cannot open ‘/data/mysql/prod3306.er’ for reading: No such file or directory [root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3306.err 2023-07-10T05:26:42.521994Z 0 [System] [MY-010910] [Server] /usr/bin/mysqld: Shutdown complete (mysqld 8.0.19-10) Percona Server (GPL), Release 10, Revision f446c04. 2023-07-10T05:26:48.210107Z 0 [System] [MY-010116] [Server] /usr/bin/mysqld (mysqld 8.0.19-10) starting as process 20477 2023-07-10T05:26:52.094196Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-07-10T05:26:52.112887Z 0 [System] [MY-010931] [Server] /usr/bin/mysqld: ready for connections. Version: '8.0.19-10' socket: '/data/mysql/3306/prod3306.sock' port: 3306 Percona Server (GPL), Release 10, Revision f446c04. 2023-07-10T05:26:52.261062Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/data/mysql/3306/prod3306x.sock' bind-address: '::' port: 33060
root@ip-172-31-128-38 3307]# tail -5 /data/mysql/prod3307.err 2023-07-10T05:26:36.032160Z 0 [System] [MY-010910] [Server] /usr/bin/mysqld: Shutdown complete (mysqld 8.0.19-10) Percona Server (GPL), Release 10, Revision f446c04. 2023-07-10T05:26:58.328962Z 0 [System] [MY-010116] [Server] /usr/bin/mysqld (mysqld 8.0.19-10) starting as process 20546 2023-07-10T05:27:02.179449Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-07-10T05:27:02.198092Z 0 [System] [MY-010931] [Server] /usr/bin/mysqld: ready for connections. Version: '8.0.19-10' socket: '/data/mysql/3307/prod3307.sock' port: 3307 Percona Server (GPL), Release 10, Revision f446c04. 2023-07-10T05:27:02.346514Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/data/mysql/3307/prod3307x.sock' bind-address: '::' port: 33070 [root@ip-172-31-128-38 3307]#
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
Planet MySQL