mysql 配置多实例

进入安装包路径


#cd /usr/local/安装包路径
#tar -zxvf mysql-5.6.29-linux-glibc2.5-x86_64.tar.gz
重命名解压后的mysql安装文件，改为mysql


#mv mysql-5.6.12-linux-glibc2.5-x86_64  mysql


添加系统mysql组和mysql用户


#groupadd mysql
添加无登陆权限mysql账户：
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql mysql
#useradd -r -g mysql mysql




安装数据库


进入安装mysql软件目录
#cd /usr/local/mysql


 修改当前目录拥有者为mysql用户


#chown -R mysql:mysql ./


 安装数据库


 ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/3306/
 ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/3307/


  修改当前目录拥有者为root用户


#chown -R root:root ./


修改当前data目录拥有者为mysql用户


#chown -R mysql:mysql data


 
配置文件：/etc/my.cnf




[client]
default-character-set=utf8




[mysqld_multi] 
mysqld=/usr/local/mysql/bin/mysqld_safe 
mysqladmin=/usr/local/mysql/bin/mysqladmin 
user = admin
password = root




[mysqld3306]
datadir=/usr/local/mysql/data/3306
socket=/usr/local/mysql/data/3306/mysql3306.sock
port=3306
pid-file=/usr/local/mysql/data/3306/mysql3306.pid
log-error=/var/log/mysql3306.log
basedir=/usr/local/mysql
server-id=3306








[mysqld3307]
datadir=/usr/local/mysql/data/3307
socket=/usr/local/mysql/data/3307/mysql3307.sock
port=3307
pid-file=/usr/local/mysql/data/3307/mysql3307.pid
log-error=/var/log/mysql3307.log
basedir=/usr/local/mysql
server-id=3307






# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd


[mysqld_safe]
#log-error=/var/log/mysql/mysql.log
#pid-file=/var/run/mariadb/mariadb.pid






修改环境变量：
PATH=/usr/local/mysql/bin:$PATH:$HOME/bin


source .bash_profile


查看数据库：
mysqld_multi report
启动数据库：
mysqld_multi start 3306
mysqld_multi start 3307
mysqld_multi report


关闭数据库：
mysqld_multi stop 3306 
mysqld_multi stop 3307 
mysqld_multi report


mysqladmin shutdown --socket=/usr/local/mysql/data/3306/app_prod.sock  -uroot -p
mysqladmin shutdown --socket=/usr/local/mysql/data/3307/appslave.sock  -uroot -p


mysqladmin -uroot -pxxx shutdown -S /var/lib/mysql/mysql.sock     
mysqladmin shutdown --socket=/usr/local/mysql/data/3307/wg_prod_slave.sock
mysqladmin -uroot -pxxx shutdown -S /tmp/mysql3306.sock 




update user set password=PASSWORD("root") where user='root';


登陆数据库赋权关闭数据库用户及修改密码：


mysql -uroot  -p -S  /usr/local/mysql/data/3306/app_prod.sock
root暂时无密码
set password for 'root'@'localhost'=password('root');
grant shutdown on *.* to 'admin'@'localhost' identified by 'root' with grant option;
flush privileges;




mysql -uroot  -p -S  mysql3307.sock
root暂时无密码
set password for 'root'@'localhost'=password('root');
update mysql.user set password=password('root_app') where user='root';
grant shutdown on *.* to 'admin'@'localhost' identified by 'root' with grant option;
flush privileges;




 查看多实例日志：
 tail -200 /usr/local/mysql/share/mysqld_multi.log 

 tail -200 /usr/share/mysql/mysqld_multi.log
 
 log = /var/log/mysqld_multi.log
 
配置多实例的自启动：


vi /etc/init.d/mysql
#!/bin/sh
#chkconfig: 2345 80 90
#description:auto_run
export PATH=/usr/local/mysql/bin:$PATH:$HOME/bin
start() {
        echo "Starting mysql process "
        /usr/bin/sh /usr/local/mysql/bin/mysqlstart.sh
}


case "$1" in
  start)
        start
        ;;
 *)
esac


chmod +x /etc/init.d/mysql


vi /usr/local/mysql/bin/mysqlstart.sh
/usr/local/mysql/bin/mysqld_multi start 3306
/usr/local/mysql/bin/mysqld_multi start 3307


chmod +x /usr/local/mysql/bin/mysqlstart.sh 


chkconfig --level 235 on mysql


检查状态：
systemctl status mysql.service 查看是否报错