CH5 安装MySQL - 《Django学习笔记》

安装MySQL
验证
登陆MySQL
查看MySQL用户
查看数据库端口
创建db
Navicat连接虚拟机（Ubuntu）Mysql

安装MySQL

sudo apt-get update #更新源
sudo apt-get install mysql-server #安装

验证

安装完成后，可以通过下面的命令来查看时候安装成功：
systemctl status mysql

mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2021-01-06 15:17:35 CST; 29s ago
   Main PID: 13082 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2441)
     Memory: 330.3M
     CGroup: /system.slice/mysql.service
             └─13082 /usr/sbin/mysqld

看到上面的信息就说明MySQL已经安装好并运行起来了。

登陆MySQL

以root用户登陆
sudo mysql -u root -p # -u 指定用户名 -p需要输入密码

或者:
sudo mysql #可以不需要指定用户名密码

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22-0ubuntu0.20.10.2 (Ubuntu)
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.

已经成功连接到数据库服务器了。那接下来我们看看这里都有哪些databases.
mysql>show databases

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

MySQL安装好后有默认的这几个数据库。其中mysql里有user表，这个表里放了user、host相关的一些信息。通过下面的命令看到这个表。
mysql>show tables from mysql;

mysql> show tables from mysql;
+----------------------------------------------+
| Tables_in_mysql                              |
+----------------------------------------------+
| columns_priv                                 |
| component                                    |
| db                                           |
| default_roles                                |
| engine_cost                                  |
| func                                         |
| general_log                                  |
| global_grants                                |
| gtid_executed                                |
| help_category                                |
| help_keyword                                 |
| help_relation                                |
| help_topic                                   |
| innodb_index_stats                           |
| innodb_table_stats                           |
| password_history                             |
| plugin                                       |
| procs_priv                                   |
| proxies_priv                                 |
| replication_asynchronous_connection_failover |
| role_edges                                   |
| server_cost                                  |
| servers                                      |
| slave_master_info                            |
| slave_relay_log_info                         |
| slave_worker_info                            |
| slow_log                                     |
| tables_priv                                  |
| time_zone                                    |
| time_zone_leap_second                        |
| time_zone_name                               |
| time_zone_transition                         |
| time_zone_transition_type                    |
| user                                         |
+----------------------------------------------+
34 rows in set (0.01 sec)

查看MySQL用户

mysql> select User, Host from mysql.user;

mysql> select User, Host from mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

这里root就是我们刚才连接的账户。debian-sys-maint账号是在安装MySQL自动产生的，可以通过下面的命令查看到它。
sudo cat /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = BOX3xverXydZorvn
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = BOX3xverXydZorvn
socket   = /var/run/mysqld/mysqld.sock

查看数据库端口

mysql> show global variables like 'port';

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.02 sec)

创建db

mysql> create database db_test;

Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

此时数据库里还没有任何表：

mysql> use db_test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

我们需要创建一个表(t_test)：

mysql> create table t_test (id int, name varchar(20));
Query OK, 0 rows affected (0.08 sec)

插入数据：

mysql> insert into t_test(id, name) values(1, "A");

Navicat连接虚拟机（Ubuntu）Mysql

查看虚拟机ip地址
ifconfig -a
inet后面为虚拟机ip地址
更多关于ifconfig命令的用法：ifconfig命令详细解释

ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.103  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::1859:19c8:5d47:afa6  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:e6:9a:98  txqueuelen 1000  (以太网)
        RX packets 28543  bytes 38519716 (38.5 MB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 18325  bytes 1763907 (1.7 MB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (本地环回)
        RX packets 340  bytes 32694 (32.6 KB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 340  bytes 32694 (32.6 KB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

授权mysql远程访问
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
可以看到mysql默认只能被本地监听。
将127.0.0.1改为0.0.0.0即可被外部访问。

# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql
# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# 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   #将127.0.0.1改为0.0.0.0
mysqlx-bind-address     = 127.0.0.1   #将127.0.0.1改为0.0.0.0
#
# * Fine Tuning

之后，在虚拟机中登陆mysql
mysql -u root -p
新增用户admin，密码为abc123

mysql>CREATE USER 'admin'@'%' IDENTIFIED BY 'abc123';
mysql> FLUSH PRIVILEGES;

Navicat Premium连接数据库
很多用户在使用Navicat Premium 12连接MySQL数据库时会出现Authentication plugin 'caching_sha2_password' cannot be loaded的错误。
出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password. 这里用第二种方式，解决方法如下
管理员权限运行命令提示符，登陆MySQL（记得添加环境变量）
mysql -u root -p
修改账户密码加密规则并更新用户密码

mysql>ALTER USER 'admin'@'%' IDENTIFIED BY 'abc123' PASSWORD EXPIRE NEVER;   #修改加密规则 
mysql>ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';   #更新一下用户的密码
mysql>FLUSH PRIVILEGES #刷新权限

单独重置密码命令：alter user 'admin'@'%' identified by 'aaa';
现在再次打开Navicat Premium 12连接MySQL问题数据库就会发现可以连接成功了