Creating multiple MySQL instances 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.

 mysql_install_db                             mysql-graphic

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
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# 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
datadir=/var/lib/mysql2
socket=/var/lib/mysql/mysql2.sock
port=3406
user=mysql
default-time-zone=SYSTEM
server_id=102
innodb_file_per_table=1
log-bin=mysql-binlog
relay-log=relay-log
binlog_format=row
bind-address = 0.0.0.0
slow_query_log=ON
long_query_time=0
innodb_monitor_enable=all
performance_schema=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
log_timestamps=SYSTEM
[mysql]
socket = /var/lib/mysql/mysql2.sock

[client]
socket = /var/lib/mysql/mysql2.sock
port = 3406
protocol=TCP

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

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql

PIDFile=/var/run/mysqld/mysqld2.pid

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

# 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;

mysqld_multi

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';
mysql>FLUSH PRIVILEGES;

We are providing shutdown privileges to multi_admin user 

Step-3: Stop the mysql service 

$ sevice mysql stop 
or
$ systemctl stop mysqld 

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

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

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

[mysqld_multi]
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
Output:
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

Backup 

$ 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: