MySQL数据备份如何做?逻辑备份工具MySQLdump案例讲解

挖数网精选
挖数网精选
挖数网精选
352
文章
0
评论
2020-04-2702:05:00 评论 158 9264字
摘要

我们知道对数据进行备份很重要,出现非正常操作可以进行对数据进行恢复,下面我们就来使用一下mysql数据库自带的一个逻辑备份工具mysqldump。本文分类详细,希望这篇文章能够带给你满满收获。

我们知道对数据进行备份很重要,出现非正常操作可以进行对数据进行恢复,下面我们就来使用一下mysql数据库自带的一个逻辑备份工具mysqldump

一. 简单概述

mysqldump:mysql数据自带的一个备份命令

1. 语法格式

    mysqldump -u用户名 -p 数据库名 > 备份的文件名

    2. mysqldump逻辑备份的工作原理

    • mysqldump命令备份数据的过程,实际是把数据从mysql库里以逻辑的sql语句形式直接输出或生成备份文件的过程

    • 恢复的时候就是把sql语句在数据库里面执行一遍的过程

    3. mysqldump帮助文档

      [root@ctos3 ~]# mysqldump --help > 1.txt

      [root@ctos3 ~]# cat 1.txt

      注:该部分剩余代码内容过多,以单独整理成文档,小伙伴添加客服领取。

      4. mysqldump备份使用场景

      • 迁移或者升级数据库时

      • 增加从库时候

      • 因为硬件或特殊异常情况,主库或从库宕机

      • 人为的DDL,DML语句,主从库没办法了,所有库都会执行

      • 跨机房灾备,需要备份

      二. mysqldump的一些关键参数

      • -B --databases:指定多个库。增加建库语句和use语句

      • --compact:去掉注释,适合调试输出

      • -A --all-databases:备份所有库

      • -F:刷新binlog 日志

      • --master-data:增加binlog日志文件名及对应的位置点

      • -x --lock-all-tables:全局的读锁,会阻止对所有表的写入操作

      • -l:该锁不会阻止读,也不会阻止新的数据插入

      • -d:只备份表结构

      • -t:只备份数据

      • --signle-transaction:适合innodb事务数据库备份

      在介绍相关参数的时候,我们先来了解一下-e参数

      作用:加上-e参数可以直接在外面交互式查看数据库里面的东西,而不用登录到数据库里面

      例一:查看数据库

        [root@cots3 ~]# mysql -uroot -p -e "show databases;"Enter password:

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

        | Database |

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

        | information_schema |

        | data |

        | db |

        | mysql |

        | performance_schema |

        | sys |

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

        例二:可以执行多个操作,如进入某个数据库然后查看表

          [root@cots3 ~]# mysql -uroot -p -e "use data;show tables;"Enter password:

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

          | Tables_in_data |

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

          | Student |

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

          例三:查看数据库连接情况,也可以show full processlist查看完整的语句

            [root@cots3 ~]# mysql -uroot -p -e "show processlist;"Enter password:

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

            | Id | User | Host | db | Command | Time | State | Info |

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

            | 31 | root | localhost | NULL | Query | 0 | starting | show processlist |

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

            参数介绍:

            • id:进程

            • user:用户

            • host:连接的主机

            • db:使用的哪个库

            • command:执行了什么命令

            • time:多长时间

            • state:状态

            • info:信息

            1. -B参数

            作用:如果删除了数据库,不加-B参数,备份回来是会报错的,除非将数据库创建回来,备份时候加了-B参数会有自动建库语句

            例子:我们来演示以下加-B和不加-B的效果

            注:演练部分代码过多,已整理成文档,请小伙伴添加客服领取。

            2. -d参数:只备份表结构

            例子:只备份data库的所有表的结构

              [root@cots3 ~]# mysqldump -uroot -p --compact -d data

              Enter password:

              /*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;

              CREATE TABLE `Student` (

              `id` int(4) NOT NULL AUTO_INCREMENT,

              `name` char(20) NOT NULL,

              `age` varchar(16) NOT NULL,

              PRIMARY KEY (`id`)

              ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;

              CREATE TABLE `test` (

              `id` int(4) NOT NULL AUTO_INCREMENT,

              `name` char(20) NOT NULL,

              PRIMARY KEY (`id`)

              ) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;

              查看test表结构

                [root@cots3 ~]# mysqldump -uroot -p --compact -d data test

                Enter password: /*!40101 SET @saved_cs_client = @@character_set_client */;

                /*!40101 SET character_set_client = utf8 */;

                CREATE TABLE `test` (

                `id` int(4) NOT NULL AUTO_INCREMENT,

                `name` char(20) NOT NULL,

                PRIMARY KEY (`id`)

                ) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;

                提示:--compact参数会提供较少的输出(对调试很有用)

                3. -t参数:只输出数据

                  [root@cots3 ~]# mysqldump -uroot -p --compact -t data

                  Enter password:

                  INSERT INTO `Student` VALUES (1,"aa","19"),(2,"bb","20"),(3,"张冲","21");

                  4. 可以使用gzip对备份的数据进行压缩

                  好处:使用压缩备份的话将会缩小文件大小,减少占用空间

                    [root@cots3 ~]# mysqldump -uroot -p -B data | gzip > /opt/mysql.bak_sql.gz

                    Enter password:

                    [root@cots3 ~]# ls -lh /opt/total 8.0K-rw-r--r-- 1 root root 2.6K Mar 12 06:32 mysql.bak_B.sql-rw-r--r-- 1 root root 851 Mar 12 07:56 mysql.bak_sql.gz

                    三. 备份库

                    1. 备份单个库

                    [root@cots3 ~]# mysqldump -uroot -p -B data > /opt/mysql.bak_data.sql

                    Enter password:

                    2. 备份多个库

                      [root@cots3 ~]# mysqldump -uroot -p -B data db | gzip > /opt/mysql.bak.sql.gz

                      Enter password:

                      提示:-B参数是关键,在-B参数后指定库名,空格隔开备份多个库

                      注意:在备份多个数据库的时候最好使用root去备份,要不然如果使用别的用户取备份,在创建用户的时候如果没有指定那个库,那么就会相对应的备份不了

                      演示效果:

                        [root@cots3 ~]# mysql -uroot -p -e "grant all privileges on db.* to test@localhost identified by "guoke123";" #创建一个test用户,指定管理db库

                        Enter password:

                        [root@cots3 ~]# mysqldump -utest -p -B data db | gzip > /opt/test_bak1.sql.gz #备份到其他库就会报错,没有权限

                        Enter password:

                        mysqldump: Got error: 1044: Access denied for user "test"@"localhost" to database "data" when selecting the database

                        [root@cots3 ~]# mysqldump -utest -p -B db | gzip > /opt/test_bak1.sql.gz #备份自己管理的就可以

                        Enter password:

                        3. 分库备份

                        简单理解:分库就是将数据库里面的库分开来进行备份

                        注意点:每个库都可以用对应备份的库作为库名结尾加.sql

                        分库备份的意义:有时一个企业的数据库里会有多个库,例如(www,guoke,blog),但是有时候出问题的可能只是某一个库,如果在备份时把所有的库都备份成一个数据文件的话,恢复某一个库的数据时就比较麻烦了

                        备份方法:

                        • 使用一条命令完成

                        [root@cots3 ~]# mkdir /opt/back

                        [root@cots3 ~]# mysql -uroot -p -e "show databases;" | grep -Evi "database|infor|per|sys" | sed -r "s#^([a-z].*$)#mysqldump -uroot -p -B 1|gzip >/opt/back/1.sql.gz#g" | bash

                        Enter password:

                        Enter password:

                        Enter password:

                        Enter password:

                        [root@cots3 ~]# ls -l /opt/back/total 232-rw-r--r-- 1 root root 851 Mar 12 08:14 data.sql.gz-rw-r--r-- 1 root root 1212 Mar 12 08:14 db.sql.gz-rw-r--r-- 1 root root 229070 Mar 12 08:14 mysql.sql.gz

                        提示:为什么是输入四次密码呢,因为第一次是登录的密码,其余3次是备份3个库的时候使用

                        命令解释:首先使用grep过滤出想要备份的库,然后使用sed来进行替换,-r是支持扩展表达式

                        如果库少的话就一个库对一个应该文件,一个一个来,如果库多久写脚本,用for循环

                          [root@cots3 ~]# mkdir /opt/bak

                          [root@cots3 ~]# cat fenku.sh

                          #!/bin/bashfor dbname in `mysql -uroot -p"guoke123" -e "show databases;" | grep -Evi "database|infor|per|sys"`do

                          mysqldump -uroot -p"guoke123" --events -B $dbname |gzip > /opt/bak/${dbname}.sql.gz

                          done

                          [root@cots3 ~]# ls -l /opt/bak/ #查看效果

                          total 232-rw-r--r-- 1 root root 868 Mar 12 08:23 data.sql.gz-rw-r--r-- 1 root root 1230 Mar 12 08:23 db.sql.gz-rw-r--r-- 1 root root 229088 Mar 12 08:23 mysql.sql.gz

                          四. 备份表

                          1. 备份单个表

                          语法格式:mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名

                          例子:

                            [root@cots3 ~]# mysqldump -uroot -p db test > /opt/mysql_test.sql

                            Enter password:

                            [root@cots3 ~]# ls -l /opt/ | grep mysql_test-rw-r--r-- 1 root root 1934 Mar 12 08:26 mysql_test.sql

                            提示:备份一个表的时候不要加-B,要不然所有表都会出来,就会报错

                            2. 备份多个表

                            语法格式:mysqldump -u 用于名 -p 数据库名 表名1 表名2 > 备份的文件名

                            例子:

                            [root@cots3 ~]# mysqldump -uroot -p db guoke test > /opt/mysql_table.sql

                            3. 使用脚本进行分库分表备份

                              [root@ctos3 ~]# cat bak.sh

                              #!/bin/bash

                              MYUSER="root"MYPASS="guoke123"MYLOG="mysql -u$MYUSER -p$MYPASS -e"MYDUMP="mysqldump -u$MYUSER -p$MYPASS -x -F"DBLIST=$($MYLOG "show databases;" | sed 1d | grep -Ev "info|mysq|per|sys")

                              DIR=/backup

                              [ ! -d $DIR ] && mkdir $DIR

                              cd $DIRfor dbname in $DBLISTdo

                              TABLIST=$($MYLOG "show tables from $dbname;" | sed 1d)for tabname in $TABLISTdo

                              mkdir -p $DIR/$dbname

                              $MYDUMP $dbname $tabname --events |gzip > $DIR/${dbname}/${tabname}_$(date +%F_%T).sql.gz

                              done

                              done

                              备份的效果

                                [root@ctos3 ~]# ls -l /backup/total 0

                                drwxr-xr-x. 2 root root 45 Mar 13 09:14 datadrwxr-xr-x. 2 root root 48 Mar 13 09:14 db

                                [root@ctos3 ~]# ls -l /backup/data/total 4-rw-r--r--. 1 root root 742 Mar 13 09:14 test_2020-03-13_09:14:26.sql.gz

                                [root@ctos3 ~]# ls -l /backup/db/total 4-rw-r--r--. 1 root root 699 Mar 13 09:14 student_2020-03-13_09:14:27.sql.gz

                                五. 恢复数据

                                1. 一般的恢复数据库命令

                                  [root@cots3 ~]# mysql -uroot -p < /opt/mysql_test1.sql #备份时加-B参数恢复方法

                                  [root@cots3 ~]# mysql -uroot -p test < /opt/mysql_test1.sql #备份时不加-B参数方法

                                  2. 恢复分库分表备份的数据

                                  思路:先将所有库找出来,然后使用for循环将其恢复。

                                  提示:如果备份的时候是压缩了,可以先解压,或者可以使用sed过滤数据库名字出来,使用for循环恢复

                                  例子:

                                  (1)首先备份所有数据库

                                    [root@cots3 ~]# mysql -uroot -p -e "show databases;" | grep -Evi "database|infor|per|sys|mysql" | sed -r "s#^([a-z].*$)#mysqldump -uroot -p -B 1|gzip >/opt/back/1.sql.gz#g" | bash

                                    Enter password:

                                    Enter password:

                                    Enter password:

                                    [root@cots3 ~]# ls -l /opt/back/total 8-rw-r--r-- 1 root root 851 Mar 12 08:48 data.sql.gz-rw-r--r-- 1 root root 1212 Mar 12 08:48 db.sql.gz

                                    (2)将数据库删除,进行解压缩再进行恢复

                                      mysql> drop database data;

                                      Query OK, 2 rows affected (0.02 sec)

                                      mysql> drop database db;

                                      Query OK, 3 rows affected (0.02 sec)

                                      (3)将备份的文件解压缩

                                        [root@cots3 ~]# cd /opt/back/[root@cots3 back]# ls

                                        data.sql.gz db.sql.gz

                                        [root@cots3 back]# gunzip *[root@cots3 back]# ls

                                        data.sql db.sql

                                        (4)使用for循环恢复

                                          [root@cots3 ~]# for dbname in `ls /opt/back/*.sql`;do mysql -uroot -p < ${dbname};done

                                          Enter password:

                                          Enter password:

                                          3. 使用source恢复数据库

                                          方法:登录数据库mysql>source 备份文件

                                          例子:

                                            [root@cots3 bak]# mysql -uroot -p -e "drop database data;" #将data库删除

                                            mysql> system ls /opt/bak

                                            data.sql db.sql mysql.sql

                                            mysql> source /opt/bak/data.sql #进行恢复

                                            六. 全量备份与增量备份

                                            1. 全量备份

                                            全量备份就是把数据库中所有的数据进进行备份

                                            例子:

                                              [root@cots3 ~]# mysqldump -uroot -p --events -F -A -B |gzip > /opt/back/mysqlback_$(date +%F).sql.gz #备份所有库

                                              Enter password:

                                              [root@cots3 ~]# ls -l /opt/back/mysqlback_2020-03-12.sql.gz

                                              -rw-r--r-- 1 root root 186470 Mar 12 10:01 /opt/back/mysqlback_2020-03-12.sql.gz

                                              [root@cots3 ~]# mysqldump -uroot -p --events -F -B data | gzip > /opt/back/mysqlbak_$(date +%F).sql.gz #备份单个库

                                              Enter password:

                                              2. 增量备份

                                              增量备份就是上一次全量备份之后,到下一次全备的中间的备份就是增量备份

                                              企业场景全量备份和增量备份部署:

                                              • 中小公司,全量一般是每天一次,业务流量低估执行全备,备份时会锁表

                                              按天备份的优缺点:

                                              优点:恢复时间短,维护成本低

                                              缺点:占用空间多,占用系统资源多,经常锁表影响用户体验

                                              • 大公司周备,每周六00点一次全量,下周日-下周六00点前都是增量

                                              按周备份的优缺点:

                                              优点:占用空间小,占用系统资源少,无需锁表,用户体验好点

                                              缺点:维护成本高,恢复麻烦,时间长

                                              3. 增量恢复

                                              利用二进制日志binlog和全备进行的恢复过程,称为增量恢复

                                              增量恢复的应用场景:

                                              • 人为操作数据库SQL语句破坏主库

                                              • 只有一个主库

                                              增量恢复的必要条件:开启MySQL的log-bin日志功能

                                                /etc/my.cnf

                                                [mysqld]

                                                log_bin=mysql_bin

                                                server-id=1

                                                七. 使用mysqlbinlog来增量恢复

                                                增量恢复主要是靠binlog日志来进行恢复,mysqlbinlog是用来解析mysql的binlog日志的。

                                                mysqlbinlog的日志作用:用来记录MySQL内部增删改查等对MySQL数据库有更新的内容的记录

                                                提示:查看binlog日志需要使用mysqlbinlog打开

                                                注:该部分剩余代码内容过多,以单独整理成文档,小伙伴添加客服领取。

                                                恢复方法:

                                                1. 基于位置点的增量恢复

                                                  [root@ctos3 data]# pwd/application/mysql/data

                                                  [root@ctos3 data]# ls -l | grep bin-rw-r-----. 1 mysql mysql 1035 Mar 13 08:44 mysql_bin.000001-rw-r-----. 1 mysql mysql 154 Mar 13 08:44 mysql_bin.000002-rw-r-----. 1 mysql mysql 38 Mar 13 08:44 mysql_bin.index

                                                  #指定开始位置到结束位置,从pos466开始到531结束

                                                  [root@ctos3 data]# mysqlbinlog --no-defaults mysql_bin.000001 --start-position=466 --stop-position=531 -r pos.sql

                                                  [root@ctos3 data]# ls -l | grep pos.sql-rw-r-----. 1 root root 936 Mar 13 08:47 pos.sql

                                                  #指定开始位置到文件结束

                                                  [root@ctos3 data]# mysqlbinlog --no-defaults mysql_bin.000001 --start-position=531 -r pos531-end.sql

                                                  [root@ctos3 data]# ls -l | grep 531-rw-r-----. 1 root root 2450 Mar 13 08:49 pos531-end.sql

                                                  #从文件开头到指定位置结束

                                                  #提示:关键参数为at,指定开始和结束的位置

                                                  2. 指定时间来恢复

                                                    [root@ctos3 data]# mysqlbinlog --no-defaults mysql-bin.000001 --start-datetime="2020-1-22 17:30:40" --stop-datetime="2020-3-22 17:30:58" -r time.sql

                                                    八. 生产场景不同引擎备份命令

                                                    myisam:

                                                      [root@ctos3 ~]# mysqldump -uroot -p -A -B --master-data=2 -x --events | gzip > /opt/all.sql.gz

                                                      innodb:

                                                      [root@ctos3 ~]# mysqldump -uroot -p -A -B --master-data=2 --events --single-transaction | gzip > /opt/all_1.sql.gz

                                                      End.

                                                      作者:guoke-boy

                                                      来源:博客园

                                                      https://www.cnblogs.com/guoke-boy/p/12458249.html

                                                      本文为转发分享,转载请联系原作者

                                                      • 我的微信公众号
                                                      • 微信扫一扫
                                                      • weinxin
                                                      • 我的微信公众号
                                                      • 微信扫一扫
                                                      • weinxin
                                                      匿名

                                                      发表评论

                                                      匿名网友 填写信息

                                                      :?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: