Mysql8.0 主备环境搭建
1.前期准备
服务器2台
下载并安装mysql8.0
见mysql官方安装指引，照着copy命令就行
2.配置主mysql服务
(1).为从mysql配置一个连接账号:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE on *.* to username@192.168.0.%
flush privileges;
REPLICATION SLAVE 权限是必须的.
可以select * from mysql.user\G检查一下.
(2).修改my.cnf(我的是在/etc/mysql/my.cnf)如下:
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8' //utf8, utf8mb4
server-id=1 //必填,主mysql唯一标识
log-bin=master-bin //二进制快照
expire-logs-days=7 //有效期
binlog-ignore-db=mysql //忽略备份的db
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
(3).配置结束之后重启一下mysql服务
service mysql restart
进入主mysql,执行show master status
File即快照日志文件,position是一个位置点,表示当前log的位置,用于slave同步数据时使用.file和position在从mysql连接主mysql时要用.
3.配置从mysql
(1).修改my.cnf,如下:
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
server-id=2
read-only=1 //从库只读,不允许insert,update,delete等(可根据情况配置)
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
(2).重启mysql
同上
(3).连接主mysql如下:
change master to master_host='192.168.0.7', //Master 服务器Ip
master_port=3306,
master_user='username', //Master 配置的Slave账号
master_password='mysql', //密码
master_log_file='master-bin.000001', //Master服务器bin日志,见status
master_log_pos=0; //Master服务器log位置点,见status
(4).完了start slave;
show slave status
Slave_IO_Running和Slave_SQL_Running都为yes,则成功.
如有遗漏,欢迎补充.
