精华内容
下载资源
问答
  • MySQL 定时备份

    2021-04-21 04:00:32
    MySQL 定时备份众所周知MySQL数据库在互联网服务中提供的重要的角色,所以他的稳定性至关重要所以对于数据的备份也是非常重要的,下面介绍一下:mysqldumpxtrabackup之innobackupexshell脚本crontab之自动执行备份...

    MySQL 定时备份

    众所周知MySQL数据库在互联网服务中提供的重要的角色,所以他的稳定性至关重要所以对于数据的备份也是非常重要的,下面介绍一下:

    mysqldump

    xtrabackup之innobackupex

    shell脚本

    crontab之自动执行备份

    mysqldump

    bin/mysqldump 是MySQL提供的内置命令,方便使用者导出mysql数据表以及数据,具体的使用方式查看 bin/mysqldump --help, 这里我们简单介绍几个命令

    1. bin/mysqldump --opt -uroot -p --databases db1 db2 db3 > /tmp/db.sqlopt Same as

    –add-drop-table,

    –add-locks,

    –create-options,

    –quick,

    –extended-insert,

    –lock-tables,

    –set-charset,

    and –disable-keys.

    Enabled by default, disable with –skip-opt.

    2. 还原mysql备份内容

    2.1 在系统命令行中,输入如下实现还原:1bin/mysql -uroot -p123456 < /tmp/db.sql

    2.2 在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原:1mysql> source /tmp/db.sql;

    Xtrabackup

    Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁)。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。

    1. Xtrabackup 特点备份过程快速、可靠

    备份过程不会打断正在执行的事务

    能够基于压缩等功能节约磁盘空间和流量

    自动实现备份检验

    还原速度快

    2. Xtrabackup 安装

    这里就不说了,网上很多教程,最简单 yum install percona-xtrabackup

    备份shell脚本1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    40

    41

    42

    43

    44

    45

    46

    47

    48

    49

    50

    51

    52

    53

    54

    55

    56

    57

    58

    59

    60

    61

    62

    63

    64

    65

    66

    67

    68

    69

    70

    71

    72

    73

    74

    75

    76

    77

    78#!/bin/bash

    name=root

    host=192.168.0.1

    DATE=`date +"%Y%m%d"`

    allbackup="/home/dbback/hotbackup/"

    ###所有集合顺序上下一一对应吗,即vpwd[0]、vchk[0]、vsocket[0]、vport[0]代表一个数据库实例

    #root用户密码集

    vpwd=("")

    #配置文件集

    vchk=("/data/mysql-5.5.43/etc/my.cnf")

    #socket集

    vsocket=("/tmp/mysql.sock")

    #设置端口集

    vport=("3306")

    ###获取端口个数

    k=${#vport[*]}

    if [ -d $allbackup ];

    then

    echo "完全备份目录存在"

    else

    echo "完全备份目录不存在,开始创建......."

    /bin/mkdir -p $allbackup

    fi

    ###for循环,循环备份不同端口数据库

    for ((l=0;l

    do

    if [ -d $allbackup/$DATE/$host/${vport[$l]} ];

    then

    echo "完全备份目录存在"

    else

    echo "完全备份目录不存在,开始创建......."

    /bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}

    #fi

    find $allbackup -type d -mtime +1 -exec rm -rf {} \;

    cd $allbackup/$DATE/$host/${vport[$l]}

    innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]} --slave-info --user=root --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8 ./ 2>bak.$DATE.log

    result=`cat bak.$DATE.log | grep "completed OK\!" | grep -v prints`

    coun=1

    if [[ -z $result ]];

    then

    if [[ $coun -lt 2 ]];

    then

    rm -rf $allbackup/$DATE/$host/${vport[$l]}

    /bin/mkdir -p $allbackup/$DATE/$host/${vport[$l]}

    cd $allbackup/$DATE/$host/${vport[$l]}

    innobackupex --defaults-file=${vchk[$l]} --ibbackup=xtrabackup --socket=${vsocket[$l]} --slave-info --user=root --password=${vpwd[$l]} --no-timestamp --parallel=4 --throttle=300 --compress --compress-threads=8 ./ 2>bak.$DATE.log

    coun=`expr $coun + 1`;

    echo $coun

    fi

    else

    rsync -zavP --password-file=/etc/rsync.pass --bwlimit=30000 $allbackup/$DATE dbback@192.168.1.1::KAPROBAK/DBBAK

    echo "备份文件推送成功!"

    fi

    fi

    done

    –defaults-file:指定my.cnf参数文件的位置[此配置文件里必须指定datadir]

    –apply-log:同xtrabackup的–prepare参数,一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据 文件仍处理不一致状态。–apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

    –copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir

    –remote-host=HOSTNAME: 通过ssh将备份数据存储到进程服务器上

    –stream=[tar]:备份文件输出格式, 该文件可在XtarBackup binary文件中获得. 在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话,xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数–tmpdir指定目录来解决这个问题.

    –tmpdir=DIRECTORY:当有指定–remote-host or –stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir

    –redo-only –apply-log:强制备份日志时只redo,跳过rollback,这在做增量备份时非常必要

    –use-memory=*:该参数在prepare的时候使用,控制prepare时innodb实例使用的内存

    –databases=LIST:列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份

    –slave-info:备份从库, 加上–slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE=’’, MASTER_LOG_POS=0

    –socket=SOCKET:指定mysql.sock所在位置,以便备份进程登录mysql.

    Linux 自动执行crontab

    crontab 就不用多说了,如果有不明白的自行搜索

    1

    2###数据库备份及备份文件推送至备份服务器

    30 2 * * * /bin/bash /home/op/shell/mysqlhotbackup5.5.sh 2 > /home/op/log/mysqlhotbackup5.5.log

    展开全文
  • MySQL 定时备份参考 zone7_ 的 实战-MySQL定时备份系列文章参考 zmcyu 的 mysql数据库的完整备份、差异备份、增量备份更多binlog的学习参考马丁传奇的 MySQL的binlog日志,这篇文章写得认真详细,如果看的认真的话,...

    MySQL 定时备份

    参考 zone7_ 的 实战-MySQL定时备份系列文章

    参考 zmcyu 的 mysql数据库的完整备份、差异备份、增量备份

    更多binlog的学习参考马丁传奇的 MySQL的binlog日志,这篇文章写得认真详细,如果看的认真的话,肯定能学的很好的。

    如果查看binlog是出现语句加密的情况,参考 mysql row日志格式下 查看binlog sql语句

    说明

    产品上线后,数据非常非常重要,万一哪天数据被误删,那么就gg了,准备跑路吧。

    所以要对线上的数据库定时做全量备份和增量备份。

    增量备份的优点是没有重复数据,备份量不大,时间短。但缺点也很明显,需要建立在上次完全备份及完全备份之后所有的增量才能恢复。

    MySQL没有提供直接的增量备份方法,但是可以通过mysql二进制日志间接实现增量备份。二进制日志对备份的意义如下:

    二进制日志保存了所有更新或者可能更新数据的操作

    二进制日志在启动MySQL服务器后开始记录,并在文件达到所设大小或者收到flush logs 命令后重新创建新的日志文件

    只需定时执行flush logs 方法重新创建新的日志,生成二进制文件序列,并及时把这些文件保存到一个安全的地方,即完成了一个时间段的增量备份。

    全量备份

    mysqldump --lock-all-tables --flush-logs --master-data=2 -u root -p test > backup_sunday_1_PM.sql

    参数 --lock-all-tables

    对于InnoDB将替换为 --single-transaction。

    该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用 --quick 选项。

    参数 --flush-logs,结束当前日志,生成并使用新日志文件

    参数 --master-data=2,该选项将会在输出SQL中记录下完全备份后新日志文件的名称,用于日后恢复时参考,例如输出的备份SQL文件中含有:CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002', MASTER_LOG_POS=106;

    参数 test,该处的test表示数据库test,如果想要将所有的数据库备份,可以换成参数 --all-databases

    参数 --databases 指定多个数据库

    参数 --quick或-q,该选项在导出大表时很有用,它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

    参数 --ignore-table,忽略某个数据表,如 --ignore-table test.user 忽略数据库test里的user表

    更多mysqldump 参数,请参考网址

    全量备份脚本shell

    #!/bin/bash

    mysql 数据库全量备份

    用户名、密码、数据库名

    username="root"

    password="tencns152"

    dbName="goodthing"

    beginTime=date +"%Y年%m月%d日 %H:%M:%S"

    备份目录

    bakDir=/home/mysql/backup

    日志文件

    logFile=/home/mysql/backup/bak.log

    备份文件

    nowDate=date +%Y%m%d

    dumpFile="${dbName}${nowDate}.sql"

    gzDumpFile="${dbName}${nowDate}.sql.tgz"

    cd $bakDir

    全量备份(对所有数据库备份,除了数据库goodthing里的village表)

    /usr/local/mysql/bin/mysqldump -u${username} -p${password} --quick --events --databases ${dbName} --ignore-table=goodthing.village --ignore-table=goodthing.area --flush-logs --delete-master-logs --single-transaction > $dumpFile

    打包

    /bin/tar -zvcf $gzDumpFile $dumpFile

    /bin/rm $dumpFile

    endTime=date +"%Y年%m月%d日 %H:%M:%S"

    echo 开始:$beginTime 结束:$endTime $gzDumpFile succ >> $logFile

    删除所有增量备份

    cd $bakDir/daily

    /bin/rm -f *

    这里全量备份只备份了一个数据库,因为如果所有数据库都备份的话,文件太大了。这里的取舍我也不是很清楚,毕竟自己还在学习阶段,没有实际的操作经验。

    增量备份

    检查log_bin是否开启

    进入mysql命令行,执行 show variables like '%log_bin%'

    mysql> show variables like '%log_bin%';

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

    | Variable_name | Value |

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

    | log_bin | OFF |

    | log_bin_basename | |

    | log_bin_index | |

    | log_bin_trust_function_creators | OFF |

    | log_bin_use_v1_row_events | OFF |

    | sql_log_bin | ON |

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

    6 rows in set (0.01 sec)

    如上所示,log_bin 未开启;如果log_bin开启,则跳过第2步,直接进入第3步。

    开启 log_bin,并重启mysql

    编辑 mysql 的配置文件 vim /etc/my.cnf,在 mysqld 下面添加下面2条配置

    [mysqld]

    log-bin=/var/lib/mysql/mysql-bin

    server_id=152

    Tip1: 一定要加 server_id,否则会报错。至于server_id的值,随便设就可以。

    Tip2: log_bin 中间可以下划线_相连,也可以-减号相连。同理server_id也一样。

    重启mysql

    service mysqld restart

    再次在mysql命令行中执行 show variables like '%log_bin%'

    mysql> show variables like '%log_bin%';

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

    | Variable_name | Value |

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

    | log_bin | ON |

    | log_bin_basename | /var/lib/mysql/mysql-bin |

    | log_bin_index | /var/lib/mysql/mysql-bin.index |

    | log_bin_trust_function_creators | OFF |

    | log_bin_use_v1_row_events | OFF |

    | sql_log_bin | ON |

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

    6 rows in set (0.01 sec)

    备份

    进入mysql命令行,执行 show master status;

    mysql> show master status;

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

    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysql-bin.000003 | 430 | | | |

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

    1 row in set (0.00 sec)

    当前正在记录日志的文件名是 mysql-bin.000003

    比如当前数据库test的bk_user只有2条记录

    mysql> select * from test.bk_user;

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

    | id | name | sex | age |

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

    | 1 | 小明 | 男 | 25 |

    | 2 | 小红 | 女 | 21 |

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

    2 rows in set (0.00 sec)

    插入一条新的记录

    mysql> insert into test.bk_user(name, sex, age) values('小强', '男', 24);

    Query OK, 1 row affected (0.02 sec)

    mysql> select * from test.bk_user;

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

    | id | name | sex | age |

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

    | 1 | 小明 | 男 | 25 |

    | 2 | 小红 | 女 | 21 |

    | 5 | 小强 | 男 | 24 |

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

    3 rows in set (0.03 sec)

    执行命令mysqladmin -uroot -p密码 flush-logs,生成并使用新的日志文件

    再次查看当前使用的日志文件,已经变为 mysql-bin.000004 了。

    mysql-bin.000003 则记录着刚才执行的 insert 语句的日志。

    mysql> show master status;

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

    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysql-bin.000004 | 154 | | | |

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

    1 row in set (0.00 sec)

    到这里,其实已经完成了增量备份了。

    恢复增量备份

    首先假装误删数据库记录

    mysql> delete from test.bk_user where id=4;

    Query OK, 1 row affected (0.01 sec)

    mysql> select * from test.bk_user;

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

    | id | name | sex | age |

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

    | 1 | 小明 | 男 | 25 |

    | 2 | 小红 | 女 | 21 |

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

    2 rows in set (0.00 sec)

    从备份的日志文件mysql-bin.000003中恢复数据

    [root@centos56 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p test

    Enter password:

    ERROR 1032 (HY000) at line 36: Can't find record in 'bk_user'

    如果你也遇到这个问题的话,不妨修改 /etc/my.cnf 配置试试。

    我在server_id那一行下添加了 slave_skip_errors=1032 ,然后就执行成功了,不再报错。

    mysql> select * from test.bk_user;

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

    | id | name | sex | age |

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

    | 1 | 小明 | 男 | 25 |

    | 2 | 小红 | 女 | 21 |

    | 5 | 小强 | 男 | 24 |

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

    3 rows in set (0.00 sec)

    增量备份的shell脚本

    #!/bin/bash

    增量备份时复制mysql-bin.00000*的目标目录,提前手动创建这个目录

    BakDir=/home/mysql/backup/daily

    日志文件

    LogFile=/home/mysql/backup/bak.log

    mysql的数据目录

    BinDir=/var/lib/mysql-bin

    mysql的index文件路径,放在数据目录下的

    BinFile=/var/lib/mysql-bin/mysql-bin.index

    这个是用于产生新的mysql-bin.00000*文件

    /usr/local/mysql/bin/mysqladmin -uroot -ptencns152 flush-logs

    Counter=wc -l $BinFile | awk '{print $1}'

    NextNum=0

    这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的

    for file in cat $BinFile

    do

    base=basename $file

    NextNum=expr $NextNum + 1

    if [ $NextNum -eq $Counter ]

    then

    echo $base skip! >> $LogFile

    else

    dest=$BakDir/$base

    #test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去

    if(test -e $dest)

    then

    echo $base exist! >> $LogFile

    else

    cp $BinDir/$base $BakDir

    echo $base copying >> $LogFile

    fi

    fi

    done

    echo date +"%Y年%m月%d日 %H:%M:%S" $Next Bakup succ! >> $LogFile

    定时备份

    执行命令 crontab -e,添加如下配置

    每个星期日凌晨3:00执行完全备份脚本

    0 3 0 /bin/bash -x /root/bash/Mysql-FullyBak.sh >/dev/null 2>&1

    周一到周六凌晨3:00做增量备份

    0 3 1-6 /bin/bash -x /root/bash/Mysql-DailyBak.sh >/dev/null 2>&1

    遇到的问题

    Can't connect to local MySQL server through socket '/tmp/mysql.sock'

    mysqladmin: connect to server at 'localhost' failed

    error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'

    Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists

    去修改mysql的配置文件,添加

    [mysqladmin]

    修改为相应的sock

    socket=/var/lib/mysql/mysql.sock

    执行mysqldump时遇到 Unknown table 'column_statistics' in information_schema (1109)

    [root@centos56 bash]# /usr/local/mysql/bin/mysqldump -uroot -ptencns152 --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > /home/mysql/backup/1.sql

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

    mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'atd' AND TABLE_NAME = 'box_info';': Unknown table 'column_statistics' in information_schema (1109)

    如果使用MySQL 8.0+版本提供的命令行工具mysqldump来导出低于8.0版本的MySQL数据库到SQL文件,会出现Unknown table 'column_statistics' in information_schema的错误,因为早期版本的MySQL数据库的information_schema数据库中没有名为COLUMN_STATISTICS的数据表。

    解决问题的方法是,使用8.0以前版本MySQL附带的mysqldump工具,最好使用待备份的MySQL服务器版本对应版本号的mysqldump工具,mysqldump可以独立运行,并不依赖完整的MySQL安装包,比如在Windows中,可以直接从MySQL安装目录的bin目录中将mysqldump.exe复制到其他文件夹,甚至从一台电脑复制到另一台电脑,然后在CMD窗口中运行。

    当前使用是的MySQL 5.7.22。把5.7.20的 MYSQL_HOME/bin/mysqldump 替换掉 5.7.22的,接着就能顺利执行mysqldump了,也真是奇了怪了。

    展开全文
  • mysql定时备份任务

    2021-01-19 18:12:11
    简介在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解crontab指令,如果你会了请跳到下一个内容mysql...

    简介

    在生产环境上,为了避免数据的丢失,通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解crontab指令,如果你会了请跳到下一个内容mysql备份。

    本文章的mysql数据库是安装在docker容器当中,以此为例进行讲解。没有安装到docker容器当中也可以参照参照。

    contab定时任务

    使用crontab -e来编写我们的定时任务。

    0 5 * * 1 [command]

    前面的5个数字分别代表分、时、日、月、周,后面的 command为你的执行命令。

    假如你需要在每天晚上8点整执行定时任务,那么可以这么写

    0 8 * * * [command]

    扩展:

    crontab -l 可以查看自己的定时任务

    crontab -r 删除当前用户的所有定时任务

    mysql备份

    快速上手

    这里我的mysql数据库是docker容器。假如你需要在每天晚上8点整执行定时任务,那么可以这么写。

    首先执行命令crontab -e。

    0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

    mysql_container 为你的数据库容器名

    mysqldump 是mysql数据库导出数据的指令

    -u 填写root账号

    -p 填写root密码

    database_name 需要备份的数据库名

    /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 备份文件,后面是文件名的格式

    如果你没什么要求,单纯的只是想要备份,那么上面那个命令就可以帮你进行定时备份。

    小坑: mysql备份的时候我使用了docker exec -it mysqldump ... 这样的命令去做bash脚本,因为-i参数是有互动的意思,导致在crontab中执行定时任务的时候,没有输出数据到sql文件当中。所以使用crontab定时的对docker容器进行备份命令的时候不要添加-i参数。

    crontab优化

    我不建议直接在crontab -e里面写要执行的命令,任务多了就把这个文件写的乱七八招了。

    建议把数据库备份的命令写成一个bash脚本。在crontab这里调用就好了

    如:建立一个/var/backups/mysql/mysqldump.sh文件,内容如下

    docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

    然后把文件改为当前用户可执行的:

    chmod 711 /var/backups/mysql/mysqldump.sh

    执行crontab -e命令修改成如下:

    0 20 * * * /var/backups/mysql/mysqldump.sh

    那么这样就比较规范了。

    mysql备份优化

    因为sql文件比较大,所以一般情况下都会对sql文件进行压缩,不然的话磁盘占用就太大了。

    假设你做了上面这一步 crontab优化,我们可以把mysqldump.sh脚本改成下面这样:

    export mysqldump_date=$(date +%Y%m%d_%H%M%S) && \

    docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql && \

    gzip /var/backups/mysql/$mysqldump_date.sql

    find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

    export 在系统中自定义了个变量mysqldump_date,给备份和压缩命令使用

    gzip 为压缩命令,默认压缩了之后会把源文件删除,压缩成.gz文件

    find ... 这行命令的意思为,查询/var/backups/mysql/目录下,创建时间15天之前(-mtime +15),文件名后缀为.sql的所有文件 执行删除命令-exec rm -f {} \;。总的意思就是:mysql的备份文件只保留15天之内的。15天之前的都删除掉。

    数据恢复

    若一不小心你执行drop database,稳住,淡定。我们首先要创建数据库被删除的数据库。

    >mysql create database database_name;

    然后恢复最近备份的数据。恢复备份的命令:

    docker exec -i mysql_container mysql -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

    虽然恢复了备份文件的数据,但是备份时间点之后的数据我们却没有恢复回来。

    如:晚上8点进行定时备份,但是却在晚上9点drop database,那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。这时候就要使用binlog日志了。

    binlog日志

    binlog 是mysql的一个归档日志,记录的数据修改的逻辑,如:给 ID = 3 的这一行的 money 字段 + 1。

    首先登录mysql后查询当前有多少个binlog文件:

    > mysql show binary logs;

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

    | Log_name | File_size | Encrypted |

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

    | binlog.000001 | 729 | No |

    | binlog.000002 | 1749 | No |

    | binlog.000003 | 1087 | No |

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

    查看当前正在写入的binlog

    mysql> show master status\G;

    生成新的binlog文件,mysql的后续操作都会写入到新的binlog文件当中,一般在恢复数据都时候都会先执行这个命令。

    mysql> flush logs

    查看binlog日志

    mysql> show binlog events in 'binlog.000003';

    小知识点:初始化mysql容器时,添加参数--binlog-rows-query-log-events=ON。或者到容器当中修改/etc/mysql/my.cnf文件,添加参数binlog_rows_query_log_events=ON,然后重启mysql容器。这样可以把原始的SQL添加到binlog文件当中。

    恢复数据

    拿回上面例子的这段话。晚上8点进行定时备份,但是却在晚上9点drop database,那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。。

    首先进入到mysql容器后,切换到/var/lib/mysql目录下,查看binlog文件的创建日期

    cd /var/lib/mysql

    ls -l

    ...

    -rw-r----- 1 mysql mysql 729 Jun 19 15:54 binlog.000001

    -rw-r----- 1 mysql mysql 1749 Jun 19 18:45 binlog.000002

    -rw-r----- 1 mysql mysql 1087 Jun 19 20:58 binlog.000003

    ...

    从文件日期可以看出:当天时间为2020-06-21,binlog.000002文件的最后更新时间是 18:45 分,那么晚上8点的备份肯定包含了binlog.000002的数据;

    binlog.000003的最后更新日期为 20:58 分,那么我们需要恢复的数据 = 晚上8点的全量备份 + binlog.000003的 20:00 - 执行drop database命令时间前的数据。

    恢复命令格式:

    mysqlbinlog [options] file | mysql -uroot -proot_password database_name

    mysqlbinlog常用参数:--start-datetime 开始时间,格式 2020-06-19 18:00:00

    --stop-datetime 结束时间,格式同上

    --start-positon 开始位置,(需要查看binlog文件)

    --stop-position 结束位置,同上

    ...

    恢复备份数据和binlog数据前建议先登录mysql后执行flush logs生成新的binlog日志,这样可以专注需要恢复数据的binlog文件。

    首先我们需要查看binlog日志,在哪个位置进行了drop database操作:

    mysql> show binlog events in 'binlog.000003';

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

    | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

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

    | binlog.000003 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.20, Binlog ver: 4 |

    | binlog.000003 | 125 | Previous_gtids | 1 | 156 | |

    | binlog.000003 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

    | binlog.000003 | 235 | Query | 1 | 318 | BEGIN |

    | binlog.000003 | 318 | Rows_query | 1 | 479 | # INSERT INTO `product_category` SET `name` = '床上用品' , `create_time` = 1592707634 , `update_time` = 1592707634 , `lock_version` = 0 |

    | binlog.000003 | 479 | Table_map | 1 | 559 | table_id: 139 (hotel_server.product_category) |

    | binlog.000003 | 559 | Write_rows | 1 | 629 | table_id: 139 flags: STMT_END_F |

    | binlog.000003 | 629 | Xid | 1 | 660 | COMMIT /* xid=2021 */ |

    | binlog.000004 | 660 | Anonymous_Gtid | 1 | 739 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

    | binlog.000004 | 739 | Query | 1 | 822 | drop database hotel_server /* xid=26 */ |

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

    根据上面的日志,我们可以看到,在End_log_pos= 822 的位置执行了drop database操作,那么使用binlog恢复的范围就在2020-06-19 20:00:00 - 660 的位置。为什么是660?因为drop database的上一个事务的提交是660的位置,命令如下:

    mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

    如果你的范围包括了822的位置,那么就会帮你执行drop database命令了。不信你试试?

    执行完上面的命令,你的数据就会恢复到drop database前啦!开不开心,激不激动!

    总结

    因为mysql定时备份是在生产环境上必须的任务。是很常用的。所以我就迫不及待的写博客。当然也很感谢我同事的帮助。这篇文章已经写了三天了,因为我也是在不断地试错,不断的更新文章。避免把错误的知识点写出来。如果帮到你了,关注我一波呗!谢谢。

    如果我的文章帮助到您,可以关注我的微信公众号,第一时间分享文章给您

    展开全文
  • mysql定时备份

    2021-02-07 23:16:40
    mysql安装在/usr/local/mysql目录中mkdri -pv /data/mysqlbak #创建备份目录cd /data/mysqlbakvim dbbackup.sh #编写备份脚本粘帖以下代码,务必更改其中的username,password和dbname。#!/bin/sh/usr/local/mysql/...

    mysql安装在/usr/local/mysql目录中

    mkdri -pv /data/mysqlbak #创建备份目录

    cd /data/mysqlbak

    vim dbbackup.sh #编写备份脚本

    粘帖以下代码,务必更改其中的username,password和dbname。

    #!/bin/sh

    /usr/local/mysql/bin/mysqldump -uuser -ppassword dbname | gzip > /data/mysqlbak/dbname`date  +%Y-%m-%d_%H%M%S`.sql.gz

    cd  /data/mysqlbak

    rm -rf `find . -name ‘*.sql.gz‘ -mtime 10`  #删除10天前的备份文件

    chmod +x dbbackup.sh

    sh -n dbbackup.sh #语法检测

    crontab -e  #用crontab定时执行备份脚本

    00 21 * * * /data/mysqlbak/dbbackup.sh #每天晚上21点00备份

    # * * * * * /data/mysqlbak/dbbackup.sh 每分钟备份一次

    PS

    1、任务调度设置文件的写法

    可用crontab -e命令来编辑,编辑的是/var/spool/cron下对应用户的cron文件,也可以直接修改/etc/crontab文件

    具体格式如下:

    Minute Hour Day Month Dayofweek   command

    分钟     小时   天     月       天每星期       命令

    每个字段代表的含义如下:

    Minute             每个小时的第几分钟执行该任务

    Hour               每天的第几个小时执行该任务

    Day                 每月的第几天执行该任务

    Month             每年的第几个月执行该任务

    DayOfWeek     每周的第几天执行该任务

    Command       指定要执行的程序

    在这些字段里,除了“Command”是每次都必须指定的字段以外,其它字段皆为可选

    字段,可视需要决定。对于不指定的字段,要用“*”来填补其位置。

    举例如下:

    5      *       *         *     *     ls           指定每小时的第5分钟执行一次ls命令

    30     5       *         *     *     ls           指定每天的 5:30 执行ls命令

    30     7       8         *     *     ls           指定每月8号的7:30分执行ls命令

    30     5       8         6     *     ls           指定每年的6月8日5:30执行ls命令

    30     6       *         *     0     ls           指定每星期日的6:30执行ls命令[注:0表     示星期天,1表示星期1,以此类推,也可以用英文来表示,sun表示星期天,mon表示星期一等。]

    30     3     10,20     *     *     ls     每月10号及20号的3:30执行ls命令[注:“,”用来连接多个不连续的时段]

    25     8-11 *           *     *     ls       每天8-11点的第25分钟执行ls命令[注:“-”用来连接连续的时段]

    */15   *       *           *     *     ls         每15分钟执行一次ls命令 [即每个小时的第0 15 30 45 60分钟执行ls命令 ]

    30   6     */10         *     *     ls       每个月中,每隔10天6:30执行一次ls命令[即每月的1、11、21、31日是的6:30执行一次ls 命令。 ]

    每天7:50以root 身份执行/etc/cron.daily目录中的所有可执行文件

    50   7       *             *     *     root     run-parts     /etc/cron.daily   [ 注:run-parts参数表示,执行后面目录中的所有可执行文件。 ]

    2、新增调度任务可用两种方法:

    1)、在命令行输入: crontab -e 然后添加相应的任务,wq存盘退出。

    2)、直接编辑/etc/crontab 文件,即vi /etc/crontab,添加相应的任务。

    展开全文
  • 本篇文章主要讲述了几种方法进行 MySQL 定时备份数据库。 一. mysqldump命令备份数据 在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先...
  • [Docker]MySql 定时备份

    2021-02-06 17:37:34
    所以研究了下,使用mysqldump来定时备份mysql的数据。分析我这块数据库是在容器中运行的,在宿主机上无法直接运行mysqldump命令进行备份(宿主机不关心数据库实例类型,且不会安装这些东西),而如果要备份就需要使用...
  • 1.创建备份目录mkdir/hmx/data/dbback/mysql2.创建备份shell脚本cd/hmx/data/dbbacktouchbkmql.shvibkmql.sh输入以下内容:#db_user="mysql账号"#db_passwd="密码"db_name="hmx"# the directory for story your ...
  • 1.检查mysql备份命令有没有作用在var目录下创建backup目录,在backup目录下创建mysql目录用于存放mysql备份文件cd到/var/backup目录下mysqldump -uroot -pwh5268925 zhaochao > mysql/zhaochao.sql如果成功,在/...
  • mysql定时备份脚本

    2021-01-27 03:22:51
    一、MySQL数据备份1.1、 mysqldump命令备份数据在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:#...
  • 一、MySQL数据备份1.1、 mysqldump命令备份数据在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:#...
  • 前言: 为了避免数据库故障导致数据丢失,我们需要定期备份数据库中数据。这样可以减少数据的丢失。提高数据的安全性,具体操作如下: 正文: 这里主要使用mysqldump备份命令...dir="/data/mysqlbackup/"$(date +%Y
  • MySQL定时备份数据库

    2021-01-18 18:39:17
    一、MySQL数据备份1.1、 mysqldump命令备份数据在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:#...
  • 目录结构如下: dbcorn.py if __name__ == "__main__": from datetime import datetime ... from datetime import datetime ... # 第一个参数是一个可以返回时间戳的函数,第二个参数可以在定时未到达之前阻塞。.
  • 本篇文章主要讲述了几种方法进行 MySQL 定时备份数据库。 一. mysqldump命令备份数据 在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,...
  • 在windows服务器上,想要定时备份数据库数据,可采用windows的任务计划程序+数据库备份脚本组合。其中,MySQL数据库备份,起到关键作用是mysqldump。有关于mysqldump命令的用法,可以找MySQL的官方文档了解。1 备份...
  • 一、定时备份 备份方式:使用的是navicat premium工具的自动运行中的备份功能: 点击自动运行,新建批处理作业,在下方点击备份,选择需要备份的数据库,选择可用的工作,双击 2.列表中显示了已经选择的工作: ...
  • 一、MySQL数据备份 1.1、 mysqldump命令备份数据 在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先我们简单了解一下mysqldump命令用法:...
  • 本篇文章主要讲述Navicat for MySQL定时备份数据库和数据恢复等功能,同时可以定时播放电影等设置,希望对您有所帮助,如果文章中存在错误或不足之处,还请海涵~一. 设置计划任务定时备份数据库计划任务就是让电脑在...
  • mysql定时备份数据库

    2021-12-06 13:58:24
    touch mysqlbackup.sh #!/bin/bash #保存备份个数,备份31天数据 number=10 #备份保存路径 backup_dir=/home/3307backup #日期 dd=`date +%Y-%m-%d-%H-%M-%S` #备份工具 tool=mysqldump #用户名 username=root #...
  • Ubuntu MySQL定时备份

    2021-01-19 22:05:37
    创建保存备份文件的路径/mysqldata#mkdir /mysqldata2.创建/usr/sbin/bakmysql文件#vim /usr/sbin/bakmysql输入以下内容:#!/bin/shDatabase=/mydata/dbbackup/mysql01/ #文件保存路径Date=`date '+%Y%m%d'` ...
  • 本篇文章主要讲述了几种方法进行 MySQL 定时备份数据库。一. mysqldump命令备份数据在MySQL中提供了命令行导出数据库数据以及文件的一种方便的工具mysqldump,我们可以通过命令行直接实现数据库内容的导出dump,首先...
  • 关键字数据发送线程适用产品AnyBackup 7.0.x问题描述执行 MySQL 数据库偶发备份失败,执行输出显示如下错误信息:备份失败,原因:数据发送线程出现异常。问题影响AnyBackup 无法完成 MySQL 数据库备份。问题原因...
  • 本篇文章主要讲述Navicat for MySQL定时备份数据库和数据恢复等功能,同时可以定时播放电影等设置,希望对您有所帮助,如果文章中存在错误或不足之处,还请海涵~一. 设置计划任务定时备份数据库计划任务就是让电脑在...
  • MySql定时备份脚本

    2021-01-18 19:13:26
    因为服务的使用频率较低,因此设置定时任务,在每天的中午以及午夜时分进行备份操作.#!/bin/bash# 设置mysql的登录用户名和密码(根据实际情况填写)mysql_user="***"mysql_password="****"mysql_host="localhost...
  • mysql定时备份与还原

    2021-01-19 08:08:01
    Linux 下 mysql数据备份与还原:数据库 test数据表 test_user test_log1、备份单个数据表,通过mysqldump来执行mysqldump -u root -pJason_2020 test test_user > /data/wwwlogs/mysql_logs/test.sql删除test_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 33,803
精华内容 13,521
关键字:

mysql定时备份

mysql 订阅
友情链接: 麦康JSP版.zip