DBAmysql数据的备份与恢复_万金油_新浪博客

数据备份的方式

物理备份

cp -r /var/lib/mysql   /dbdir.bak

cp -r /var/lib/mysql/mysqldb*  /mysqldb.bak
  

数据备份的策内

把备份的文件，拷贝会对应的数据库分目录，把所有的者和组修改为mysql 重起数据库服务器。

授权库的一定要叫mysql，要修改授权库的目录为mysql

cp -r /var/lib/mysql  /dbdir.bak

cp -r /var/lib/mysql/mysql    /mysqldb.bak

cp -r /var/lib/mysql/mysql/db.*    /root/

tar  - zcvf  /mysql.tar.gz   /var/lib/mysql/*

缺点：跨平台比较差，备份时间长，冗余备份，浪费空间

实操步骤：

50：

[root@host50 ~]# cp -r /var/lib/mysql/mysql/    /mysqldb.bak

[root@host50 ~]# scp -r /mysqldb.bak/  root@192.168.4.52:/root

root@192.168.4.52's password: 

52：

[root@host52 ~]# systemctl  stop  mysqld

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

[root@host52 ~]# cp -r /root/mysqldb.bak/  /var/lib/mysql/mysql

[root@host52 ~]# ls -ld /var/lib/mysql/mysql

drwxr-x---. 3 root root 4096 7月  16 18:32 /var/lib/mysql/mysql

[root@host52 ~]# chown -R mysql:mysql  /var/lib/mysql/mysql

[root@host52 ~]# ls -ld /var/lib/mysql/mysql

drwxr-x---. 3 mysql mysql 4096 7月  16 18:32 /var/lib/mysql/mysql

[root@host52 ~]# systemctl  start mysqld

[root@host52 ~]# ss -ntulp | grep mysqld

tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=4088,fd=18))

存在的缺点：

[root@host50 ~]# du -sh /var/lib/mysql

201M /var/lib/mysql

只适合小量的数据进行备份，不适合大量的式狙击备份；

1.逻辑备份：

执行备份命令时，根据备份的库表，生产对应的sql命令， 把命令保存到指定的文件里。恢复时，执行保存sql命令的备份文件 把数据写回数据库里。

2.数据备份策类

完全备份  备份所有数据（通常包获3类对象： 一张表里的所有数据 一个库所有表  一台服务器的所有数据）

备份新产生的数据：

差异备份：备份自完全备份后所有新产生的数据

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

生产环境的使用方法：

完全+增量 完全+差异

周一做完全备份  其他做增量或差异备份

增量和差异也有各自的优缺点

生产环境中是：

使用crond服务执行备份脚本（在备份脚本调用备份命令） 做数据备份

00 23 * * 1 /root/allbak.sh  ——完全备份

00 23 * * 2-7 /root/newbak.sh ——增量备份

3.备份数据和数据恢复

完全备份：mysqldum——备份所有数据

[root@host50 ~]# man mysqldump  ——学会看帮助的信息

[root@host50 ~]# mysql -uroot -p123 库名  > 目录名.文件名.sql

缺点 ： 备份和还原时间慢 效率低 备份过程中可能会有数据的插入和更新的同步操作：

实验步骤：

[root@host50 ~]# mkdir   /bakdir

1.[root@host50 ~]# mysqldump  -u root -p123456   -A >  /bakdir/alldb.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

2.[root@host50 ~]# mysqldump  -u root -p123456   db3 >  /bakdir/db3.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

3.[root@host50 ~]# mysqldump  -u root -p123456   db3 user   >  /bakdir/db3-user.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

4.[root@host50 ~]# mysqldump  -u root -p123456  -B db3 db55   >  /bakdir/twodb.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

库名的表示方式： 

1.--all -databases:   所有库所有表  等同于 -A

2.库名  ——备份库里的所有表，（备份库里所有数据）

3.库名.表名——备份一张表的所有记录

4.-B 库名1  库名2    备份某几个库的所有数据

完全恢复：mysql

方法：

1.[root@host50 ~]# mysql -uroot -p123456  库名  <   目录名.文件名.sql

2.mysql > sourcre  目录名.文件名.sql

实验步骤：

mysql> drop table db3.user;

Query OK, 0 rows affected (0.09 sec)

mysql> select * from db3.user;

ERROR 1146 (42S02): Table 'db3.user' doesn't exist

[root@host50 ~]# mysql -uroot -p123456 db3 < /bakdir/db3-user.sql 

mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> select * from  db3.user;

+-----+---------------------+------+----------+-------+------+-----------------------------------------------------------------+---------------------------+----------------+

| id  | name                | age  | password | uid   | gid  | comment                                                         | homedir                   | shell          |

+-----+---------------------+------+----------+-------+------+-----------------------------------------------------------------+---------------------------+----------------+

|   1 | root                |   18 | x        |     1 | 1000 | root                                                            | /root                     | /bin/bash      |

|   2 | bin                 |   18 | NULL     |     2 | 2013 | bin                                                             | /bin                      | /sbin/nologin  |

完全备份的优点和缺点：

缺点：

1.不能同步备份新产生的数据，插入 ，更新 ，删除的数据就不能备份，万一数据丢失就不能恢复，新产生的数据可以，用增量备份，

2.做完全备份和恢复，都会锁表—— 要在生产环境不忙的时候进行备份

vim  /bakdir/db3-user.sql

lock tables user write

insert into

unlock  tables;

优点：

可以完全备份数据

可以利用计划任务时间进行备份数据

vim  /root/alldb3.sh——完全备份脚本

#!/bin/bash

if [!-e  /bakdir];then

mkdir  /bakdir

fi

x=`date +%F`

mysqldump -uroot -p123456 db3 > /bakdir/db3_${x}.sql

:wq

chmod  + x /root/alldb3.sh

crontab -e    ——创建时间周期任务

00 23 * * 1 /root/alldb3.sh  &>/dev/null

:wq

crontab -l ——查看时间周期

验证脚本：

[root@host50 ~]# chmod  +x /root/alldb3.sh

[root@host50 ~]# /root/alldb3.sh

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# ls /bakdir/

alldb.sql  db3_2018-07-17.sql  db3.sql  db3-user.sql  twodb.sql

[root@host50 ~]# ls /bakdir/db3_2018-07-17.sql 

/bakdir/db3_2018-07-17.sql

增量备份： 备份上一次备份后新产生的新数据

生存环境下是完全备份+增量备份

方法：

1.启动MYSQL 服务自带的binlog 日志文件

binlog 日志的使用：

日志的作用：

日志介绍——是服务日志文件中的一种（默认是没有启用） 记录除查询之外的sql的命令

启用日志 

vim /etc/my.cnf

[mysqld]

server_id=50

log-bin

binlog-format="mixed"

:wq

[root@host50 ~]# systemctl  stop mysqld

[root@host50 ~]# systemctl  restart  mysqld

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

host50-bin.000001 日志文件  > 500M  才会新的一个日志《最多存储的文件 999999个  这个文件不是文本文件不能用系统命令查看 ，要用专属命令查看，这个文件是用二进制编写

host50-bin.index  索引文件

日志的内容

[root@host50 mysql]# mysqlbinlog  host50-bin.000001 ——初始的日志

[root@host50 mysql]# mysqlbinlog  host50-bin.000001  | grep desc

实验步骤：

mysql> create table t10(id int);

Query OK, 0 rows affected (0.06 sec)

mysql> desc t10;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into t10 values(111);

Query OK, 1 row affected (0.04 sec)

mysql> insert into t10 values(211);

Query OK, 1 row affected (0.02 sec)

mysql> insert into t10 values(311);

Query OK, 1 row affected (0.04 sec)

mysql> insert into t10 values(411);

Query OK, 1 row affected (0.02 sec)

mysql> select * from t10;

+------+

| id   |

+------+

|  111 |

|  211 |

|  311 |

|  411 |

+------+

4 rows in set (0.00 sec)

另起一个终端检测：

[root@host50 mysql]# mysqlbinlog  host50-bin.000001  | grep insert

insert into t10 values(111)

insert into t10 values(211)

insert into t10 values(311)

insert into t10 values(411)

自定义日志目录的：

mkdir /logbir  

vim /etc/my.cnf

[mysqld]

server_id=50

log-bin=/logbir/db5——不指定目录 ，就是默认  /var/lib/mysql  第一是目录 第二个是文件

binlog-format="mixed"

:wq

创建数据：

mysql> creat table t10(id int);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table t10(id int)' at line 1

mysql> create table t10(id int);

Query OK, 0 rows affected (0.04 sec)

mysql> insert into t10 values(333);

Query OK, 1 row affected (0.03 sec)

mysql> insert into t10 values(666);

Query OK, 1 row affected (0.03 sec)

mysql> insert into t10 values(999);

Query OK, 1 row affected (0.04 sec)

mysql> select * from t10;

+------+

| id   |

+------+

|  333 |

|  666 |

|  999 |

+------+

3 rows in set (0.00 sec)

另起一个终端：查看日志记录：

[root@host50 mysql]# mysqlbinlog   /logdir/db50.000001   | grep insert

insert into t10 values(333)

insert into t10 values(666)

insert into t10 values(999)

日志记录sql命令格式：

偏移量：

时间点

[root@host50 mysql]# mysqlbinlog  选项     /logdir/db50.000001  

[root@host50 mysql]# mysqlbinlog   /logdir/db50.000001  

--start-position  =数字

--stop-position=数字

--start-datetime="2018-05-03 23:25:15"

--stop-datetime="2018-05-03 23:25:15"

执行日志文件

模拟数据丢失：

mysql> select * from t10;

+------+

| id   |

+------+

|  333 |

|  666 |

|  999 |

+------+

3 rows in set (0.00 sec)

mysql> delete from t10 where id in (999,666);

Query OK, 2 rows affected (0.02 sec)

mysql> select * from t10;

+------+

| id   |

+------+

|  333 |

+------+

1 row in set (0.00 sec)

查看操作记录的文件：

[root@host50 ~]# mysqlbinlog  /logdir/ db50.000012 ——最新的操作记录，记录在数值最大的文件里

[root@host50 ~]# ls /logdir/

db50.000001  db50.000004  db50.000007  db50.000010  db50.index

db50.000002  db50.000005  db50.000008  db50.000011

db50.000003  db50.000006  db50.000009  db50.000012

每次刷新 都会有新的文件产生??

根据日志记录。重新恢复数据

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# mysqlbinlog  --start-position=1095    --stop-position=1194  /logdir/db50.000001  | mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@host50 ~]# mysqlbinlog  --start-position=776    --stop-position=875  /logdir/db50.000001  | mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

BEGIN 开始 到COMMIT 结束  ，也可以通过时间进行恢复数据

[root@host50 ~]# mysqlbinlog  --start-datetime="2018-07-17 14:12:15" --stop-datetime="2018-07-17 14:21:18"   /logdir/db50.000001  | mysql -uroot -p123456

mysql> select * from t10;

+------+

| id   |

+------+

|  333 |

|  999 |

|  666 |

+------+

3 rows in set (0.00 sec)

日志记录格式——3种格式

1.statement :记录每一条修改的sql的命令都会记录到binlog日志中。

2.row不记录sql命令的上下文相关信息，只记录那条sql命令被修改过。

3.mixed 是以上两种的结合体。

mysql> show variables like "binlog_format";——查看当前文件记录的格式

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

[root@host50 ~]# vim /etc/my.cnf  ——修改日志记录的格式

[mysqld]

server_id=50

log-bin=/logdir/db50

binlog-format="mixed"

手动生成日志文件

[root@host50 ~]# ls /logdir/

db50.000001  db50.000002  db50.000003   db50.index

——那一个数字大，新产生的记录就会存储的那一个日志文件下,不能一个库指定一个日志文件，记录永远保存数字最大的日志文件

[root@host50 ~]# cat /logdir/db50.index 

/logdir/db50.000001

/logdir/db50.000002

/logdir/db50.000003

  flush logs;每一次刷新就不会新成成一个新的日志文件记录

mysql> show master status; ——查看当前库的记录日志保存文件

+-------------+----------+--------------+------------------+-------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------+----------+--------------+------------------+-------------------+

| db50.000003 |      154 |              |                  |                   |

+-------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

产生新的日志文件的四种方法：

[root@host50 ~]# systemctl  restart  mysqld

[root@host50 ~]# mysql -uroot -p123456 -e "flush logs"

[root@host50 ~]# mysqldump -uroot -p123456  --flush-logs db3 >   /bakdir/db.sql

mysql> flush  logs;

如何利用新的日志记录恢复数据：

删除日志文件

命令行删除

rm -rf /logdir/*

sql命令行删除

mysql> purge master logs to "db50.000005";——删除05之前的所有日志文件

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    6

Current database: db3

Query OK, 0 rows affected (0.11 sec)

[root@host50 ~]# ls /logdir/

db50.000005  db50.000006  db50.index

[root@host50 ~]# cat  /logdir/db50.index 

/logdir/db50.000005

/logdir/db50.000006

mysql> reset master;——删除所有的数据日志文件

Query OK, 0 rows affected (0.12 sec)

[root@host50 ~]# ls /logdir/

db50.000001  db50.index

生产环境使用日志文件恢复数据

可以把日志文件，拷贝到其他数据库中保存下来，用来恢复数据；

DBA 岗位最重要的技术