mysql一台机器多个数据库_同一台阿里云机器上安装启动连接多个MySQL数据库

how to run and connect to multiple MySQL on the same machine.

以RPM方式安装MySQL

以下命令来自于icoding的老师：

1、下载rpm包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

# 如果提示需要账号密码，就用这个方式下载

# wget [email protected] --http-passwd=yourpassword https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

2、查看系统是否自带mariadb

rpm -qa | grep mariadb

3、将查出的mariadb进行卸载

rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

4、把刚刚下载的mysql tar解压

tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

5、在解压目录安装如下4个mysql核心包

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

6、安装到server时缺少依赖报错

error: Failed dependencies:

libaio.so.1()(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

7、安装缺少的依赖

yum -y install libaio

8、再次安装server

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

9、启动mysql服务

service mysqld start

10、查看v5.7版本的默认登录密码

grep password /var/log/mysqld.log

11、登录到mysql命令行，修改默认密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

12、会告诉你密码不符合规范，可以修改校验等级和长度后再次执行上面的语句

set global validate_password_policy=LOW;

set global validate_password_length=6;

13、账户授权

use mysql;

select host,user from user;

# 所有ip都可以访问数据库

grant all privileges on *.* to [user name]@'%' identified by '123456';

# 只有内网网段ip才可访问，并授权账号可以授权其他人

# grant all privileges on *.* to [user name]@'192.168.%' identified by '123456' with grant option;

flush privileges;

配置多个instances

MySQL 官网 有一篇介绍 https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-multiple-mysql-instances，可以copy里面的配置文件过来，将里面的文件目录改成自己机器上的目录，并且确保mysql账户有读写权限(chown -R mysql:mysql [相应目录或者文件])。为了方便连接进入mysql, 我在各个instance的最后都先加上了(skip-grant-tables)。

[[email protected]]

server-id=212

datadir=/var/lib/mysql-replica01

socket=/var/lib/mysql-replica01/mysql.sock

port=3307

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

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

slow_query_log=ON

slow_query_log_file=/usr/local/slowlog-replica01/slow.log

slow_launch_time=4

long_query_time=3

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

log_bin=/usr/local/binlog/mysql-bin-replica01

binlog_format=ROW

# binlog日志切割的大小

max_binlog_size=500m

# binlog过期清理时间

expire_logs_days=3

skip-grant-tables

[[email protected]]

server-id=213

datadir=/var/lib/mysql-replica02

socket=/var/lib/mysql-replica02/mysql.sock

port=3308

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

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

slow_query_log=ON

slow_query_log_file=/usr/local/slowlog-replica02/slow.log

slow_launch_time=4

long_query_time=3

log_bin=/usr/local/binlog/mysql-bin-replica02

binlog_format=ROW

# binlog日志切割的大小

max_binlog_size=500m

# binlog过期清理时间

expire_logs_days=3

skip-grant-tables

启动各个instance

service [email protected] restart

mysql -S /var/lib/mysql-replica01/mysql.sock

service [email protected] restart

mysql -S /var/lib/mysql-replica02/mysql.sock

连接进入之后，可以在修改密码：

mysql> update user set password=password("新密码") where user="root";

mysql> flush privileges;

mysql> quit

注意：如果在执行该步骤的时候出现ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 错误。则执行下 flush privileges 命令，再执行该命令即可。

root账户就已经重置成新的密码了。

最后编辑my.cnf,去掉一开始添加的内容(skip-grant-tables)，然后重启MySQL即可。