How to manage multiple MySQL binary installations with SYSTEMD
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 Debianapt 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
