MySQL必知必会 -- 数据库维护

数据备份

mysqldump

使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。

[root@server1 sql]# ls
[root@server1 sql]# mysqldump -uroot -pcaoaoyuan test > /sql/test.sql	/备份
[root@server1 sql]# ls
test.sql

MariaDB [(none)]> drop database test;		/删除数据库
Query OK, 3 rows affected (0.06 sec)

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

MariaDB [(none)]> quit
Bye

[root@server1 sql]# mysql -uroot -pcaoaoyuan -e "create database test;"		/创建数据库
[root@server1 sql]# mysql -uroot -pcaoaoyuan test < test.sql		/导入之前的数据
[root@server1 sql]# mysql -uroot -pcaoaoyuan -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@server1 sql]# mysql -uroot -pcaoaoyuan -e "use test;show tables;"
+----------------+
| Tables_in_test |
+----------------+
| hour           |			/之前的数据回来了
| linux          |
| ss             |
+----------------+

mysqlhotcopy

[root@server1 sql]# mysqlhotcopy -u root -p caoaoyuan test  /sql/
Flushed 3 tables with read lock (`test`.`hour`, `test`.`linux`, `test`.`ss`) in 0 seconds.
Locked 0 views () in 0 seconds.
Copying 4 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 3 tables (4 files) in 0 seconds (0 seconds overall).
[root@server1 sql]# ls
test
[root@server1 sql]# cd test/
[root@server1 test]# ls
db.opt  hour.frm  linux.frm  ss.frm

SELECT INTO OUTFILE

在使用SELECT INTO OUTFILE的时候要注意关闭 selinux 。

备份：

[root@server1 sql]# chown -R mysql.mysql /sql	/更改目录拥有者
[root@server1 sql]# getenforce 
Permissive

MariaDB [test]> select * from linux  into outfile '/sql/linux.txt'; 
Query OK, 12 rows affected (0.01 sec)

MariaDB [test]> Ctrl-C -- exit!
Aborted
[root@server1 test]# cd ..
[root@server1 sql]# ls
linux.txt		/文件出现

恢复：

MariaDB [test]> delete from linux;
Query OK, 12 rows affected (0.08 sec)

MariaDB [test]> select * from linux;
Empty set (0.00 sec)

MariaDB [test]> load data infile '/sql/linux.txt' into table linux;
Query OK, 12 rows affected (0.00 sec)                
Records: 12  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> select * from linux;
+--------+--------+------+------+-------+------------+-------+
| user   | passwd | sex  | age  | price | math_price | class |
+--------+--------+------+------+-------+------------+-------+
| user2  | 222    | girl | 23   | 155   | 36         |     1 |
| user3  | 333    | boy  | 12   | 78    | 66         |     1 |
| user4  | 444    | boy  | 22   | NULL  | NULL       |     2 |
| user5  | 555    | boy  | 30   | NULL  | 43         |     2 |
| user1  | 111    | boy  | 18   | 35    | 88         |     1 |
| user6  | 666    | girl | 16   | 35    | 43         |     2 |
| user7  | 777    | boy  | NULL | NULL  | NULL       |  NULL |
| user8  | 888    | girl | 19   | NULL  | NULL       |     2 |
| user9  | 999    | boy  | 24   | 27    | 46         |     2 |
| user10 | 000    | boy  | 30   | 28    | 39         |     1 |
| user11 |        |      | NULL | NULL  | NULL       |  NULL |
| user12 | 222    | boy  | 27   | NULL  | NULL       |  NULL |
+--------+--------+------+------+-------+------------+-------+
12 rows in set (0.00 sec)

数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。

ANALYZE TABLE

用来检查表键是否正确。

MariaDB [test]> analyze table linux;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| test.linux | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.00 sec)

CHECK TABLE

CHECK TABLE用来针对许多问题对表进行检查。

MariaDB [test]> check table linux,ss,hour;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| test.linux | check | status   | OK       |
| test.ss    | check | status   | OK       |
| test.hour  | check | status   | OK       |
+------------+-------+----------+----------+
3 rows in set (0.00 sec)

• 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。
• 如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

诊断问题

1. mysql --help /获取帮助
2. [root@server1 mariadb]# mysql --version /查看版本
3. [root@server1 mariadb]# mysql -uroot -pcaoaoyuan --verbose /显示全文本消息

查看日志文件

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用–log-error命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此
  日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用–log命令行选项更改。
• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。
• 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。