MYSQL 主从备份(一主多从)
- 资源相关
Master 192.168.44.161
Slave 192.168.44.153
Slave 192.168.44.154
- 开启log-bin日志
#192.168.44.161 master节点
log-bin=mysql-bin
server-id=161
#192.168.44.153 slave节点
log-bin=mysql-bin
server-id=153
#192.168.44.154 slave节点
log-bin=mysql-bin
server-id=154
#重启mysql
systemctl restart mysql
- 在192.168.44.161 Master节点上创建账户【用于从节点连接】
#创建账户: slave 密码:147258 授权44网段内都可访问
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.44.%' IDENTIFIED BY '147258';
#刷新
flush privileges;
- 查看192.168.44.161 Master节点状态
show master status;
- 配置192.168.44.153,192.168.44.154 Slave从节点
#在192.168.44.153 192.168.44.154节点上都执行
CHANGE MASTER TO
MASTER_HOST='192.168.44.161',
MASTER_USER='slave',
MASTER_PASSWORD='147258',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=328;
#启动Slave 节点
start slave;
- 查看配置的Master节点信息
#切换目录
cd /var/lib/mysql && ll
#查看
cat master.info
- 查看192.168.44.153,192.168.44.154 Slave 进程信息
show slave status\G;
- 测试主从
-
192.168.44.161 主节点创建数据库信息
CREATE DATABASE `task_db`; USE `task_db`; DROP TABLE IF EXISTS `tb_user`; CREATE TABLE `tb_user` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; insert into `tb_user`(`id`,`name`) values (1,'刘一'),(2,'陈二'),(3,'张三'),(4,'李四'),(5,'王五'); insert into tb_user(name) values(@@hostname);
-
查看153,154从节点信息
-
到此,一主多从配置完成
-
MYSQL 互为主备(双主)
- 资源相关
Master 192.168.44.161
Master 192.168.44.162
- 开启bin-log日志配置
#192.168.44.161 Master节点配置
log-bin=mysql-bin
server-id=161
#忽略指定数据库
binlog-ignore-db=mysql
#192.168.44.162 Master节点配置
log-bin=mysql-bin
server-id=162
#忽略指定数据库
binlog-ignore-db=mysql
#重启mysql
systemctl restart mysql
-
以161为Master、162为Slave
- 在161主机上创建授权信息、允许162 Slave节点访问
#创建账户: slave_m_162密码:147258 允许192.168.44.162访问 GRANT REPLICATION SLAVE ON *.* TO 'slave_m_162'@'192.168.44.162' IDENTIFIED BY '147258'; #刷新 flush privileges;
- 查询192.168.44.161 上Master信息
show master status;
- 以192.168.44.162为Slave节点、链接192.168.44.161 Master节点
#在192.168.44.162上执行 CHANGE MASTER TO MASTER_HOST='192.168.44.161', MASTER_USER='slave_m_162', MASTER_PASSWORD='147258', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=336; #启动Slave 节点 start slave;
- 查看192.168.44.162 Slave节点状态
show slave status\G;
-
以162为Master、161为Slave
- 在162主机上创建授权信息、允许162 Slave节点访问
#创建账户: slave_m_161密码:147258 允许192.168.44.161访问 GRANT REPLICATION SLAVE ON *.* TO 'slave_m_161'@'192.168.44.161' IDENTIFIED BY '147258'; #刷新 flush privileges;
- 查询192.168.44.162 上Master信息
show master status;
- 以192.168.44.161为Slave节点、链接192.168.44.162 Master节点
#在192.168.44.161上执行 CHANGE MASTER TO MASTER_HOST='192.168.44.162', MASTER_USER='slave_m_161', MASTER_PASSWORD='147258', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=336; #启动Slave 节点 start slave;
- 查看192.168.44.161 Slave节点状态
show slave status\G;
-
测试双主(互为准备)
- 192.168.44.161上创建数据库、192.168.44.162上查看备份
- 192.168.44.162上删除刚创建的数据库、192.168.44.161上查看是否删除
- 到此、双主搭建完成
- 192.168.44.161上创建数据库、192.168.44.162上查看备份
MYSQL 双主双从
- 资源准备
master 192.168.44.191 3306 root SYS_666_admin Mysql 5.7
master 192.168.44.192 3306 root SYS_666_admin Mysql 5.7
slave 192.168.44.193 3306 root SYS_666_admin Mysql 5.7
slave 192.168.44.194 3306 root SYS_666_admin Mysql 5.7
- 编辑MYSQL配置
#双主 191
log-bin=mysql-bin
server-id=191
#略过数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要主备的数据库【根据情况填写】
binlog-do-db=task_db
#设置logbin格式
binlog_format=STATEMENT
#作为从库、也写入日志
log-slave-updates
#自增长字段每次递增的量、指定自增字段的起始值,取值范围1-65535
auto_increment_increment=2
#自增长字段从哪个数开始、即一次递增多少、取值1-65535
auto-increment-offset=1
#双主 192
log-bin=mysql-bin
server-id=192
#略过数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要主备的数据库【根据情况填写】
binlog-do-db=task_db
#设置logbin格式
binlog_format=STATEMENT
#作为从库、也写入日志
log-slave-updates
#自增长字段每次递增的量、指定自增字段的起始值,取值范围1-65535
auto_increment_increment=2
#自增长字段从哪个数开始、即一次递增多少、取值1-65535
auto-increment-offset=2
#双从 193
log-bin=mysql-bin
server-id=193
#只读哪个数据库
replicate-do-db=task_db
read_only=1
relay-log=mysql-relay
#双从 194
log-bin=mysql-bin
server-id=194
#只读哪个数据库
replicate-do-db=task_db
read_only=1
relay-log=mysql-relay
- 授权并链接主从
#191/192分别创建授权账户
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'SYS_admin_147258';
flush privileges;
#相关清除命令
flush logs;
reset master;
reset slave;
#双主 s/192 链接 m/191
CHANGE MASTER TO MASTER_HOST='192.168.44.191',MASTER_USER='slave',MASTER_PASSWORD='SYS_admin_147258', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
#双主 s/191 链接 m/192
CHANGE MASTER TO MASTER_HOST='192.168.44.192',MASTER_USER='slave',MASTER_PASSWORD='SYS_admin_147258', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
#双主 s/193 链接 m/191
CHANGE MASTER TO MASTER_HOST='192.168.44.191',MASTER_USER='slave',MASTER_PASSWORD='SYS_admin_147258', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LO