前言
你需要准备什么？
- 可以连接互联网的一台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.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 创建表
# 退出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>
# 查看拥有的数据库
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的基本入门！！
探索更多MySQL技能 MySQL进阶
