MySQL多实例配置
实验环境：RHEL6.4为最小化安装，mysql安装包为通用二进制安装包，版本为mysql-5.6.26
1.创建mysql用户
#useradd –M –s /sbin/nologin mysql
#yum –y install ncurses-devel libaio-devel
#安装mysql的依赖包，否则下面无法初始化成功
2.软件包解压缩
# tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz -C /usr/local
# mv /usr/local/mysql-5.6.26-linux-glibc2.5-x86_64/ /usr/local/mysql
3.创建目录
# mkdir /data/{3306,3307}/data –pv
# mkdir /data/{3306,3307}/log –pv
# tree /data/ #查看目录树
4. /data/3306中新建my.cnf
# cd /data/3306/
# vim my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
port=3306
socket = /data/3306/mysql.sock
pid-file = /data/3306/data/mysql.pid
basedir = /usr/local/mysql
datadir = /data/3306/data
server-id=1
#log-bin=mysql-bin
#log-bin-index= mysql-bin.index
# LOGGING
log_error=/data/3306/log/mysql-error.log
slow_query_log_file=/data/3306/log/mysql-slow.log
slow_query_log=1
5. /data/3307中新建my.cnf
# cd ../3307/
# vim my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock
[mysqld]
port=3307
socket = /data/3307/mysql.sock
pid-file = /data/3307/data/mysql.pid
basedir = /usr/local/mysql
datadir = /data/3307/data
server-id=2
#log-bin=mysql-bin
#log-bin-index= mysql-bin.index
# LOGGING
log_error=/data/3307/log/mysql-error.log
slow_query_log_file=/data/3307/log/mysql-slow.log
slow_query_log=1
6. 在/data/3306中新建mysql启动文件
# cd /data/3306/
# vim mysql
#!/bin/sh
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
port=3306
mysql_user="root"
mysql_pwd=""
mysql_sock="/data/${port}/mysql.sock"
CmdPath="/usr/local/mysql/bin"
#startup function
usage(){
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
}
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
action "Starting MySQL..." /bin/true
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
action "Stoping MySQL..." /bin/true
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
function_stop_mysql &>/dev/null
sleep 2
function_start_mysql &>/dev/null
action "Restarting MySQL..." /bin/true
}
if [ $# -ne 1 ];then
usage
fi
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
usage
esac
#chmod +x mysql
7、在/data/3307中新建mysql启动文件
# cd /data/3307/
# vim mysql
#!/bin/bash
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
port=3307
mysql_user="root"
mysql_pwd=""
mysql_sock="/data/${port}/mysql.sock"
CmdPath="/usr/local/mysql/bin"
usage(){
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
}
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
action "Starting MySQL..." /bin/true
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
action "Stoping MySQL..." /bin/true
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
function_stop_mysql &>/dev/null
sleep 2
function_start_mysql &>/dev/null
action "Restarting MySQL..." /bin/true
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
usage
esac
#chmod +x mysql
8. 修改文件拥有者和权限
#chown -R mysql:mysql /data
9. 添加mysql启动路径
#echo 'export PATH=$PATH:/usr/local/mysql/bin' >>/etc/profile
#source /etc/profile
10. 初始化数据库
# cd /usr/local/mysql/scripts/
#./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
#./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
11. 启动mysql
#/data/3306/mysql start
#/data/3307/mysql start
#netstat -lntp | grep 330 #查看是否启动进程
12. 登陆mysql
①# mysql -S /data/3306/mysql.sock #刚安装完的mysql是没有登陆密码的
#如果不成功，检查/data/3306/log目录下的mysql-error.log日志，逐一排除错误
如果登陆成功，下面就修改登录密码
mysql> update mysql.user set password=password("123456") where user='root';
mysql> flush privileges;
不建议在shell环境下修改密码，否则别人只要查看命令历史就能看到密码。当然你也可以是shell下进行，但是记得要清楚历史命令记录。
②同理，使用上面的方法修改3307的登陆密码
# mysql -S /data/3307/mysql.sock
mysql> update mysql.user set password=password("123456") where user='root';
mysql> flush privileges;
③要把上面更改后的密码写回到mysql的启动文件中（否则每次启动、关闭、重启mysql都要输入密码）
# sed -i 's/mysql_pwd=\"\"/mysql_pwd=\"123456\"/g' /data/3306/mysql
#sed -i 's/mysql_pwd=\"\"/mysql_pwd=\"123456\"/g' /data/3307/mysql
也可以手动进行修改
#vim /data/3306/mysql
# vim /data/3307/mysql
13. 重启mysql
#/data/3306/mysql restart
#/data/3307/mysql restart
#netstat -lntp | grep 330
14.知识点
进入mysql时，要记得加-S 指定mysql套接字的路径
# mysql –u root –p -S /data/3307/mysql.sock
下面的命令可以平滑关闭mysql
# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown