mysql命令速成

官方下载地址：MySQL :: Download MySQL Community Server

安装：

C:\Users\Administrator> mysqld --initialize-insecure   // 1.初始化MySQL, 生成data等相关目录以及root用户及默认密码  --user=mysql  --console

C:\Users\Administrator> mysqld install                // 2.注册 mysql 服务 mysqld --install "NewMySQL" --defaults-file="C:\confluence\mysql-8.0.30-winx64\my.ini"
Service successfully installed.

C:\Users\Administrator> sc delete mysql              // 删除 mysql 服务
[SC] DeleteService 成功
 
C:\Users\Administrator> net start mysql              // 3.启动 mysql 服务
MySQL 服务正在启动 .
MySQL 服务已经启动成功。
C:\Users\Administrator> net stop mysql               // 停止 mysql 服务
  
C:\Users\Administrator>mysql -u root -p              // 4.登录 mysql
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1.访问MySQL

dos> mysql --version
dos> mysql -u root -h 127.0.0.1 -P 3306 -p 
******root

2. 创建查看数据库

//创建、查看数据库
mysql> SELECT VERSION();
mysql> SHOW GLOBAL VARIABLES LIKE 'port';
mysql> CREATE DATABASE bugs;                //创建一个数据库bugs
mysql> SHOW DATABASES;
mysql> USE bugs;                            //访问数据库

3. 创建用户、密钥，并授权

//创建用户、密钥，并授权
mysql> USE bugs;
mysql> CREATE USER  bugs@localhost IDENTIFIED BY '123456' ;       //高版本8.0以上,创建一个用户bugs
mysql> SELECT host,user,plugin FROM mysql.user;
mysql> SELECT PASSWORD('bugs');                 //高版本8.0以上版本,去掉了select password()函数,bu再支持
mysql> GRANT ALL ON bugs.* TO bugs@localhost;   //为用户bugs授权 
//低版本5.7以下：mysql> GRANT select,insert,update,delete,index,alter,create,lock tables,drop,references ON bugs.* TO bugs@localhost IDENTIFIED BY '123456';
mysql> ALTER USER 'bugs'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
mysql> SELECT host,user,plugin FROM mysql.user;  
mysql> FLUSH PRIVILEGES;                        //刷新用户权限

root@ubuntu:/home/vmuser#    mysql -u root -e "GRANT ALL PRIVILEGES ON bugs.* TO bugs@localhost IDENTIFIED BY '123456'"

4. 用户密钥变更

//密码变更,：
mysql> ALTER USER 'bugs'@'localhost' IDENTIFIED BY '新密码'; 
mysql> ALTER USER 'bugs'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
mysql> FLUSH PRIVILEGES;                        //刷新用户权限

bugzilla: Bugzilla installation error with MySQL version 8.0 - Stack Overflow