mysql5.7多实例logbin_mysql5.7二进制包进行多实例安装

一、需求

在一台服务器上安装mysql5.7,并且部署两个实例：3306用于本机主库，3307用于其他MYSQL服务器的从库

二、下载mysql二进制包

[root@push-5-222 src]# mkdir /usr/local/src/mysql-5.7.26-linux-glibc2.12[root@push-5-222 src]# cd /usr/local/src/mysql-5.7.26-linux-glibc2.12[root@push-5-222 mysql-5.7.26-linux-glibc2.12]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

三、解压、添加环境变量

[root@push-5-222 mysql-5.7.26-linux-glibc2.12]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gzmv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.26

[root@push-5-222 /]# vim /etc/profile

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

root@push-5-222 /]# source /etc/profile

四、配置3306和3307各自对应的目录

数据目录: /home/mysql-5.7.26/data/3306 /home/mysql-5.7.26/data/330X

日志目录:/home/mysql-5.7.26/log/3306 /home/mysql-5.7.26/log/330X

socket：/var/lib/mysql/330X/mysql.sock

pid-file：/var/run/mysqld/330X/mysqld.pid

log-error：/home/mysql-5.7.26/log/330X/mysqld.log

[root@push-5-222 home]# mkdir -p /home/mysql-5.7.26/data/3306[root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/data/3307[root@push-5-222 mysql-5.7.26]# mkdir -p /var/lib/mysql/3306[root@push-5-222 mysql-5.7.26]# mkdir -p /var/lib/mysql/3307[root@push-5-222 mysql-5.7.26]# mkdir -p /var/run/mysqld/3306[root@push-5-222 mysql-5.7.26]# mkdir -p /var/run/mysqld/3307[root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/log/3306[root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/log/3307

五、将对应的目录授权给mysql用户

[root@push-5-222 mysql-5.7.26]# chown -R mysql:mysql /home/mysql-5.7.26/[root@push-5-222 /]# chown -R mysql:mysql /var/lib/mysql

[root@push-5-222 /]# chown -R mysql:mysql /var/run/mysqld

六、编辑my-330x配置文件

[root@push-5-222 /]# mkdir /etc/mysql

[root@push-5-222 /]# chown -R mysql:mysql /etc/mysql/[root@push-5-222 /]# vim /etc/mysql/my-3306.cnf

[mysqld]

#innodb_buffer_pool_size=128M

# join_buffer_size=128M

# sort_buffer_size=2M

# read_rnd_buffer_size=2M

user=mysql

port=3306datadir=/home/mysql-5.7.26/data/3306socket=/var/lib/mysql/3306/mysql.sock

server_id=2223306#log-bin=master-221#binlog_format=row

#skip-grant-tables

symbolic-links=0pid-file=/var/run/mysqld/3306/mysqld.pid

log-error=/home/mysql-5.7.26/log/3306/mysqld.log

[mysqld_safe]

log-error=/home/mysql-5.7.26/log/3306/mysqld.log

[root@push-5-222 /]# vim /etc/mysql/my-3307.cnf

[mysqld]

# innodb_buffer_pool_size=128M

# join_buffer_size=128M

# sort_buffer_size=2M

# read_rnd_buffer_size=2M

user=mysql

port=3307datadir=/home/mysql-5.7.26/data/3307socket=/var/lib/mysql/3307/mysql.sock

server_id=2223307symbolic-links=0pid-file=/var/run/mysqld/3307/mysqld.pid

log-error=/home/mysql-5.7.26/log/3307/mysqld.log

[mysqld_safe]

log-error=/home/mysql-5.7.26/log/3307/mysqld.log

七、初始化3306和3307数据库

[root@push-5-222 /]# mysqld --defaults-file=/etc/mysql/my-3306.cnf --initialize --basedir=/usr/local/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/3306[root@push-5-222 /]#

没有报错，查看数据目录是否有系统数据文件

[root@push-5-222 /]# ll /home/mysql-5.7.26/data/3306总用量110628

-rw-r----- 1 mysql mysql 56 5月 27 08:46auto.cnf-rw-r----- 1 mysql mysql 419 5月 27 08:46ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 5月 27 08:46ibdata1-rw-r----- 1 mysql mysql 50331648 5月 27 08:46ib_logfile0-rw-r----- 1 mysql mysql 50331648 5月 27 08:46ib_logfile1

drwxr-x--- 2 mysql mysql 4096 5月 27 08:46mysql

drwxr-x--- 2 mysql mysql 8192 5月 27 08:46performance_schema

drwxr-x--- 2 mysql mysql 8192 5月 27 08:46 sys

再查看日志文件查找生成的随机root密码，用于第一次登陆数据库使用

[root@push-5-222 /]# cat /home/mysql-5.7.26/log/3306/mysqld.log |greppassword2019-05-27T00:46:31.406309Z 1 [Note] A temporary password is generated for root@localhost: -QOhO4-KccHl

此时，数据库初始化完成了，3307也是同样的操作

[root@push-5-222 /]# mysqld --defaults-file=/etc/mysql/my-3307.cnf --initialize --basedir=/usr/local/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/3307[root@push-5-222 /]# ll /home/mysql-5.7.26/data/3307总用量110628

-rw-r----- 1 mysql mysql 56 5月 27 08:49auto.cnf-rw-r----- 1 mysql mysql 419 5月 27 08:49ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 5月 27 08:49ibdata1-rw-r----- 1 mysql mysql 50331648 5月 27 08:49ib_logfile0-rw-r----- 1 mysql mysql 50331648 5月 27 08:49ib_logfile1

drwxr-x--- 2 mysql mysql 4096 5月 27 08:49mysql

drwxr-x--- 2 mysql mysql 8192 5月 27 08:49performance_schema

drwxr-x--- 2 mysql mysql 8192 5月 27 08:49sys

[root@push-5-222 /]# cat /home/mysql-5.7.26/log/3307/mysqld.log |greppassword2019-05-27T00:49:44.147398Z 1 [Note] A temporary password is generated for root@localhost:

八、分别启动3306和3307数据库

[root@push-5-222 /]# mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf&[1] 15879[root@push-5-222 /]# 2019-05-27T00:55:47.964704Z mysqld_safe Logging to '/home/mysql-5.7.26/log/3306/mysqld.log'.2019-05-27T00:55:48.031905Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data/3306

查看是否有mysqld进程

[root@push-5-222 /]# ps -ef|grepmysqld

root15879 14538 0 08:55 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf

mysql16053 15879 0 08:55 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3306.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3306 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3306/mysqld.log --pid-file=/var/run/mysqld/3306/mysqld.pid --socket=/var/lib/mysql/3306/mysql.sock --port=3306root16120 14538 0 08:56 pts/0 00:00:00 grep --color=auto mysqld

[root@push-5-222 /]# mysqld_safe --defaults-file=/etc/mysql/my-3307.cnf&[2] 16163[root@push-5-222 /]# 2019-05-27T00:57:23.035649Z mysqld_safe Logging to '/home/mysql-5.7.26/log/3307/mysqld.log'.2019-05-27T00:57:23.095446Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data/3307

^C

[root@push-5-222 /]# ps -ef|grepmysqld

root15879 14538 0 08:55 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf

mysql16053 15879 0 08:55 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3306.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3306 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3306/mysqld.log --pid-file=/var/run/mysqld/3306/mysqld.pid --socket=/var/lib/mysql/3306/mysql.sock --port=3306root16163 14538 0 08:57 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3307.cnf

mysql16337 16163 3 08:57 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3307.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3307 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3307/mysqld.log --pid-file=/var/run/mysqld/3307/mysqld.pid --socket=/var/lib/mysql/3307/mysql.sock --port=3307root16371 14538 0 08:57 pts/0 00:00:00 grep --color=auto mysqld

此时可以看到3306和3307两个mysqld_safe进程都启动了

八、分别登录3306和3307数据库修改密码

[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.26Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql>

mysql> alter user 'root'@'localhost' identified by 'xxxxxxxxxxxx';

Query OK,0 rows affected (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>

注意，这里一定要使用 -S 方式来连接mysql，修改3307数据库密码

[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3307/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.26Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql> alter user 'root'@'localhost' identified by 'xxxxxxxxx';

Query OK,0 rows affected (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>

退出，并重新用新密码登录。

九、设置远程访问权限

[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 5Server version:5.7.26MySQL Community Server (GPL)

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql>use mysql;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> update user set host='%' where user='root';

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>

，然后通过其他机器客户端连接3306成功。

3307也是同样操作

[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3307/mysql.sock

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 5Server version:5.7.26MySQL Community Server (GPL)

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql>use mysql;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> update user set host='%' where user='root';

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0mysql>flush privileges;

Query OK,0 rows affected (0.00sec)

mysql>

此时，双实例安装成功。

十、多实例的启动和停止

停止3306实例:  mysqladmin -uroot -p -S /var/lib/mysql/3307/mysql.sock shutdown

启动3306实例: mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf &

连接3306实例:mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock