2020-08-18 数据库备份及恢复

mysqldump 数据库备份

[root@localhost ~]# mysqldump -B hellodb > /data/hellodb.sql 备份数据库 -B后面可跟多个数据库
root@localhost ~]# mysqldump -A > /data/all.sql 备份所有数据库
MariaDB [(none)]> show master logs; 查看最新二进制日志位置
[root@localhost data]# mysqldump -A --master-data=2 > /data/all.date +%F.sql 完全备份数据库 查看二进制位置 如000001 707

1 数据库修改 
insert students (name,age)values('a',20);
insert students (name,age)values('b',30);
drop table teachers;
insert treachers (name,age)values('b',30);
2 删除库 rm -rf /var/lib/mysql/*

3 还原
确保无用户访问数据库
1）systemctl restart mariadb
2)mysql > show master logs; 查看当前二进制位置
3）根据/data/all.sql中日志位置和2）定位需要的二进制日志范围
mysqlbinlog  --start-position=707 mysql-bin.000001 > /data/inc.sql
mysqlbinlog  mysql-bin.000002 >> /data/inc.sql
mysqlbinlog  mysql-bin.000003 >> /data/inc.sql
vim /data/inc.sql
 #DROP TABLE `teachers` /* generated by server */  将删表命令行注释掉

4) mysql > set sql_log_bin=off;        暂停二进制日志记录
mysql>source /data/all.sql                 还原
mysql>source /data/inc.sql                日志还原

5) mysql > set sql_log_bin=on;

6) 做检查确认数据库恢复成功，恢复用户访问

分库备份
for循环
[root@localhost data]# for db in `mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'`;do mysqldump -B $db --single-transaction --master-data=2 > /data/$db.sql ;done
[root@localhost data]# for db in `mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'`;do mysqldump -B $db --single-transaction --master-data=2|gzip > /data/$db.sql.gz ;done
[root@localhost data]# ll
总用量 1192
-rw-r--r-- 1 root  root  521946 8月  20 09:47 all.2020-08-20.sql
-rw-r--r-- 1 root  root    8202 8月  20 15:02 hellodb.sql
-rw-r--r-- 1 root  root    2046 8月  20 15:05 hellodb.sql.gz
-rw-r--r-- 1 root  root    3135 8月  20 10:23 inc.sql
drwxr-xr-x 2 mysql mysql    238 8月  20 10:27 logbin
-rw-r--r-- 1 root  root  514963 8月  20 15:02 mysql.sql
-rw-r--r-- 1 root  root  139601 8月  20 15:05 mysql.sql.gz
-rw-r--r-- 1 root  root    1551 8月  20 15:02 test.sql
-rw-r--r-- 1 root  root     608 8月  20 15:05 test.sql.gz
-rw-r--r-- 1 root  root    1556 8月  20 15:02 yangt.sql
-rw-r--r-- 1 root  root     610 8月  20 15:05 yangt.sql.gz
[root@localhost data]# 

[root@localhost data]# mysql -e 'show databases'|grep -Ev '^(information_schema|performance_schema|Database)$'|sed -rn 's@(.*)@mysqldump -B \1 --single-transaction --master-data=2 |gzip > /data/back/\1\.sql\.gz@p'|bash
[root@localhost back]# ll
总用量 152
-rw-r--r-- 1 root root   2046 8月  20 15:22 hellodb.sql.gz
-rw-r--r-- 1 root root 139601 8月  20 15:22 mysql.sql.gz
-rw-r--r-- 1 root root    608 8月  20 15:22 test.sql.gz
-rw-r--r-- 1 root root    611 8月  20 15:22 yangt.sql.gz

xtraback
安装
官网网址：www.percona.com
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：
免费 支持热备
备份还原过程快速、可靠
能够基于压缩等功能节约磁盘空间和流量

yum -y install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm 安装 需要将二进制日志打开

1 在原主机做完全备份到/backups        文件夹backup要创建
xtrabackup --backup --target-dir=/backup/
scp -r /backup/* 目标主机:/backup
2 在目标主机上
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/
3）还原属性
chown -R mysql:mysql /var/lib/mysql
4）启动服务
systemctl start mariadb

增量备份
1 备份过程
1）完全备份：xtrabackup --backup --target-dir=/backup/base
2）第一次修改数据
3）第一次增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
5）第二次增量
xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
6）scp -r /backup/* 目标主机:/backup/
备份过程生成三个备份目录
/backup/{base，inc1，inc2}

2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份，
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：chown -R mysql:mysql /var/lib/mysql
6）启动服务：systemctl start mariadb

例 主机
xtrabackup --backup --target-dir=/backup/base -uroot -p 完全备份  
更改数据后的第一次增量备份
 xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base -uroot -p
更改数据后的第二次增量备份
 xtrabackup --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 -uroot -p
将备份复制到要还原的主机
scp -r /backup/ 172.16.100.47:/
备份主机还原
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
xtrabackup --copy-back --target-dir=/backup/base
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

实验：在现有mysql服务器上基础上，实现主从复制

1 主服务器
vim /etc/my.cnf
server-id=1
log-bin=/data/logbin/mysql-bin

mysql> grant replication slave on *.* to repluser@'192.168.50.%' identified by  'centos';

mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql 
scp /data/all.sql 将来的从服务器上:/data

 
2 从服务器 
vim /etc/my.cnf
server-id=2
read-only


vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,   
MASTER_LOG_FILE='mariadb-bin.000002',                                                                  
MASTER_LOG_POS=245;

mysql < /data/all.sql
mysql> start slave;

故障解决
1 同步故障
解决：临时忽略 后面排查修复 故障太多就全备份恢复


image.png

2 主服务宕机

将从服务器升级为主服务器 
停止主从复制线程
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.03 sec)
清空所有从节点信息
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)

查看是否有复制数据库权限的账户 如无则创建
MariaDB [(none)]> select user,host,password from mysql.user;
+----------+----------------+-------------------------------------------+
| user     | host           | password                                  |
+----------+----------------+-------------------------------------------+
| root     | localhost      | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root     | 127.0.0.1      | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| root     | ::1            | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| test     | 192.168.50.%   | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| magedu   | 192.168.1.0/24 | *A547BD3AD0B17ADE3A90D1A14559C13B22E0946A |
| repluser | 192.168.50.%   | *128977E278358FF80A246B5046F51043A2B1FCED |
+----------+----------------+-------------------------------------------+
MariaDB [(none)]>  grant replication slave on *.* to repluser@'172.16.100.%' identified by  'centos'; 创建命令

更改配置文件 将要升级为主服务器的从服务器二进制日志打开

[root@localhost logbin]# cat /etc/my.cnf
[mysqld]
server_id=2
log-bin=/data/logbin/mysql-bin
重启服务
[root@localhost data]#  mysqldump -A --master-data=1 > /data/all.`date +%F`.sql -uroot -p 做全量备份
[root@localhost data]# scp all.2020-08-25.sql 172.16.100.46:/data 发送到新从服务器
[root@localhost data]# vim all.2020-08-25.sql  更改配置
CHANGE MASTER TO
 MASTER_HOST='172.16.100.47',
 MASTER_USER='repluser',
 MASTER_PASSWORD='centos',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=245;
[root@localhost data]# mysql < all.2020-08-25.sql   复原备份
[root@localhost ~]# systemctl restart mariadb          重启服务
MariaDB [(none)]>  show slave status\G                  查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.100.47
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

级联复制

1  master 服务器配置
vim /etc/my.cnf
server-id=1
log-bin=/data/logbin/mysql

mysql> grant replication slave on *.* to repluser@'192.168.36.%' identified by  'centos';

mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql 
scp /data/all.sql 将来的级联从服务器上:/data
[root@centos7 ~]#scp /data/all.sql 172.16.100.46:/data/

2 级联从服务器

vim /etc/my.cnf
server-id=2
log-bin
log_slave_updates
read-only

vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='主服务器',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=400;   

mysql <  /data/all.sql 
mysql> start slave;
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.50.133' identified by  'centos'; 将从服务器IP加入
mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql 
scp /data/all.sql 最终的从服务器上:/data
[root@localhost data]# scp /data/all.sql 192.168.50.133:/data/

最后的从服务器
vim /etc/my.cnf
server-id=3
read-only

vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='级联从服务器',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=400;   

mysql <  /data/all.sql 
mysql> start slave;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,923评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,154评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,775评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,960评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,976评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,972评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,893评论 3赞 416
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,709评论 0赞 271
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,159评论 1赞 308
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,400评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,552评论 1赞 346
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,265评论 5赞 341
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,876评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,528评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,701评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,552评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,451评论 2赞 352