mysql实例

当数据库服务器数量有限时，为了有效利用单台服务器资源，在服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。

单台服务器部署多个MySQL实例，这里以MySQL5.7为例，部署步骤如下：

1、 下载压缩包，解压安装

从官网：https://dev.mysql.com/downloads/mysql/下载合适版本和系统的mysql压缩包

解压到指定位置

[root@localhost src]# tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

[root@localhost src]# mv mysql-5.7.25-linux-glibc2.12-x86_64 mysql

2、 创建文件夹和文件

[root@localhost src]# cd mysql

[root@localhost mysql]#mkdir {data,logs}

[root@localhost mysql]#mkdir logs/error.log

[root@localhost mysql]#cd data

[root@localhost data]#mkdir {3306,3307}

[root@localhost mysql]#cd 3306

[root@localhost 3306]#mkdir {data,logs}

[root@localhost mysql]#touch logs/error.log

[root@localhost mysql]#cd ../3307

[root@localhost 3307]#mkdir {data,logs}

[root@localhost 3307]#touch logs/error.log

3、 创建组和用户

[root@localhost mysql]# groupadd mysql

[root@localhost mysql]# useradd -r -g mysql mysql

[root@localhost mysql]# chown -R mysql:mysql mysql

4、 把mysql加入环境变量和修改配置文件

[root@localhost mysql]#vim /etc/profile

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

[root@localhost mysql]#source /etc/profile

[root@localhost mysql]#vim /etc/my.cnf

[client]

default-character-set = utf8

[mysqld_multi]

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

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

log = /usr/local/src/mysql/logs/error.log

[mysqld3306]

mysqld = mysqld

mysqladmin = mysqladmin

port = 3306

user = mysql

basedir = /usr/local/src/mysql

datadir = /usr/local/src/mysql/data/3306/data

log-error = /usr/local/src/mysql/data/3306/logs/error.log

pid-file = /usr/local/src/mysql/data/3306/mysql.pid

socket = /usr/local/src/mysql/data/3306/mysql.sock

character-set-server = utf8

default_storage_engine = InnoDB

log-bin = mysql-bin

binlog-format = ROW

server_id = 1

relay-log = relay-log.log

relay-log-index = relay-log.index

relay_log_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = ON

max_allowed_packet = 20M

symbolic-links = 0

[mysqld3307]

mysqld = mysqld

mysqladmin = mysqladmin

port = 3307

user = mysql

basedir = /usr/local/src/mysql

datadir = /usr/local/src/mysql/data/3307/data

log-error = /usr/local/src/mysql/data/3307/logs/error.log

pid-file = /usr/local/src/mysql/data/3307/mysql.pid

socket = /usr/local/src/mysql/data/3307/mysql.sock

character-set-server = utf8

default_storage_engine = InnoDB

log-bin = mysql-bin

binlog-format = ROW

server_id = 1

relay-log = relay-log.log

relay-log-index = relay-log.index

relay_log_info_repository = TABLE

gtid_mode = on

enforce_gtid_consistency = ON

max_allowed_packet = 20M

symbolic-links = 0

[mysqldump]

quick

max_allowed_packet = 50M

5、 初始化数据库

添加好配置之后，就可以初始化3306和3307的数据库了。执行命令之后，会出现临时密码，这个密码需要记住，用来首次登录mysql数据库

[root@localhost mysql]# ./bin/mysqld --initialize --basedir=/usr/local/src/mysql/ --datadir=/usr/local/src/mysql/data/3306/data --user=mysql

[root@localhost mysql]# ./bin/mysqld --initialize --basedir=/usr/local/src/mysql/ --datadir=/usr/local/src/mysql/data/3307/data --user=mysql

6、 启动

mysqld_multi多实例管理的基本命令：

1）启动全部实例

# /usr/local/src/mysql/bin/mysqld_multi start

2）查看全部实例状态

# /usr/local/src/mysql/bin/mysqld_multi report

3）启动单个实例

#/usr/local/src/mysql/bin/mysqld_multi start 3306

# /usr/local/src/mysql/bin/mysqld_multi start 3307

4）停止单个实例

#/usr/local/src/mysql /bin/mysqld_multi stop 3306

#/usr/local/src/mysql /bin/mysqld_multi stop 3307

5）查看单个实例状态

# /usr/local/src/mysql/bin/mysqld_multi report 3306

# /usr/local/src/mysql/bin/mysqld_multi report 3307

使用命令，启动两个数据库实例

#/usr/local/src/mysql/bin/mysqld_multi start

执行命令没有报错，我们可以看一下日志有没有错误。

[root@localhost log]# cat /usr/local/src/mysql/logs/error.log

为了确保两个数据库实例已经启动，通过如下命令查看实例状态

# ./bin/mysqld_multi report

也可以通过命令查看相应端口是否有进程。

# ps -ef | grep 3306

# ps -ef | grep 3307

或者查看相应的实例中的日志信息

# cat /usr/local/src/mysql /data/3306/logs/error.log

# cat /usr/local/src/mysql /data/3307/logs/error.log

7、 登陆

启动成功后，就可以登录mysql进行修改密码等操作了。

登录时需要使用在配置文件中配置的sock地址

mysql -S ./data/3306/mysql.sock -uroot -pBBewl*7G=dk0

// 设置密码

mysql> set password=password(‘root‘);

// 设置允许远程登录

mysql> ｇｒａｎｔ ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ ;

mysql> flush privileges;

3307数据库同样跟3306操作一样：

mysql --socket=./data/3307/mysql.sock -uroot -p

// 设置密码

mysql> set password=password(‘root‘);

// 设置允许远程登录

mysql> ｇｒａｎｔ ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ ;

mysql> flush privileges;

以上，两个数据库实例已经配置并启动成功。可以通过客户端连接操作。也可以再进行mysql主从复制等配置。

最后时关闭数据库：

/usr/local/src/mysql/bin/mysqld_multi --user=root --password=root stop 3306

/usr/local/src/mysql/bin/mysqld_multi --user=root --password=root stop 3307

查看mysql是否关闭：

# ./bin/mysqld_multi report