mysql5.7.13二进制包安装多实例

4532

mysql最新版本：5.7.13
操作系统：centos6.5
#######################################################
首先查看系统有无已安装mysql，
rpm -qa | grep -i mysql
如果有，应全部卸载，注意删除rm -rf /etc/my.cnf
规划：
1、创建mysql用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
2、目录规划(2个实例）
/data/3306/data
/data/3307/data
3、创建目录
mkdir -p /data/3306/data
mkdir -p /data/3307/data
chown -R mysql:mysql  /data/
chown -R mysql:mysql /data/3306/data
chown -R mysql:mysql /data/3307/data
4、官网下载二进制包mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
5、解压
tar xf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mysql-5.7.13-linux-glibc2.5-x86_64 /usr/local/mysql
cd mysql
chown -R root:mysql ./*
6、初始化3306实例
首先安装：yum -y install libaio
初始化实例（与5.7.6之前版本不同）：
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/3306/data/
注意此处会有初始密码：
[iyunv@mysql-N mysql]# bin/mysqld --initialize --user=mysql --datadir=/data/3306/data/
2016-07-27T04:42:31.622680Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-07-27T04:42:34.326881Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-07-27T04:42:34.747686Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-07-27T04:42:34.864996Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 89921569-53b4-11e6-82ee-000c292d3cc3.
2016-07-27T04:42:34.869308Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-07-27T04:42:34.874353Z 1 [Note] A temporary password is generated for root@localhost: 2PewpKEBhT,f
6-1、配置文件
cp -rf /usr/local/mysql/support-files/my-default.cnf /data/3306/my.cnf
vim /data/3306/my.cnf
基本配置如下：
[mysqld]
datadir = /data/3306/data
socket = /data/3306/mysql.sock
port = 3306
7、启动测试
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
查看端口是否存在：
[iyunv@mysql-N ~]# netstat -ntpl | grep mysql
tcp        0      0 :::3306                     :::*                        LISTEN      2281/mysqld
说明启动成功
[iyunv@mysql-N ~]# ll /data/3306/
total 16
drwxr-xr-x 5 mysql mysql 4096 Jul 27 22:35 data     ---数据目录
-rw-r--r-- 1 mysql mysql 1195 Jul 27 12:44 my.cnf    ---配置文件
-rwxr-xr-x 1 root  root   992 Jul 27 22:34 mysql         ---自定义启动脚本（见文章最后）
srwxrwxrwx 1 mysql mysql    0 Jul 27 22:35 mysql.sock   --sock
-rw------- 1 mysql mysql    5 Jul 27 22:35 mysql.sock.lock
[iyunv@mysql-N ~]# ll /data/3306/data/mysql-N.*
-rw-r----- 1 mysql mysql 29927 Jul 28 09:10 /data/3306/data/mysql-N.err  --- 日志
-rw-r----- 1 mysql mysql     5 Jul 27 22:35 /data/3306/data/mysql-N.pid     --- pid
8、登陆mysql3306实例
/usr/local/mysql/bin/mysql -S /data/3306/mysql.sock -uroot -p2PewpKEBhT,f
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
以下mysql使用，和其他安装方法类似
配置永久生效路径，即不需要输入绝对路径使用mysql
[iyunv@mysql-N ~]# tail -1 /etc/profile
PATH=$PATH:/usr/local/mysql/bin
[iyunv@mysql-N ~]# source /etc/profile
[iyunv@mysql-N ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper
[iyunv@mysql-N ~]# mysqldump -V
mysqldump  Ver 10.13 Distrib 5.7.13, for linux-glibc2.5 (x86_64)
可以这样登陆：
[iyunv@mysql-N ~]# mysql -uroot -p -S /data/3306/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
9、创建另一个实例3307
chown -R mysql:mysql  /data
(1)、直接初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/3307/data/
(2)、配置文件
cp -rf /usr/local/mysql/support-files/my-default.cnf /data/3307/my.cnf
vim /data/3307/my.cnf
基本配置如下：
[mysqld]
datadir = /data/3307/data
socket = /data/3307/mysql.sock
port = 3307
(3)、启动
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
(4)、查看端口是否启动：
netstat -tunlp
(5)、登录测试
/usr/local/mysql/bin/mysql -S /data/3306/mysql.sock -uroot -p'初始密码'
更改初始密码：
mysql>alter user 'root'@'localhost' identified by 'QWer@1234';
更改登陆授权：
mysql>update mysql.user set  host='%' where user='root';
mysql>flush privileges;
查询用户信息:
mysql>select user,host,authentication_string from mysql.user;
如需要再增加实例，依次类推
注意再创建目录时的再授权，chown -R mysql:mysql /data
以下为一个简易的启动、关闭、重启脚本，可参考（即/data/3306/mysql）
*****************************************************************************************
启动、关闭、重启脚本：
#!/bin/bash
port=3306
cmdpath="/usr/local/mysql/bin"
mysql_sock="/data/$port/mysql.sock"
user="root"
passwd="QWer@1234"
start_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "Starting MySQL ...\n"
$cmdpath/mysqld_safe --defaults-file=/data/$port/my.cnf 2>&1 >/dev/null &
else
printf "MySQL is running...\n"
fi
exit
}
stop_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Stopping MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -u$user -p$passwd shutdown 2>&1 >/dev/null &
fi
}
restart_mysql() {
if [[ ! -e "$mysql_sock" ]];then
printf "MySQL is stopped ..\n"
else
printf "Restarting MySQL ..\n"
$cmdpath/mysqladmin -S $mysql_sock -u$user -p$passwd shutdown 2>&1 >/dev/null &
/bin/sh $cmdpath/mysqld_safe --defaults-file=/data/$port/my.cnf 2>&1 >/dev/null &
fi
}
case $1 in
start)
start_mysql;;
stop)
stop_mysql;;
restart)
restart_mysql;;
*)
printf "Usage: $0 {start|stop|restart}\n"
esac
*****************************************************************************************