Mysql5.7多实例配置

Mysql采用离线的二进制包安装方式,安装在/usr/local/mysql

两个实例配置

创建目录

> mkdir -p /usr/local/mysql/data/{3306,3307}

3306配置

> vim /etc/my3306.cnf
[client]

socket=/usr/local/mysql/data/3306/mysql.sock
[mysqld]

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data/3306
socket=/usr/local/mysql/data/3306/mysql.sock

port=3306
binlog-do-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys

expire_logs_days=7
log-bin=mysql3306-bin

server-id=110
log-error=/usr/local/mysql/data/3306/error.log
[mysqld_safe]
log-error=/usr/local/mysql/data/3306/mysql.log
pid-file=/usr/local/mysql/data/3306/mysql.pid

3307配置

> vim /etc/my3307.cnf
[client]

socket=/usr/local/mysql/data/3307/mysql.sock
[mysqld]

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data/3307
socket=/usr/local/mysql/data/3307/mysql.sock

port=3307


log-bin=mysql-3307bin
server-id=111
read-only=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-error=/usr/local/mysql/data/3307/error.log
[mysqld_safe]
log-error=/usr/local/mysql/data/3307/mysql.log
pid-file=/usr/local/mysql/data/3307/mysql.pid

MySql数据初始化

3306

> /usr/local/mysql/bin/mysqld --user=mysql 
--basedir=/usr/local/mysql 
--datadir=/usr/local/mysql/data/3306 --initialize

3307

> /usr/local/mysql/bin/mysqld --user=mysql 
--basedir=/usr/local/mysql 
--datadir=/usr/local/mysql/data/3307 --initialize

注意记录初始密码

Mysql服务配置

配置3306服务

> vim /etc/systemd/system/mysql3306.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
LimitNOFILE = 10000
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf 

配置3307服务

> vim /etc/systemd/system/mysql3307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
LimitNOFILE = 10000
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf 

启动两个Mysql实例

> systemctl daemon-reload
> systemctl start mysql3306
> systemctl start mysql3307
//查看mysql状态
> systemctl status mysql3306
> systemctl status mysql3307

访问两个实例

访问3306实例

> mysql -uroot -p -S /usr/local/mysql/data/3306/mysql.sock

输入刚才记录的密码

访问3307实例

> mysql -uroot -p -S /usr/local/mysql/data/3307/mysql.sock

输入刚才记录的密码

修改密码

> alter user 'root'@'localhost' identified by '123456';
> flush privileges;

mysql server的连接有两种方式：

• 1.tcp/ip的连接，即：mysql -uroot -p -h127.0.0.1 -P3306
• 2.本地socket连接，即：mysql -S /socket文件的路径。（只能在本机使用）

历史文章