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.
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.
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.
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.
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.
[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 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.
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
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.
systemctl enable mysql@instance1 systemctl start mysql@instance1 root@binary:~# systemctl status mysql@instance1 â— email@example.com - 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: /firstname.lastname@example.org â””â”€11374 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.instance1.cnf --daemonize Jun 22 14:51:48 binary systemd: Starting Oracle MySQL... Jun 22 14:51:48 binary systemd: 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
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.
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
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.
Interested in working with Kevin? Schedule a tech call.