MySQL学习笔记4

客户端工具的使用：

MySQL： mysql命令行工具，一般用来连接访问mysql的数据。

案例：使用mysql客户端工具连接服务器端（用户名：root；密码：123456）.

[root@mysql-server ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.43 MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

mysql官方不建议，将密码直接写在-p选项之后。而是-p之后按回车键，然后在Enter password：后面输入密码。这样安全些。

案例：连接10.1.1.100服务器上的MySQL数据库（用户名：chang，密码：123）

mysql -h 10.1.1.100 -P 3306 -uchang -p 
Enter Password: 123

案例：根据不同的套接字连接不同的数据库。

[root@mysql-server ~]# mysql -S /mysql_3307/mysql.sock -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.43 Source distributionCopyright (c) 2000, 2023, 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.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_3307         |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)[root@mysql-server ~]# mysql -S /tmp/mysql.sock -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.43 MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_3306         |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

连接哪个数据库是由套接字来连接的。-S这个选项。

案例：使用非交互式操作(在shell终端执行sql语句)，在不进入MySQL内部的情况下，执行SQL语句，获取数据信息。

[root@mysql-server ~]# mysql -uroot -p -e "show databases;"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_3306         |
| performance_schema |
| sys                |
+--------------------+

没有进入到数据库，直接在外边执行了show databases命令查看数据库。

扩展了解：

客户端工具：mysqladmin使用：

mysqladmin：客户端管理mysql数据库工具。

案例：更改root账号的密码为root。

mysqladmin password 'root' -uroot -p
Enter Password: 

案例：更改密码后，建议刷新授权表（mysql>flush privileges;）

mysqladmin reload -uroot -p
Enter password: 

案例：停止mysql实例：

mysqladmin shutdown -p
Enter password: 

我们也可以使用service mysql_3306 stop命令来停止，是可以的。

案例：查看mysql的状态：

mysqladmin status -p
Enter password: 

[root@mysql-server ~]# mysqladmin status -p
Enter password:
Uptime: 2864  Threads: 1  Questions: 24  Slow queries: 0  Opens: 115  Flush tables: 1  Open tables: 108  Queries per second avg: 0.008

案例：打印可用变量（mysql本身预置了很多变量信息）。

mysqladmin variables -p
Enter password: 

案例：管理mysql的时候，要确认下mysql的版本。

[root@mysql-server ~]# mysqladmin version -p
Enter password:
mysqladmin  Ver 8.42 Distrib 5.7.43, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2023, 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.Server version          5.7.43
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 53 min 0 secThreads: 1  Questions: 28  Slow queries: 0  Opens: 116  Flush tables: 1  Open tables: 109  Queries per second avg: 0.008