Sometimes it’s needed to have 2 instances of mysql daemon in the same server.
In that case I decided to use the port 3307 for the second instance.
Although there are other (maybe even easier) ways to do that, here is one of them.
Creating a second instance of MySQL server on port 3307.
Create a copy of the admin config file /etc/mysql/debian.cnf to /etc/mysql/debian2.cnf with the following content example:
cp /etc/mysql/debian.cnf /etc/mysql/debian2.cnf
vim /etc/mysql/debian2.cnf
Content:
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = 1cddmCxKskBGzzA6
socket = /var/run/mysqld/mysqld2.sock
#
[mysql_upgrade]
user = debian-sys-maint
password = 1cddmCxKskBGzzA6
socket = /var/run/mysqld/mysqld2.sock
basedir = /usr
Create a copy mysql main config file /etc/mysql/my.cnf to /etc/mysql/my2.cnf and change the port, socket, daemon etc. as follows:
cp /etc/mysql/my.cnf /etc/mysql/my2.cnf
vim /etc/mysql/my2.cnf
Content:
[client]
port = 3307
socket = /var/run/mysqld/mysqld2.sock
#
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
port = 3307
basedir = /usr
datadir = /var/lib/mysql2
# .....................
master-host = 127.0.0.1
master-port = 3306
master-user = replicant
master-password = 'xxxxxx'
#
relay-log = /var/log/mysql/relay2.log
relay-log-info-file = /var/log/mysql/relay-log2.info
relay-log-index = /var/log/mysql/relay-log2.index
#
#Disable the binary logs for replicate
#log_bin = /var/log/mysql/mysql-bin.log
#log-bin-index = /var/log/mysql/log-bin.index
#
# Disable the master error behavior
#slave-skip-errors=1045,126,1062
#
log-error = /var/log/mysql/error2.log
log = /var/log/mysql/mysql2.log
the rest can stay the same.
Create a copy of the file /etc/mysql/debian-start to /etc/mysql/debian-start2 with the following content differences:
cp /etc/mysql/debian-start /etc/mysql/debian-start2
vim /etc/mysql/debian-start2
Content:
MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian2.cnf"
MYADMIN="/usr/bin/mysqladmin --port=3307 --defaults-file=/etc/mysql/debian2.cnf"
MYUPGRADE="/usr/bin/mysql_upgrade --port=3307 --defaults-extra-file=/etc/mysql/debian2.cnf"
MYCHECK="/usr/bin/mysqlcheck --port=3307 --defaults-file=/etc/mysql/debian2.cnf"
MYCHECK_SUBJECT="WARNING: mysqlcheck has found corrupt tables"
MYCHECK_PARAMS="--all-databases --fast --silent"
MYCHECK_RCPT="root"
Create a copy of the MySQL init script /etc/init.d/mysql to /etc/init.d/mysql2 and modify the following at the beginning of the file:
cp /etc/init.d/mysql /etc/init.d/mysql2
vim /etc/init.d/mysql2
Content:
CONF=/etc/mysql/my2.cnf
# Modified by michel to use another defauls file (/etc/mysql/debian2.cnf --port=3307)
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian2.cnf --port=3307"
#
# priority can be overriden and "-s" adds output to stderr
# Modified by michel: /etc/init.d/mysql2 -i
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql2 -i"
#
..........
#
# Usage: void mysqld_get_param option
# Modified by michel to use the config file of second instance of mysql (/etc/mysql/my2.cnf)
mysqld_get_param() {
/usr/sbin/mysqld --defaults-file=/etc/mysql/my2.cnf --print-defaults \
| tr " " "\n" \
| grep -- "--$1" \
| tail -n 1 \
| cut -d= -f2
}
#
## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
# check for config file
# Modified by michel : /etc/mysql/my2.cnf
if [ ! -r /etc/mysql/my2.cnf ]; then
#
.................
.................
case "${1:-''}" in
'start')
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld2"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my2.cnf --mysqld=mysqld2 &
# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg "."
done
if mysqld_status check_alive warn; then
log_end_msg 0
# Now start mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start2)
[ -n "$output" ] && log_action_msg "$output"
else
log_end_msg 1
log_failure_msg "Please take a look at the syslog"
fi
fi
Create a symlink from /usr/sbin/mysqld called /usr/sbin/mysqld2 using the command:
ln -s /usr/sbin/mysqld /usr/sbin/mysqld2
Create the new Database Directory and make a copy of the existing databases to /var/lib/mysql2.
Note: make sure the mysqld daemon is not running during this.
mkdir /var/lib/mysql2
cp -a /var/lib/mysql /var/lib/mysql2
Give ownership of the databases to mysql user using the command:
chown -R mysql. /var/lib/mysql2
Enter the new init script in default runlevels with the command:
update-rc.d mysql2 defaults
[Optional] Create a symlink for manual start/stop with the command:
ln -s /etc/init.d/mysql2 /usr/sbin/rcmysql2
Start the both mysql databases with the commands:
rcmysql start
rcmysql2 start
You will have a replica of the original mysql database running on port 3307 as well.