Blog | Pythian

How to manage multiple MySQL binary installations with SYSTEMD

Written by Kevin Markwardt | Jul 30, 2018 4:00:00 AM
This blog will go into how to manage multiple MySQL binary installations with SYSTEMD using the systemctl command. With package installations of MySQL using YUM or APT, it's quick and easy to manage your server's state by executing systemctl commands to stop, start, restart and status. But what do you do when you want to install MySQL using the binary installation with a single or with multiple MySQL instances? You can still use SYSTEMD to easily manage the MySQL instances. All commands and testing have been done on Debian and some details may change in other distro's.

MySQL preparation

These are the steps to set up MySQL with multiple instances. If you currently have a MySQL server package installation using YUM or APT, it will need to be removed first. Make sure you keep your client. I also had to install some base packages for MySQL on Debian 
apt install libaio1 libaio-dev numactl

Download MySQL binary installation

Download the compressed tar file binary installation and extract to /usr/local, and create a soft link for mysql to the extracted binaries. Example : 
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
 tar zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local
 ln -s /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64/ /usr/local/mysql
 
 Example result
 
 root@binary:/usr/local# ls -al
 total 44
 drwxrwsr-x 11 root staff 4096 Jun 19 17:53 .
 drwxr-xr-x 10 root root 4096 Apr 17 18:09 ..
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 bin
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 etc
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 games
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 include
 drwxrwsr-x 4 root staff 4096 Apr 17 18:22 lib
 lrwxrwxrwx 1 root staff 9 Apr 17 18:09 man -> share/man
 lrwxrwxrwx 1 root staff 47 Jun 19 17:53 mysql -> /usr/local/mysql-5.7.22-linux-glibc2.12-x86_64/
 drwxr-sr-x 9 root staff 4096 Jun 19 17:52 mysql-5.7.22-linux-glibc2.12-x86_64
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 sbin
 drwxrwsr-x 7 root staff 4096 Apr 17 18:22 share
 drwxrwsr-x 2 root staff 4096 Apr 17 18:09 src

Export path and aliases

Create an export of the MySQL path and aliases to log in to the MySQL instances using pre-made client config files. The password doesn't matter right now as it will get updated in a couple of steps. Update the socket for each config file so they are unique because this needs to be different for each MySQL instance. Reboot your server to ensure that the configuration is loaded during boot time correctly. Run "echo $PATH" after reboot and validate that the new path is configured to include /usr/local/mysql:/usr/local/mysql/bin. Example : 
echo "export PATH=$PATH:/usr/local/mysql:/usr/local/mysql/bin" >> /etc/profile.d/mysql.sh
 echo "alias mysql1='mysql --defaults-file=/etc/instance1_client.cnf'" >> /etc/profile.d/mysql.sh
 echo "alias mysql2='mysql --defaults-file=/etc/instance2_client.cnf'" >> /etc/profile.d/mysql.sh
Example client config : /etc/instance1_client.cnf 
[client]
 user=root
 password='mysqlpass'
 socket=/var/run/mysql/mysqld_instance1.sock
Example path : 
root@binary:~# echo $PATH
 /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql:/usr/local/mysql/bin

Create user/group, paths and MySQL permissions

Next, create the user and group that will be used by the MySQL services. Then create the paths and set the proper permissions. Example : 
groupadd mysql
 useradd -r -g mysql -s /bin/false mysql
 mkdir -p /mysql/data/instance1
 mkdir -p /mysql/data/instance2
 mkdir -p /mysql/logs/instance1
 mkdir -p /mysql/logs/instance2
 mkdir /var/run/mysql/
 chown mysql:mysql /var/run/mysql
 chown -R mysql:mysql /mysql

Create MySQL configuration for each instance

Below is an example of the first instance I placed in /etc/my.instance1.cnf. My naming convention is instanceX. As an example, my first instance is instance1, and my second instance is instance2. I then place that naming convention in the configuration filename my.instance1.cnf. I could have done my.cnf.instance1 or instance1.my.cnf. Having the naming convention in the configuration files is very important as it will come into effect with the configuration of SYSTEMD. I also set my naming convention in the PID file because this will also be used by configuration of SYSTEMD. Make sure the socket you have configured in your configuration files matches what was in your client configuration files in the previous step. Example : 
[mysqld]
 
 ## Server
 basedir = /usr/local/mysql
 datadir = /mysql/data/instance1
 binlog_format = MIXED
 log_slave_updates = 1
 
 log-bin = /mysql/logs/instance1/mysql-bin
 relay-log = /mysql/logs/instance1/relay-bin
 log_error = /mysql/logs/instance1/mysql_error.log
 slow_query_log_file = /mysql/logs/instance1/slow_query.log
 
 socket = /var/run/mysql/mysqld_instance1.sock
 pid-file = /var/run/mysql/mysqld_instance1.pid
 
 port = 3306
 user = mysql
 server-id = 1

Initialize MySQL

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. Next, update the MySQL client configuration files (/etc/instance1_client.cnf and /etc/instance2_client.cnf in my example) with the temporary password. This will make it simpler to log in and change the initial password. Repeat this for each instance. Example : 
root@binary:/usr/local# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.instance1.cnf --initialize
 
 Database files are present in the data directory 
 
 root@binary:/usr/local# ls -al /mysql/data/instance1
 total 110628
 drwxr-xr-x 5 mysql mysql 4096 Jun 22 13:19 .
 drwxr-xr-x 4 mysql mysql 4096 Jun 19 18:04 ..
 -rw-r----- 1 mysql mysql 56 Jun 22 13:18 auto.cnf
 -rw-r----- 1 mysql mysql 417 Jun 22 13:19 ib_buffer_pool
 -rw-r----- 1 mysql mysql 12582912 Jun 22 13:19 ibdata1
 -rw-r----- 1 mysql mysql 50331648 Jun 22 13:19 ib_logfile0
 -rw-r----- 1 mysql mysql 50331648 Jun 22 13:18 ib_logfile1
 drwxr-x--- 2 mysql mysql 4096 Jun 22 13:18 mysql
 drwxr-x--- 2 mysql mysql 4096 Jun 22 13:18 performance_schema
 drwxr-x--- 2 mysql mysql 12288 Jun 22 13:19 sys
 
 Capture the temporary root password
 
 root@binary:/usr/local# cat /mysql/logs/instance1/mysql_error.log
 2018-06-22T17:18:50.464555Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 2018-06-22T17:18:50.978714Z 0 [Warning] InnoDB: New log files created, LSN=45790
 2018-06-22T17:18:51.040350Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2018-06-22T17:18:51.129954Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5506e36e-7640-11e8-9b0f-0800276bf3cb.
 2018-06-22T17:18:51.132700Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2018-06-22T17:18:51.315917Z 1 [Note] A temporary password is generated for root@localhost: ptraRbBy<6Wm

SYSTEMD configuration

Now that the MySQL instances are prepared and ready to be started. We will now configure SYSTEMD so that systemctl can manage the MySQL instances.

SYSTEMD MySQL 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. You want to make sure that the PIDfile and the MySQL configuration file in the ExecStart will match up with your previous configurations. You only need to create one SYSTEMD configuration file. As you enable each service in the next step, SYSTEMD will make copies of the configuration for you and replace the %I accordingly with your naming convention. Example /etc/systemd/system/mysql@.service : 
[Unit]
 Description=Oracle MySQL
 After=network.target
 
 [Service]
 Type=forking
 User=mysql
 Group=mysql
 PIDFile=/var/run/mysql/mysqld_prd_%I.pid
 ExecStart=
 ExecStart=/usr/cd --defaults-file=/etc/my.%I.cnf --daemonize
 Restart=on-failure
 RestartPreventExitStatus=1
 
 [Install]
 WantedBy=multi-user.target

Enable and start the MySQL instances

Enable the service, placing the naming convention after the @ symbol using the systemctl command. SYSTEMD will make a copy of the configuration file in the previous step and replace the %I with the text after the @. When viewing the status of the service, you will see that the process is using the correct configuration file based upon the naming convention. Repeat for each instance. Example : 
systemctl enable mysql@instance1
 systemctl start mysql@instance1
 
 root@binary:~# systemctl status mysql@instance1
 ● mysql@instance1.service - Oracle MySQL
  Loaded: loaded (/etc/systemd/system/mysql@.service; enabled; vendor preset: enabled)
  Active: active (running) since Fri 2018-06-22 14:51:48 EDT; 10min ago
  Process: 11372 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.instance1.cnf --daemonize (code=exited, status=0/SUCCESS)
  Main PID: 11374 (mysqld)
  Tasks: 28 (limit: 4915)
  CGroup: /system.slice/system-mysql.slice/mysql@instance1.service
  └─11374 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.instance1.cnf --daemonize
 
 Jun 22 14:51:48 binary systemd[1]: Starting Oracle MySQL...
 Jun 22 14:51:48 binary systemd[1]: Started Oracle MySQL.
Example PID and Socket files : 
root@binary:/var/log# ls -al /var/run/mysql
 total 16
 drwxr-xr-x 2 mysql mysql 160 Jul 20 10:33 .
 drwxr-xr-x 19 root root 640 Jul 20 10:33 ..
 -rw-r----- 1 mysql mysql 6 Jul 20 10:33 mysqld_instance1.pid
 srwxrwxrwx 1 mysql mysql 0 Jul 20 10:33 mysqld_instance1.sock
 -rw------- 1 mysql mysql 6 Jul 20 10:33 mysqld_instance1.sock.lock
 -rw-r----- 1 mysql mysql 6 Jul 20 10:33 mysqld_instance2.pid
 srwxrwxrwx 1 mysql mysql 0 Jul 20 10:33 mysqld_instance2.sock
 -rw------- 1 mysql mysql 6 Jul 20 10:33 mysqld_instance2.sock.lock

Managing MySQL

Now that we have started the two MySQL instances, we can log in to them using the aliases that we created pointing to the client configuration files that we updated to use the temporary root password. Next, we can log in and change the initial root password, and then update the configuration files accordingly with the new credentials.

Change root password

Log in to MySQL using the alias mysql1 and mysql2 which we configured previously and change the root password. Repeat for each instance. Example : 
mysql1
 
 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
 mysql> exit

Update MySQL client configuration

Update the MySQL client configuration files (/etc/instance1_client.cnf and /etc/instance2_client.cnf in my example) with the new passwords. Repeat for each instance. Example client config /etc/instance1_client.cnf : 
[client]
 user=root
 password='MyNewPass'
 socket=/var/run/mysql/mysqld_instance1.sock

Conclusion

Configuring MySQL to be controlled by systemctl makes it much easier to manage your MySQL instances. This process also allows for easy configuration of multiple instances, even beyond two. But keep in mind when configuring multiple MySQL instances on a single server, you allocate the memory for each of the MySQL instances accordingly to allow for overhead.

MySQL Database Consulting Services

Ready to optimize your MySQL Database for the future?
View full post