9.MySQL管理 mysqladmin mysqldump mysqlshow

可以对MySQL进行一些管理级别
[root@localhost ~]# mysqladmin --help
mysqladmin  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
  --bind-address=name IP address to bind to.
  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  --debug-info        This is a non-debug version. Catch this and exit.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name 
                      Directory for character set files.
  --default-character-set=name 
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -b, --no-beep       Turn off beep on error.
  -p, --password[=name] 
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -,, --password1[=name] 
                      Password for first factor authentication plugin.
  -,, --password2[=name] 
                      Password for second factor authentication plugin.
  -,, --password3[=name] 
                      Password for third factor authentication plugin.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --relative      Show difference between current and previous values when
                      used with -i. Currently only works with extended-status.
  -s, --silent        Silently exit if one can't connect to server.
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl-mode=name     SSL connection mode.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1.2
  --ssl-fips-mode=name 
                      SSL FIPS mode (applies only for OpenSSL); permitted
                      values are: OFF, ON, STRICT
  --tls-ciphersuites=name 
                      TLS v1.3 cipher to use.
  --ssl-session-data=name 
                      Session data file to use to enable ssl session reuse
  --ssl-session-data-continue-on-failed-reuse 
                      If set to ON, this option will allow connection to
                      succeed even if session data cannot be reused.
  --server-public-key-path=name 
                      File path to the server public RSA key in PEM format.
  --get-server-public-key 
                      Get server public key
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect-timeout=# 
  --shutdown-timeout=# 
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.
  --enable-cleartext-plugin 
                      Enable/disable the clear text authentication plugin.
  --show-warnings     Show warnings after execution
  --compression-algorithms=name 
                      Use compression algorithm in server/client protocol.
                      Valid values are any combination of
                      'zstd','zlib','uncompressed'.
  --zstd-compression-level=# 
                      Use this compression level in the client/server protocol,
                      in case --compression-algorithms=zstd. Valid range is
                      between 1 and 22, inclusive. Default is 3.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}          Value (after reading options)
----------------------------------------- --------------------------------
bind-address                              (No default value)
count                                     0
force                                     FALSE
compress                                  FALSE
character-sets-dir                        (No default value)
default-character-set                     auto
host                                      (No default value)
no-beep                                   FALSE
port                                      0
relative                                  FALSE
socket                                    (No default value)
sleep                                     0
ssl-ca                                    (No default value)
ssl-capath                                (No default value)
ssl-cert                                  (No default value)
ssl-cipher                                (No default value)
ssl-key                                   (No default value)
ssl-crl                                   (No default value)
ssl-crlpath                               (No default value)
tls-version                               (No default value)
tls-ciphersuites                          (No default value)
ssl-session-data                          (No default value)
ssl-session-data-continue-on-failed-reuse FALSE
server-public-key-path                    (No default value)
get-server-public-key                     FALSE
user                                      (No default value)
verbose                                   FALSE
vertical                                  FALSE
connect-timeout                           43200
shutdown-timeout                          3600
plugin-dir                                (No default value)
default-auth                              (No default value)
enable-cleartext-plugin                   FALSE
show-warnings                             FALSE
compression-algorithms                    (No default value)
zstd-compression-level                    3

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

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 in current 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-replica        Start replication
  start-slave        Deprecated: use start-replica instead
  stop-replica        Stop replication
  stop-slave        Deprecated: use stop-replica instead
  variables             Prints variables available
  version        Get version info from server

创建数据库

[root@localhost ~]# mysqladmin -uroot -p000000 create db02
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

查看数据库

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p000000 -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| db02               |
| db1                |
| information_schema |
| ithe               |
| mysql              |
| nltest             |
| performance_schema |
| sys                |
| testit             |
+--------------------+

删除数据库

[root@localhost ~]# mysqladmin -uroot -p000000 drop db02
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'db02' database [y/N] y
Database "db02" dropped

二进制日志

查找数据库中的对象

统计所有表的记录

[root@localhost mysql]# mysqlshow -uroot -p000000 --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| db1                |      2 |            3 |
| information_schema |     79 |        34106 |
| ithe               |     11 |      2351109 |
| mysql              |     38 |         3969 |
| nltest             |     10 |          123 |
| performance_schema |    111 |       450109 |
| sys                |    101 |         7120 |
| testit             |      2 |       999999 |
+--------------------+--------+--------------+
8 rows in set.
[root@localhost mysql]# 

指定表的纪录

[root@localhost mysql]# mysqlshow -uroot -p000000 db1 --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: db1
+-----------+----------+------------+
|  Tables   | Columns  | Total Rows |
+-----------+----------+------------+
| score     |        6 |          3 |
| user_logs |        5 |          0 |
+-----------+----------+------------+
2 rows in set.

数据备份

[root@localhost ~]# mysqldump -uroot -p000000 db1 > db1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

-t不包含创建表的语句
[root@localhost ~]# mysqldump -uroot -p000000 -t db1 > db2.sql

mysql备份的默认目录

导入数据

加载sql文件

会一次性执行所有sql文件