MariaDB SHOW,ANALYZE,CHECK
jlive@MacBook-Pro:mariadb_scripts $mysql
Welcome to the MySQL monitor.
Your MySQL connection id is 71
Server version: 5.5.5-10.1.10-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(jlive)[(none)]>SHOW DATABASES;
+--------------------+
| Database
+--------------------+
| crashcourse
| information_schema |
| mysql
| performance_schema |
| phpmyadmin
| zabbix
+--------------------+
6 rows in set (0.00 sec)
(jlive)[(none)]>DROP DATABASE crashcourse;
Query OK, 14 rows affected (0.03 sec)
(jlive)[(none)]>CREATE DATABASE crashcourse;
Query OK, 1 row affected (0.00 sec)
(jlive)[(none)]>USE crashcourse;
Database changed
(jlive)[crashcourse]>SOURCE ./create.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
(jlive)[crashcourse]>SOURCE ./populate.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
(jlive)[crashcourse]>SHOW TABLES;
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers
| orderitems
| orders
| productnotes
| products
| vendors
+-----------------------+
6 rows in set (0.01 sec)
(jlive)[crashcourse]>SHOW CREATE DATABASE crashcourse;
+-------------+----------------------------------------------------------------------+
| Database
+-------------+----------------------------------------------------------------------+
| crashcourse | CREATE DATABASE `crashcourse` |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for jlive@%
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jlive'@'%' IDENTIFIED BY PASSWORD
'*C15A2384C61922FBA3DA581D
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>SHOW ERRORS;
Empty set (0.00 sec)
(jlive)[crashcourse]>SHOW WARNINGS;
Empty set (0.00 sec)
(jlive)[crashcourse]>SHOW PROCESSLIST;
+----+--------+---------------------+-------------+---------+------+-------+------------------+----------+
| Id | User
+----+--------+---------------------+-------------+---------+------+-------+------------------+----------+
|
|
|
|
|
|
| 10 | zabbix | localhost
| 13 | zabbix | localhost
| 14 | zabbix | localhost
| 15 | zabbix | localhost
| 17 | zabbix | localhost
| 18 | zabbix | localhost
| 23 | zabbix | localhost
| 62 | zabbix | localhost
| 66 | zabbix | localhost
| 67 | zabbix | localhost
| 68 | zabbix | localhost
| 71 | jlive
+----+--------+---------------------+-------------+---------+------+-------+------------------+----------+
18 rows in set (0.00 sec)
SHOW STATUS;
SHOW CREATE TABLE orders;
ANALYZE,CHECK也可以查看相关信息
(jlive)[crashcourse]>ANALYZE TABLE orders;
+--------------------+---------+----------+----------+
| Table
+--------------------+---------+----------+----------+
| crashcourse.orders | analyze | status
+--------------------+---------+----------+----------+
1 row in set (0.00 sec)
(jlive)[crashcourse]>CHECK TABLE orders, orderitems;
+------------------------+-------+----------+----------+
| Table
+------------------------+-------+----------+----------+
| crashcourse.orders
| crashcourse.orderitems | check | status
+------------------------+-------+----------+----------+
2 rows in set (0.00 sec)
显示执行进度--变量progress_report_time
默认为每5秒刷新一次执行进度，取值范围为1-5，单位秒，当取值为0时就禁用了progress report
mytop脚本提供了更丰富的功能
MariaDB [(none)]> SHOW VARIABLES LIKE 'progress_report_time';
+----------------------+-------+
|
Variable_name
+----------------------+-------+
|
progress_report_time | 5
+----------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET progress_report_time = 0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [crashcourse]> ALTER TABLE products engine=myisam;
Query OK, 14 rows affected (0.01 sec)
Records:
14
定期check,repair,tunning数据库
1.创建系统用户sysuser
root@jlive:~#useradd
-r -m sysuser -s /sbin/nologin
2.创建数据库用户maint并赋予SELECT,INSERT权限
GRANT SELECT,INSERT ON *.* TO maint IDENTIFIED BY 'maintuserpassword';
3.在系统用户sysuer家目录创建用户级的.my.cnf
root@jlive:~#cat >/home/sysuser/.my.cnf <<HERE
[client]
user = maint
password = maintuserpassword
HERE
root@jlive:~#chmod
600 /home/sysuser/.my.cnf
4.创建计划任务
root@jlive:~#cat >/etc/cron.d/mariadb <<HERE
15 23 * *
1
15 23 * * 2-7 sysuser /usr/bin/mysqlcheck -A --optimize
HERE
