1. 系统版本

centos6.5

2. mysql版本

5.7版本

3. 更新mysql源版本

3.1 centos6.5默认mysql版本是5.1，需要更新mysql源

[root@test ~]# rpm -Uvh https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

3.2 mysql安装

[root@test ~]# yum install mysql-community-server

3.3 mysql配置文件修改，文件默认路径/etc/my.cnf

[mysqld_multi]

#mysqld=mysqld_safe

#mysqladmin=/usr/bin/mysqladmin

log=/data/mysqldata/log/mysqld_multi.log

#[mysqld]

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

#user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links=0

[mysqld3306]

server-id=110

default-time_zone='+8:00'

innodb_buffer_pool_size=800M

basedir=/data/mysqldata/3306/mysql/

datadir=/data/mysqldata/3306/mysql

socket=/tmp/mysql_3306.sock1

#language=/var/lib/mysql/share/mysql/english

user=mysql

port=3306

innodb_file_per_table=1

[mysqld3307]

server-id=120

default-time_zone='+8:00'

innodb_buffer_pool_size=800M

basedir=/data/mysqldata/3307/mysql/

datadir=/data/mysqldata/3307/mysql

socket=/tmp/mysql_3307.sock2

#language=/var/lib/mysql/share/mysql/english

user=mysql

port=3307

innodb_file_per_table=1

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

4. 安装和管理多实例

[root@test ~]# mysqld --initialize --user=mysql --datadir=/data/mysqldata/3307/mysql

[root@test ~]# mysqld --initialize --user=mysql --datadir=/data/mysqldata/3306/mysql

PS: 需要保存mysql初始化生成的密码，后续操作需要用到

5 启动和停止多实例

启动单个实例

[root@test ~]# mysqld_multi start 3306

启动所有

[root@test ~]# mysqld_multi start

查看实例状态

[root@test ~]# mysqld_multi report

6. 多实例的连接

[root@9qu-idc ~]# mysql -u root -S /tmp/mysql_3306.sock1 -P 3306 -p

输入初始化生成密码,然后修改密码（如果不修改密码是无法执行任何mysql命令，除了help）

mysql> set PASSWORD=PASSWORD("123456");

创建mysql账号，并指定一个库的访问权限，并且局域网可以访问数据库

mysql> GRANT ALL PRIVILEGES ON test.* TO users@'10.10.10.%' IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION;

其中test是库名，users是账号名，xxxxxx表示密码