MySQL日志和备份还原工具示例

MySQl日志

Mariadb日志类型：

查询日志：general_log
慢查询日志：log_slow_queries
错误日志：log_error，log_warnings
二进制日志：binlog
中继日志：relay_log
事务日志：innodb_log

1. 查询日志：

记录查询语句，日志存储位置：

文件：file
表：table（mysql.general_log）
general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}

2. 慢查询日志：

慢查询：运行时间超出指定时长的查询；
long_query_time
存放位置：
- 文件：FILE
- 表：TABLE，mysql.slow_log
  log_slow_queries={ON|OFF} : 是否开启慢查询日志
  slow_query_log={ON|OFF} : 同上，为了兼容
  slow_query_log_file= : 慢查询日志的存放路径
  log_output={FILE|TABLE|NONE} : 慢查询日志的记录方式{文件|表|NONE}
  log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
  log_slow_rate_limit
  log_slow_verbosity

注意：慢查询并不一定是语句查询慢，并不是需要消耗更多的资源导致的，有可能是查询语句，所需要查询以来到的表被别的语句锁定，被阻塞，这个语句就有可能执行很长时间，这会被记录到慢查询日志文件中；
一般开启慢查询日志，主要作用分析查询语句执行较慢的原因；实际中前端程序很多语句写的与数据库设计不融洽，经常导致慢查询有可能会导致死锁发生；

3. 错误日志：

记录信息：

mysqld启动和关闭过程输出的信息
mysqld运行中产生的错误信息
event scheduler运行时产生的信息
主从复制架构中，从服务器复制线程启动时产生的日志
log_error=/var/log/mariadb/mariadb.log|OFF
log_warnings={ON|OFF}

4. 二进制日志：

用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT)或改变后的结果（ROW），也可能是二者混合；

注意：千万不能使用cat命令打开查看，否则有可能会导致文件损坏；
功用：“重放”；备份恢复，mysql扩展功能中的复制都基于二进制日志文件进行的
二进制日志记录格式：
binlog_format={STATEMENT|ROW|MIXED}
- STATEMENT：语句；记录的是执行的语句；会导致重放结果不一样；
- ROW：行；记录的是语句执行后的结果；数量大；
- MIXED：混编；让系统自行判定该基于哪种方式进行；
查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件
SHOW MASTER STATUS;
查看二进制日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
- IN 'log_name' ：指明哪个二进制日志文件中读取事件，如果不指明，则从第一个文件读到最后一个文件；
- FROM pos：指明事件从哪个位置开始
- LIMIT [offset，] row_count：显示指定个数的事件； offset表示便宜量后显示指定个数
二进制日志文件得构成：
- 日志文件：mysql-bin.文件名后缀，二进制格式
- 索引文件：mysql-bin.index,文本格式
二进制日志相关的服务器变量：
- sql_log_bin=ON|OFF：是否记录二进制日志；
- log_bin=/PATH/TO/BIN_LOG_FILE：记录的文件位置；数据库中通常表现为ON
- binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式
- max_binlog_size=1073741824：单个二进制日志文件的最大体积，默认为1G
  - 注：(1)到达最大值后悔自动滚动；(2)文件达到上限时的大小未必为指定的精确值就滚动了；
- sync_binlog=1|0：设定是否启用二进制日志同步功能；1表示当遇到commit时，则触发一次二进制同步；

二进制日志查看工具

mysqlbinlog命令：
用法：mysqlbinlog [options] log-files
- --start-datetime=name: Start reading the binlog at first event having a datetime; equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).
- -j, --start-position=# : Start reading the binlog at position N. Applies to the first binlog passed on the command line.
- --stop-datetime=name: Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly).
- --stop-position=# Stop reading the binlog at position N. Applies to the last binlog passed on the command line.
- --user, --host, --password

二进制日志的格式：

# at 553
            #160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
            SET TIMESTAMP=1472608568/*!*/;
            BEGIN
            /*!*/;

            事件的起始位置：# at 553
            事件发生的日期时间：#160831  9:56:08
            事件发生的服务器id：server id 1
            事件的结束位置：end_log_pos 624
            事件的类型：Query
            事件发生时所在服务器执行此事件的线程的ID： thread_id=2 
            语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
            错误代码：error_code=0
            事件内容：SET TIMESTAMP=1472608568/*!*/;

5. 中继日志：

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；一般用于复制架构中

6. 事务日志：

主要用于帮助事务性存储引擎能满足acid检试；事务型存储引擎innodb用于保证事务特性的日志文件：

    mariadb默认的事务存储引擎为InnoDB；事务日志参数：
            innodb_log_group_home_dir ./  #数据目录，文件名为ib_logfile0
            innodb_log_files_in_group 2 一个日志文件组中有2个文件
            innodb_log_file_size 5242880 单个事务日志文件最大值
            
            redo log ：重做日志；事务提交了但没同步到磁盘数据文件中，则使用redo log
            undo log ：撤销日志；事务没提交成功要撤销；

MyISAM存储引擎不能够安全从崩溃中恢复，但是InnoDB引擎可以，因为Innodb有事务日志；事务日志可以把此前所有事件，按照事务提交或定义的比如每秒一次从内存中同步到磁盘上的事务日志中；当服务器崩溃，下次启动时可以通过读取事务日志来判断此前有没有事务没能正常提交，这样的事务教会滚，如果正常提交了，但没有同步到磁盘文件中，就需要把这个时间从事务日志中读出来，存放到数据文件中；
所谓事务日志就是在磁盘上能把用户的所有操作，不直接修改数据的原处，而是把所有的修改操作直接顺序的保存在指定的文件中；（建议最好使用另外一块磁盘，可以分散io压力）
顺序的保存在文件中的不是数据内容本身，因此事务日志会有问题：所有操作提交了，可能刚同步到事务日志文件中，还没同步到磁盘中，此时如果有人做查询操作，则只悔针对原数据操作查询，新修改的数据就查不到了；
因此，真正执行查询时，既要根据数据文件做查询，还要根据事务日志做查询，这两种操作特别麻烦，为了避免这样的问题，事务型存储引擎如Innodb，就会在主机内存空间中，打开一个非常大的内存空间，把事务日志中记录的所有事件都在内存中做缓冲/缓存；查询语句则都针对于对应的内存空间中所缓存的数据进行，如果内存缓存中没了，也要从原数据和事务日志中读进来合并在内存中执行；
Innodb中的这个内存空间叫做buffer pool，并且是innodb修改数据的重要组件，在管理mysql服务器时，这个内存空间需要自行定义：指明打算使用多大内存来缓冲这类数据；
这个内存空间越大就意味着在内存空间中所能够缓存的数据量越大，在时间其对应的数据操作时的性能也越好；
但是，这个事务日志文件不能太大，意味如果文件太大，mysql为了从崩溃中恢复过来，需要很长时间，而且这个文件还不能太小，如果太小了，有些数据还没来的及同步到磁盘上事务日志文件就满了；
因此，实际上事务日志文件是一组同时工作的，写满了第一个文件，就写第二个同时把第一个同步到磁盘上的数据文件中；这样等第二个写满了在使用第一个写，同时把第二个同步到磁盘上，来达到循环利用；一个日志文件组内最少应该有两个，但组内的单个文件不要太大。

备份和恢复（数据）

备份：存储的数据副本；（原始数据：持续改变）
恢复：把副本应用到线上系统；（仅能恢复至备份操作时刻的数据状态）
- 时间点恢复：可以通过二进制日志（binary log）来做时间点恢复

为什么要备份：

灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、...
测试

备份时应该注意事项：

能容忍最多丢失多少数据；
恢复数据需要在多长时间内完成；
需要恢复哪些数据
- 做恢复演练：
  - 测试备份的可用性
  - 增强恢复操作效率

备份类型：

按备份的数据集的范围划分：
- 完全备份：整个数据集
- 部分备份：数据集的一部分，比如部分表
全量备份、增量备份、差异备份
- 完全备份：
- 增量备份：仅备份自上一次完全备份或增量备份以来变量的那部分数据；
- 差异备份：仅备份自上一次完全备份以来的变量的那部分数据
物理备份、逻辑备份
- 物理备份：复制数据文件进行的备份
- 逻辑备份：从数据库导出数据另存在一个或多个文件中
根据数据服务是否在线划分：
- 热备：读写操作均可进行的状态下所做的备份
- 温备：可读但不可写状态下进行的备份
- 冷备：读写操作均不可进行的状态下所做的备份

备份需要考虑的因素：

锁定资源多长时间？
备份过程的时长？
备份时服务器的负载？
恢复过程的时长？

备份策略：

全量+差异+binlogs
全量+增量+binlogs
备份手段：物理、逻辑

备份什么：

数据
二进制日志、Innodb的事务日志
代码（存储过程、存储函数、触发器、时间调度器）
服务器的配置文件

备份工具

1. mysqldump：mysql服务自带的备份工具；逻辑备份工具
mysqldump事实上就是向mysql服务器发起一个全量查询操作，把所有数据拿到本地以后，并将读取到的数据保存在文件中从而完成备份操作的；

支持完全备份、部分备份
InnoDB支持：热备

MyISAM支持：温备

  mysqldump：
  用法： mysqldump [OPTIONS] database [tables]
  OR      mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3 ...]
  OR       mysqldump [OPTIONS] --all-databases [OPTIONS]

  MyISAM存储引擎：支持温备，备份时要锁定表
          -x, --lock-all-tables:  锁定所有库的所有表，读锁
          -l，--lock-tables：锁定指定库所有表
  InnoDB存储引擎：支持温备和热备
          --single-transaction:  创建一个事务，基于此快照执行备份
          其它选项：
              -R, --routines：存储过程和存储函数
              --triggers：备份表相关的触发器
              --skip-triggers：不备份触发器
              -E, --events：备份指定数据库相关的所有event scheduler
              
              --master-data[=#]:#表示1或2
                  1：记录为CHANGE MASTER TO 语句，此语句不被注释；为从服务器时使用；
                  2：记录为CHANGE MASTER TO语句，此语句被注释；建议使用--master-data=2；
                  0：表示不启用
              -F, --flush-logs:  锁定表完成后，即进行日志刷新操作


              示例方案一：mysqldump+复制binlog
                                      mysqldump：做完全备份
                                      复制binlog中指定时间范围内的event；做增量备份
                                      2018-11-3 14:46做一次完全备份：
                                      [root@localhost ~]# mysqldump --all-databases -R --master-data=2 -F --single-transaction -E > all.sql
                                      14:46之后数据发生改变，再做一次增量备份：
                                      [root@localhost ~]# mysqlbinlog /var/lib/mysql/ON.000010 > binlog.sql
                                      把all.sql和binlog.sql拷贝到在另外一台mysql服务器上恢复：
                                      [root@localhost ~]# scp all.sql binlog.sql root@192.168.43.13:
                                      root@192.168.43.13's password: 
                                      all.sql                                                                                                    100%  511KB   9.6MB/s   00:00    
                                      binlog.sql         
                                      [root@zabbix ~]# mysql -uroot -p < all.sql
                                      Enter password: 
                                      [root@zabbix ~]# mysql -uroot -p < binlog.sql
                                      Enter password: 
                                      [root@zabbix ~]# mysqladmin flush-logs   #手动滚动二进制日志，也可以重启mariadb服务实现滚动

注意：二进制日志文件不应该与数据文件放在同一磁盘；
事务日志可以跟数据文件放在同一磁盘，但有条件还是把事务日志放在非数据磁盘上；
任何关键性数据都放在有冗余能力是磁盘设备上，一般用raid10；可以做多组，4块硬盘做raid10，用来放数据，另外两块硬盘做raid1，用来放二进制日志；把事务日志放在非数据磁盘上；
还有InnoDB的事务日志非常关键的数据，用来做数据库崩溃后恢复；事务日志也应该放在有冗余能力的磁盘设备上；

2. cp/tar：基于lvm备份：
第一步：连接到mysql上，请求锁定所有表：

请求施加全局锁：
> FLUSH TABLES WITH READ LOCK;
施加读锁，把所有表中的数据从内存同步到磁盘上；如果能请求到读锁，其它线程只能读操作；如果此时，正有人在执行一个大事务，且正在做写操作，此时就可能会等很长时间才能请求到读锁；
> SHOW MASTER STATUS;

第二步：锁定后，滚动一次二进制日志；
记录二进制日志文件及事件位置：
> FLUSH LOGS;
手动记录下来：

    ]# mysql -e 'show master status;'
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000004 |      245 |              |                  |
    +------------------+----------+--------------+------------------+

或：
> SHOW MASTER STATUS;
或：
]# mysql -e 'SHOW MASTER STATUS' > /root/Ppos.date +%F
]# ls
其中显示内容：
pos-2016-06-05

]# cat pos-2016-06-05
显示内容：
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000004 245

注意：二进制日志文件要从原卷中备份：

]# cp -a /data/mysql/binlogs/ /tmp

第三步：为逻辑创建快照：

]# lvcreate -L 2G -n mydata-snap -s -p r /dev/myvg/mydata

第四步：释放全局读锁

> UNLOCK TABLES;

模拟数据库变化，做些修改操作；

> USE hellodb;
> DELETE FROM students WHERE StuID=5;
> DELETE FROM students WHERE StuID=6;
> DELETE FROM students WHERE StuID=11;
> SELECT * FROM students;

> SHOW MASTER STATUS;

第五步：挂载快照卷并备份
挂载快照卷：

]# mount -r /dev/myvg/mydata-snap /mnt
]# ls /mnt
binlogs  lost+found  mysql

]# cd /mnt/
]# ls mysql/
aria_log.00000001  hellodb  ib_logfile0  mydb   performance_schema
aria_log_control   ibdata1  ib_logfile1  mysql  test

]# ls binlogs/
mysql-bin.000001  mysql-bin.000003  mysql-bin.index
mysql-bin.000002  mysql-bin.000004

备份数据文件

]# cp -a mysql/ /tmp

注意：二进制日志文件要从原卷中备份：

第六步：删除快照卷

]# umount /mnt
]# lvremove /dev/myvg/mydata-snap

模拟数据库服务器崩溃：

]# systemctl stop mariadb.service
]# rm -rf /data/mysql/*

模拟数据文件崩溃

]# rm -rf /data/binlogs/*

模拟二进制日志文件崩溃；

演示基于lvm还原：

第一步：还原数据文件：

]# cp -a /tmp/mysql/* /data/mysql/

还原数据文件；即直接拷贝回来；

]# ll /data/mysql/
总用量 28712
-rw-rw---- 1 mysql mysql    16384 6月   5 20:16 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 6月   5 20:16 aria_log_control
drwx------ 2 mysql mysql     4096 6月   5 20:22 hellodb
-rw-rw---- 1 mysql mysql 18874368 6月   5 20:23 ibdata1
-rw-rw---- 1 mysql mysql  5242880 6月   5 20:23 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 6月   5 20:16 ib_logfile1
drwx------ 2 mysql mysql     4096 6月   5 20:22 mydb
drwx------ 2 mysql mysql     4096 6月   5 20:22 mysql
drwx------ 2 mysql mysql     4096 6月   5 20:16 performance_schema
drwx------ 2 mysql mysql     4096 6月   5 20:16 test

确保复制回来以后，文件的属主、属组是mysql；

]# systemctl start mariadb.service

> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       245 |
+------------------+-----------+

> USE hellodb;

> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
崩溃前的数据；

第二步：读取二进制日志文件中备份的事件位置

]# cat /pos-2016-06-05 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000004    245

在备份的二进制日志文件中找到mysql-bin.000004文件，从245位置后面，读出所有事件，然后保存在一个sql文件中；

]# mysqlbinlog --start-position=245 mysql-bin.000004 > recovery.sql

重放事件：

]# mysql < recovery.sql

以上就是通过lvm完成几乎热备份；

3. xtrabackup：由Percona提供的开源工具，支持InnoDB做热备，物理备份工具
官方：https://www.percona.com/software/mysql-database/percona-xtrabackup 下载xyrabackup程序包
安装：

[root@mysql ~]# yum -y install ./percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
[root@mysql ~]# rpm -ql percona-xtrabackup-24-2.4.12-1.el7
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.12
/usr/share/doc/percona-xtrabackup-24-2.4.12/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

xtrabackup是使用C语言开发的，链接到InnoDB库和标准MySQL客户端库，因此，能通过mysql客户端（协议）应用程序链接至mysql服务器端完成远程备份；所以，这就也就是为什么xtrabackup必须在线备份的原因；能实现基于完全奔备份+增量方式还原；还可以读取/etc/my.cnf来获取有关自己的配置；
只需在/etc/my.cnf中定义[xtrabackup]配置段；
它会读取两段配置[mysqld]和[xtrabackup]；读取mysqld配置段是了解服务器端相关配置参数配置从而知道怎么去链接mysql服务器端；其次就是读取自己的配置段；

在my.cnf配置文件中添加：
[xtrabackup]
target_dir=/data/backups/mysql/

备份示例：
]# xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/
]# ls /data/backups/mysql/
backup-my.cnf  ibdata1  mysql               xtrabackup_binlog_info  xtrabackup_info
hellodb        mydb     performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

--backup: 表示为备份操作
--datadir=/var/lib/mysql/: 指明要备份的数据文件存放路径；
--target-dir=/data/backups/mysql/ 指明备份文件存放路径；
--apply-log：表示将多个增量备份合并到主干备份后以便完成一次性还原操作；
--prepare：能够把备份出来的数据当中那些该提交的事务，从事务日志文件中，合并至数据文件，以便在后面合并时能直接合并成一致的数据

所有的备份操作都是基于log sequence number（LSN）日志序列号实现的，热备就是通过读取日志序列号来实现；
备份工具除了xtrabackup命令，还可以使用innobackupex命令

innobackupex命令：
是对xtrabackup的C程序作了二次封装的perl脚本，其实innobackupex只是xtrabackup的前段工具，建议使用innobackupex；
用法: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]

备份：
]# innobackupex --user=root /data/backups/mysql
不用指明datadir，因为他会通过mysql server读取mysqld配置段或server配置段中的datadir选项，并从而知道要备份的数据在什么地方；
/data/backups/mysql/: 指定的备份路径
备份压缩:
]# innobackupex --stream=tar ./ | gzip - > backup.tar.gz
]# innobackupex --stream=tar ./ | bzip2 - > backup.tar.bz2
增量备份：
]# innobackupex --incremental /backup --incremental-basedir=BASEDIR
--incremental：表示指明做增量备份；
/backup：表示指明增量备份存储的路径；
--incremental-basedir=BASEDIR：表示指明基于哪个目录做增量备份；可基于上一次完全备份做增量，也可基于上一次增量备份做增量；真正的增量备份是基于最近一次完全备份或增量做的；如果每一次都基于完全备份做增量实际做的是差异备份；
因此，这里的BASEDIR如果每次指的都是完全备份的路径，做的就是差异备份；
注意：增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

准备：innobackupex --apply-log --redo-only BASEDIR
innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR
恢复：innobackupex --copy-back BASEDIR

使用innobackupex全量备份、增量备份，还原数据示例：

第一步：全量备份数据
创建备份文件路径：
[root@node3 ~]# mkdir -pv /data/backups/mysql/
[root@node3 ~]# cd /data/backups/mysql/
[root@node3 mysql]# innobackupex --user=root --backup /data/backups/mysql/

第二步：连接上数据库做写修改后再做增量备份
[root@node3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| mytest             |
| performance_schema |
+--------------------+
5 rows in set (0.02 sec)

MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> CREATE TABLE students FROM 'SELECT * FROM hellodb.students';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM 'SELECT * FROM hellodb.students'' at line 1
MariaDB [mydb]> CREATE TABLE students (id int not null, name varchar(50),age int );
Query OK, 0 rows affected (0.05 sec)

MariaDB [mydb]> desc students;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

MariaDB [mydb]> INSERT INTO students (id,name,age) VALUE (1,'TOM',34);
Query OK, 1 row affected (0.01 sec)

MariaDB [mydb]> SELECT * FROM students;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | TOM  |   34 |
+----+------+------+
1 row in set (0.03 sec)

MariaDB [mydb]> exit
Bye

增量备份：
[root@node3 ~]# innobackupex --incremental /data/backups/mysql/ --incremental-basedir=/data/backups/mysql/2018-11-04_14-49-30/ 

第三步： 压缩并复制到新的数据库服务器准备还原
[root@node3 ~]# cd /data/backups/mysql/ 
[root@node3 mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09
[root@node3 mysql]# tar -zcf backup.tar.gz ./*
[root@node3 mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09  backup.tar.gz
[root@node3 mysql]# scp backup.tar.gz root@192.168.43.14:
root@192.168.43.14's password: 
backup.tar.gz  

[root@localhost ~]# tar xf backup.tar.gz -C /data/backups/mysql/
[root@localhost ~]# cd /data/backups/mysql/
[root@localhost mysql]# ls
2018-11-04_14-49-30  2018-11-04_14-57-09    
[root@localhost mysql]# less 2018-11-04_14-49-30/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1849407
last_lsn = 1849407
compact = 0
recover_binlog_info = 0
[root@localhost mysql]# less 2018-11-04_14-57-09/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1849407
to_lsn = 1852838
last_lsn = 1852838
compact = 0
recover_binlog_info = 0

准备：
[root@localhost mysql]# innobackupex --apply-log --redo-only 2018-11-04_14-49-30/
[root@localhost mysql]# innobackupex --apply-log --redo-only 2018-11-04_14-49-30/ --incremental-dir=./2018-11-04_14-57-09/
[root@localhost mysql]# less 2018-11-04_14-49-30/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1852838
last_lsn = 1852838
compact = 0
recover_binlog_info = 0

还原：
[root@localhost mysql]# innobackupex --copy-back 2018-11-04_14-49-30/
注意：还原时要保证新数据库服务器数据文件路径下为空
[root@localhost mysql]# chown -R mysql.mysql /var/lib/mysql/
[root@localhost mysql]# ll /var/lib/mysql/
total 18464
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 hellodb
-rw-r----- 1 mysql mysql 18874368 Nov  4 15:11 ibdata1
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mydb
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mysql
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 mytest
drwxr-x--- 2 mysql mysql     4096 Nov  4 15:11 performance_schema
-rw-r----- 1 mysql mysql       20 Nov  4 15:11 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      535 Nov  4 15:11 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Nov  4 15:11 xtrabackup_master_key_id
[root@localhost mysql]# systemctl start mariadb.service
验证数据：
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mydb               |
| mysql              |
| mytest             |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mydb]> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students       |
+----------------+
1 row in set (0.00 sec)

MariaDB [mydb]> select * from students;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | TOM  |   34 |
+----+------+------+
1 row in set (0.00 sec)

4. mysqlhotcopy：几乎冷备工具，不在使用
5. select：

备份：SELECT cluase INTO OUTFILE 'FILENAME';
恢复：CREATE TABLE
导入：LOAD DATA