linux服务器安装Mysql5.7多实例并实现主从分离

MySQL5.7多实例

mysql官网下载，我的是mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz，放在/home目录下的。

解压

tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mv /home/mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql

创建mysql实例用户和文件

useradd mysql -r -s /sbin/nologin
mkdir -p /usr/local/mysql/mysql_files

如果创建不成功，将mysql删掉再useradd

sudo vipw
sudo vipw -s

创建多实例数据目录

根据要创建的实例数创建，我只建了两个，mysql_data1和mysql_data2

mkdir -p /data/mysql_data{1..2}

修改mysql目录的属组及用户

chown root.mysql -R /usr/local/mysql

修改专用目录的属主及属组

chown mysql.mysql -R /usr/local/mysql/mysql_files  /data/mysql_data{1..2}

配置Mysql的配置文件

vi /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /tmp/mysql_multi.log


[mysqld1]
datadir = /data/mysql_data1      #设置数据目录
socket = /tmp/mysql.sock1        #设置sock文件存放路径
port = 3306                      #设置监听开放端口　
user = mysql                     #设置运行用户
performance_schema = off          #关闭监控
innodb_buffer_pool_size = 32M     #设置innodb 缓存大小
bind_address = 0.0.0.0            #设置监听IP地址
skip-name-resolve = 0             #关闭DNS反向解析

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

初始化各个实例

记得保存每个实例的密码

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2

复制多实例脚本到服务管理目录下

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/rc.d/init.d/mysqld_multi

给予脚本可执行权限

chmod +x /etc/rc.d/init.d/mysqld_multi

加入service服务管理

chkconfig --add mysqld_multi

添加环境变量

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH

使修改的配置文件生效

source /etc/profile

启动实例

mysqld_multi start

查看实例的监听端口

ss -tulpn|grep mysqld

连接实例

实例1

mysql -S /tmp/mysql.sock1 -p
输入实例1初始化后的密码

修改密码策略，第一条为修改为只需满足长度要求，第二条为设置长度最低为4

set global validate_password_policy=0;
set global validate_password_length=4;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
flush privileges;

退出实例1登录实例2，对实例2重复实例1操作

关闭实例

所有

killall -u mysql

单个

kill -9 进程号
或
/etc/rc.d/init.d/mysqld_multi stop 1
/etc/rc.d/init.d/mysqld_multi stop 2

配置主从同步

修改配置

先关闭所有数据库，修改my.cnf

修改完成启动mysql，登录实例1
添加从库用户work

create user 'work' @ '本机IP' identified by '123456';
grant select,update,insert,delete,create,drop on *.* to 'work' @ '本机IP';
flush privileges;
grant replication slave ON *.* TO 'root' @ '本机IP' identified by '123456';
flush privileges;

记住file和position

show master status;

退出
登录实例2

change master to master_host='主库IP',master_port=3306,master_user='work',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1059;

start slave;

show slave status \G;
Error当中没有提示错误信息表示成功

参考链接：
https://www.liangzl.com/get-article-detail-2905.html