数据备份是理员非常重要的工作之一,系统意外崩溃或者硬件的损坏都可能导致数据库的丢失,因此MariaDB管理员应该定期地备份数据库,使得在意外情况发生时,尽可能减少损失.
♥ 文章声明 ♥
该系列文章部分文字描述,参考于以下文献,化繁为简.
《MySQL5.7从入门到精通》 - 刘增杰
◆数据备份还原◆
全局锁定: 在备份数据库之前,我们应该先将数据库设置为只读模式,下面将设置全局为只读模式.
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