Centos6.x二进制安装mysql5.6.38多实例

参考文档：http://blog.csdn.net/fzdba/article/details/40111939

一丶什么是多实例

一台服务器运行多个mysql进程，mysql进程之间是逻辑上是独立的，但是公用一台服务器上的资源；

二丶多实例作用与问题

1）有效利用服务器资源

2）节约服务器资源

3）资源互相抢占问题

三丶多实例的应用场景

1）资金紧张型公司的选择

2）并发访问不是特别大的业务

3）门户网站

四丶mysql多实例常见配置方案

1）多个配置文件多个启动程序多个数据文件；

2）单一配置文件部署方案：使用的是mysql_multi方式；

五丶下载mysql，创建用户，解压，关闭selinux

mkdir /tools

cd /tools

wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz

useradd mysql -s /sbin/nologin -M

rm -rf /etc/my.cnf

tar xf mysql-5.6.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local

ln -sv /usr/local/mysql-5.6.38-linux-glibc2.12-x86_64/ /usr/local/mysql

六丶创建相关目录

cd /

mkdir /data

cd /data

mkdir mysql

cd mysql

mkdir {mysql_3306,mysql_3307}

cd /data/mysql/mysql_3306

mkdir {data,log,tmp}

cd /data/mysql/mysql_3307

mkdir {data,log,tmp}

七丶更改目录权限

chown -R mysql:mysql /data/mysql/

chown -R mysql:mysql /usr/local/mysql/

八丶添加环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

source /etc/profile

九丶创建/etc/my.cnf 文件

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld_multi]

mysqld = /usr/local/mysql /bin/mysqld_safe

mysqladmin = /usr/local/mysql /bin/mysqladmin

log = /data/mysql/mysqld_multi.log

[mysqld]

user=mysql

basedir = /usr/local/mysql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3306/data

port=3306

server_id=3306

socket=/tmp/mysql_3306.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3306/log/slow.log

log-error = /data/mysql/mysql_3306/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3306/log/mysql3306_bin

character-set-server=utf8

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3307/data

port=3307

server_id=3307

socket=/tmp/mysql_3307.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3307/log/slow.log

log-error = /data/mysql/mysql_3307/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3307/log/mysql3307_bin

character-set-server=utf8

十丶初始化数据库

初始化3306数据库

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf

初始化3307数据库

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf

说明：

--basedir：mysql的安装目录

--datadir：数据库的数据文件目录

--defaults-file：mysql配置文件目录

注意：看到两个OK表示初始化数据库成功

十一丶使用mysqld_multi进行多实例管理

启动全部实例：/usr/local/mysql/bin/mysqld_multi start

查看全部实例状态：/usr/local/mysql/bin/mysqld_multi report

启动单个实例：/usr/local/mysql/bin/mysqld_multi start 3306

停止单个实例：/usr/local/mysql/bin/mysqld_multi stop 3306

查看单个实例状态：/usr/local/mysql/bin/mysqld_multi report 3306

十二丶修改密码

由于mysql的root用户初始密码是空，所以需要登录mysql进行修改密码，下面以3306为例：

mysql -S /tmp/mysql_3306.sock

set password for root@'localhost'=password('redhat');

flush privileges;

十三丶创建远程用户

grant all privileges on *.* to wangfang@'192.168.1.%' identified by 'redhat';

flush privileges;

十四丶安装报错

1）scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory

解决方案：

yum remove perl perl-devel -y

yum –y install perl perl-devel

2）db2start: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No