mysql-主从复制

超帅懒羊羊

已于 2024-03-22 20:24:10 修改

阅读量602

点赞数 9

文章标签： mysql adb 数据库

于 2024-03-22 20:19:37 首次发布

本文链接：https://blog.csdn.net/weixin_53800035/article/details/136950968

版权

mysql-主从复制

一、无数据的主从备份

#准备两台干净的服务器
#关闭防火墙和selinux
[root@localhost ~]#systemctl stop firewalld
[root@localhost ~]#setenforce 0
#两台主机配置hosts解析，这一步也可以不做
[root@localhost ~]# vim /etc/hosts
192.168.230.148 master
192.168.230.149 slave
#主节点操作
#在主节点开启binlog日志
[root@localhost ~]#vim /etc/my.cnf
server-id=1
log-bin=/data/mysql/mysql-logbin #这里可以自定义
[root@localhost ~]#mkdir -p /data/mysql #创建目录
[root@localhost ~]#chown mysql.mysql /data/mysql -R
#重启mysql服务
[root@localhost ~]#systemctl restart mysqld
#进入数据库，创建从节点连接主节点的账号
mysql>grant replication slave on *.* to 'slave'@'%' identified by 'slave@123';
mysql>flush privileges;
#记录binlog日志的位置
mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      590 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
#从库操作
#配置server-id
[root@localhost ~]# vim /etc/my.cnf
server-id=2
#重启数据库服务
[root@localhost ~]# systemctl restart mysqld
#进入数据，配置主从同步
mysql>edit
CHANGE MASTER TO
  MASTER_HOST='192.168.230.148',
  MASTER_USER='slave',
  MASTER_PASSWORD='slave@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=59-,
  MASTER_CONNECT_RETRY=10;
 #开启slave
 mysql>start slave;
 #检查主从复制状态
 mysql>show slave status\G

#如果这里报错，就重做主从
#先停止slave
mysql>stop slave;
#清楚slave配置
mysql>reset slave;
#重新进行change	master to配置
mysql>start slave;

二、有数据的情况下做主从备份

#确认防火墙和selinux关闭
#主库操作
#导出数据
[root@localhost ~]# mysqldump -p'密码' -A > back.sql
#将导出的数据发给从库
[root@localhost ~]# scp ./back.sql 192.168.230.149:/root/
#开启binlog日志
[root@localhost ~]#vim /etc/my.cnf
server-id=1
log-bin=/data/mysql/mysql-logbin #这里可以自定义
[root@localhost ~]#mkdir -p /data/mysql #创建目录
[root@localhost ~]#chown mysql.mysql /data/mysql -R
#重启mysql服务
[root@localhost ~]#systemctl restart mysqld
#进入数据库，创建从节点连接主节点的账号
mysql>grant replication slave on *.* to 'slave'@'%' identified by 'slave@123';
mysql>flush privileges;
#记录binlog日志的位置
mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
#从库操作
#将主库发过来的数据导入
[root@localhost ~]#mysql -p'密码' < /back.sql
#配置server-id
[root@localhost ~]#vim /etc/my.cnf
server-id=2
#重启数据库
[root@localhost ~]#systemctl restart mysqld
#进入数据，配置主从同步
mysql>edit
CHANGE MASTER TO
  MASTER_HOST='192.168.80.130',
  MASTER_USER='slave',
  MASTER_PASSWORD='slave@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
 #启动slave
 mysql>start slave;
 #查看主从复制状态
 mysql>show slave status\G