MySQL多实例配置

配置文件

[mysql@mysql-1 ~]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port           =3306
socket         =/var/lib/mysql3306/mysql.sock

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
user=test
password=test
log=/var/log/multi.log

[mysqld1]
server-id=1
port            = 3306
datadir         =/var/lib/mysql3306
socket          =/var/lib/mysql3306/mysql.sock
pid-file        = /tmp/mysqld1.pid
symbolic-links  =0
sql_mode        =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 


[mysqld2]
server-id=2
port            = 3307
datadir         =/var/lib/mysql3307
socket          =/var/lib/mysql3307/mysql.sock
pid-file        = /tmp/mysqld2.pid
symbolic-links  =0
sql_mode        =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

初始化数据库目录

mysql_install_db --datadir=/var/lib/mysql3306 --user=mysql
mysql_install_db --datadir=/var/lib/mysql3307 --user=mysql

启动、关闭

  全部启动、关闭：
  mysqld_multi --defaults-extra-file=/etc/my.cnf start     //启动
  mysqld_multi --defaults-extra-file=/etc/my.cnf start 1,2 //启动
  mysqld_multi --defaults-extra-file=/etc/my.cnf report 1,2 //查看实例状态
  mysqld_multi --defaults-extra-file=/etc/my.cnf stop    //关闭
  mysqld_multi --defaults-file=/etc/my.cnf stop 1,2 --user=test --password=test //关闭

单实例启动、关闭：

  mysqld_multi --defaults-extra-file=/etc/my.cnf start 1 //启动
  mysqld_multi --defaults-extra-file=/etc/my.cnf stop  1  //关闭
  mysqladmin -utest -ptest shutdown -h127.0.0.1 -P3306    //关闭

登陆

# mysql -uroot -p111111 -h127.0.0.1 -P3306
# mysql -uroot -p111111 -h127.0.0.1 -P3307

新建数据库用户权限设置

创建root密码，删除空密码用户:
update mysql.user set password=password('111111') where user = 'root';
delete from mysql.user where user='' or Password='';
创建shutdown用户:
drop user test@localhost;
grant shutdown on *.* to test@localhost identified by 'test';
select user,Password,host from mysql.user;
flush privileges;
show grants for test@localhost;
exit