MySQL 5.7.4 同机双实例简单配置

Question

安装 mysql 5.7.4

rpm -qa|grep mysql|xargs -i rpm -e --nodeps {}
rpm -ivh MySQL-devel-5.7.4_m14-1.el6.x86_64.rpm 
rpm -ivh MySQL-embedded-5.7.4_m14-1.el6.x86_64.rpm 
rpm -vih MySQL-server-5.7.4_m14-1.el6.x86_64.rpm 
rpm -ivh MySQL-client-5.7.4_m14-1.el6.x86_64.rpm 

编辑 /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
 
[mysqld1]
port            = 3306
socket          = /tmp/mysql.sock
datadir         =/var/lib/mysql
pid-file        =/var/lib/mysql/mysql.pid
user            =mysql
lower_case_table_names=1
 
log-bin         =master-bin
log-bin-index   =master-bin.index
server-id       =1

[mysqld2]
port            = 3307
socket          =/tmp/mysql2.sock
datadir         =/var/lib/mysql2
pid-file        =/var/lib/mysql2/mysql.pid
user            =mysql
lower_case_table_names=1

启动 mysql1 和 mysql2

service mysql start
mysqld_multi  start 2    # 注意，这里的2是指 /etc/my.cnf 中配置的[mysqld2],如果配置为[mysqld3307],则相应启动实例命令为：mysqld_multi  start 3307
netstat -tlunp|grep mysql

初始化 mysql 1

mysql -uroot -p	  # 密码在 /root/.mysql_secret第一个
SET PASSWORD = PASSWORD('xxx');  # 设置root密码
use mysql;
update user set password=password('xxx') where user = 'root';  # 更新root密码
update user set host='%' where user='root';    # 设置mysql被允许访问的主机
flush privileges;
quit
#重启mysql，再登录
service mysql restart
mysql -uroot -p
use mysql
select host from user where user='root'; # 已经有了host为%,不用再更改，如果没有就更改一下
--update user set password_expired='N' where user='root';
--flush privileges;
quit
#重启
service mysql restart

初始化 mysql 2

mysql --user=root --port=3307 --socket=/tmp/mysql2.sock -p	 # 密码在 /root/.mysql_secret第一个
SET PASSWORD = PASSWORD('xxx'); 
use mysql;
update user set password=password('xxx') where user = 'root';
update user set host='%' where user='root';
flush privileges;
quit
#重启mysql，再登录
service mysql restart
mysql -uroot -p
use mysql
select host from user where user='root'; # 已经有了host为%,不用再更改，如果没有就更改一下
--update user set password_expired='N' where user='root';
--flush privileges;
quit
# 重启mysql2
mysqld_multi stop 2	# 不行的话直接kill进程
mysqld_multi start 2

同机双实例配置完毕！