MySQL 5.7.15 多实例安装（二进制安装模式）

单实例安装请参考 MySQL 5.7.15 安装（二进制安装模式）

当前安装两个实例，更多实例参考一样。

# 操作系统
CentOS release 6.5 (Linux version 2.6.32-431.el6.x86_64)

# 数据库 mysql 5.7.17 下载 (623.7MB) http://dev.mysql.com/downloads/mysql/
mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 
>> linux generic 
>> Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive

 

 官网安装参考 http://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

 

安装前准备：

# Linux查看系统版本
shell> uname -a
shell> lsb_release -a
shell> cat /proc/version
shell> cat /etc/redhat-release
shell> cat /etc/issue


# 安装相关包
shell> yum -y install gcc glibc libaio libstdc++ libstdc ncurses-libs  


# 如有则卸载自带mysql （手动删除 /etc/my.cnf或者/etc/mysql）
rpm -qa | grep mysql
rpm -e mysql	#普通删除模式
rpm -e --nodeps mysql	#强力删除模式

# 基本配置

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> ll /root/mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz
shell> cd /root
shell> tar -zxvf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz
shell> mv /root/mysql-5.7.15-linux-glibc2.5-x86_64 /usr/local/mysql
shell> cd /usr/local/mysql
shell> mkdir -p /var/run/mysqld
shell> mkdir -p /usr/local/mysql/data3306 /usr/local/mysql/binlog3306
shell> mkdir -p /usr/local/mysql/data3307 /usr/local/mysql/binlog3307
shell> chmod 750 /var/run/mysqld /usr/local/mysql/data* /usr/local/mysql/binlog*
shell> chown -R mysql:mysql /usr/local/mysql/ /var/run/mysqld

# 配置参数文件

shell> vi /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
#user       = root
#pass       = rootpwd
#password   = rootpwd

[mysqld3306]
port = 3306
server_id = 3306
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data3306
log-bin=/usr/local/mysql/binlog3306/mysql-bin
socket =/tmp/mysql3306.sock
log-error =/var/log/mysqld3306.log
pid-file =/var/run/mysqld/mysqld3306.pid
binlog_format = row
expire_logs_days = 15
binlog-ignore-db=mysql,sys,performance_schema,information_schema

sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log = /usr/local/mysql/relaylog3306
log_slave_updates=1
#auto-increment-offset=1
#auto-increment-increment=2
sql_mode=STRICT_TRANS_TABLES

# slave
[mysqld3307]
port = 3307
server_id = 3307
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data3307
log-bin=/usr/local/mysql/binlog3307/mysql-bin
socket =/tmp/mysql3307.sock
log-error =/var/log/mysqld3307.log
pid-file =/var/run/mysqld/mysqld3307.pid
binlog_format = row
expire_logs_days = 15
binlog-ignore-db=mysql,sys,performance_schema,information_schema

sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log = /usr/local/mysql/relaylog3307
log_slave_updates=1
#auto-increment-offset=2
#auto-increment-increment=2
sql_mode=STRICT_TRANS_TABLES

# 添加环境变量

shell> echo "PATH=$PATH:/usr/local/mysql/bin  " >> /etc/profile
shell> source /etc/profile

# 安装,完成后记住root密码

shell> bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3306 --explicit_defaults_for_timestamp
shell> bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data3307 --explicit_defaults_for_timestamp

# 启动实例服务

shell> mysqld_multi report
shell> mysqld_multi start 3306,3307
#或者
shell> mysqld_multi start /tmp/mysql3306.sock,/tmp/mysql3307.sock

shell> netstat -ntlp | grep mysql
shell> ll /tmp/mysql*.sock  

# 访问管理

# 访问数据库(进入后要求更改root密码)
shell> mysql -u root -p -S /tmp/mysql3306.sock
Enter password:

shell> mysql -u root -p -S /tmp/mysql3307.sock
Enter password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootpwd';
mysql> select Host,User from mysql.user;


# 超级管理员
mysql> create user root@'192.168.1.%' IDENTIFIED by 'rootpwd';
mysql> grant all privileges on *.* to root@'192.168.1.%';
mysql> flush privileges;


# 可用端口登录了(此时登录账户为: root@'192.168.1.%')
shell> mysql -h 192.168.1.110 -u root -p -P3306
shell> mysql -h 192.168.1.110 -u root -p -P3306

 

# 关闭某个实例

shell> mysqladmin -uroot -prootpwd -S /tmp/mysql3306.sock shutdown

 

# 添加自启动

shell> cat /etc/rc.local
shell> echo "/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306,3307" >> /etc/rc.local

 

# 添加防火墙规则 (贴在 icmp-host-prohibited 的上面)

shell> vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3307 -j ACCEPT

shell> service iptables restart


/*防火墙基本命令:

0）查看当前规则
iptables -L -n  --line-number
service iptables status

1） 临时生效，重启后复原
保存:  service iptables save
开启： service iptables start
关闭： service iptables stop
重启:  service iptables restart

2） 永久性生效，重启后不会复原
开启： chkconfig iptables on
关闭： chkconfig iptables off
*/

 

使用 mysqld_multi stop 3306 停止服务的时候则有问题，这是一个bug，现在有两种方法解决：

【方法一】
mysq 中创建一个用于服务停止的账号
mysql> grant shutdown on *.* to multi@localhost identified by '123456';


将账号添加到组 mysqld_multi，
# vim /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=multi
pass=123456


【方法一】
# vim /usr/local/mysql/bin/mysqld_multi
# 216 行,增加 “-s”

my $com= join ' ', 'my_print_defaults', @defaults_options, $group;
替换为： 
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;


# 停止服务时指定密码
# mysqld_multi stop 3306 --password=rootpwd

另快速简单例子：

#解压配置
tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.23-linux-glibc2.12-x86_64 /data/mysql/5.7

mkdir -p /data/mysql
groupadd mysql && useradd -m -d /data/mysql/home -g mysql mysql
chown -R mysql:mysql /data/mysql
su - mysql
echo 'export PATH=$PATH:/data/mysql/5.7/bin' >> ~/.bashrc
source ~/.bashrc


#配置文件
mkdir -p /data/{mysql3306,mysql3307}/{data,tmp,binlog}

for port in {3306..3307}
do
cat << EOF > /data/mysql${port}/my.cnf
[client]
port = ${port}
socket = /data/mysql${port}/tmp/mysql.sock
[mysqld]
port = ${port}
server_id = 81${port}
basedir =/data/mysql${port}
datadir =/data/mysql${port}/data
log-bin=/data/mysql${port}/binlog/mysql-bin
log-error =/data/mysql${port}/mysqld.log
pid-file =/data/mysql${port}/mysqld.pid
socket =/data/mysql${port}/tmp/mysql.sock
plugin-dir=/data/mysql/5.7/lib/plugin
innodb_buffer_pool_size = 512M
autocommit = 1
character_set_server=utf8
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
lower_case_table_names=1
explicit_defaults_for_timestamp = 1
binlog_format = row 
skip-external-locking
[mysqld_safe]
log-error=/data/mysql${port}/mysqld.log
pid-file=/data/mysql${port}/mysqld.pid
EOF
done

#初始化数据
mysqld --defaults-file=/data/mysql3306/my.cnf --initialize --user=mysql --explicit_defaults_for_timestamp
mysqld --defaults-file=/data/mysql3307/my.cnf --initialize --user=mysql --explicit_defaults_for_timestamp

#启动服务
mysqld_safe --defaults-file=/data/mysql3306/my.cnf 2>&1 > /dev/null &
mysqld_safe --defaults-file=/data/mysql3307/my.cnf 2>&1 > /dev/null &

#连接实例更改root密码
mysql -P 3306 -u root -p -S /data/mysql3306/tmp/mysql.sock
mysql -P 3307 -u root -p -S /data/mysql3307/tmp/mysql.sock

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
select Host,User from mysql.user;

create user root@'192.168.1.%' IDENTIFIED by 'mysql';
grant all privileges on *.* to root@'192.168.1.%';
flush privileges;

 

参考：

MySQL多实例配置

mysql多实例的安装和管理(一台服务器上运行两个mysql实例)