MySQL三分钟快速入门

前言

你需要准备什么？

• 可以连接互联网的一台Macbook
• Terminal终端

一、安装

• 执行安装命令。

brew install mysql

Tips：brew是MacOS下的包管理工具，若命令不存在，请安装后执行。

• 执行结果

Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/services).
No changes to formulae.

==> Downloading https://mirrors.aliyun.com/homebrew/homebrew-bottles/bottles/protobuf-3.13.0.catalina.bottle.tar.gz
==> Downloading https://mirrors.aliyun.com/homebrew/homebrew-bottles/bottles/mysql-8.0.21_1.catalina.bottle.tar.gz
==> Installing dependencies for mysql: protobuf
==> Installing mysql dependency: protobuf
==> Pouring protobuf-3.13.0.catalina.bottle.tar.gz
==> Caveats
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> Summary
🍺  /usr/local/Cellar/protobuf/3.13.0: 266 files, 19.8MB
==> Installing mysql
==> Pouring mysql-8.0.21_1.catalina.bottle.tar.gz
==> /usr/local/Cellar/mysql/8.0.21_1/bin/mysqld --initialize-insecure --user=yunnasheng --basedir=/usr/local/Cellar/mysql/8.0.21_1 --datadir=/usr/local/var/mysql --tmpdir=/tmp
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql/8.0.21_1: 290 files, 291.2MB
==> `brew cleanup` has not been run in 30 days, running now...
Removing: /usr/local/Cellar/gettext/0.20.1... (1,893 files, 18.4MB)
Removing: /Users/yunnasheng/Library/Caches/Homebrew/gettext--0.20.1.catalina.bottle.tar.gz... (8.3MB)
Removing: /usr/local/Cellar/openssl@1.1/1.1.1d... (7,983 files, 17.9MB)
Removing: /usr/local/Cellar/pcre/8.43... (204 files, 5.5MB)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/tree... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/wget... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/libidn2... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/libunistring... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/nginx... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/gettext... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/openssl@1.1... (64B)
Removing: /Users/yunnasheng/Library/Logs/Homebrew/pcre... (64B)
Pruned 1 symbolic links and 3 directories from /usr/local
==> Caveats
==> protobuf
Emacs Lisp files have been installed to:
  /usr/local/share/emacs/site-lisp/protobuf
==> mysql
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

1.2 启动

• 执行启动MySQL命令

 mysql.server start 

• 执行结果

Starting MySQL
. SUCCESS!

1.3 配置安全策略

执行命令

mysql_secure_installation

执行命令后，根据提示完成密码设置，执行结果

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file
# 选择一个密码级别
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
# 设置密码
New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

# 是否要删除匿名用户？按 y 表示同意，其他任意键表示拒绝
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

# 通常root用户只从本地登录，这样可以确保有人无法猜测来自网络的root密码
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

# 是否要禁止远程root登录？
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

# 是否要删除测试数据库？
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : no

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
# 现在重新加载授权表？
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

二、快速开始

2.1 登录MySQL

• 输入命令后，根据提示输入刚才设置的root用户的登录密码

mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21 Homebrew

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

2.2 查看有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

2.3 创建数据库

• 新建一个数据库名为local_test的库，设置字符集为utf8mb4

create database local_test character set utf8mb4;

• 验证数据库是否创建成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| local_test         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2.4 创建用户

• 创建一个登录名为dev_user，密码为@dev6pWd，允许在任意IP登录的用户

mysql>create user 'dev_user'@'%' identified by '@dev6pWd';
Query OK, 0 rows affected (0.01 sec)

如果创建用户时出现错误：Your password does not satisfy the current policy requirements 请先查看密码策略后，修改密码再创建。

• 查看密码策略

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

扩展语法

• 修改用户 - bash rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
• 修改密码 - set password for '用户名'@'IP地址'=Password('新密码');
• 指定ip登录 -（ 192.168.1.开头的local_admin用户,密码为admin登录） create user 'local_admin'@'192.168.1.%' identified by 'admin';

2.5 用户授权

• 给dev_user用户授予local_test库的所有权限，当然grant这个命令不会给dev_user，只有root用户才有这个命令的权限。

grant all privileges on local_test.* to 'dev_user'@'%';
Query OK, 0 rows affected (0.01 sec)

• 刷新授权信息

flush privileges;

2.6 创建表

• 切换dev_user用户

# 退出MySQL
mysql> exit
Bye
# 使用dev_user用户登录
mysql -u dev_user -p
# 输入密码
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.21 Homebrew

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

• 切换到local_test数据库

# 查看拥有的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| local_test         |
+--------------------+
2 rows in set (0.00 sec)
# 使用local_test库
mysql> use local_test;
Database changed
mysql>
# 显示所有表
mysql> show tables;
Empty set (0.00 sec)

• 创建表

CREATE TABLE IF NOT EXISTS `lt_dict`(
   `id` VARCHAR(64) NOT NULL ,
   `code` VARCHAR(128) NOT NULL,
   `name` VARCHAR(128) NOT NULL,
   `status` VARCHAR(12) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 插入数据

INSERT INTO lt_dict (id, code, name, status) VALUES ('3c3da1d4b3b94bd9b33fb340dfdb605c', '001', 'cjack-60', '0');

• 查询数据

mysql> select * from lt_dict;
+----------------------------------+------+----------+--------+
| id                               | code | name     | status |
+----------------------------------+------+----------+--------+
| 3c3da1d4b3b94bd9b33fb340dfdb605c | 001  | cjack-60 | 0      |
+----------------------------------+------+----------+--------+
1 row in set (0.00 sec)

恭喜你！完成了MySQL的基本入门！！