数据库管理入门之五（数据库管理 入门）

一、数据备份

1.1数据备份目的：

当数据硬删除或误操作删除数据或硬件损坏等导致数据丢失，通过数据备份后实现将数据还原的目的；

1.2数据备份方式：

1.2.1物理备份：直接拷贝或压缩库和表对应的文件；

1）优点：简单粗暴

2）缺点：随着数据库数据的增长，占据存储空间越来越大，速度越来越慢，而且跨平台性不好，比如linux的mysql备份数据放到windows的mysql进行恢复，由于不同系统的文件系统格式不一样，不一定兼容。

3）如果存储引擎是innodb的话，由于有事务日志 （备份数据并没有将事务日志备份），不适合进行物理备份

4）物理备份适合myisam，因为没有事务及事务回滚；

5）eg:将本机的数据库授权信息复制给另一台主机进行使用，使得另一台主机的数据库具有相同的用户信息，无需自己操作创建授权用户；

本机：

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

[root@host50 ~]# scp -r mysql.bak/ 192.168.4.51:/root

另一台主机：

[root@host51 ~]# systemctl stop mysqld

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

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

[root@host51 ~]# ls -l /var/lib/mysql/mysql/

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

[root@host51 ~]# mysql -uroot -p123456

[root@host51 ~]# mysql -uroot -p654321

mysql> select user,host from mysql.user;

6）步骤总结：

1）将本机的授权库文件夹保留权限不变进行物理备份；

2）将备份发送到另一台主机；

3）停掉另一台主机正在运行的mysql服务，并删除存储其授权信息的授权库；

4）将备份的授权库复制到数据库目录下作为新的授权库；

5）此时查看授权库的属主信息发现不是mysql用户，因此将授权库的属主属组权限修改为mysql；

6）重启服务，使用旧密码登陆失败，使用新授权库里的用户及密码登陆成功。

1.2.2逻辑备份：生产环境下用，执行备份操作时，根据备份的库表产生对应的sql命令，把sql命令存储到指定的文件里；

恢复时执行备份文件里所存储的sql命令，相当于把数据库再建立一遍，而且由于存储的是命令，占据空间少。

1.3数据备份策略：

1.3.1完全备份：备份所有数据，有多少备多少，可以是一台服务器上的所有数据，也可以是一个库上的所有数据，一张表上的所有数据；

1.3.2只备份新产生的数据：

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

2）增量备份：备份上次备份（上次备份可以是增量备份、差异备份或完全备份）后所有新产生的数据；

3）相关命令：

完全备份/恢复：

mysqldump命令

mysql命令

增量备份/恢复：

启用mysql服务的binlog日志，实现增量备份/恢复；

安装第三方软件，提供innobackupex命令做增量备份/恢复。

1.4备份方式

1.4.1生产环境下对数据做备份的方法

常用手法1（设计周期性计划任务）：crond 执行 备份脚本（shell python go ruby perl）

常用手法2搭建mysql主从结构实现数据的自动备份：

缺点：主删一条，从也删一条，因此生产环境下一般都是两者结合

1.4.2使用脚本对数据做备份要考虑的问题：

1）备份频率：取决于数据量产生的速度，周

2）备份策略：完全加增量较多，也有完全加差异用

3）备份时间：数据库不忙的时间备份，因为会锁表之类的

4）备份文件名：通常用日期_xx.sql

5）存储空间:可拓展的存储 lv+ raid，将raid设备做成lv

6）完全+差异 对比 完全+增量

t1表数据23:00文件名备份量

1 完全 10条 1.sql 10条

2 差异2条2.sql 2条

3 差异5条3.sql 7条

4 差异3条4.sql 10条

5 差异1条5.sql 11条

6 差异4条6.sql 15条

7差异9条7.sql 24条

特点：冗余备份，备份过的又继续被备份，占据时间长，占据空间大

优点：把之前的（1-6天的备份数据）丢了的都行

t1表数据23:00文件名备份量

1 完全 10条 1.sql 10条

2 增量2条2.sql 2条

3 增量5条3.sql 5条

4 增量3条4.sql 3条

5 增量1条5.sql 1条

6 增量4条6.sql 4条

7增量9条7.sql 9条

特点：任意一天的丢失了都不行，但是可以把备份文件再备一份；

优点：速度快，占据空间少

1.5逻辑备份工具mysqldump

1.5.1基本用法：mysql -uroot -pxxxx 备份的库名 > 库名.sql

一般用root身份备份，因为root对所有库都有权限，默认备份数据存放在当前路径下，可以手动创建文件夹，备份时指定路径；

ps:备份命令错了也会生成相应的文件；

1.5.2备份库的表示

--all-databases或 -A 指备份所有库

数据库名 指备份单个库

数据库名 表名 指备份单张表

-B 数据库1 数据库2 ... 指同时备份几个数据库

eg:

[root@host50 ~]# mysqldump -uroot -p654321 db1 > db1.sql

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

[root@host50 ~]# mysqldump -uroot -p654321 db1 > /mybakdata/db1.sql

[root@host50 ~]# mysqldump -A > /mybakdata/all.sql

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

[root@host50 ~]# ls /mybakdata/

all.sql db1.sql

[root@host50 ~]# mysqldump -uroot -p654321 -A > /mybakdata/all2.sql

[root@host50 ~]# mysqldump -uroot -p654321 A user > /mybakdata/A_user.sql

[root@host50 ~]# mysqldump -uroot -p654321 -B db1 A > /mybakdata/twodb.sql

[root@host50 ~]# mysqldump --help

1.5.3完全恢复数据：

基本用法：mysql -uroot -pxxxx [库名] < 目录/xxx.sql

不管是删表了还是只删了表记录，恢复都可以把表和记录全部恢复回来；

当恢复的是表时，前面一定要跟库名，因为备份文件里记录的是直接建表时开始的记录，而没有记录建在哪个库；

恢复单个库，要先建空库，再进行恢复；

若备份时是同时备份多个库，则文件里有记录创建库的记录，可以不指定库

eg：

mysql> drop database A;

[root@host50 ~]# mysql -uroot -p654321 < /mybakdata/A_user.sql

[root@host50 ~]# mysql -uroot -p654321 A < /mybakdata/A_user.sql

[root@host50 ~]# mysql -uroot -p654321 < /mybakdata/twodb.sql

mysql> create database db1;

[root@host50 ~]# mysql -uroot -p654321 db1 < /mybakdata/db1.sql

ps：

恢复的另一种方法：

create database dbnew;

use dbnew

source /mybakdata/A_user.sql

mysql> create database dbnew;

mysql> use dbnew;

mysql> source /mybakdata/A_user.sql;

eg:每周一半夜23点把db1库的所有数据全部备份到系统的/mybakdata目录里

[root@host50 ~]# mkdir /shell

[root@host50 ~]# vim /shell/db1bak.sh

#!/bin/bash

if [ ! -e /mybakdata ];then

mkdir /mybakdata

fi

day=$(date +%F )

mysqldump -uroot -p654321 db1 > /mybakdata/${day}-db1.sql

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

[root@host50 ~]# /shell/db1bak.sh

//脚本运行后备份时会提示密码输出在命令行，因此运行脚本时将输出信息放入黑洞：

[root@host50 ~]# /shell/db1bak.sh &> /dev/null

crontab -e

00 23 * * 1 /shell/db1bak.sh

1.5.4小结

完全备份缺点：备份和恢复时会锁表，不适合线上服务器，备份间隔期间的数据存在被删危险；

解决方法：结合增量备份；

1.6binlog日志（二进制日志）

记录除查询（select desc show...）外的sql命令,即使得记录发生改变的命令（insert,update,delete...）;

binlog日志默认没有启动，可以在数据库模式下查看binlog默认记录模式：

mysql> show variables like "binlog_format";

ps：在数据库里不允许改，提示只读，需要启动前改，有些版本可以改，但也是临时的，重启服务就失效，需要在配置文件里改；

1.7记录命令时的3种记录方式：

1）statement:每一条修改数据的sql命令都水记录在binlog日志中

2）row：不记录sql语句上下文相关信息（sql命令），仅保存那条记录被修改了；

3）mixed：以上两种格式的混合使用，既记录命令，又记录哪条记录被修改了，常用。

1.8修改记录方式

1）在配置文件里添加：

server_id=xx

//数据库服务器的标识，xx为1-255其中一个数字，一般用主机为作为ip，这样不会与别的数据库服务器冲突；

log-bin

//启动logbin日志，可在后面加路径及文件名以自定义日志的存放位置，但是不能只指定目录不指定名字，要么只指令名字，要么指定路径加名字，格式如下：logbin=xxx/xx

binlog_format=”mixed”

//设置默认记录模式为mixed

max_binlog_size=数字m

//可写可不写，修改默认容量

2）保存并重启服务

此时可在数据库目录下看到多出2个文件，分别是：主机名-bin.编号和主机名-bin.index

-主机名-bin.编号:这个就是logbin日志文件，默认容量>500M 就生成第二个文件，错误的命令不记录；

-主机名-bin.index 记录当前有哪些binlog日志文件的索引文件

3）查看并分析日志文件

查看日志文件需要用专用的文件翻译查看：

基本用法：mysqlbinlog [选项] 日志文件名

eg：

[root@host50 ~]# mysqlbinlog /logdir/plj.000009

...

/*!*/;

# at 295

#180623 14:13:31 server id 50 end_log_pos 397 CRC32 0x6334af7d Querythread_id=3exec_time=0error_code=0

SET TIMESTAMP=1529734411/*!*/;

insert into buydb.a values(100)

/*!*/;

# at 397

#180623 14:13:31 server id 50 end_log_pos 428 CRC32 0x899a2f6e Xid = 5

COMMIT/*!*/;

...

分析关键词：

at：起始位置值

end_log_pos:结束位置值

两者之差为偏移量

at下方记录命令执行时的系统时间

查看日志的常用选项：

--start-position=起始偏移量数值

--stop-position=结束偏移量数值

//乱指也会执行，会显示初始内容且在末尾会报错

//偏移量数值需要从日志里查找

//初始内容无论加不加选项都会显示出来

--start-datetime=”yyyy-mm-dd hh:mm:ss”

--stop-datetime=”yyyy-mm-dd hh:mm:ss”

//时间不能写相同，不然不执行，手动把结束时间调大一点

4）执行binlog日志文件里的sql命令恢复数据

使用 mysqlbinlog 提取历史 SQL 操作，然后通过管道符“|”交给 mysql 命令执行

eg:

[root@host50 ~]# mysqlbinlog --start-position=1886 --stop-position=1987 /logdir/plj.000001 | mysql -uroot -p654321

[root@host50 ~]# mysqlbinlog --start-datetime="2018-06-22 15:42:45" --stop-datetime="2018-06-22 15:42:50" /logdir/plj.000001 | mysql -uroot -p654321

5）手动生成新的binlog日志文件（默认500M才生成），用于想用某个日志文件对某个库单独进行日志操作，方法有如下几种：

1.在数据库模式下执行： flush logs;

//谁的编号最新就往哪里记录

2.mysql -uroot -p654321 -e “flush logs”

//-e:不进入数据库执行命令

3.mysqldump -uroot -p654321 --flush-logs db1 > db1.sql

对某个库进行完全备份时生成新的日志文件，备份完成后再执行一遍flush logs再生成一个新的日志文件，这杨刚刚那个日志文件就只记录了某个库的信息，这样还可以把占据空间小的日志文件拷贝走，以后想恢复直接执行日志文件即可，无需对完全备份的文件进行拷贝这么占空间

4.重启mysql服务也会生成新的日志，但是线上服务器绝对不用，因为一重启数据就没了

6）其他相关指令

show master status;

//查看正在使用的binlog日志信息

purge master logs to “plj000004”;

//删除04之前的日志文件，不包括04，对于已经备份过的数据的日志文件才可以删

reset master;

//删除所有binlog日志，重建新日志，也可以用系统命令rm-rf删日志文件，但是这样不会同步到index文件。

三、innobackupex备份恢复

percona开源软件，提供innobackup命令，在线热备不锁表，增量备份

主要包含两个组建：

xtrabackup:C程序，只支持备份存储引擎是innodb/xtradb的库;

innobackupex:以perl脚本封装xtrabackup,还支持myisam，但是对于myisam是完全备份

安装percona

1）[root@host50 08.dba-1]# rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

//提示需要几个依赖

libev.so.4()(64bit) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要

//模块

perl(DBD::mysql) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要

//软件包

perl(Digest::MD5) 被 percona-xtrabackup-24-2.4.7-1.el7.x86_64 需要

//软件包

[root@host50 08.dba-1]# yum -y install perl-Digest-MD5 perl-DBD-mysql

[root@host50 08.dba-1]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

//默认需要网上下载，这个包里包含了那个模块

[root@host50 08.dba-1]# rpm -qa | grep -i percona

[root@host50 08.dba-1]# rpm -ql percona-xtrabackup-24

//查看是否有innobackupex和xtrabackup命令，可以看出都在/usr/bin/下，说明可以直接执行，无需加路径

2）使用innobackupex

基本用法：innobackupex [选项]

3）innobackup做完全备份：

基本用法：innobackupex --host xxx --port xx --user xx --password xxx [--databases=”备份的数据库名”] 备份存储目录 [--no-timestamp]

ps:

//在本机上执行可以不写host和port;

//若没有指名备份哪些库和表，则默认备份所有；

//备份存储的目录无需提前创建，若目录已有，必须是空目录；

//默认还会生成时间戳表明备份时间，备份时间就存放在时间戳目录里，若不想生成时间戳作为备份的子目录，可以加选项--no-timetamap；

//目录下还生成了备份数据的配置文件，记录本次备份信息,统称为日志文件，用于往后做增量备份，黑色的那些，如图：

备份前：

备份后：

eg：

备份全部库：

[root@host50 ~]# innobackupex --user root --password 654321 /allbak --no-timestamp

备份指定库：

[root@host50 ~]# innobackupex --user root --password 654321 --databses="buydb" /buydballbak --no-timestamp

[root@host50 ~]# innobackupex --user root --password 654321 --databases="buydb paydb" /twodbbak --no-timestamp

备份指定表：

[root@host50 ~]# innobackupex --user root --password 654321 --databases="buydb.a" /allbakatab --no-timestamp

小结：生产环境下多用全部备份或备份单个库

缺点：innobackup恢复时要求数据库目录是空的才可以恢复成功,因此要求每次备份时包含默认的3个库或备份全部库,如：

[root@host50 ~]# innobackupex --user root --password 654321 --databases="buydb mysql performance_schema sys" /buydbfull --no-timestamp

ps：数据库默认的4个基本库：

mysql：授权库

performance_schema当前已有库和表的信息以及创建库和表时的信息

sys当前服务器的运行服务的参数和配置值

in...当前运行时加载到内存的信息

4）innobackup做完全恢复：

前提及关键：要求数据库目录为空

1.重做日志：--apply-log选项

2.停服务，删目录

3.恢复数据：--copy-back 选项

4.将数据库目录权限改回mysql（由于恢复数据的操作是以root身份操作的，因此恢复回来的数据库目录权限都是root的）

5.重启数据库服务，查看数据验证

eg：(将使用innobackupex备份的buydb库进行恢复，备份目录为/buydbfull)

[root@host50 ~]# innobackupex --user root --password 654321 --apply-log /buydbfull

[root@host50 ~]# systemctl stop mysqld

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

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

[root@host50 ~]# innobackupex --user root --password 654321 --copy-back /buydbfull

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

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

[root@host50 ~]# systemctl restart mysqld

ps：

查看是否做了重做日志：

重做前：

[root@host50 buydbfull]# cat xtrabackup_checkpoints

backup_type = full-backuped

重做后：

[root@host50 buydbfull]# cat xtrabackup_checkpoints

backup_type = full-prepare

5）innobackup做增量备份（一般做之前做了完全备份作为首次备份存储目录）

用到的选项：

--incremental=增量文件要存储目录

--incremental-basedir=上次备份文件存储目录

基本用法：

innobackupex --user xx --password xx --databases=”备份库” --incremental xx --incremental-basedir=xx --no-timestamp

//由于上次备份文件那里有记录要备份哪些库，因此--databases可省略；

eg：

[root@host50 ~]# innobackupex --user root --password 654321 --databases="mysql sys performance_schema buydb" /fullbuydb --no-timestamp

[root@host50 ~]# innobackupex --user root --password 654321 --databases="mysql sys performance_schema buydb" --incremental /new1dir --incremental-basedir=/fullbuydb --no-timestamp

[root@host50 ~]# ls /new1dir/

[root@host50 ~]# innobackupex --user root --password 654321 --databases="mysql sys performance_schema buydb" --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp

发现2个目录下的文件都一样

6）innobackup做增量恢复：

--incremental-dir=增量备份目录名

1.重做并合并日志：--apply-log --redo-only 选项

ps：先对完全备份目录做重做及合并日志，使得之后合并的日志会把数据不断合并到完全备份的目录下，最终完全备份目录下的日志即综合了所有增量备份日志的内容

2.清空数据库目录

3.停止服务

4.恢复数据

5.启动服务

eg:

[root@host50 ~]# innobackupex --user root --password 654321 --apply-log --redo-only /fullbuydb

[root@host50 ~]# innobackupex --user root --password 654321 --apply-log --redo-only /fullbuydb --incremental-dir=/new1dir

[root@host50 ~]# innobackupex --user root --password 654321 --apply-log --redo-only /fullbuydb --incremental-dir=/new2dir

[root@host50 ~]# systemctl stop mysqld

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

[root@host50 ~]# innobackupex --copy-back /fullbuydb

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

[root@host50 ~]# systemctl restart mysqld

7）在innobackupex完全备份数据中，恢复某个表的数据

默认innodb表创建的时候在系统的数据库目录下会生成2个文件：.ibd文件和.frm文件

.ibd：表空间文件（存放表的数据和索引）

.frm：表结构文件（只存放表结构，不存放记录）

恢复原理：将备份目录下的对应的表空间文件替换成数据库目录下的表空间文件，因此，在某个库的完全备份情况下去恢复某个表是不需要删除整个数据库目录的，只需要替换掉关于该表的空间文件即可；

环境准备：在buydb库下有表a

[root@host50 ~]# innobackupex --user root --password 654321 --database="buydb" /buydbfull2 --no-timestamp

登陆数据库

mysql> drop table buydb.b;

恢复步骤：

1.从备份数据里导出表信息（--export) ；

ps：备份的目录下对应的库目录下会多出了.cfg .exp文件

2.创建删除的表（创建的表的表结构必须与之前的结构相同），若只是表记录被删则跳过此部；

3.在数据库模式下删除表空间文件.ibd

4.将导出的表信息文件拷贝到数据库目录下(.cfg .ibd .exp)并改权限为mysql

5.在数据库模式下导入表空间文件

6.查看数据

eg:

[root@host50 ~]# innobackupex --user root --password 654321 --databses="buydb" --apply-log --export /buydbfull2

mysql> create table buydb.b(name char(10));

[root@host50 fullbuydb]# ls /buydbfull2/buydb/

a.cfg a.exp a.frm a.ibd b.cfg b.exp b.frm b.ibd db.opt

[root@host50 fullbuydb]# ls /var/lib/mysql/buydb/

a.frm a.ibd b.frm b.ibd db.opt

mysql> alter table buydb.b discard tablespace;

[root@host50 fullbuydb]# ls /var/lib/mysql/buydb/

a.frm a.ibd b.frm db.opt

mysql> system cp /buydbfull2/buydb/b.{ibd,cfg,exp} /var/lib/mysql/buydb/

mysql> system chown mysql:mysql /var/lib/mysql/buydb/b.*

mysql> alter table buydb.b import tablespace;

mysql> select * from buydb.b;

[root@host50 ~]# rm -rf /var/lib/mysql/buydb/b.{cfg,exp}

8）增量备份存储目录下的某些文件解读

1.事务日志文件：

ibdate1：未提交的命令，未正确执行的命令，每一条命令都有一个lsn（日志序列号）

ib_logfile1:提交后的

ib_logfile0：提交后的

2.xtrabackup_checkpoints

记录当前目录下备份数据时的日志信息与备份类型

//full-backuped完全备份

//incremental 增量备份

//记录了日志序列号从哪到哪

3.backup-my.cnf

备份时分配的系统资源，不修改则为默认

4..meta增量备份日的志