How to configure multiple mysql instance in Ubuntu
In this tutorial we will learn,how to configure multiple mysql instance in Ubuntu.Here multiple mysql instance means we can run multiple mysql server in single machine.We expect you have already install mysql-server in your system(if not then run the command apt-get install mysql-server).
To configure multiple mysql instance in Ubuntu,follow the given below steps
Login as a root or superuser(See given below command)
sudo su - or su -
Step 1: Create a new mysql configuration directory for new mysql instance
cp -prvf /etc/mysql/ /etc/mysql2
Step 2: Create new data directory for new instance and give permission and ownership to new directory.
mkdir -p /var/lib/mysql2 chown --reference /var/lib/mysql /var/lib/mysql2 chmod --reference /var/lib/mysql /var/lib/mysql2
Step 3: Create new log directory for new instance
mkdir -p /var/log/mysql2 chown --reference /var/log/mysql /var/log/mysql2 chmod --reference /var/log/mysql /var/log/mysql2
Step 4: Now edit the /etc/mysql2/my.cnf file as given below.
Note: Here we are changing the port no. to 3337.
Either You can paste this configuration after making blank /etc/mysql2/my.cnf file
vi /etc/mysql2/my.cnf [client] port = 3337 socket = /var/run/mysqld/mysqld2.sock [mysqld_safe] socket = /var/run/mysqld/mysqld2.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld2.pid socket = /var/run/mysqld/mysqld2.sock port = 3337 basedir = /usr datadir = /var/lib/mysql2 tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql2/error.log expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql2/conf.d/
Step 5: Now edit /etc/mysql2/debian.cnf file. You have to only change the value of socket paramter in two section.Apart of it there is no changes in /etc/mysql2/debian.cnf file.
In section [client] socket = /var/run/mysqld/mysqld2.sock In section [mysql_upgrade] socket = /var/run/mysqld/mysqld2.sock
Below given is reference of my debian.conf file.It is just an example.
[client]
host = localhost
user = debian-sys-maint
password = s5ozLSHeoO4cRaDi
socket = /var/run/mysqld/mysqld2.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = s5ozLSHeoO4cRaDi
socket = /var/run/mysqld/mysqld2.sock
basedir = /usr
Step 6: Now edit the apparmor file for mysql. This is important step. Paste the given below configuration in /etc/apparmor.d/usr.sbin.mysqld file
Using vi editor for editing
vi /etc/apparmor.d/usr.sbin.mysqld
Paste the given below code in /etc/apparmor.d/usr.sbin.mysqld
### secondary a.k.a mysql2 ####
/etc/mysql2/*.pem r,
/etc/mysql2/conf.d/ r,
/etc/mysql2/conf.d/* r,
/etc/mysql2/*.cnf r,
/var/lib/mysql2/ r,
/var/lib/mysql2/** rwk,
/var/log/mysql2/ r,
/var/log/mysql2/* rw,
/{,var/}run/mysqld/mysqld2.pid w,
/{,var/}run/mysqld/mysqld2.sock w,
########################
Given below is reference of my apparmor.d mysql file. Check your apparmor’s mysql file with the given below reference.This step is very important.If there is any mistake the mysql instance will not run and you can see the error in /var/log/syslog file
Step 7: Now restart the apparmor service
/etc/init.d/apparmor restart
Step 8: Now initiate new databases in new mysql data directory
mysql_install_db --user=mysql --datadir=/var/lib/mysql2
Before going to next step, see the given below output come when I first run the above command. We can see two WARNINGS hence we will solve this warning also for new instance as per the output.
First have a look on output
root@ubuntu:~# mysql_install_db --user=mysql --datadir=/var/lib/mysql2 Installing MySQL system tables... 131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. OK Filling help tables... 131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h ubuntu password 'new-password' Alternatively you can run: /usr/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl Please report any problems with the /usr/scripts/mysqlbug script! root@ubuntu:~#
Step 9: You can observe two warnings here.Hence we will solve this warning first.
sed -i 's/key_buffer/key_buffer_size/' /etc/mysql2/my.cnf
As per warning,hence we changed the key_buffer to key_buffer_size in /etc/mysql2/my.cnf
131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
Filling help tables…
131204 21:07:39 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
OK
Step 10: Now all are set, we will start the new mysql instance by given below command.
To start the new mysql instance
nohup mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
Press two time enter.
You can check the port for new mysql instance is listening or not.You can see in port no. 3337 mysql is listening
netstat -tanp|grep mysql
Here is my system reference
root@ubuntu:~# netstat -tanp|grep mysql tcp 0 0 127.0.0.1:3337 0.0.0.0:* LISTEN 4257/mysqld tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 3151/mysqld root@ubuntu:~#
Step 11: Now connecting to new mysql instance.
Here,
-u = user
-P = port no. (In our case we are using 3337,check the mysql2/my.cnf file for reference)
-h = host
mysql -u root -P 3337 -h 127.0.0.1
Step 12: It is new data structure hence there is no password for mysql user roor. Now you can see the mysql prompt like this mysql >
We will now reset the root password because of security reason. Replace NEW-PASSWORD with your password
mysql > update mysql.user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql > flush privileges;
mysql > exit
Step 13: You already now in above steps how to start the mysql instance. Now we will use the command to stop the new mysql instance.
Here,
-p = password
-S = Path of MySQL socket file
To stop the new mysql instance
mysqladmin -S /var/lib/mysql/mysql2.sock shutdown -p
Because you have already set root password, give that password here.
With the same method you can create other MySQL instances in same server but only you have to replace the mysql2 to mysql3 or mysql4 or so on …. mysqlN
I follow your step until step 5 but my connection dropped in the middle execution can we restore it to previous state? because mysql instance error with this eror
Feb 16 09:46:23 simpeg1 systemd[1]: Stopped MySQL Community Server.
Feb 16 09:46:23 simpeg1 systemd[1]: Starting MySQL Community Server…
Feb 16 09:46:23 simpeg1 mysql-systemd-start[11116]: my_print_defaults: Can’t read dir of ‘/etc/mysql2/conf.d/’ (Errcode: 2 – No such file or directory)
Feb 16 09:46:23 simpeg1 mysql-systemd-start[11116]: my_print_defaults: [ERROR] Fatal error in defaults handling. Program aborted!
Feb 16 09:46:23 simpeg1 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Feb 16 09:46:47 simpeg1 systemd[1]: Stopped MySQL Community Server.
Feb 16 09:46:47 simpeg1 systemd[1]: mysql.service: Unit entered failed state.
Feb 16 09:46:47 simpeg1 systemd[1]: mysql.service: Failed with result ‘exit-code’.
Feb 16 09:47:13 simpeg1 systemd[1]: Stopped MySQL Community Server.
it seems its read as its config etc/mysql2/conf.d/’
please urgent
Hi Daniel,
In error find this line – “my_print_defaults: Can’t read dir of ‘/etc/mysql2/conf.d/’ (Errcode: 2 – No such file or directory)”.
Seems either directory /etc/mysql2/conf.d/ not present or may have permission issue. Check your main my.cnf file and read its config .
Regards
Sharad
mysql_install_db –user=mysql –datadir=/var/lib/mysql2
I am getting below error in the above step
170803 15:30:40 [Warning] Can’t create test file /var/lib/mysql2/sowmya-Latitude-E7440.lower-test
170803 15:30:40 [Warning] Can’t create test file /var/lib/mysql2/sowmya-Latitude-E7440.lower-test
Installation of system tables failed! Examine the logs in
/var/lib/mysql2 for more information.
You can try to start the mysqld daemon with:
shell> /usr/sbin/mysqld –skip-grant &
and use the command line tool /usr/bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/bin/mysql -u root mysql
mysql> show tables
Try ‘mysqld –help’ if you have problems with paths. Using –log
gives you a log in /var/lib/mysql2 that may be helpful.
Please consult the MySQL manual section
‘Problems running mysql_install_db’, and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before submitting a bug report
at http://bugs.mysql.com/
Anyone please advise
Check the ownership and permission on /var/lib/mysql2/.
You should follow step2 and step3 .
Regards
Sharad
Not working
why not mysqld_multi ?
Hello,
Thank you very much for your tutorial!
I stopped on the step 10: nohup mysqld_safe –defaults-file=/etc/mysql2/my.cnf &
Everything is OK, but when I run this command the instance from mysql don´t start…
Any ideia?
Im using a ubuntu;
thanks!
I resolved the problem!
Anyway, thanks for your tutorial!!!!!
Hi, I am facing same issue. The server is not starting and getting below message. May I know what you fixed?
root@ip-172-31-60-135:/var/lib/mysqldev# nohup mysqld_safe –defaults-file=/etc/mysqldev/my.cnf &
[1] 3796
root@ip-172-31-60-135:/var/lib/mysqldev# nohup: ignoring input and appending output to ‘nohup.out’
Hi Tim,
Which MySQL version are you using ? What operating system?
Regards
Sharad
awesome turotrial, very detailed.
just followed your steps and all went very well thank you.
Thank you for commenting,
We are glad to see your positive feedback on this tutorial. Keep reading our blog.
Regards
Sharad
hi, i started new instance of mysql successufully after following your steps . But how to stop this instance? i tried some ways as kill command, mysqladmin to shutdown the instance but not succeed. please help me :(. Thanks
i killed it successfully but this instance auto start. I cannot completely stop it.
Hello Thai,
Check the correct socket you are using in mysqladmin command.
I hope you are not running mysql with monit or any mysql entry in /etc/inittab file.
Regards
Sharad
Hi, Please add a detail easy to use post on creating multiple mongodb instances on CentOS/RHEL/Fedora and Ubuntu/Debian/Mint
Thank you so much. Was struggling with this till I came across your post.
You are welcome Arun,
Thank you for giving us the feedback.
Regards
Sharad