Friday, 9 February 2018

Creating multiple MySQL instances in linux server using mysql_install_db and mysqld_multi

To run multiple instances using MySQL we need to have a couple of things separate from the initial install on MySQL like data directory, init script, config file etc.


Open the firewalld ports :

$ firewall-cmd --zone=public --add-port=3406/tcp --permanent
$ firewall-cmd --zone=public --add-port=3406/udp --permanent
$ firewall-cmd --reload
$ systemctl restart firewalld

Create a new data directory [/var/lib/mysql2] and make mysql user own it.

$ mkdir /var/lib/mysql2
$ chown -R mysql:mysql /var/lib/mysql2/

Create or copy existing mysql configuration file, call it my2.cnf and update data directory/port values

$ cp /etc/my.cnf /etc/my2.cnf
$ vi /etc/my2.cnf

Sample my.cnf 

# For advice on how to change settings please see

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
bind-address =
# Disabling symbolic-links is recommended to prevent assorted security risks

socket = /var/lib/mysql/mysql2.sock

socket = /var/lib/mysql/mysql2.sock
port = 3406

Update datadir,Port,socket,pid details etc 

Initializes the MySQL data directory and create the system tables using mysql_install_db

$ mysql_install_db --datadir=/var/lib/mysql2  --user=mysql

Note : mysql_install_db is depricated for MySQL version 5.7 

Instead use :

$ mysqld --initialize-insecure -u mysql --datadir=/var/lib/mysql2

Add variable skip-grant-tables in my.cnf file to reset the root password 

Make following changes on each systemd unit files as below to start the instance as systemd service.

$  vi /lib/systemd/system/mysqld2.service

Description=MySQL Server



# Disable service start and stop timeout logic of systemd for mysqld service.

# Start main service
ExecStart=/usr/sbin/mysqld  --defaults-file=/etc/my2.cnf --socket=/var/lib/mysql/mysql2.sock

Reload the service file :

$ systemctl daemon-reload

Start the mysql instance

$ systemctl start mysqld2.service

Login to the instance as below 

$ mysql -uroot -p -P3406

Reset the root password as below 

mysql> use mysql;
mysql> update user set authentication_string=password('new_Password') where user='root';
mysql> flush privileges;
$ exit 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_Password' ;
mysql> flush privileges;


Step-1:Login to your server as root user

Step-2:Login to your mysql server as root and execute the following command

mysql>GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'sample_password';

We are providing shutdown privileges to multi_admin user 

Step-3: Stop the mysql service 

$ sevice mysql stop 
$ systemctl stop mysqld 

Step-4: Locate the mysql config file “my.cnf” and change it as per our requirement 

user= mysql
pid-file= /var/run/mysqld/
socket= /var/run/mysqld/mysqld.sock
port= 3306
datadir= /var/lib/mysql
user= mysql
pid-file= /var/run/mysqld/
socket= /var/run/mysqld/mysqld2.sock
port= 3406
datadir= /var/lib/mysql2

Step-6:Now just below [mysqld] section put the following lines

mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = sample_password

Step-7:Save the configuration file and now create the files and folders as we have mentioned in the above configuration.To do that execute the following commands.

$ mkdir -P /var/lib/mysql2

Step-8:Copy the mysql database files from the original instance to the second instances database directory and change the ownership of the data directory to the mysql user so the instance can read them.

$ cp -r /var/lib/mysql/mysql/ /var/lib/mysql2
$ chown -R mysql:mysql /var/lib/mysql2

Step-9: Now the two instances are ready to run.We can start them by the following command

$ mysqld_multi start

To view the status of the instances you can run

$  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running

You can see that the mysqld_multi script has started multiple mysql processes with the following commands.

ps -ef | grep "mysql"

To stop the instance execute the below command.

$ mysqld_multi stop

We can also control individual instances by referring to the assigned number

$ mysqld_multi stop 1


$ mysqldump --socket=/var/run/mysqld/mysqld2.sock --all-databases > mysqld2.sql

$ innobackupex --defaults-file=/etc/my.cnf --defaults-group=mysqld2 --socket=/var/run/mysqld/mysqld2.sock /root/Back

1 comment:

  1. Thanks for sharing an these types of the simple steps for creating an multiple mysql instances in the linux dedicated server with us.