mysql数据备份与恢复

最新推荐文章于 2022-11-08 09:18:22 发布

Jiali Li

最新推荐文章于 2022-11-08 09:18:22 发布

阅读量208

点赞数 1

本文链接：https://blog.csdn.net/weixin_43669585/article/details/90070670

版权

概述：

1.物理备份：cp,tar

2.服务提供备份命令备份 mysqldump

3.时时备份 binlog日志

4.安装其他软件提供备份命令备份

一。数据备份的方式：

(1)物理备份（使用系统命令备份数据库目录）

冷备：cp,tar

(2) 逻辑备份：mysql 自带的服务命令 ,

数据的备份策略：

(1) 完全备份：备份所有的数据

(2)增量备份：备份上次备份后，所有新产生的数据

(3)差异备份：备份完全备份后，所有新产生的数据

cal 能查看日期

物理备份：适合备份比较小的数据，操作系统相同的，兼容性不好

将主机50的数据库备份到主机51数据库上面：

在主机50上操作：

[root@host50 ~]# mkdir /dbbak
[root@host50 ~]# cp -r /var/lib/mysql /dbbak/mysql.bak
[root@host50 ~]# scp -r /dbbak/mysql.bak/ root@192.168.4.51:/root

在主机51上操作：

[root@host51 ~]# systemctl stop mysqld.service # 停止数据库的服务
[root@host51 ~]# rm -rf /var/lib/mysql/* # 删除原来数据库的里面的内容
[root@host51 ~]# cp -r /root/mysql.bak/* /var/lib/mysql # 将50主机备份的数据库拷贝了51主机数据库上
[root@host51 ~]# ls /var/lib/mysql

[root@host51 ~]# ls -l /var/lib/mysql/
[root@host51 ~]# chown -R mysql:mysql /var/lib/mysql # 将数据库下所有的目录的所有者所属组修改为mysql

[root@host51 ~]# systemctl restart mysql
[root@host51 ~]# mysql -uroot -pabc123 # 因为备份了主机50数据库中包括了 mysql 库，所以登陆的时候密码用主机50登陆数据库的密码。

逻辑备份：在执行备份命令时，把数据保存到指定的备份文件里，可以使用备份文件恢复数据

备份所有库或者是多个库时，在完全恢复时可以不指定库名，因为在备份库的时候会有创建库的命令

1使用数据库服务提供的备份命令备份mysqldump命令做完全备份

182.168.4.50 主机上做操作：

完全备份：

格式： [root@host50 dbbak]mysqldump -uroot -p密码库名 > /路径/xxx.sql # 路径后面一定要以.sql结尾,,在系统操作界面上操作

#库名表示方法：

all-databases 或者 -A 所有库

数据库名单个数据库

数据库名表名单张表

-B 数据库1 数据库2 多个库

[root@host50 dbbak]midir /dbbak

[root@host50 dbbak]# mysqldump -uroot -pabc123 -A > /dbbak/all.sql
[root@host50 dbbak]# mysqldump -uroot -pabc123 db3 > /dbbak/db3.sql
[root@host50 dbbak]# mysqldump -uroot -pabc123 -B db1 db2 > /dbbak/dbtwo.sql

[root@host50 dbbak]# scp /dbbak/dbtwo.sql root@192.168.4.51:/root
[root@host50 dbbak]# scp /dbbak/db3.sql root@192.168.4.51:/root

192.168.4.51主机上面做操作：

完全恢复

格式：

[root@host51 ~]# mysql -uroot -p密码库名 < /路径/xxx.sql # 在主机系统界面上操作

mysql> drop database db1; mysql> drop database db2; #在主机51上误删除db1,db2库

mysql> drop database db3;
[root@host51 ~]# mysql -uroot -pabc123 < /root/dbtwo.sql # 恢复两个库以上可以不加库名,

mysql -uroot -pabc123 db3 < /root/db3.sql # 会报错，要先在数据库里面创建db3库

mysql> create database db3; #需要进入数据库里面创建db3库，
mysql -uroot -pabc123 db3 < /root/db3.sql # 恢复db3的数据库

逻辑备份缺点：

解决方法：时时备份

/var/log/mysql 默认日志

> show master status; 显示当前服务器的日志信息

日志文件名字

时时备份使用 mysql 自带的binlog日志实现

binlog 日志文件的使用：

1定义：记录除查询之外的sql命令

2.启用binlog日志： [root@host50 admin]# vim /etc/my.cnf

[mysqld]
log_bin # 启用binlog
server_id=50 #指定id 值，范围：1～255

[root@host50 admin]# mysql -uroot -pabc123

mysql> show master status; #显示当前服务器的日志信息
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| host50-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+

[root@host50 dbbak]# ls /var/lib/mysql

host50-bin.000001 host50-bin.index

>system ls /var/lib/mysql

手动生成新的日志文件的方法：

[root@host50 dbbak]# mysqldump -uroot -pabc123 --flush-logs db3 > /dbbak/db3.sql
[root@host50 mysql]# mysql -uroot -pabc123 -e "flush logs"
[root@host50 mysql]# systemctl restart mysqld # 实际环境中不这样用
mysql> flush logs;

删除日志文件：

mysql> purge master logs to "host50-bin.000004";

mysql> system ls /var/lib/mysql;
auto.cnf   client-key.pem db4       ibdata1   mysql.sock   server-cert.pem
bbsdb       db1       host50-bin.000004 ib_logfile0 mysql.sock.lock server-key.pem
ca-key.pem   db_1       host50-bin.000005 ib_logfile1 performance_schema studb
ca.pem       db2       host50-bin.index ibtmp1   private_key.pem sys
client-cert.pem db3       ib_buffer_pool mysql   public_key.pem

mysql> reset master; #删除所有的的日志文件 ,重新建立新的日志

mysql> system ls /var/lib/mysql;
auto.cnf   client-key.pem db4       ib_logfile0 mysql.sock.lock server-key.pem
bbsdb       db1       host50-bin.000001 ib_logfile1 performance_schema studb
ca-key.pem   db_1       host50-bin.index ibtmp1   private_key.pem sys
ca.pem       db2       ib_buffer_pool mysql   public_key.pem
client-cert.pem db3       ibdata1   mysql.sock   server-cert.pem

mysql> system cat /var/lib/mysql/host50-bin.index
./host50-bin.000001

4.自定义日志存储目录及日志文件名

[root@host50 ~]# mkdir /mylog
log_bin=/mylog/plj
server_id=50

[root@host50 ~]# ls /mylog
plj.000001 plj.index

5.使用binlog恢复数据：

在主机50上操作：

mysql> create database db6;
mysql> create table t1(id int);
mysql> insert into t1 values(99999);

mysql> insert into t1 values(99997);

在主机51上操作：

[root@host51 ~]# ls /root/plj.000001
[root@host51 ~]# mysqlbinlog /root/plj.000001 | mysql -uroot -pabc123
mysql> show databases;
mysql> select * from db6;

修改binlog日志文件记录sql命令的格式：

查看默认的记录格式：

mysql> show variables like "%binlog%";
mysql> show variables like "%binlog_format%";
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+

[root@host50 ~]# vim /etc/my.cnf

binlog_format="mixed" # 修改binlog日志文件记录sql命令的格式

[root@host50 ~]# systemctl restart mysqld # 会自动新产生新的日志文件

mysql>use db6;

mysql> insert into t1 values(123); # 进行这些操作在新的日志文件中写入
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(456);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(789);
Query OK, 1 row affected (0.06 sec)

[root@host50 ~]# mysqlbinlog /mylog/plj.000002

[root@host50 ~]# mysqlbinlog /mylog/plj.000002 | grep insert
insert into t1 values(123)
insert into t1 values(456)
insert into t1 values(789)

使用binlog日志文件恢复部分数据 percona

日志文件如何区分记录的多条sql命令: 偏移量和命令执行时间

格式: ]# mysqlbinlog 选项 binlog日志文件 | mysql -uroot -p密码

--start-position=数字 //起始偏移量
--stop-position=数字 //结束偏移量

--start-datetime="yyyy-mm-dd hh:mm:ss" //起始时间
--stop-datetime="yyyy-mm-dd hh:mm:ss" //结束时间

192.168.4.50 ]# scp /mylog/plj.000002 root@192.168.4.51:/root/

192.168.4.51]# mysqlbinlog --start-position=296 --stop-position=1510 /root/plj.000002 | mysql -uroot -pabc123

安装其他软件提供备份命令备份，，percona软件

- percona 介绍

环境准备：把50主机的存储引擎修改为innodb

删除所有的自定义，只保留初始的4个库

准备软件包
libev-4.15-1.el6.rf.x86_64.rpm
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

二、安装percona软件192.168.4.50:
]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
]# rpm -ql percona-xtrabackup-24

innobackupex 完全备份
[root@host50 ~]# innobackupex --help

[root@host50 ~]# man innobackupex

innobackupex格式： innobackupex <选项>

常用的选项：

innobackupex --user 用户名 --password 密码备份目录 --no-timestamp # --no-timestamp备份目录下面的目录不用时间命名

mysql> create database db6;

mysql> insert into t1 values (1),(2),(3),(4);

mysql> insert into t1 values (11),(22),(33),(44);

mysql> insert into t1 values (111),(222),(333),(444);

mysql> select * from t1;
mysql> show create table t1;
[root@host50 ~]# innobackupex --user root --password abc123 /allbak #/allbak不用先在系统创建

[root@host50 ~]# ls /allbak/
2019-05-11_14-45-07 # 以时间命名的目录

[root@host50 ~]# innobackupex --user root --password abc123 /allbak --no-timestamp
[root@host50 ~]# ls /allbak/ # 没有以时间命名的目录
backup-my.cnf ibdata1 sys xtrabackup_info
db6 mysql xtrabackup_binlog_info xtrabackup_logfile
ib_buffer_pool performance_schema xtrabackup_checkpoints

[root@host50 ~]scp -r /allbak/ root@192.168.4.51:/root
[root@host50 ~] scp libev-4.15-1.el6.rf.x86_64.rpm root@192.168.4.51:/root
[root@host50 ~] scp percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm root@192.168.4.51:/root

innobackupex 完全恢复

在51主机上面操作

[root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

[root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
[root@host51 ~]# which innobackupex

2.恢复数据

# systemctl stop mysql

[root@host51 ~]# rm -rf /var/lib/mysql/*

[root@host51 ~] systemctl stop mysqld.service
[root@host51 ~]rm -rf /var/lib/mysql/*
[root@host51 ~] ls /var/lib/mysql
[root@host51 ~] innobackupex --apply-log /root/allbak/ # 恢复日志
[root@host51 ~] cat /root/allbak/xtrabackup_checkpoints # 查看信息文件
[root@host51 ~] innobackupex --copy-back /root/allbak/ # 拷贝数据
[root@host51 ~] ls -l /var/lib/mysql
[root@host51 ~] ls /var/lib/mysql # 查看数据库的信息
[root@host51 ~] chown -R mysql:mysql /var/lib/mysql # 在目录下面的子目录下面的所有者和所属组的改为 mysql
[root@host51 ~] ls -l /var/lib/mysql
[root@host51 ~] systemctl restart mysqld
[root@host51 ~] mysql -uroot -pabc123 # 登陆数据库

mysql> show databases; # 查看库

mysql> select * from db6.t1; # 查看表

innobackupex 增量备份

命令格式：
]# innodbackupex --user root --password 密码 --incremental 目录名 --incremental-basedir=目录名 --no-timestamp

例子：主机50

首次备份（备份所有的数据）周一

[root@host50 ~]# innobackupex --user root --password abc123 /fullbak --no-timestamp
[root@host50 ~]# ls /fullbak/

[root@host50 fullbak]# cat /fullbak/xtrabackup_checkpoints # 查看备份的类型
mysql> insert into t1 values(99999999)

增量备份(第一次增量备份) 周二
[root@host50 fullbak]# innobackupex --user root --password abc123 --incremental /new1dir --incremental-basedir=/fullbak --no-timestamp

[root@host50 fullbak]# cat /new1dir/xtrabackup_checkpoints # 查看备份的类型

增量备份（第2次增量备份) 周三

[root@host50 ] innobackupex --user root --password abc123 --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp

[root@host50 ]ls /new2dir/

[root@host50 ]cat /new2dir/xtrabackup_checkpoints

[root@host50 fullbak]# scp -r /fullbak/ root@192.168.4.51:/root
[root@host50 fullbak]# scp -r /new1dir/ root@192.168.4.51:/root
[root@host50 fullbak]# scp -r /new2dir/ root@192.168.4.51:/root

innobackupex 增量恢复

命令格式：

】# innobackupex --apply-log --redo--only 完全备份目录 //准备恢复数据

】# innobackupex --apply-log --redo--only 完全备份目录 --incrementa-dir=目录 //l 合并日志

】# innobackupe --copy-back 完全备份 //拷贝数据

在51 主机

例子： 192.168.4.51
]# systemctl stop mysqld
]# rm -rf /var/lib/mysql/*

]# innobackupex --apply-log --redo-only /root/fullbak
]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir
]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir

]# innobackupex --copy-back /root/fullbak/
]# chown -R mysql:mysql /var/lib/mysql
]# systemctl start mysqld
]# mysql -uroot -pabc123
mysql> select * from db6.t1;

恢复完全备份中的单张表 192.168.4.50

完全备份：

[root@host50 fullbak]# innobackupex --user root --password abc123 /backup --no-timestamp
[root@host50 fullbak]# ls /backup/
[root@host50 fullbak]# ls /backup/db6/

恢复完全备份中的单张表的步骤
   1 把删除的按照原表结构创建出来
       mysql> create table db6.t2 (name char(10));
       mysql> system ls /var/lib/mysql/db6/t2.*
   2 删除表空间
       mysql> alter table db6.t2 discard tablespace;

3 在备份文件里导出表信息
]# innobackupex --apply-log --export /backup

】# ls /backup

   4 把导出的表信息文件拷贝到数据库目录下，并修改所有者和组用户为mysql
       ]# cp /backup/db6/t2.{cfg,exp,ibd} /var/lib/mysql/db6/
       ]# chown mysql:mysql /var/lib/mysql/db6/t2.*
   5 导入表信息
       mysql> alter table db6.t2 import tablespace;
   6 删除数据库目录下的表信息文件
       ]# rm -rf /var/lib/mysql/db6/t2.cfg
   ]# rm -rf /var/lib/mysql/db6/t2.exp
   7 查看表记录
    mysql> select * from db6.t2;

数据备份：增量备份（binlog日志 innobackupex）

数据备份：增量备份（binlog日志：时时备份 innobackupex:热备，进行行锁

50～55

Jiali Li

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据备份与恢复

概述：1.物理备份：cp,tar2.服务提供备份命令备份 mysqldump3.时时备份 binlog日志4.安装其他软件提供备份命令备份一。数据备份的方式： (1)物理备份（使用系统命令备份数据库目录）冷备：cp,tar (2) 逻辑备份：mysql 自带的服务命令 , ...
复制链接

扫一扫