【MySQL进阶篇】管理

1、系统数据库

MySQL数据库安装完成之后，自带以下四个数据库，具体作用如下：
数据库含义mysql存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）information_schema提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等performance_schema为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数sys包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

2、常用工具

· mysql

mysql的客户端工具

#语法：

    mysql [options] [database]

选项：

    -u,--user=name        #指定用户名

    -p,--password[=name]        #指定密码

    -h,--host=name        #指定服务器IP或域名

    -P,--port=port        #指定连接端口

    -e,--execute=name        #执行SQL语句并退出

-e选项可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤为方便。

 mysql -h192.168.235.129 -P3306 -uroot -p0219423 test -e 'select * from course'

· mysqladmin

mysqladmin是一个执行管理操作的客户端程序，可以用它来检查服务器的配置和当前状态，创建并删除数据库等。

#通过帮助文档查看选项：

    mysqladmin --help 

where command is a one or more of: (commands may be shortened)
  create databasename   create a new database
  debug                 instruct server to write debug information to log
  drop databasename     delete a database and all its tables
  extended-status       gives an extended status message from the server
  flush-hosts           flush all cached hosts
  flush-logs            flush all logs
  flush-status          clear status variables
  flush-tables          flush all tables
  flush-threads         flush the thread cache
  flush-privileges      reload grant tables (same as reload)
  kill id,id,...        kill mysql threads
  password new-password change old password to new-password, mysql 4.1 hashing.
  old-password new-password change old password to new-password in old format.

  ping                  check if mysqld is alive
  processlist           show list of active threads in server
  reload                reload grant tables
  refresh               flush all tables and close and open logfiles
  shutdown              take server down
  status                gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server

#示例：

    mysqladmin -uroot -p0219423 drop 'test'

    mysqladmin -uroot -p0219423 version 

· mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog日志管理工具。

#语法：

    mysqlbinlog [options] log-files1 log-files2 ...

选项：

    -d,--database=name        #指定数据库名称，只列出指定的数据库相关操作

    -o.--offset=#        #忽略掉日志中的前n行命令

    -r,--result-file=name        #将输出的文本格式日志输出到指定文件

    -S,--short-form        #显示简单格式，省略掉一些信息

    --start-datatime=data1 --stop -datatime=data2        #指定日期间隔内的所有日志

    --start-position=pos1 --stop -position=pos2        #指定位置间隔内的所有日志

· mysqlshow

mysqlshow客户端查找工具，用来很快的查找存在哪些数据库、数据库中的表、表中的列或者索引

#语法:

    mysqlshow [options] [db_name [table_name [col_name]]]

#选项：

    --count        #显示数据库及表的统计信息（数据库、表均可以不指定）

    -i        #显式指定数据库或指定表的状态信息

示例：

mysqlshow -uroot -p0219423 --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| information_schema |     79 |        26188 |
| mysql              |     37 |         3861 |
| performance_schema |    110 |       206515 |
| sys                |    101 |         4672 |
| test               |      3 |           24 |
+--------------------+--------+--------------+
5 rows in set.
[root@localhost mysql]# mysqlshow -uroot -p0219423 test --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: test
+---------+----------+------------+
| Tables  | Columns  | Total Rows |
+---------+----------+------------+
| course  |        3 |          3 |
| score   |        3 |         18 |
| teacher |        2 |          3 |
+---------+----------+------------+
3 rows in set.

[root@localhost mysql]# mysqlshow -uroot -p0219423 test course --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: test  Table: course  Rows: 3
+--------+-------------+--------------------+------+-----+---------+-------+---------------------------------+--------------+
| Field  | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment      |
+--------+-------------+--------------------+------+-----+---------+-------+---------------------------------+--------------+
| c_id   | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |       | select,insert,update,references | 课程编号 |
| c_name | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |       | select,insert,update,references | 课程名称 |
| t_id   | varchar(50) | utf8mb4_0900_ai_ci | YES  |     |         |       | select,insert,update,references | 教师编号 |
+--------+-------------+--------------------+------+-----+---------+-------+---------------------------------+--------------+
 mysqlshow -uroot -p0219423 test course -i
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: test  Wildcard: course
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| course | InnoDB | 10      | Dynamic    | 3    | 5461           | 16384       | 0               | 0            | 0         |                | 2024-07-19 15:06:44 |             |            | utf8mb4_0900_ai_ci |          |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

· mysqldump

mysqldump客户端工具是用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

#语法：

    mysqldump [options] db_name [tables]

    mysqldump [options] --database/-B db1 [db2 db3 ...]

    mysqldump [options] --all databases/-A

#连接选项：

    -u,--user=name        #指定用户名

    -p,--password[=name]        #指定密码

    -h,--host=name        #指定服务器ip或域名

    -p,--port=#        #指定连接端口

#输出选项：

    --add-drop -database        #在每个数据库创建语句前加上drop database语句

    --add-drop -table        #在每个表创建语句前加上drop table语句，默认开启；不开启（--skip -add -drop -table）

    -n,--no-create-db        #不包含数据库的创建语句

    -t,--no-create-info        #不包含数据表的创建语句

    -d,--no-data        #不包含数据

    -T,--tab=name        #自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件

mysqldump -uroot -p0219423 -t test > test.sql
mysqldump -uroot -p0219423 -d test > test.sql
mysqldump -uroot -p0219423 -T /var/lib/mysql-files/ test score;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# cd /var/lib/mysql-files/;
[root@localhost mysql-files]# ll
总用量 8
-rw-r--r--. 1 root  root  1469 8月   2 21:59 score.sql
-rw-r-----. 1 mysql mysql  162 8月   2 21:59 score.txt

· mysqlimport/source

mysqlimport是客户端数据导入工具，用来导入mysqldump 加 -T参数后导出的文本文件。

#语法：

    mysqlimport [options] da_name textfile1 [textfile2...]

示例：

 mysqlimport -uroot -p0219423 test /var/lib/mysql-files/score.txt;
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
test.score: Records: 18  Deleted: 0  Skipped: 0  Warnings: 0

如果需要导入sql文件，可以使用source命令

source /root/xxx.sql