2023年7月30日发(作者：)

mysql备份数据库实例_MySQL数据库备份实例详解对于任何数据库来说，备份都是⾮常重要的数据库复制不能取代备份的作⽤⽐如我们由于误操作，在主数据库上删除了⼀些数据，由于主从复制的时间很短，在发现时，从数据库上的数据可能也已经被删除了，我们不能使⽤从数据库上的数据来恢复主数据库上的数据，只能通过备份进⾏误删除数据的恢复⼀. 备份的分类1.按备份的结果来分：逻辑备份其备份结果为SQL语句，适合于所有存储引擎，恢复时需要较多时间，逻辑备份时，对于MyISAM存储引擎是需要进⾏锁表操作的，通过使⽤的MySQLdump就是⼀种逻辑备份⼯具物理备份是对数据库⽬录的拷贝，其备份结果的⼤⼩通常也与备份数据的数据⽬录⼤⼩相同，物理备份和恢复通常⽐逻辑备份要快，因为只需要对mysql数据⽬录拷贝即可，也正是因为这点，对于内存表只能备份其结构，⽆法备份数据(因为其数据存储在内存中，没有实际的物理数据⽂件)物理备份的⽅式进⾏物理备份，我们可以采⽤离线备份和在线备份的⽅式进⾏备份离线备份：需要对数据库进⾏停机，或对整个数据库进⾏锁定的情况下进⾏在线备份：需要使⽤第三⽅⼯具，如 XtraBackup2. 按备份的数据库的内容来分2.1 全量备份是对整个数据库的⼀个完整备份2.2 增量备份是在上次全量或增量备份的基础上，对更改过的数据进⾏的备份注意Mysql官⽅提供的mysqldump命令并不⽀持增量备份通常情况要使⽤mysqldump来进⾏增量备份的话，增量备份只能通过备份Mysql的⼆进制⽇志来实现XtraBackup本⾝就提供了增量备份的功能，所以对于Innodb来说，使⽤XtraBackup进⾏备份更加安全⾼效⽆论是使⽤XtraBackup还是Mysqldump 进⾏备份，要进⾏基于时间点的恢复时都需要利⽤Mysql的⼆进制⽇志，所以通常情况下我们需要对Mysql的⼆进制⽇志也进⾏备份⼆. 使⽤mysqldump进⾏备份mysqldump 是mysql官⽅提供的逻辑备份⼯具，其备份结果是可读的SQL⽂件mysqldump ⽀持多种语法1. 常⽤语法对⼀个数据库下的⼀个或多个表进⾏备份mysqldump [OPTIONS] database [tables]备份多个表时，table直接⽤空格进⾏分隔对指定的多个数据库进⾏备份mysqldump [OPTIONS] --database [OPTIONS] DB1 [DB2..]对整个mysql实例下的所有数据库进⾏备份mysqldump [OPTIONS] --all-database [OPTIONS]2. 常⽤参数-u，--user = name # 指定备份时所使⽤的数据库账号-p. --password [=name] # 指定账号的密码--single-transaction # 使⽤此参数会在备份前先执⾏start transaction命令启动⼀个事务，以此来获得数据库备份时的数据的⼀致性，由于是通过事务保证数据的⼀致性，所以此参数只对Innodb存储引擎有效；当使⽤此参数进⾏备份时，要确保没有任何DDL语句在执⾏，因为Innodb的数据⼀致性的隔离级别并不能隔离DDL操作-l, --lock-tables # 如果没有使⽤⾮事务存储引擎，使⽤此参数保证备份时数据的⼀致性，在备份时会依次锁住每个数据库下的所有表，⼀般⽤于MyISAM存储引擎的备份，使⽤了此参数，在数据库备份时，只能进⾏读操作，由于此参数是锁住⼀个数据库下的所有表，备份时可以保证⼀个数据库下的所有表的数据⼀致性，但不能保证整个Mysql实例下的所有数据库的所有表的数据⼀致性，这也是为什么推荐使⽤Innodb引擎的⼀个原因lock-tables 与 single-transaction 参数是互斥的，不能同时使⽤，所以数据库中如果混合使⽤了Innodb表和MyISAM表就只能使⽤lock-tables来进⾏备份了-x, --lock-all-tables # 此参数可以对整个Mysql实例下的所有数据库进⾏加锁，可以避免lock-tables不能保证整个Mysql实例下的所有数据库的所有表的数据⼀致性的问题，备份时同样会将数据库变为只读的状态--master-data = [1/2] # ⽆论是时间恢复还是新建slave实例都要⽤到这个参数，此参数有两个可选值，当值为1时，备份中只记录change_master语句，当值为2时，change_master语句会以注释的形式出现在备份⽂件中；默认值为1，且当使⽤了此参数时会忽略lock-tables参数，在备份时如果使⽤了此参数，但是没有使⽤single-transaction参数，则会⾃动使⽤lock-all-tables参数如果我们要备份的数据库中包含了存储过程，触发器，数据库调度事件时，要备份这些数据库对象时，必须指定以下参数才能对相应数据库进⾏备份-R, --routines # 指定要备份的数据库中存在的的存储过程--triggers # 指定要备份的数据库中存在的的触发器-E,--events # 指定要备份的数据库中存在的的调度事件除了以上参数，还有⼀些参数在备份时也会⽤到--hex-blob # 因为mysqldump备份导出的是⽂本⽂件，如果导出的数据中含有以上类型，在⽂本格式下，有些字符是不可见的，如果使⽤了此参数使，将会对数据库中所存在binary、varbinary、blob类型的数据以16进制的形式保存，就不会出现有些字符不可见的情况了--tab =path # 使⽤了此参数会在指定的路径下对数据库的每个表⽣成两个⽂件，⼀个⽂件⽤于存储表结构，另⼀个⽤于存储表中的数据-w, --where = '过滤条件' # 导出指定条件的数据(只⽀持单表数据条件导出)备份账号所需要的权限 ： SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS如果使⽤--tab参数则还需要：FILE权限3. 演⽰⾸先创建备份⽤户create user 'backup'@'localhost' identified by '123456';赋予⽤户备份权限grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';使⽤mysqldump进⾏全量备份备份某个数据库[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --eventsmc_orderdb > mc_如果遇到以下问题mysqldump: Error: Binlogging on server not activeMySQL中⼆进制⽇志功能默认是关闭，去/etc/ ⽂件中加⼊下⾯配置，开启log_bin(数据库的操作⽇志)功能，然后重启mysql即可解决问题log_bin=mysql-bin之后使⽤ “systemctl start mysql” 重启服务器，报错Job for e failed because the control process exited with error code. See "systemctl status e" and"journalctl -xe" for details.解决⽅法：在设置 log-bin 的时候同时需要设置 server-id 变量，即在配置⽂件中添加：[mysqld]log-bin=mysqlserver-id=1然后再次重启即可补充知识装mysql,运⾏⼀段时间后，在mysql⽬录下出现⼀堆类似mysql-bin.000***，从mysql-bin.000001开始⼀直排列下来，⽽且占⽤了⼤量硬盘空间，⾼达⼏⼗个G. 对于这些超⼤空间占⽤量的⽂件我们应该怎么办呢?那么mysql数据库⽂件夹中的mysql-bin.00001是什么⽂件？mysql-bin.000001、mysql-bin.000002等⽂件是数据库的操作⽇志，例如UPDATE⼀个表，或者DELETE⼀些数据，即使该语句没有匹配的数据，这个命令也会存储到⽇志⽂件中，还包括每个语句执⾏的时间，也会记录进去的。这些形如mysql-bin.00001的⽂件主要是⽤来做什么的呢?1：数据恢复如果你的数据库出问题了，⽽你之前有过备份，那么可以看⽇志⽂件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。2：主从服务器之间同步数据主服务器上所有的操作都在记录⽇志中，从服务器可以根据该⽇志来进⾏，以确保两个同步。如果不想要这些⽂件应该怎么做呢?1：只有⼀个mysql服务器，那么可以简单的注释掉这个选项就⾏了。vi /etc/把⾥⾯的 log-bin 这⼀⾏注释掉，重启mysql服务即可。2：如果你的环境是主从服务器，那么就需要做以下操作了。A：在每个从属服务器上，使⽤SHOW SLAVE STATUS来检查它正在读取哪个⽇志。B：使⽤SHOW MASTER LOGS获得主服务器上的⼀系列⽇志。C：在所有的从属服务器中判定最早的⽇志，这个是⽬标⽇志，如果所有的从属服务器是更新的，就是清单上的最后⼀个⽇志。D：清理所有的⽇志，但是不包括⽬标⽇志，因为从服务器还要跟它同步。简单地说,这些MySQL⽬录下的形如mysql-bin.000***的⽂件时MySQL的事务⽇志。删除复制服务器已经拿⾛的binlog是安全的，⼀般来说⽹络状况好的时候，保留最新的那⼀个⾜以。再次执⾏之前的备份命令，即可成功被封mc_orderdb数据库下的所有表，我们可以查询⼀下备份的SQL⽂件中是否包含所有表[root@localhost db_backup]# grep "CREATE TABLE" mc_ATE TABLE `order_cart` (CREATE TABLE `order_customer_addr` (CREATE TABLE `order_detail` (CREATE TABLE `order_master` (CREATE TABLE `region_info` (CREATE TABLE `shipping_info` (CREATE TABLE `warehouse_info` (CREATE TABLE `warehouse_proudct` ([root@localhost db_backup]#通过上⾯结果可以看出我们的⼏个表都在其中备份某个数据库下的某个表[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --eventsmc_orderdb order_master > order_er password:[root@localhost db_backup]# lsmc_ order_备份MySQL实例下的所有数据库[root@localhost db_backup]# mysqldump -ubackup -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > er password:[root@localhost db_backup]# lsmc_ order_由于master-data的值设置为1，change master命令并没有被注释掉，如果我们使⽤这个命令进⾏恢复，change master命令就会被执⾏，在⼀些情况下可能会造成⼀些错误，所以建议使⽤时最好还是设置为2可以通过下⾯的命令查看，备份⽂件中包含哪些数据库[root@localhost db_backup]# grep "Current Database" -- Current Database: `mc_orderdb`-- Current Database: `mc_productdb`-- Current Database: `mc_userdb`-- Current Database: `mysql`[root@localhost db_backup]#使⽤-tab参数指定备份⽂件的位置⾸先在/tmp ⽬录下建⽴⼀个mc_orderdb⽬录⽤来存放指定的备份⽂件，之所以使⽤在此⽬录下建⽴⽬录，是因为使⽤--tab参数时，⽤户必须对⽬标⽬录有可写权限，⽽tmp⽬录对任何⽤户都有可写权限[root@localhost db_backup]# mkdir -p /tmp/mc_orderdb现在我们可以使⽤--tab参数指定备份路径[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdbEnter password:---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when executing 'SELECTINTO OUTFILE'可以发现，报错了，其实我们在这之前还缺少⼀步，由于⽤户需要有写⽂件的权限，所以我们还需要对备份⽤户赋予file权限mysql> grant file on *.* to 'backup'@'localhost';Query OK, 0 rows affected (0.00 sec)我们再次执⾏上⾯的备份命令[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdbEnter password:---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=347;mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute thisstatement when executing 'SELECT INTO OUTFILE'可以很清楚地从提⽰看到是因为mysql服务启⽤了–secure-file-priv，所以才⽆法执⾏。那么–secure-file-priv⼜是什么呢，应该如何解决才能是它可以备份呢？--secure-file-priv=name ：Limit LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE() to files within specified directory可以看到secure-file-priv参数是⽤来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定⽬录的。当secure_file_priv的值为null ，表⽰限制mysqld 不允许导⼊|导出当secure_file_priv的值为/tmp/ ，表⽰限制mysqld 的导⼊|导出只能发⽣在/tmp/⽬录下当secure_file_priv的值没有具体值时，表⽰不对mysqld 的导⼊|导出做限制查看数据库当前该参数的值mysql> show global variables like '%secure%';+--------------------------+-----------------------+| Variable_name | Value |+--------------------------+-----------------------+| require_secure_transport | OFF || secure_auth | ON || secure_file_priv | /var/lib/mysql-files/ |+--------------------------+-----------------------+3 rows in set (0.00 sec)清楚地看到secure_file_priv 的值是NULL，说明此时限制导⼊导出的所以应该改变该参数可是查看了中居然没有对这个参数进⾏设定，就说明这个参数默认便是null所以再中的[mysqld]加⼊secure_file_priv =[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/home/mysql/data # mysql数据存放的⽬录socket=/var/lib/mysql/# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/-file=/var/run/mysqld/_bin=mysql-binserver-id=1secure_file_priv =再重启mysql服务[root@localhost tmp]# systemctl restart mysqld然后再查⼀下此时参数的值mysql> show global variables like '%secure%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| require_secure_transport | OFF || secure_auth | ON || secure_file_priv | |+--------------------------+-------+3 rows in set (0.01 sec)已经是我们要的结果 ，现在我们再次执⾏备份命令[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdbEnter password:---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;mysqldump: Got error: 1: Can't create/write to file '/tmp/mc_orderdb/order_' (Errcode: 13 - Permission denied) whenexecuting 'SELECT INTO OUTFILE'结果还是权限被拒绝，⽆法写⼊，我们可以查询⼀下⽬录mc_orderdb的权限，[root@localhost tmp]# ls -lh mc_orderdb/total 4.0K-rw-r--r-- 1 root root 1.9K Jan 10 10:51 order_可以发现，是root⽤户建⽴的⽬录，我们需要修改其所属⽤户为mysql⽤户，然后再次执⾏备份命令[root@localhost tmp]# chown mysql:mysql mc_orderdb[root@localhost tmp]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events --tab="/tmp/mc_orderdb" mc_orderdbEnter password:---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;---- Dumping events for database 'mc_orderdb'------ Dumping routines for database 'mc_orderdb'--[root@localhost tmp]#可以发现，修改成功后即可备份成功进⼊该⽬录下会发现mc_orderdb数据库下的每个表都有两种⽂件，⼀种.sql结尾记录是表结构，⼀种是.txt结尾的表数据[root@localhost tmp]# cd mc_orderdb/[root@localhost mc_orderdb]# lsorder_ order_customer_ order_ order_ region_ shipping_ehouse_ warehouse_er_ order_customer_ order_ order_ region_ shipping_ warehouse_ehouse_qldump如何使⽤全备where参数使⽤场景假设我们要对订单id为1000到1050的主表进⾏修改，修改之前，我们需要先对数据进⾏备份，这⾥我们就可以使⽤where参数来完成此需求执⾏命令进⾏备份[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --where "order_id>1000 andorder_id<1050" mc_orderdb order_master > order_master_1000_er password:查看备份⽂件可以发现，订单id是从1001开始的在⽇程⼯作中我们不可能⼀直⼿⼯备份，所以我们需要将备份进⾏脚本话，然后使⽤计划任务去执⾏脚本定义备份脚本脚本⽂件#!/bin/bash###############Basic parameters##########################DAY=`date +%Y%m%d` # 记录发⽣备份的当前⽇期Environment=$(/sbin/ifconfig | grep "inet" | head -1 |grep -v "127.0.0.1" | awk '{print $2;}' ) # 当前主机的IPUSER="backup"PASSWD="123456"HostPort="3306"MYSQLBASE="/home/mysql/"DATADIR="/home/db_backup/${DAY}" # 备份存放的⽬录(/home/db_backup⽬录下的以⽇期命名的⼦⽬录中)MYSQL=`/usr/bin/which mysql` # 定义mysql命令的⽬录MYSQLDUMP=`/usr/bin/which mysqldump` # 定义mysqldump命令的⽬录mkdir -p ${DATADIR} # 创建存储⽬录# 定义备份函数，使⽤到上⾯定义的变量Dump(){${MYSQLDUMP} --master-data=2 --single-transaction --routines --triggers --events -u${USER} -p${PASSWD} -P${HostPort}${database} > ${DATADIR}/${Environment}-${database}.sqlcd ${DATADIR}gzip ${Environment}-${database}.sql # 对⽂件进⾏了压缩}# 利⽤for循环对当前服务器下的每⼀个数据库(排除了⼀些系统视图所在的数据库)分别来调⽤上⾯的Dump函数来进⾏备份for db in `echo "SELECT schema_name FROM information_ta where schema_name not in('information_schema','sys','performance_schema')" | ${MYSQL} -u${USER} -p${PASSWD} --skip-column-names`dodatabase=${db}Dumpdone执⾏脚本⽂件[root@localhost home]# bash l: [Warning] Using a password on the command line interface can be ump: [Warning] Using a password on the command line interface can be ump: [Warning] Using a password on the command line interface can be ump: [Warning] Using a password on the command line interface can be ump: [Warning] Using a password on the command line interface can be insecure.[root@localhost home]# cd db_backup/[root@localhost db_backup]# ls20190110 mc_ order_master_1000_ order_[root@localhost db_backup]# cd 20190110/[root@localhost 20190110]# ls172.17.0.1-mc_ 172.17.0.1-mc_ 172.17.0.1-mc_ 可以看到结果已备份，可以使⽤crontab命令定时执⾏此脚本如何恢复mysqldump备份的数据库⽅法⼀：mysql -u -p dbname < ⽅法⼆：mysql> source /tmp/使⽤mysqldump备份时，恢复的速度完全取决于MySQL实例执⾏SQL的速度和服务器的IO性能，并且恢复过程是单线程的，所以对于⾮常⼤的数据集来说，要恢复的话可能需要很长的时间演⽰：把刚才的全备数据恢复到bak数据库中创建bak_orderdb数据库[root@localhost db_backup]# mysql -uroot -p -e"create database bak_orderdb"Enter password:将mc_orderdb备份的数据恢复到bak数据库中[root@localhost db_backup]# mysql -uroot -p bak_orderdb < mc_er password:[root@localhost db_backup]#检验恢复结果的正确性mysql> SELECT COUNT(*) FROM mc__master;+----------+| COUNT(*) |+----------+| 10010 |+----------+1 row in set (0.00 sec)mysql> SELECT COUNT(*) FROM bak__master;+----------+| COUNT(*) |+----------+| 10010 |+----------+1 row in set (0.00 sec)模拟误操作并恢复数据假设我们现在不⼩⼼删除了mc_orderdb下的order_master中的10条数据，我们现在需要通过刚刚恢复的备份数据库把这10条数据恢复回来mysql> DELETE FROM mc__master LIMIT 10;Query OK, 10 rows affected (0.01 sec)mysql> SELECT COUNT(*) FROM mc__master;+----------+| COUNT(*) |+----------+| 10000 |+----------+1 row in set (0.00 sec)⾸先查出被误删的数据SELECT a.* FROM bak__master a LEFT JOIN mc__master b ON _id=_id _id IS NULL;然后执⾏insert语句将查出的数据插⼊回去完整语句为INSERT INTO mc__master(order_id,order_sn,customer_id,shipping_user,province,city,district,address,payment_method,order_money,district_money,shipping_money,payment_money,shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,order_status,order_point,invoice_title,modified_time)SELECT a.* FROM bak__master aLEFT JOIN mc__master b ON _id=_idWHERE _id IS NULL;执⾏结果：mysql> INSERT INTO mc__master(-> order_id,order_sn,customer_id,shipping_user,province,city,district,address,-> payment_method,order_money,district_money,shipping_money,payment_money,-> shipping_comp_name,shipping_sn,create_time,shipping_time,pay_time,receive_time,-> order_status,order_point,invoice_title,modified_time)-> SELECT a.* FROM bak__master a-> LEFT JOIN mc__master b ON _id=_id-> WHERE _id IS NULL;Query OK, 10 rows affected (0.03 sec)Records: 10 Duplicates: 0 Warnings: 0mysql> SELECT COUNT(*) FROM mc__master;+----------+| COUNT(*) |+----------+| 10010 |+----------+1 row in set (0.00 sec)对于⼀些静态型数据我们可以这样进⾏，但是对于⼀些时刻有数据在往表⾥写的数据还原，就不能使⽤这种⽅式了对于⽣产环境中，时刻有数据写⼊的表如何进⾏数据恢复呢？mysqldump单表备份恢复(使⽤了--tab参数备份的结果集)需要进⼊mysql客户端中先恢复表结构mysql> source /tmp/mc_orderdb/region_;再导⼊数据mysql> load data infile '/tmp/mc_orderdb/region_' info table region_info;如何进⾏指定时间点的恢复进⾏某⼀时间点的数据恢复，恢复到误操作的时间先决条件：具有指定时间点前的mysqldump的全备具有全备到指定时间点的mysql⼆进制⽇志演⽰⾸先我们需要有个数据库的全备，此处我们对mc_orderdb 数据库进⾏全备[root@localhost db_backup]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --eventsmc_orderdb > mc_然后我们模拟⼀下⽣产环境中对数据库的操作，这样才能看到时间点恢复的效果我们到mc_orderdb数据库中新建⼀个统计表 t, uid列是⽤户id,cnt 是⽤户的总消费⾦额，将统计结果插⼊表t中后，模拟误操作，删除表t中的100⾏数据mysql> use mc_orderdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table t(id int auto_increment not null,uid int,cnt decimal(8,2),primary key (id));Query OK, 0 rows affected (0.02 sec)mysql> insert into t(uid,cnt) select customer_id,sum(order_money) from order_master group by customer_id;Query OK, 6314 rows affected (0.05 sec)Records: 6314 Duplicates: 0 Warnings: 0mysql> select count(*) from t;+----------+| count(*) |+----------+| 6314 |+----------+1 row in set (0.00 sec)mysql> delete from t limit 100;Query OK, 100 rows affected (0.01 sec)mysql> select count(*) from t;+----------+| count(*) |+----------+| 6214 |+----------+1 row in set (0.01 sec)先恢复⼀个最近的全备，进⾏全量数据恢复[root@localhost db_backup]# mysql -uroot -p mc_orderdb < mc_然后查看mc_⽂件中change master命令中在进⾏这个全备时Mysql⼆进制⽇志的⽂件名(MASTER_LOG_FILE)，以���时间点(MASTER_LOG_POS)CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;此处⽂件名为mysql-bin.000001，⽇志时间点为154;下⾯我们要恢复这个时间点(154)之后到第⼀次删除数据之前的数据要找到这个时间点(154)之后到第⼀次删除数据之前的数据查看⼆进制⽇志，进⾏分析[root@localhost db_backup]# cd /home/mysql[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=mc_orderdbmysql-bin.000001| grep -B3 DELETE | more#190110 13:44:54 server id 1 end_log_pos 83285 CRC32 0xf679d195 Table_map: `mc_orderdb`.`t` mapped to number119# at 83285#190110 13:44:54 server id 1 end_log_pos 84620 CRC32 0xa3408e6c Delete_rows: table id 119 flags: STMT_END_F### DELETE FROM `mc_orderdb`.`t`--### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2=1 /* INT meta=0 nullable=1 is_null=0 */### @3=1042.34 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */### DELETE FROM `mc_orderdb`.`t`--### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2=3 /* INT meta=0 nullable=1 is_null=0 */### @3=803.37 /* DECIMAL(8,2) meta=2050 nullable=1 is_null=0 */...省略从中可以看到刚刚的⼆进制⽇志中的第⼀个DELETE，在它之前的⽇志结束点为84620所以我们需要恢复的是154 到84620 之间，且数据库为mc_orderdb ，⽇志所在⽂件名 为mysql-bin.000001的数据我们通过mysqlbinlog 将这些数据导出来[root@localhost mysql]# mysqlbinlog --start-position=154 --stop-position=84620 --database=mc_orderdb mysql-bin.000001 > mc_order_将数据导⼊恢复[root@localhost mysql]# mysql -uroot -p mc_orderdb < mc_order_实时⼆进制⽇志备份Mysql5.6版本之后，可以实时备份Binlog(⼆进制⽇志)要使⽤这个功能，我们需要进⾏以下配置⾸先新建⼀个⽤户，这个⽤户要有replication slave 权限mysql> grant replication slave on *.* to 'repl'@'localhost' identified by '123456';Query OK, 0 rows affected, 1 warning (0.00 sec)建⽴⼀个存储备份的⼆进制⽇志⽂件的⽬录mkdir -p binlog_backup最后在这个⽬录下，执⾏以下命令，就可以实时的进⾏⼆进制⽇志的备份了[root@localhost binlog_bak]# mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u repl-p123456 mysql-bin.000001mysqlbinlog: [Warning] Using a password on the command line interface can be ：代表这个命令是输出的是raw格式的⼆进制⽇志read-from-remote-server： 从mysql服务器上读取这个⽇志stop-never：备份的这个进程会持续在后台运⾏最后的是指定要备份的⼆进制⽂件的名称此命令执⾏后这个终端会⼀直执⾏这个命令现在打开另外⼀个终端，进⼊备份的⽬录进⾏查看[root@localhost mysql]# cd /home/binlog_bak/[root@localhost binlog_bak]# lsmysql-bin.000001可以看到⽇志已经备份成功进⼊mysql命令⾏，刷新⽇志mysql> flush logs;Query OK, 0 rows affected (0.01 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 3560467 || mysql-bin.000002 | 154 |+------------------+-----------+2 rows in set (0.00 sec)现在有两个⽇志，我们再回到备份⽬录进⾏查看[root@localhost binlog_bak]# lsmysql-bin.000001 mysql-bin.000002可以看到最新的⽇志也已实时备份三. xtrabackup1. xtrabackup介绍xtrabackup 物理备份⼯具，⽤于在线备份innodb存储引擎的表在所有表全是innodb存储引擎表的情况下：xtrabackup 可以保证在备份过程中，不影响表的读写操作在最初的时候，xtrabackup 本⾝只⽀持对innodb存储引擎表的备份，且只会备份数据⽂件，不会备份表的结构innobackupex 是对xtrabackup 的插件，提供了备份表结构及其他配置信息的功能，并⽀持MyISAM表的备份，但也会锁表因为在当前的mysql版本下，还有⼀些系统表使⽤的是MyISAM存储引擎，所以⼀般情况下使⽤的是innobackupex 脚本进⾏备份的2. 安装xtrabackup3. 利⽤innobackupex 进⾏全备innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/[root@localhost home]# innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/xtrabackup: recognized server arguments: --datadir=/home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2xtrabackup: recognized client arguments: --datadir=home/mysql --log_bin=mysql-bin --server-id=1 --parallel=2190110 15:15:30 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes the end of a successful backup run innobackupexprints "completed OK!".... 省略190110 15:15:37 [00] Writing /home/db_backup/2019-01-10_15-15-30/xtrabackup_info190110 15:15:37 [00] ...donextrabackup: Transaction log of lsn (79088947) to (79088956) was copied.190110 15:15:37 completed OK!当看到completed OK!代表备份已完成和mysqldump单线程备份不同，我们可以通过parallel参数指定备份的线程数/home/db_backup/ 是我们指定的备份⽂件的存储⽬录xtrabackup 会已当前时间在⽬标⽬录中⽣成⼀个⼦⽬录⽤来存放当前的备份⽂件我们进⼊⽬录中查看⼀下[root@localhost db_backup]# ls20190110 2019-01-10_15-15-30 mc_ order_master_1000_ order_[root@localhost db_backup]# cd 2019-01-10_15-15-30/[root@localhost 2019-01-10_15-15-30]# ib_buffer_pool mc_orderdb mc_userdb performance_schema xtrabackup_binlog_info xtrabackup_infobak_orderdb ibdata1 mc_productdb mysql sys xtrabackup_checkpoints xtrabackup_logfile与原数据⽂件⽬录相⽐，少了ib_logfile0等⽇志⽂件xtrabackup_binlog_info ⽐较重要，其中记录了备份的⽇志名和⽇志点，相当于mysqldump中设置master-data参数的作⽤如果我们不想以时间戳的形式⾃动⽣成⼦⽬录，我们可以通过指定--no-timestamp参数来实现innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/2019-01-10 --no-timestamp[root@localhost db_backup]# innobackupex --user=backup --password=123456 --parallel=2 /home/db_backup/2019-01-10 --no-timestamp[root@localhost db_backup]# ls20190110 2019-01-10 2019-01-10_15-15-30 mc_ order_master_1000_ order_[root@localhost db_backup]# cd 2019-01-10[root@localhost 2019-01-10]# ib_buffer_pool mc_orderdb mc_userdb performance_schema xtrabackup_binlog_info xtrabackup_infobak_orderdb ibdata1 mc_productdb mysql sys xtrabackup_checkpoints xtrabackup_logfile[root@localhost 2019-01-10]#4. 利⽤innobackupex 进⾏全备的恢复innobackupex --apply-log /path/to/BACKUP-DIRmv /path/to/BACKUP-DIR /home/mysql/data利⽤xtrabackup备份产⽣的备份集并不能直接⽤于数据库的恢复进⾏数据库恢复之前，我们必须把备份中产⽣的备份集 进⾏应⽤，此处我们使⽤的是备份⽂件的⽬录伪2019-01-10[root@localhost db_backup]# innobackupex --apply-log /home/db_backup/2019-01-10[root@localhost db_backup]# cd 2019-01-10[root@localhost 2019-01-10]# ib_logfile0 mc_productdb sys xtrabackup_infobak_orderdb ib_logfile1 mc_userdb xtrabackup_binlog_info xtrabackup_logfileib_buffer_pool ibtmp1 mysql xtrabackup_binlog_pos_innodb xtrabackup_master_key_idibdata1 mc_orderdb performance_schema xtrabackup_checkpoints可以发现，使⽤了上⾯的命令后，备份集中多出了之前缺少的ib_logfile0等⽂件使⽤xtrabackup进⾏数据库恢复时，我们必须对数据库实例进⾏重启先停⽤mysql服务然后将原数据库⽂件所在的⽂件夹重命名为data_bak,然后将此时的 2019-01-10⽂件夹移动到data_bak所在的⽂件夹，并重命名为data，即覆盖了原来的⽂件然后 chown -R mysql:mysql data对⽂件夹修改所属⽤户最后重启mysql服务即可5. 利⽤innobackupex 进⾏增量备份innobackupex --user=backup --password=123456 /home/db_backup # 先进⾏全备innobackupex --user=backup --password=123456 --incremental /home/db_backup/ --incremental-basedir=/home/db_backup/2019-01-10/incremental ：表⽰我们要进⾏的是⼀个全备， 指定全备的⽬录incremental-basedir ：指定增量备份所依赖的数据基础的备份⽬录，这个增量备份所依赖的上⼀个全备演⽰：先进⾏全备innobackupex --user=backup --password=123456 /home/db_backup在 /home/db_backup ⽬录下⽣产了⼀个新的全备⼦⽬录 2019-01-10_16-19-37再依赖上个全量备份进⾏增量备份innobackupex --user=backup --password=123456 --incremental /home/db_backup/ --incremental-basedir=/home/db_backup/2019-01-10_16-19-37/此时在 /home/db_backup ⽬录下⽣产了⼀个增量备分的⼦⽬录2019-01-10_16-22-09增量备份会把⾃上⼀个全备后的数据变更记录下来然后再进⾏⼀次增量备份，此时的命令和前⾯基本相同，只是所基于的增量备份的数据⽬录要变成上次增量备份⽣成的⽬录innobackupex --user=backup --password=123456 --incremental /home/db_backup/ --incremental-basedir=/home/db_backup/2019-01-10_16-22-09/此时，第⼆次增量备份的⽣成的⽬录名为2019-01-10_16-24-096. 利⽤innobackupex 进⾏增量恢复innobackupex --apply-log --redo-only 全备⽬录我们要循环的在多次增量备份中应⽤上⾯步骤innobackupex --apply-log --redo-only 全备⽬录 --incremental-dir=第⼀次增量⽬录所有增量备份的都应有了上⾯的命令后，就可以像全备⼀样，在全备⽬录上再进⾏崩溃恢复的过程innobackupex --apply-log /path/to/BACKUP-DIRmv /path/to/BACKUP-DIR /home/mysql/data最后和全备⼀样，要⽤全备⽬录替换mysql数据库⽬录演⽰下⾯演⽰只恢复到第⼀次备份[root@localhost db_backup]# innobackupex --apply-log --redo-only /home/db_backup/2019-01-10_16-19-37[root@localhost db_backup]# innobackupex --apply-log --redo-only /home/db_backup/2019-01-10_16-19-37 --incremental-basedir=/home/db_backup/2019-01-10_16-22-09[root@localhost db_backup]# innobackupex --apply-log /home/db_backup/2019-01-10_16-19-37[root@localhost db_backup]# mv 2019-01-10_16-19-37 /home/mysql[root@localhost db_backup]# cd /home/mysql[root@localhost mysql]# systemctl stop mysqld[root@localhost mysql]# mv 2019-01-10_16-19-37 data[root@localhost mysql]# chown -R mysql:mysql data[root@localhost mysql]# systemctl start mysqld四. 要制定备份计划每天凌晨对数据库进⾏⼀次全备实时对⼆进制⽇志进⾏远程备份