MySQL多实例配置

实验环境：RHEL6.4为最小化安装，mysql安装包为通用二进制安装包，版本为mysql-5.6.261.创建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/mysql3.创建目录# 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 = 3306socket = /data/3306/mysql.sock[mysqld]port=3306socket = /data/3306/mysql.sockpid-file = /data/3306/data/mysql.pidbasedir = /usr/local/mysqldatadir = /data/3306/dataserver-id=1#log-bin=mysql-bin#log-bin-index= mysql-bin.index# LOGGINGlog_error=/data/3306/log/mysql-error.logslow_query_log_file=/data/3306/log/mysql-slow.logslow_query_log=15. /data/3307中新建my.cnf# cd ../3307/# vim my.cnf[client]port = 3307socket = /data/3307/mysql.sock[mysqld]port=3307socket = /data/3307/mysql.sockpid-file = /data/3307/data/mysql.pidbasedir = /usr/local/mysqldatadir = /data/3307/dataserver-id=2#log-bin=mysql-bin#log-bin-index= mysql-bin.index# LOGGINGlog_error=/data/3307/log/mysql-error.logslow_query_log_file=/data/3307/log/mysql-slow.logslow_query_log=1 6. 在/data/3306中新建mysql启动文件# cd /data/3306/# vim mysql#！/bin/sh[ -f /etc/init.d/functions ] && . /etc/init.d/functionsport=3306mysql_user="root"mysql_pwd=""mysql_sock="/data/${port}/mysql.sock"CmdPath="/usr/local/mysql/bin"#startup functionusage（）{printf "Usage: /data/${port}/mysql {start|stop|restart} "}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/trueelseprintf "MySQL is running... "exitfi}#stop functionfunction_stop_mysql（）{if [ ！ -e "$mysql_sock" ];thenprintf "MySQL is stopped... "exitelseaction "Stoping MySQL..." /bin/true${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdownfi}#restart functionfunction_restart_mysql（）{function_stop_mysql &>/dev/nullsleep 2function_start_mysql &>/dev/nullaction "Restarting MySQL..." /bin/true}if [ $# -ne 1 ];thenusageficase $1 instart）function_start_mysql;;stop）function_stop_mysql;;restart）function_restart_mysql;;*）usageesac#chmod +x mysql7、在/data/3307中新建mysql启动文件# cd /data/3307/ # vim mysql#！/bin/bash[ -f /etc/init.d/functions ] && . /etc/init.d/functionsport=3307mysql_user="root"mysql_pwd=""mysql_sock="/data/${port}/mysql.sock"CmdPath="/usr/local/mysql/bin"usage（）{printf "Usage: /data/${port}/mysql {start|stop|restart} "}#startup functionfunction_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/trueelseprintf "MySQL is running... "exitfi}#stop functionfunction_stop_mysql（）{if [ ！ -e "$mysql_sock" ];thenprintf "MySQL is stopped... "exitelseaction "Stoping MySQL..." /bin/true${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdownfi}#restart functionfunction_restart_mysql（）{function_stop_mysql &>/dev/nullsleep 2function_start_mysql &>/dev/nullaction "Restarting MySQL..." /bin/true}case $1 instart）function_start_mysql;;stop）function_stop_mysql;;restart）function_restart_mysql;;*）usageesac#chmod +x mysql8. 修改文件拥有者和权限#chown -R mysql:mysql /data9. 添加mysql启动路径 #echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile
#source /etc/profile10. 初始化数据库 # 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/data11. 启动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/mysql13. 重启mysql#/data/3306/mysql restart#/data/3307/mysql restart#netstat -lntp | grep 33014.知识点进入mysql时，要记得加-S 指定mysql套接字的路径# mysql –u root –p -S /data/3307/mysql.sock下面的命令可以平滑关闭mysql# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown本文永久更新链接地址