python开发实战Django配置数据库
01 安装数据库
??MySQL 是一个开源的数据库管理系统，通常作为最受欢迎的 LAMP（Linux，Apache，MySQL，PHP/Python/Perl）的一部分进行安装，其通过关系型数据库和 SQL 来管理数据。
??在 Ubuntu 中，默认只有最新版本的 MySQL 包含在 APT 软件包存储库中。
要安装它，需要使用 apt 更新服务器上的软件包索引：
sudo apt-get update #更新软件依赖关系
然后安装默认的 MySQL 软件包：
sudo apt install mysql-server
??这一步不会进行一些配置相关的提示（例如：设置密码），因为会使 MySQL 的安装不安全，我们将在下一步解决该问题。
02.配置MySQL
??在Ubuntu下MySQL缺省是只允许本地访问的，使用workbench连接工具是连不上的；如果你要其他机器也能够访问的话，需要进行配置；在安装完 MySQL 之后，应该运行一下包含的安全脚本
$ sudo mysql_secure_installation #这将会通过一系列的提示，帮助我们做一些常规化的安全设置：
重点说一下第一个提示，这会询问我们是否愿意设置验证密码插件，该插件可用于测试MySQL密码的强度。
03更改用户认证方式
虽然上面设置了 root 用户的密码，但当通过 MySQL 终端登录时，并不能通过密码进行认证：
$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
??在这是因为在 MySQL 5.7 及之后的版本中，root 用户被默认设置为通过 auth_socket 插件（而非密码）认证，其主要原因是出于对数据库的安全性考虑。
话虽如此，但偶尔也需要外部程序来访问，这时就会很麻烦了。为了使 root 用户能通过密码方式连接 MySQL，先通过终端打开 MySQL 的提示符：
作者：waws520
链接：https://juejin.cn/post/6970850972687925262
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
mysql> SELECT user, authentication_string, plugin, host FROM mysql.user; #通过该命令检查Mysql的每个用户认证方式。
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | $A$005$0RA(%M\;<5nqa`3RyQv2U5BkS0rq0czXinXSzyoVIBDZN275oR2vi.Jft7 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root | | auth_socket | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)
显而易见，root 用户的认证方式是 auth_socket。现在运行如下命令，将认证方式更改为密码认证（即：mysql_native_password）：
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'luyu1234';
注意：务必设置一个高强度的密码（这里的“luyu1234”仅仅是为了测试），该操作将会改变步骤 2 中设置的密码。
?? 如果显示可知我们设置的密码太简单了，所以不能通过，下面我们可以修改参数，来达到修改密码的目的。
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 | STRONG |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.08 sec)
mysql> set global validate_password.policy=LOW; #把所有密码安全等级设置为低
Query OK, 0 rows affected (0.01 sec)
mysql> set global validate_password.length=8; #把密码长度设置为8
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'luyu1234'; #设置密码为luyu1234
Query OK, 0 rows affected (0.00 sec)
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 | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.07 sec)
mysql> exit #设置成功，退出MySQL 数据库
Bye
完成之后，再来查看一下 root 用户的认证方式：
mysql> SELECT user, authentication_string, plugin, host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| debian-sys-maint | $A$005$0RA(%M\;<5nqa`3RyQv2U5BkS0rq0czXinXSzyoVIBDZN275oR2vi.Jft7 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root | *7E1A05E34500B051A5553061CDF96A2CC8768727 | mysql_native_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.01 sec)
mysql>exit
Bye
可以看到，数据库MySQL的认证方式改变了，现在，我们退出数据库，再来尝试一下，让 root用户以密码形式登录：
boot@boot-virtual-machine:~$ mysql -u root -p
Enter password: #输入我们设置的密码luyu1234
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.27-0ubuntu0.21.10.1 (Ubuntu)
Copyright (c) 2000, 2021, 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>
可以正常连接了,大功告成。接下来我们配置远程访问，
04 配置远程访问
??默认情况下，MySQL 只监听本地主机，（localhost）的连接。若要启用远程连接，需要进行以下配置。编辑 MySQL 的 mysqld.cnf 配置文件：
001 编辑MySQL 的 mysqld.cnf 配置文件：
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
......................................................
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
# max_allowed_packet = 64M
.......................................................
002更改 user表中的host项，将“localhost”改称“%”（表示所有用户都可以访问），并给 root用户授权.
boot@boot-virtual-machine:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
................................................
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all on *.* to root@'%' identified by 'luyu1234' with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'luyu1234' with grant option' at line 1
??mysql远程连接权限grant all privileges on *.* to ‘root‘@‘%‘ identified by ‘luyu1234‘ with grant optio语句报错,这条语句适用于MySQL8.0之前的。而MySQL8.0及之后的，设置远程连接权限要用下面的语句才可以
mysql> GRANT ALL ON *.* TO 'root'@'%'; #授权root 的所有权限，并且设置远程访问。
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; #刷新设置
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user, authentication_string, plugin, host FROM mysql.user; #查看表格 root的权限发生改变，
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| root | *7E1A05E34500B051A5553061CDF96A2CC8768727 | mysql_native_password | % |
| debian-sys-maint | $A$005$0RA(%M\;<5nqa`3RyQv2U5BkS0rq0czXinXSzyoVIBDZN275oR2vi.Jft7 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
5 rows in set (0.00 sec)
mysql> use mysql; #再次使用 mysql 数据发生改变
Database changed
mysql> grant all privileges on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql>
1、创建用户
CREATE USER 'luyu'@'root' IDENTIFIED BY 'luyu1234';
2.授权（全部数据库权限）
grant all privileges on *.* to 'root'@'%' ;
3.刷新权限
flush privileges;
mysql> CREATE USER 'luyu'@'root' IDENTIFIED BY 'luyu1234';
Query OK, 0 rows affected (0.04 sec)
........................................
mysql> grant all privileges on *.* to 'luyu'@'%' ;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant all privileges on *.* to 'root'@'%' ;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
...........................................
查看mysql的版本号
mysql> status
--------------
mysql Ver 8.0.27-0ubuntu0.21.10.1 for Linux on x86_64 ((Ubuntu))
mysql> select host,user from user; #查看所有mysql 用户信息
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | debian-sys-maint |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| root | luyu |
+-----------+------------------+
6 rows in set (0.00 sec)