1. 数据库的6种日志
数据库有6种日志,分别是:查询日志、慢查询日志、错误日志、二进制日志、中继日志以及事务日志。
1> 查询日志
查询日志记录每一条sql语句,建议不开启,因为如果访问量较大,会占用相当大的资源,影响性能。
查询日志的开启:
编辑配置文件:/etc/my.cnf.d/server.cnf
vim /etc/my.cnf.d/server.cnf general_log = ON| OFF #查询日志开关,开为1,关为0 general_log_file localhost.log #查询日志的文件名字(/var/lib/mysql),可以是绝对路径,也可以是相对路径,如果是绝对路径则生成绝对路径下的日志文件文件;如果是相对路径,则默认生成/var/lib/mysql下的路径 log_output TABLE | FILE | NONE #查询日志的存储形式,有三种形式,table是以表的方式存储,file是以文件的方式,none是使用两种方式存储
示例:
[root@alph ~]# vim /etc/my.cnf.d/server.cnf [server] general_log=1 #开启查询日志 general_log_file=localhost.log #日志文件名,自定义 # this is only for the mysqld standalone daemon 保存退出,重启数据库,进入数据库查看日志查询方式 MariaDB [(none)]> show variables like '%general%'; +------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | ON | | general_log_file | localhost.log | +------------------+---------------+ 2 rows in set (0.001 sec) [root@alph ~]# cd /var/lib/mysql/ [root@alph mysql]# ls ............ localhost.log [root@alph mysql]# tail -f localhost.log #已经开始监控 /usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with: Tcp port: 0 Unix socket: (null) Time Id Command Argument /usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with: Tcp port: 0 Unix socket: (null) Time Id Command Argument 190803 2:53:53 8 Connect root@localhost as anonymous on 8 Query select @@version_comment limit 1 190803 2:53:58 8 Query show variables like '%general%' 190803 2:59:43 8 Quit #监控到的sql语句 可以看到,查询日志会记录每一条操作过得SQL语句
2> 慢查询日志
执行时长超出指定时间的查询操作(测试命令:select sleep(4);)(超出指定的时长后才记录下来)。一般企业都将慢查询日志打开,用来优化SQL语句
慢查询日志的开启:
同样的,编辑配置文件/etc/my.cnf.d/server.cnf
slow_query_log = OFF|ON #开启慢查询日志 slow_query_log_file = LOCALHOST-SLOW.log #慢查询日志的文件路径,默认在/var/lib/mysql,文件名默认为LOCALHOST-SLOW.log,localhost为主机名。 long_query_time #慢查询时长;默认是10s,超出10s将会被记录 log_slow_rate_limit #如果要记录的慢查询日志非常多的话,会按照速率来记录,默认1秒记录一个 log_slow_verbosity=1|0 #记录的详细级别,1代表详细,0代表不太详细;
例如:
[root@alph ~]# vim /etc/my.cnf.d/server.cnf [serve #general_log=1 #general_log_file=localhost.log slow_query_log=1 #开启详细的慢查询 long_query_time=4 #超出时长为4秒,四秒后开始记录 没有指定路径和文件,默认为/var/lib/mysql/ LOCALHOST-SLOW.log 保存退出,重启生效 [root@alph ~]# systemctl restart mariadb [root@alph mysql]# ls -ltr -rw-rw---- 1 mysql mysql 142 Aug 3 16:53 alph-slow.log
进入数据库查看,同时打开交互界面进行监控日志 执行一个模拟5秒的SQL语句,等待tail -f监控文件内容 MariaDB [mysql]> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ [root@alph mysql]# tail -f alph-slow.log /usr/sbin/mysqld, Version: 10.3.16-MariaDB-log (MariaDB Server). started with: Tcp port: 0 Unix socket: (null) Time Id Command Argument # Time: 190803 17:00:31 # User@Host: root[root] @ localhost [] # Thread_id: 8 Schema: mysql QC_hit: No # Query_time: 5.000527 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 63 use mysql; SET timestamp=1564822831; select sleep(5);
3> 错误日志
错误日志记录mysqld启动和关闭过程中输出的事件信息,mysqld运行中产生的错误信息。同样的,配置文件为/etc/my.cnf.d/server.cnf
event scheduler #运行一个event时产生的日志信息,在主从复制架构中的从服务器上启动从服务器线程时产生的信息 log_error = /var/log/mysql_error.log #指定错误日志的输出位置,该路径必须具有mysql权限(不指定路径默认放在/var/lib/mysql/) log_warnings #为0, 表示不记录告警信息。为1, 表示告警信息写入错误日志。大于1, 表示各类告警信息,例如有关网络故障的信息和重新连接信息写入错误日志。(默认为2)
[root@alph ~]# vim /etc/my.cnf.d/server.cnf [server] #general_log=1 #general_log_file=localhost.log #slow_query_log=1 #long_query_time=4 log_error = /var/lib/mysql/mysql_error.log 保存退出,重启生效 [root@alph ~]# systemctl restart mariadb [root@alph ~]# cd /var/lib/mysql/ [root@alph mysql]# ls -ltr -rw-rw---- 1 mysql mysql 1883 Aug 3 17:22 mysql_error.lo [root@alph mysql]# tail -f mysql_error.log 在交互界面重新启动一下数据库,这些信息都会被记录到该日志当中 MariaDB [(none)]> exit Bye [root@alph ~]# mysql -uroot -p123 root@alph mysql]# tail -f mysql_error.log 2019-08-03 17:22:12 0 [Note] InnoDB: Waiting for purge to start 2019-08-03 17:22:12 0 [Note] InnoDB: 10.3.16 started; log sequence number 1630860; transaction id 21 2019-08-03 17:22:12 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2019-08-03 17:22:12 0 [Note] InnoDB: Buffer pool(s) load completed at 190803 17:22:12 2019-08-03 17:22:12 0 [Note] Plugin 'FEEDBACK' is disabled. 2019-08-03 17:22:12 0 [Note] Server socket created on IP: '::'. 2019-08-03 17:22:12 0 [Note] Reading of all Master_info entries succeeded 2019-08-03 17:22:12 0 [Note] Added new Master_info '' to hash table 2019-08-03 17:22:12 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.3.16-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server 查看当前错误日志的报警级别 MariaDB [(none)]> show variables like '%warning%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 2 | #默认为2 | sql_warnings | OFF | | warning_count | 0 | +---------------+-------+ 3 rows in set (0.001 sec)
4> 二进制日志
二进制日志是MariaDB最重要的日志,针对时间点还原起着至关重要的作用,可以根据时间点还原数据。所有的增删改操作都会被记录到二进制日志当中。
log_bin = OFF | ON #二进制日志开关 log_bin_basename = /var/lib/mysql/mysql-bin #日志路径,默认在/var/lib/mysql下 binlog_format=STATEMENT|ROW|MIXED #二进制记录格式 STATEMENT:基于“语句”记录,记录SQL语句,占用空间小,但是当数据库有该日志记录的语句(如数据库)的时候,回复数据时是会报错、失败 ROW:基于“行”记录,记录的是每一行的内容,对于大量的记录来说占用空间较大,但记录较精准 MIXED:混合模式,让系统自行判定该基于哪种方式进行,现在采用的基本都是这种混合模式 sql_log_bin=1|0 #是否启用二进制日志,或者指定文件名如mysql-bin log_bin_index=PATH #二进制日志索引位置,默认在/var/lib/mysql下,myssql-bin.index,记录二进制日志文件的路径,该index不属于二进制文件,可以用cat或者vim打开,如: [root@alph mysql]# cat mysql-bin.index ./mysql-bin.000001 sync_binlog=1|0 #设定是否启动二进制日志同步功能。由于磁盘写入文件较慢,开启同步则每记录一条既写入磁盘,对数据库的性能以及磁盘io来说是有影响的 max_binlog_size=SIZE #单个二进制文件最大体积,默认为1G,超过设定大小就会滚动到另一个增加的二进制文件 expire_logs_days=0 #消除日志参数,超过多少天就清除二进制日志,默认为0,代表不启用此功能,二进制日志不清除会一直生成并保存
[root@alph mysql]# vim /etc/my.cnf.d/server.cnf [server] #general_log=1 #general_log_file=localhost.log #slow_query_log=1 #long_query_time=4 #log_error = /var/lib/mysql/mysql_error.log log_bin=mysql-bin #开启二进制日志 保存退出,重启生效 [root@alph mysql]# ls -ltr -rw-rw---- 1 mysql mysql 328 Aug 3 17:41 mysql-bin.000001
查看二进制日志:
MariaDB [(none)]> show master status; #当前所使用的二进制文件 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> show master logs; #所有的二进制文件 +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 328 | +------------------+-----------+ 1 row in set (0.000 sec)
SHOW {BINARY | MASTER} LOGS SHOW BINLOG EVENTS [IN 'log_name'] #二进制文件记录的内容 show master status;
二进制日志记录的是增删改操作,先创建一个表,在查看状态
MariaDB [(none)]> create database testdb; MariaDB [(none)]> ues testdb; MariaDB [testdb]> create table classes (id tinyint unsigned primary key,name varchar(20)); MariaDB [testdb]> show master status; #position数据开始变化,二进制日志mysql-bin.000001开始记录数据 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 643 | | | +------------------+----------+--------------+------------------+
查看二进制日志文件内容:
由于该日志是二进制形式,因此无法用cat或者vim命令来查看,可以用mysql提供的mysqlbinlog来查看指定的二进制日志文件
[root@alph mysql]# mysqlbinlog mysql-bin.000001 .......... #增删改均会被记录 create database testdb /*!*/; # at 461 #190803 18:07:55 server id 1 end_log_pos 503 CRC32 0x8ca3d982 GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 503 #190803 18:07:55 server id 1 end_log_pos 643 CRC32 0x3490771f Query thread_id=9 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1564826875/*!*/; create table classes (id tinyint unsigned primary key,name varchar(20)) /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog *//*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
二进制文件的滚动的三种情况:
1)flush logs:手动滚动
2) 文件超出指定大小
3) service mariadb restart
每滚动一次,日志查询show master log 中的mysql-bin会增1
MariaDB [testdb]> flush logs; Query OK, 0 rows affected (0.003 sec) MariaDB [testdb]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 690 | | mysql-bin.000002 | 385 | +------------------+-----------+ MariaDB [testdb]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 385 | | | +------------------+----------+--------------+------------------+ [root@alph mysql]# cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002
二进制日志格式:
190613 14:17:32 server id 1 end_log_pos 666 CRC32 0xeb1cde6b Query thread_id=9 exec_time=0 error_code=0 use `testdb`/*!*/;
事件发生的日期和时间:190613 14:17:32,19-06-13 14:17:32;
事件发生的服务器标识:server id 1,主从架构同步时将按照server-id来同步;
事件的结束位置:end_log_pos 666;
事件的类型:Query;
事件发生时所在服务器执行此事件的线程ID:thread_id=9;
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0,记录SQL语句所用的时间,一般不为0;
错误代码:error_code=0;
事件内容:GTID:Global Transaction ID;
专属属性:GTID
客户端命令工具mysqlbinlog:
mysqlbinlog [options] log_file ...
--start-datetime= --stop-datetime= #基于时间点恢复。从start到stop时间点之间的内容将会被恢复 --start-position= --stop-position= #基于position点恢复。从start到stop之间的内容将会被恢复
5> 中继日志
复制架构中,备服务器用于保存主服务器的二进制日志中读取到的事件。
6> 事务日志
事务日志transaction log,有两个文件:ib_logfile0,ib_logfile1。事务日志一般不需要手动的管理,由事务型存储引擎自行管理和使用(Innodb,myisam引擎是不支持事务,外键,行级锁);
数据库执行的所有事务都会写入到事务日志当中,事务里进行的增删改等操作先记录到事务日志当中,再写入到数据库。由此带来的问题是由于先将数据写入事务日志当中,当查询数据库时有可能查询不到。同样的,在读取事务日志的内容的同时又读不到原磁盘的内容。事务日志的数据加上磁盘里的数据才是完整的数据,数据库的解决办法是划分内存的一块区域buffer_pool(缓冲池),将事务日志数据可磁盘数据放入内存,直接调用内存。buffer_pool的大小影响数据库的性能。
[root@alph mysql]# ll -rw-rw---- 1 mysql mysql 50331648 Aug 3 18:07 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Aug 2 22:54 ib_logfile1
innodb_buffer_pool_size #一般在没有其他业务的情况下设置成为物理内存的3/4,或者4/5 innodb_log_files_in_group = 2 #事务日志文件的个数,默认为2个事务日志文件,数据交替写入 innodb_log_file_size = 50331648(48M) #事务日志文件的单个大小48M innodb_log_group_home_dir = ./ #事务日志文件的所在路径,默认就在mariadb的数据目录/var/lib/mysql
事务型存储引擎自行管理和使用事务日志,
redo log:重做日志; undo log:撤销日志。
2. 数据库的备份
1> 为什么要备份:
灾难恢复:硬件故障,软件故障,自然灾害,黑客攻击,误操作;测试
2> 备份要注意的点:
- 备份需要多少时间:需要估算出来;
- 能容忍最多丢失多少数据;
- 恢复数据需要在多长时间内完成;
- 需要恢复哪些数据。
数据库备份完成后要做还原测试,定期的去做还原演练,用于测试备份的可用性。
3> 备份类型:
a) 完全备份和部分备份:
i. 完全备份:整个数据集(整个库的整个表);
ii.部分备份:只备份数据子集;
b) 完全备份、增量备份、差异备份:
i. 增量备份:仅备份最近一次完全备份或增量备份(如果有增量备份)以来变化的数据;
ii. 差异备份:仅备份最近一次完全备份以来变化的数据。
c) 热备、温备、冷备
i. 热备:读写操作均可执行(不影响业务的操作);
ii. 温备:读操作可以、写不行(需要将整个库的整个表锁住);
iii.冷备:读写操作均不能执行(停掉业务);
- iv. MyISAM:温备,不能热备;
d) 物理备份、逻辑备份
i. 物理备份:直接复制数据文件进行备份,与存储引擎无关(如cp,备份的时候要注意不能有数据插入);
ii. 逻辑备份:从数据库中“导出”数据库另存而进行备份(如二进制日志、Mysqldump备份的数据,结构不变)。
4> 备份时需要考虑的因素:
1) 持续多久;
2) 备份过程的时长;
3) 备份负载;
4) 恢复过程的时长。
5> 备份什么
1) 数据;
2) 二进制日志(基于时间点还原),innodb的事务日志;
3) 代码(存储过程,存储函数,触发器,时间调度器);
4) 服务器的配置文件。
6> 设计备份方案:
1) 数据集:完全+增量+二进制日志 | 完全+差异+二进制日志(最大量的恢复,接近100%);
2) 备份手段:物理,逻辑;
3) 对于备份较大的数据建议物理备份,对于较小的数据建议用逻辑备份。
7> 备份工具的选择:
1)mysqldump+复制binlog:
mysqldump:完全备份
复制binlog中指定时间范围内的event:增量备份
2)lvm2快照+复制binlog:
lvm2快照:适用cp或者tar等做物理备份:完全备份;
复制binlog中指定时间范围内的event:增量备份;
注意:lvm2属于温备,要先锁表。
3)xtrabackup | mariabackup:
xtrabackup作用于mysql数据库而不是mariadb,由Percona公司提供的支持对InnoDB做热备(物理备份)工具。xtrabackup在mariadb 10版本之前是可以用的。
mariabackup专门为mariadb设计的,可以做:完全备份,增量工具
3. 数据库的恢复
1> 基于二进制文件的恢复
1)基于时间点:
a. 算好要恢复数据的时间段,重定向输入到bin.sql文件中,如:
1 mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql
b. 执行bin.sql文件还原
1 source bin.sql
2)基于position,如:
mysqlbinlog /var/lib/mysql/mysql-bin.000005 --stop-position=1389 | mysql -uroot -p123
如果不提供start则从头开始恢复;position从二进制日志中查询。
2> 基于lvm2的备份
因为mariadb的默认数据文件位置是/var/lib/mysql目录里,并不是文件系统,因此这里用新加磁盘来创建文件系统测试
1.添加磁盘 2.重启服务器识别硬盘 3.[root@localhost ~]#fdisk /dev/sdb #分区 4.[root@localhost ~]#pvcreate /dev/sdb3 #创建pv 5.[root@localhost ~]#pvdisplay #查看pv "/dev/sdb3" is a new physical volume of "100.00 GiB" --- NEW Physical volume --- PV Name /dev/sdb3 VG Name PV Size 100.00 GiB Allocatable NO PE Size 0 Total PE 0 Free PE 0 Allocated PE 0 PV UUID zYUBTH-Eqfa-ZYf7-q8pZ-gWBQ-UtUL-qeZzyd 6.[root@localhost ~]# vgcreate test3vg /dev/sdb3 #创建vg Volume group "test3vg" successfully created 7.[root@localhost ~]# lvcreate -L 99G -n testlv3 test3vg #创建lv Logical volume "testlv3" created. 8.[root@localhost ~]# mkfs.ext4 /dev/test3vg/testlv3 #格式化lv 9.[root@localhost ~]# mkdir /test3 #根目录下创建一个test3目录 10.[root@localhost ~]# mount /dev/test3vg/testlv3 /test3/ #挂在刚才所创建的目录 11.如果是yum安装默认数据目录在/var/lib/mysql,如果需要更改 vim /etc/my.cnf.d/server.cnf [mysqld] datadir=/test3/ pid_file=/test3/localhost.pid socket=/test3/mysql.sock wsrep_data_home_dir=/test3/ log-bin=mysql-bin [client] socket=/test3/mysql.sock 12.[root@localhost ~]#chown -R mysql.mysql /test3 #修改目录下面所有文件的属主属组,必须为mysql不然数据库起不来 13.[root@localhost ~]#service mariadb restart #重启数据库 14. MariaDB [(none)]> flush tables with read lock; #将所有表锁住(只能读,不能写) 15.[root@localhost ~]# lvcreate -L 100G -s -p r -n snap_test3 /dev/test3vg/testlv3 #创建快照lv(这里要注意,test3vg中必须有足够的空间,不然创建lv会失败) 备注: -L: --size -s: --snap -p: --permission rw|r -n: --name 16. MariaDB [(none)]> unlock tables; #释放锁 17.[root@localhost testvg]# mkdir /snap_test3 #创建快照目录 18.[root@localhost ~]# mount /dev/testvg/snap_test3 /snap_test3/#挂载快照lv 19.这时候你就可以在快照目录下看到跟源文件系统一模一样的内容了,此时就可以用cp拷贝了!
3> 基于mysqldump工具进行备份(逻辑备份工具)
mysqldump -uroot -proot -l --databases testdb > testdb.sql #备份testdb数据库(-l代表备份单个数据库时锁定该库的所有表;-x当对所有数据库备份时锁定所有数据库的所有表) mysqldump -uroot -proot --all-databases > all_databases.sql #备份所有数据库 mysqldump -uroot -proot testdb students > students.sql #备份testdb数据库下的students表 source testdb.sql #还原数据(sql命令行下)
4. 备份工具mariabackup
Mariabackup是MariaDB提供的一个开源工具,用于对InnoDB,Aria和MyISAM表进行物理在线备份。这个工具是基于Percona的XtraBackup(版本2.3.8)的解决方案。
在MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。
Percona的官方文档:https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
Mariabackup的官方文档:https://mariadb.com/kb/en/library/mariabackup-overview/
安装方法
1 yum install MariaDB-backup #需要有MariaDB数据库的源
备份与恢复
全备+恢复
1)全量备份
1 mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root
2)准备全备数据
1 mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root
3)还原数据(请确保数据目录下是空的)
mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root
4)修改属组和属主
chown -R mysql.mysql /var/lib/mysql
全备+增量+二进制->恢复
1) 全量备份数据库
2) 创建一个数据库
3) 增量备份
4) 删除一个创建的数据库
5) 恢复数据
1>> 全量备份
1 mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root
2>> 增量备份
mariabackup --backup --target-dir=/root/inc1 --incremental-basedir=/root/fullbackup/ --user=root --password=root
3>> 准备全备数据
mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root
4>> 增量和全备数据合并
mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root --incremental-dir=/root/inc1/ --apply-log-only
5>> 恢复数据(请确保数据目录下是空的)
mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root
6>> 修改属组和属主
chown -R mysql.mysql /var/lib/mysql
还原的时候确保属组和属主是mysql:mysql