mysql单机多实例配置

软件下载
1、下载地址: https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz
2、选择5.6版本

安装
1、准备文件和目录

tar -zxvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -sv mysql-5.6.40-linux-glibc2.12-x86_64 mysql

mkdir -pv /home/data/mysql_3306
mkdir -pv /home/data/mysql_3307
mkdir -pv /home/data/mysql_3306/{data,log}
mkdir -pv /home/data/mysql_3307/{data,log}
2、修改权限
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
[root@ebs-62521 local]# id mysql
uid=500(mysql) gid=500(mysql) groups=500(mysql)
[root@ebs-62521 local]#

chown -R mysql.mysql /usr/local/mysql
chown -R mysql.mysql /home/data
3、初始化数据

/usr/local/mysql/scripts/mysql_install_db –datadir=/home/data/mysql_3306/home/data –user=mysql –basedir=/usr/local/mysql –no-defaults
/usr/local/mysql/scripts/mysql_install_db –datadir=/home/data/mysql_3307/home/data –user=mysql –basedir=/usr/local/mysql –no-defaults

第二种方法
/usr/local/mysql/scripts/mysql_install_db –defaults-file=/home/data/mysql_3306/my3306.cnf –basedir=/usr/local/mysql –datadir=/home/data/mysql_3306 –user=mysql
/usr/local/mysql/scripts/mysql_install_db –defaults-file=/home/data/mysql_3307/my3307.cnf –basedir=/usr/local/mysql –datadir=/home/data/mysql_3307 –user=mysql
4、配置文件

/home/data/mysql_3306/my3306.cnf
[client]
user=root
port=3306
socket=/tmp/mysql3306.sock

[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/home/data/mysql_3306
pid-file=/home/data/mysql_3306/mysql_3306.pid
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216
log-error = /home/data/mysql_3306/log/error.log
binlog_format = mixed
expire_logs_days = 30
log-bin = /home/data/mysql_3306/log/mysql-bin.log
log-bin-index = /home/data/mysql_3306/log/mysql-bin.index

编辑第二个实例的配置文件
/home/data/mysql_3306/my3307.cnf
[client]
root=root
port=3307
socket=/tmp/mysql3307.sock

[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/home/data/mysql_3307
pid-file=/home/data/mysql_3307/mysql_3307.pid
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216
log-error = /home/data/mysql_3307/log/error.log
binlog_format = mixed
expire_logs_days = 30
log-bin = /home/data/mysql_3307/log/mysql-bin.log
log-bin-index = /home/data/mysql_3307/log/mysql-bin.index

5、启动服务

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3306.cnf &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3307.cnf &

第二种方法
/usr/local/mysql/bin/mysqld_safe –defaults-file=/home/data/mysql_3306/my3306.cnf
–pid-file=/home/data/mysql_3306/mysql_3306.pid &

/usr/local/mysql/bin/mysqld_safe –defaults-file=/home/data/mysql_3307/my3307.cnf
–pid-file=/home/data/mysql_3307/mysql_3307.pid &

1.3.4 启动实例:
sh mysqld_safe –defaults-file=/home/data/mysql_3307/my.cnf –pid-file=/home/data/mysql_3307/3307.pid &

shell脚本管理多实例服务:
vim /etc/init.d/mysql3306
chmod +x /etc/init.d/mysql3306

#!/bin/bash

. /etc/init.d/functions
. /etc/profile

Start=’/usr/local/mysql/bin/mysqld_safe –defaults-file=/home/data/mysql_3306/my3306.cnf –pid-file=/home/data/mysql_3306/mysql_3306.pid’
Stop=’/usr/local/mysql/bin/mysqladmin -uroot -S /tmp/mysql3306.sock shutdown’
Port=`ss -tunlp|grep 3306|wc -l`

function START(){
if [ $Port -ne 1 ];then
$Start >/dev/null 2>&1 &
sleep 3
if [ $? -eq 0 ];then
action ‘MySQL 3306 Starting’ /bin/true
fi
else
action ‘MySQL 3306 Already Exists’ /bin/true
fi
}
function STOP(){
if [ $Port -ne 0 ];then
$Stop
if [ $? -eq 0 ];then
action ‘MySQL Stoping Successfuly’ /bin/true
fi
else
action ‘MySQL already Stoped’ /bin/true
fi
}
function RESTART(){
STOP
sleep 1
START
}
case $1 in
start)
START
;;
stop)
STOP
;;
restart)
RESTART
;;
*)
echo “Usage: $0 {start|stop|restart}”
;;
esac

编辑另外一个实例启动脚本
vim /etc/init.d/mysql3307
chmod +x /etc/init.d/mysql3307

#!/bin/bash

. /etc/init.d/functions
. /etc/profile

Start=’/usr/local/mysql/bin/mysqld_safe –defaults-file=/home/data/mysql_3307/my3307.cnf –pid-file=/home/data/mysql_3307/mysql_3307.pid’
Stop=’/usr/local/mysql/bin/mysqladmin -uroot -S /tmp/mysql3307.sock shutdown’
Port=`ss -tunlp|grep 3307|wc -l`

function START(){
if [ $Port -ne 1 ];then
$Start >/dev/null 2>&1 &
sleep 3
if [ $? -eq 0 ];then
action ‘MySQL 3307 Starting’ /bin/true
fi
else
action ‘MySQL 3307 Already Exists’ /bin/true
fi
}
function STOP(){
if [ $Port -ne 0 ];then
$Stop
if [ $? -eq 0 ];then
action ‘MySQL Stoping Successfuly’ /bin/true
fi
else
action ‘MySQL already Stoped’ /bin/true
fi
}
function RESTART(){
STOP
sleep 1
START
}
case $1 in
start)
START
;;
stop)
STOP
;;
restart)
RESTART
;;
*)
echo “Usage: $0 {start|stop|restart}”
;;
esac

6、连接服务

mysql -S /tmp/mysql_3306.sock
mysql -S /tmp/mysql_3307.sock
或
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P3306 -S /tmp/mysql_3306.sock
/usr/local/mysql/bin/mysql -h 127.0.0.1 -P3307 -S /tmp/mysql_3307.sock

登陆管理MySQL多实例数据库

（1）登陆MySQL数据库

mysql -S /tmp/mysql_3306.sock #需要指定sock文件，不然使用默认的3306
blob.png

（2）上面看到MySQL的root的密码还是空的，我们需要修改。

mysqladmin -u root password ‘pcm123456’ -S /tmp/mysql_3306.sock
blob.png

到这里，MySQL的多实例配置就完成了。如果要远程登陆多实例的MySQL的话，记得加上端口号。