Mysql 8 常用命令测试

1.创建数据库，帐号及授权

create database testdb;

CREATE USER 'rusking'@'%' IDENTIFIED BY '12345678';

CREATE USER 'rusking'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
GRANT ALL ON testdb.* TO 'rusking'@'%' WITH GRANT OPTION;

flush privileges;

2.登录Mysql:

mysql -u db_user -pdb_password -h db_host db_name
mysql -urusking -p12345678 -h loclahost testdb

3.查看Mysql版本

[root@www ~]# mysql -V
mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)
[root@www ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.01 sec)

mysql> status;
--------------
mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          20
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.15 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 hour 5 min 14 sec

4. 查看及修改加密方式

select user,plugin from user ;

ALTER USER 'rusking'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';

5. 导入数据库：

常用source 命令
进入mysql数据库控制台,
如mysql -u root -p
mysql>use 数据库
然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
mysql>source d:wcnc_db.sql

 

6.其它命令

show databases;--查看所有数据库

use testdb;--切换到testdb数据库

show tables;---查看当前库中的所有表

select user(); --查看当前登录用户

select database();--查看当前连接的数据库

describe tablename; --查看表结构

show status like '%connections'; --查看当前运行的Mysql所使用的跟connections相关的参数。

show status;  --查看当前运行的Mysql所使用的所有参数

show variables like ‘%connections%'; --查看跟connections相关的所有变量

show variables ; --查看所有变量

show processlist; --查看当前数据库正在处理的队列

show full processlist; 

这两个命令的结果都是从information_schema.processlist表取的结果

 

 

=====MySQLTuner

这个工具会根据你的数据库环境的配置给出一些修改建议。

What is MySQLTuner
MySQLTuner is a great little utility to tune up your MySQL instance to perform at it best.

Install MySQLTuner
#yum -y install mysqltuner

Run MySQLTuner
Simply type mysqltuner and get overwhelmed with a list of tweak suggestions.
#mysqltuner