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

2018-11-04  本文已影响0人  Net夜风

MySQl日志

Mariadb日志类型：

1. 查询日志：

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

2. 慢查询日志：

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

3. 错误日志：

记录信息：

4. 二进制日志：

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

二进制日志查看工具

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为了从崩溃中恢复过来，需要很长时间，而且这个文件还不能太小，如果太小了，有些数据还没来的及同步到磁盘上事务日志文件就满了；
因此，实际上事务日志文件是一组同时工作的，写满了第一个文件，就写第二个同时把第一个同步到磁盘上的数据文件中；这样等第二个写满了在使用第一个写，同时把第二个同步到磁盘上，来达到循环利用；一个日志文件组内最少应该有两个，但组内的单个文件不要太大。

备份和恢复（数据）

为什么要备份：
备份时应该注意事项：
备份类型：
备份需要考虑的因素：
备份策略：
备份什么：

备份工具

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

注意：二进制日志文件不应该与数据文件放在同一磁盘；
事务日志可以跟数据文件放在同一磁盘，但有条件还是把事务日志放在非数据磁盘上；
任何关键性数据都放在有冗余能力是磁盘设备上，一般用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全量备份、增量备份，还原数据示例：
第一步：全量备份数据
创建备份文件路径：
[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：

上一篇下一篇

猜你喜欢

热点阅读