Ubuntu 20安装MySQL

环境

Ubuntu 20.04

安装

sudo apt-get install mysql-server  

sudo apt install mysql-client   

sudo apt install libmysqlclient-dev

验证

➜  ~ sudo netstat -tap | grep mysql 
tcp        0      0 localhost:mysql         0.0.0.0:*               LISTEN      34468/mysqld        
tcp6       0      0 [::]:33060              [::]:*                  LISTEN      34468/mysqld 

➜  ~ sudo service mysql status 
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-07-17 16:31:42 CST; 2h 15min ago
    Process: 34445 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCC>
   Main PID: 34468 (mysqld)
     Status: "Server is operational"
      Tasks: 47 (limit: 4624)
     Memory: 343.4M
     CGroup: /system.slice/mysql.service
             └─34468 /usr/sbin/mysqld

Jul 17 16:31:41 ubuntu systemd[1]: Starting MySQL Community Server...
Jul 17 16:31:42 ubuntu systemd[1]: Started MySQL Community Server.

➜  ~ mysql --version

mysql  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

MySQL常用命令

• 进入交互命令行：

sudo mysql -u root

注：如果设置了密码，则需要加上 -p 参数（ sudo mysql -u root -p 或者 sudo mysql -u root -p 123456 ）。

• 列出所有DB：

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

• 创建DB：

mysql> create database repo;
Query OK, 1 row affected (0.00 sec)

• 切换DB：

mysql> use repo;
Database changed

• 创建table

mysql> create table job (jobid varchar(100), jobname varchar(100));
Query OK, 0 rows affected (0.01 sec)

• 插入1条记录

mysql> insert into job values ('aaa', 'this is aaa');
Query OK, 1 row affected (0.01 sec)

• 查询table

mysql> select * from job;
+-------+-------------+
| jobid | jobname     |
+-------+-------------+
| aaa   | this is aaa |
+-------+-------------+
1 row in set (0.00 sec)

• 列出所有table

mysql> show tables;
+----------------+
| Tables_in_repo |
+----------------+
| job            |
+----------------+
1 row in set (0.00 sec)

• 退出交互命令行

mysql> quit
Bye

非root用户登录

如果用非root用户登录MySQL，报错如下：

➜  ~ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

用root登录MySQL，使用默认的 mysql DB，查看 user table：

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

更新 root 的 plugin 字段如下：

mysql> update user set plugin='mysql_native_password' where User = 'root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

刷新权限：

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

重启service：

sudo service mysql restart

现在，就可以用非root用户登录MySQL了：

mysql -u root

设置登录密码

有好几种方式可以设置密码，我使用的是如下方式：

➜  ~ mysqladmin -uroot -p password
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

在 Enter password: 处，直接回车就行（因为还没有设置密码）。

接下来在 New password: 和 Confirm new password: 处，输入新密码，比如 123456 。

参考

• https://blog.csdn.net/mr_hui_/article/details/88878836
• https://blog.csdn.net/chenhuibrus/article/details/104665068