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

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/

注意此处会有初始密码：

[root@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 &

查看端口是否存在：

[root@mysql-N ~]# netstat -ntpl | grep mysql

tcp        0      0 :::3306                     :::*                        LISTEN      2281/mysqld

说明启动成功

[root@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

[root@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

[root@mysql-N ~]# tail -1 /etc/profile

PATH=$PATH:/usr/local/mysql/bin

[root@mysql-N ~]# source /etc/profile

[root@mysql-N ~]# mysql -V

mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

[root@mysql-N ~]# mysqldump -V

mysqldump  Ver 10.13 Distrib 5.7.13, for linux-glibc2.5 (x86_64)

可以这样登陆：

[root@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

*****************************************************************************************