MariaDB 备份与日志管理

数据备份是理员非常重要的工作之一,系统意外崩溃或者硬件的损坏都可能导致数据库的丢失,因此MariaDB管理员应该定期地备份数据库,使得在意外情况发生时,尽可能减少损失.

◆数据备份还原◆

全局锁定: 在备份数据库之前,我们应该先将数据库设置为只读模式,下面将设置全局为只读模式.

 
MariaDB [(none)]> show global variables like "%read_only%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> set global read_only=1;                         #1是只读,0是读写
Query OK, 0 rows affected (0.08 sec)
 
MariaDB [(none)]> show global variables like "%read_only%";       #再次查询读写状态
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

表的锁定: 在备份数据库之前,我们应该先将数据库设置为只读模式,下面将设置指定表为只读模式.

 
MariaDB [(none)]> flush tables with read lock;                #锁定数据表
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> unlock tables;                              #解除表锁定
Query OK, 0 rows affected (0.00 sec)

MySQLDump备份数据: 通过内置备份命令,来备份数据库,以下是常用语法.

 
[root@localhost ~]# mysqldump -u root -p 数据库名 >备份文件.sql             #备份单个数据库
[root@localhost ~]# mysqldump -u root -p 数据库名 表名 >备份文件.sql         #备份数据库中单个表
[root@localhost ~]# mysqldump -u root -p --databases 数据库名 >备份文件.sql  #备份指定的数据库
[root@localhost ~]# mysqldump -u root -p --all-databases >备份文件.sql      #备份整个数据库

MySQL还原数据: 通过内置备份命令,来还原数据库,以下是常用语法.

 
[root@localhost ~]# mysql -u root -p 数据库名 < 备份文件.sql               #还原单个数据库
[root@localhost ~]# mysql -u root -p 还原到的数据库名 < 备份文件.sql        #还原数据库中单个表
[root@localhost ~]# mysql -u root -p < 备份文件.sql                        #还指定的数据库
[root@localhost ~]# mysql -u root -p < 备份文件.sql                        #还原整个数据库

MySQLHotcopy热备份: 使用hotcopy完成一次热备份.

 
[root@localhost ~]# mysqlhotcopy --flushlog -u="root" -p="123" --regexp=^l /root/
 
#以上参数解释: -u 用户名	-p 密码 --regexp=^l 备份开头是l的数据库 /root 备份文件保存位置
 
[root@localhost ~]# ll
总用量 0
drwxr-x---. 2 mysql mysql 39 9月  15 04:31 lyshark

MySQLHotcopy冷恢复: 使用hotcopy完成一次冷恢复,冷恢复需要关闭数据库.

 
[root@localhost ~]# systemctl stop mariadb
 
[root@localhost ~]# cp -a /root/lyshrk /usr/local/mysql/data
[root@localhost ~]# chown -R mysq.mysql /usr/local/mysql/data
 
[root@localhost ~]# systemctl start mariadb

◆数据导入导出◆

select 语句导出:

 
MariaDB [none]> select * from 数据库名称.表名称 INTO OUTFILE "/root/xxx.txt"
[root@localhost ~]# cat /root/xxx.txt

mysql命令导出文件

 
[root@localhost ~]# mysql -uroot -p --execute="select * from 表名;" 数据库名 > filename.txt       
[root@localhost ~]# mysql -uroot -p --verical --execute="select * from person;" 数据库名称 > /root/lyshark.txt
[root@localhost ~]# mysql -uroot -p --html --execute="select * from person;" 数据库名称 > /root/lyshark.html
[root@localhost ~]# mysql -uroot -p --xml --execute="select * from person;" 数据库名称 > /root/lyshark.xml

load data 导入数据:

 
[root@localhost ~]# load data INFILE '/root/wang.txt' INTO TABLE 数据库.表名称;
[root@localhost ~]# mysqlmport -uroot -p 数据库名称 filename.txt

MariaDB 日志配置与管理

MariaDB 日志记录了MariaDB数据库日常操作和错误信息,MariaDB有不同类型的日志文件(各自存储了不同类型的日志),从日志当中可以查询到MaraiDB数据库的运行情况、用户操作、错误信息等,可以为MariaDB管理和优化提供必要的信息,对于MariaDB的管理工作而言,这些日志文件是不可缺少的.

MariaDB默认分为以下4类,使用这些日志可查看MariaDB内部发生的事情,4类分别是: ● 错误日志: 记录MaraiDB服务的启动,运行或停止MariaDB服务时出现的问题 ● 查询日志: 记录建立的客户端连接和执行的语句 ● 二进制日志: 记录所有更改数据的语句,可以用于数据复制 ● 慢查询日志: 记录所有执行时间超过默认值的所有查询或不适用索引的查询

默认情况下,所有日志创建于MariaDB数据目录中,通过刷新日志,可以强制关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志),当执行一个FLUSH LOGS语句或执行mysqlladmin flush-logs或mysqladmin refresh时,将刷新日志. 如果正使用MariaDB复制功能,在复制服务器上可以维护更多日志文件,这种日志称为接替日志.启动日志功能会降低数据库的性能,如果开启慢查询日志则会占用大量的磁盘空间.

◆二进制日志◆

二进制日志主要记录数据库的变化,二进制日志以一种有效的格式,并且是事务安全的方式包含更新日志中可用的所有信息,二进制日志包含了所有更新了数据或者己经潜在更新了数据,语句以"事件"的形式保存,描述数据更改.

二进制日志还包含关于每个更新数据库的语句的执行时间信息,它不包含没有修改任何数据的语句,如果想要记录所有语句,需要使用一般查询日志,使用二进制日志的主要目的是最大可能地恢复数据库,因为二进制日志包含备份后进行的所有更新.

启动二进制日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

 
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
log-bin="/tmp"              #设置开启日志,也可不指定日志保存位置
expire_logs_days = 10       #设置日志自动清理天数
max_binlog_size = 100M      #定义了单个文件的大小限制

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

 
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# 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 variables like 'log_%';
+---------------------------------+-------------------------------------------------
| Variable_name                   | Value
+---------------------------------+-------------------------------------------------
| log_bin                         | ON
| log_bin_trust_function_creators | OFF
| log_error                       | /var/log/mariadb/mariadb.log
| log_output                      | FILE
| log_queries_not_using_indexes   | OFF
| log_slave_updates               | OFF
| log_slow_filter                 | admin,filesort,filesort_on_disk,full_join,
| log_slow_queries                | OFF
| log_slow_rate_limit             | 1
| log_slow_verbosity              |
| log_warnings                    | 1
+---------------------------------+-------------------------------------------------
11 rows in set (0.01 sec)

查看二进制日志:

1.可以使用show binary logs语句查看二进制日志文件个数及文件名,SQL语句如下:

 
MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.02 sec)

可以看到当前只有一个二进制日志,日志文件的个数与MariaDB服务启动的次数相同,每次启动一次数据库,将会产生一个新的日志文件.

2.也可以使用mysqlbinlog命令查看日志内容,SQL语句如下:

 
[root@localhost ~]# mysqlbinlog mariadb-bin.000001
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'mariadb-bin.000001' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

删除二进制日志:

1.我们可以手动删除二进制日志,通过使用reset master语句删除所有日志,SQL语句如下:

 
[root@localhost ~]# mysql
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> reset master;
Query OK, 0 rows affected (0.01 sec)

2.也可以单独删除指定的日志文件,如下删除第一个日志文件,SQL代码如下:

 
MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       264 |
| mariadb-bin.000002 |       264 |
| mariadb-bin.000003 |       245 |
+--------------------+-----------+
3 rows in set (0.00 sec)
 
MariaDB [(none)]> purge master logs to "mariadb-bin.000001";
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000002 |       264 |
| mariadb-bin.000003 |       245 |
+--------------------+-----------+
3 rows in set (0.00 sec)

3.或者单独删除指定日期的日志文件,如下删除2018/01/01以前所有日志,SQL代码如下:

 
MariaDB [(none)]> purge master logs before "20180101";
Query OK, 0 rows affected (0.00 sec)

◆系统错误日志◆

错误日志包含了数据库启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息,错误日志对于数据库异常排查,有很大的帮助.

开启错误日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

 
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
log-error="/var/log/mariadb/mariadb.log"

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

 
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# 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 variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.01 sec)
 
[root@localhost ~]# cat /var/log/mariadb/mariadb.log |head -n 10
181224 20:28:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
181224 20:28:49 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 1622 ...
181224 20:28:49 InnoDB: The InnoDB memory heap is disabled
181224 20:28:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
181224 20:28:49 InnoDB: Compressed tables use zlib 1.2.7
181224 20:28:49 InnoDB: Using Linux native AIO
181224 20:28:50 InnoDB: Initializing buffer pool, size = 128.0M
181224 20:28:50 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!

删除错误日志:

 
[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
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)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

◆慢查询日志◆

慢查询日志是记录查询时长超过指定时间的日志,慢查询日志主要用来记录执行时间较长的查询语句,通过慢查询日志,可以找出执行时间较长、执行效率较低的语句,然后进行优化.

开启错误日志:

1.默认情况下二进制日志是关闭状态的,可以通过修改数据库配置文件来设置开启日志.

 
[root@localhost ~]# vim /etc/my.cnf
 
[mysqld]
log-slow-queries="/tmp"
long_query_time=n

2.添加完毕后,重启数据库进程,即可打开二进制日志啦.

 
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql

	MariaDB [(none)]> show global variables like "%read_only%";
	+---------------+-------+
	\| Variable_name \| Value \|
	+---------------+-------+
	\| read_only \| OFF \|
	+---------------+-------+
	1 row in set (0.00 sec)

	MariaDB [(none)]> set global read_only=1; #1是只读,0是读写
	Query OK, 0 rows affected (0.08 sec)

	MariaDB [(none)]> show global variables like "%read_only%"; #再次查询读写状态
	+---------------+-------+
	\| Variable_name \| Value \|
	+---------------+-------+
	\| read_only \| ON \|
	+---------------+-------+
	1 row in set (0.00 sec)

	MariaDB [(none)]> flush tables with read lock; #锁定数据表
	Query OK, 0 rows affected (0.00 sec)

	MariaDB [(none)]> unlock tables; #解除表锁定
	Query OK, 0 rows affected (0.00 sec)

	[root@localhost ~]# mysqldump -u root -p 数据库名 >备份文件.sql #备份单个数据库
	[root@localhost ~]# mysqldump -u root -p 数据库名表名 >备份文件.sql #备份数据库中单个表
	[root@localhost ~]# mysqldump -u root -p --databases 数据库名 >备份文件.sql #备份指定的数据库
	[root@localhost ~]# mysqldump -u root -p --all-databases >备份文件.sql #备份整个数据库

	[root@localhost ~]# mysql -u root -p 数据库名 < 备份文件.sql #还原单个数据库
	[root@localhost ~]# mysql -u root -p 还原到的数据库名 < 备份文件.sql #还原数据库中单个表
	[root@localhost ~]# mysql -u root -p < 备份文件.sql #还指定的数据库
	[root@localhost ~]# mysql -u root -p < 备份文件.sql #还原整个数据库

	[root@localhost ~]# mysqlhotcopy --flushlog -u="root" -p="123" --regexp=^l /root/

	#以上参数解释: -u 用户名 -p 密码 --regexp=^l 备份开头是l的数据库 /root 备份文件保存位置

	[root@localhost ~]# ll
	总用量 0
	drwxr-x---. 2 mysql mysql 39 9月 15 04:31 lyshark

	[root@localhost ~]# systemctl stop mariadb

	[root@localhost ~]# cp -a /root/lyshrk /usr/local/mysql/data
	[root@localhost ~]# chown -R mysq.mysql /usr/local/mysql/data

	[root@localhost ~]# systemctl start mariadb

	MariaDB [none]> select * from 数据库名称.表名称 INTO OUTFILE "/root/xxx.txt"
	[root@localhost ~]# cat /root/xxx.txt

	[root@localhost ~]# mysql -uroot -p --execute="select * from 表名;" 数据库名 > filename.txt
	[root@localhost ~]# mysql -uroot -p --verical --execute="select * from person;" 数据库名称 > /root/lyshark.txt
	[root@localhost ~]# mysql -uroot -p --html --execute="select * from person;" 数据库名称 > /root/lyshark.html
	[root@localhost ~]# mysql -uroot -p --xml --execute="select * from person;" 数据库名称 > /root/lyshark.xml

	[root@localhost ~]# load data INFILE '/root/wang.txt' INTO TABLE 数据库.表名称;
	[root@localhost ~]# mysqlmport -uroot -p 数据库名称 filename.txt

	[root@localhost ~]# vim /etc/my.cnf

	[mysqld]
	log-bin="/tmp" #设置开启日志,也可不指定日志保存位置
	expire_logs_days = 10 #设置日志自动清理天数
	max_binlog_size = 100M #定义了单个文件的大小限制

	[root@localhost ~]# systemctl restart mariadb
	[root@localhost ~]# 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 variables like 'log_%';
	+---------------------------------+-------------------------------------------------
	\| Variable_name \| Value
	+---------------------------------+-------------------------------------------------
	\| log_bin \| ON
	\| log_bin_trust_function_creators \| OFF
	\| log_error \| /var/log/mariadb/mariadb.log
	\| log_output \| FILE
	\| log_queries_not_using_indexes \| OFF
	\| log_slave_updates \| OFF
	\| log_slow_filter \| admin,filesort,filesort_on_disk,full_join,
	\| log_slow_queries \| OFF
	\| log_slow_rate_limit \| 1
	\| log_slow_verbosity \|
	\| log_warnings \| 1
	+---------------------------------+-------------------------------------------------
	11 rows in set (0.01 sec)

	MariaDB [(none)]> show binary logs;
	+--------------------+-----------+
	\| Log_name \| File_size \|
	+--------------------+-----------+
	\| mariadb-bin.000001 \| 245 \|
	+--------------------+-----------+
	1 row in set (0.02 sec)

	[root@localhost ~]# mysqlbinlog mariadb-bin.000001

	/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
	/!40019 SET @@session.max_insert_delayed_threads=0/;
	/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
	DELIMITER /!/;
	mysqlbinlog: File 'mariadb-bin.000001' not found (Errcode: 2)
	DELIMITER ;
	# End of log file
	ROLLBACK /* added by mysqlbinlog */;
	/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
	/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

	[root@localhost ~]# mysql

	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MariaDB connection id is 4
	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)]> reset master;
	Query OK, 0 rows affected (0.01 sec)

	MariaDB [(none)]> purge master logs before "20180101";
	Query OK, 0 rows affected (0.00 sec)

	[root@localhost ~]# vim /etc/my.cnf

	[mysqld]
	log-error="/var/log/mariadb/mariadb.log"

	[root@localhost ~]# mysql
	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MariaDB connection id is 3
	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)]> flush logs;
	Query OK, 0 rows affected (0.01 sec)

	[root@localhost ~]# vim /etc/my.cnf

	[mysqld]
	log-slow-queries="/tmp"
	long_query_time=n