Ubuntu MySQL 联网使用包管理工具安装 && 离线下使用压缩包安装、配置、使用

清疚 2023-07-05 13:22 129阅读 0赞

### 文章目录 ###

*   *  联网安装
     *  准备工作
     *  配置
     *  运行 && 修改密码
     *  环境变量
     *  新建用户 && 授权
     *  GUI客户端连接工具

## 联网安装 ##

命令如下：

sudo apt-get install 包名           # 安装软件包
    dpkg -L 包名                        # 查询安装位置/包是否安装
    sudo apt-get remove 包名 --purge    # 删除包

相关软件包：  
`mysql-server` 服务器  
`mysql-client` 客户端  
可选(Linux端的数据库GUI客户端操作工具)：  
推荐：`dbeaver-ce`  
不推荐：  
`mysql-workbench` MySQL官方客户端。  
`phpmyadmin` 用PHP写的MySQL客户端工具。

若对以上安装过程有疑问，可以参考[https://wiki.ubuntu.org.cn/MySQL][https_wiki.ubuntu.org.cn_MySQL]

如果没网，只能用以下流程通过压缩包进行安装。

## 准备工作 ##

*  下载 `wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz`  
    更多版本见[MySQL 官网下载页][MySQL]  
    不需要登录，直接点击\[No thanks, just start my download.\]即可。  
    我下载好的`mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz`大小为**463MB**。
 *  解压
    
     *  把`*.tar.xz`解压为`*.tar` `xz -d -k mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz`  
        解压时间会比较长，把463MB的包解压到2.5GB。
     *  解压`*.tar`档案。(我是直接解压到`/opt/`目录下)  
        `sudo tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar -C /opt`
 *  修改文件夹名称 `sudo mv /opt/mysql-8.0.19-linux-glibc2.12-x86_64/ /opt/mysql8`
 *  修改文件夹权限  
    我的用户是`wuyujin`，若无普通用户，创建即可：`useradd wuyujin`  
    `sudo chown wuyujin -R /opt/mysql8/`
 *  查看文件夹内容 `ll /opt/mysql8`

wuyujin@ubuntu18:~$ ll /opt/mysql8
    total 440
    drwxr-xr-x  9 wuyujin root    4096 2月   8 12:58 ./
    drwxr-xr-x 13 root    root    4096 2月   8 14:25 ../
    drwxr-xr-x  2 wuyujin 31415   4096 12月 10 07:32 bin/
    drwxr-xr-x  2 wuyujin 31415   4096 12月 10 07:32 docs/
    drwxr-xr-x  3 wuyujin 31415   4096 12月 10 07:32 include/
    drwxr-xr-x  6 wuyujin 31415   4096 12月 10 07:32 lib/
    -rw-r--r--  1 wuyujin 31415 405571 12月 10 03:53 LICENSE
    drwxr-xr-x  4 wuyujin 31415   4096 12月 10 07:32 man/
    -rw-r--r--  1 wuyujin 31415    687 12月 10 03:53 README
    drwxr-xr-x 28 wuyujin 31415   4096 12月 10 07:32 share/
    drwxr-xr-x  2 wuyujin 31415   4096 12月 10 07:32 support-files/
    wuyujin@ubuntu18:~$

*  进入MySQL根目录 `cd /opt/mysql8/`

## 配置 ##

*  修改`./support-files/mysql.server`文件  
    注意行号，找到`basedir`和`datadir`两个配置项：  
    ![在这里插入图片描述][20200304201827978.png]  
    修改为如下：

basedir=/opt/mysql8
    datadir=/opt/mysql8/data

保存退出。**NOTE**: 这两项配置一定要改。

*  安装 `./bin/mysqld --initialize --user=wuyujin --basedir=/opt/mysql8 --datadir=/opt/mysql8/data`  
    这一步大概率会**报错**说缺一个叫`libaio`的库文件：  
    `./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory`  
    不过没关系，安装`libaio1`即可：`sudo apt-get install libaio1`
 *  重新安装。会打印出`root`用户在`localhost`即本机登录的临时密码。记住这个密码。  
    `2020-02-14T12:37:14.802673Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kwacFRjam9,:`，密码为`kwacFRjam9,:`

过程如下：

wuyujin@ubuntu18:/opt/mysql8$ # 1. 安装
    wuyujin@ubuntu18:/opt/mysql8$ ./bin/mysqld --initialize --user=wuyujin --basedir=/opt/mysql8 --datadir=/opt/mysql8/data
    ./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
    wuyujin@ubuntu18:/opt/mysql8$ # 2. 报错了，先查询一下相关包名
    wuyujin@ubuntu18:/opt/mysql8$ sudo apt-cache search libaio
    [sudo] password for wuyujin: 
    libaio-dev - Linux kernel AIO access library - development files
    libaio1 - Linux kernel AIO access library - shared library
    wuyujin@ubuntu18:/opt/mysql8$ # 3. 安装libaio1
    wuyujin@ubuntu18:/opt/mysql8$ sudo apt-get install libaio1
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following packages were automatically installed and are no longer required:
      liberror-perl libjemalloc1 libpcre2-8-0 linux-headers-4.18.0-15 linux-headers-4.18.0-15-generic linux-image-4.18.0-15-generic
      linux-modules-4.18.0-15-generic linux-modules-extra-4.18.0-15-generic
    Use 'sudo apt autoremove' to remove them.
    The following NEW packages will be installed:
      libaio1
    0 upgraded, 1 newly installed, 0 to remove and 14 not upgraded.
    Need to get 6,476 B of archives.
    After this operation, 30.7 kB of additional disk space will be used.
    Get:1 http://mirrors.aliyun.com/ubuntu bionic-updates/main amd64 libaio1 amd64 0.3.110-5ubuntu0.1 [6,476 B]
    Fetched 6,476 B in 3s (2,524 B/s)   
    Selecting previously unselected package libaio1:amd64.
    (Reading database ... 211554 files and directories currently installed.)
    Preparing to unpack .../libaio1_0.3.110-5ubuntu0.1_amd64.deb ...
    Unpacking libaio1:amd64 (0.3.110-5ubuntu0.1) ...
    Setting up libaio1:amd64 (0.3.110-5ubuntu0.1) ...
    Processing triggers for libc-bin (2.27-3ubuntu1) ...
    wuyujin@ubuntu18:/opt/mysql8$ # 4. 查询libaio1包的安装位置
    wuyujin@ubuntu18:/opt/mysql8$ dpkg -L libaio1
    /.
    /lib
    /lib/x86_64-linux-gnu
    /lib/x86_64-linux-gnu/libaio.so.1.0.1
    /usr
    /usr/share
    /usr/share/doc
    /usr/share/doc/libaio1
    /usr/share/doc/libaio1/TODO
    /usr/share/doc/libaio1/changelog.Debian.gz
    /usr/share/doc/libaio1/copyright
    /usr/share/lintian
    /usr/share/lintian/overrides
    /usr/share/lintian/overrides/libaio1
    /lib/x86_64-linux-gnu/libaio.so.1
    wuyujin@ubuntu18:/opt/mysql8$ # 5. 重新安装
    wuyujin@ubuntu18:/opt/mysql8$ ./bin/mysqld --initialize --user=wuyujin --basedir=/opt/mysql8 --datadir=/opt/mysql8/data
    2020-02-14T12:37:08.857833Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
    2020-02-14T12:37:08.857871Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
    2020-02-14T12:37:08.879890Z 0 [System] [MY-013169] [Server] /opt/mysql8/bin/mysqld (mysqld 8.0.19) initializing of server in progress as process 15565
    2020-02-14T12:37:14.802673Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kwacFRjam9,:
    wuyujin@ubuntu18:/opt/mysql8$ #

我的是：`kwacFRjam9,:`

## 运行 && 修改密码 ##

*  启动MySQL服务器

wuyujin@ubuntu18:/opt/mysql8$ ./support-files/mysql.server status   # 查看服务器启动状态
     * MySQL is not running
    wuyujin@ubuntu18:/opt/mysql8$ ./support-files/mysql.server help     # 查看该脚本的参数选项
    Usage: mysql.server  { start|stop|restart|reload|force-reload|status}  [ MySQL server options ]
    wuyujin@ubuntu18:/opt/mysql8$ ./support-files/mysql.server start    # 启动MySQL服务器
    Starting MySQL
    .. * 
    wuyujin@ubuntu18:/opt/mysql8$ ./support-files/mysql.server status   # 再次查看MySQL服务器状态
     * MySQL running (16567)
    wuyujin@ubuntu18:/opt/mysql8$

*  修改密码  
    首先用`root`用户和刚才生成的随机密码登入MySQL`./bin/mysql -u root -p`。  
    然后，先查看一下数据库，报错：`ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.`  
    也就是说，安装好后，第一次登进来必须先重设密码：`alter user 'root'@'localhost' identified by 'www.wuyujin.com';`

过程如下：

root@ubuntu18:/opt/mysql8# ./bin/mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 18
    Server version: 8.0.19
    
    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> show databases;
    ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
    mysql> # test
    mysql> alter user 'root'@'localhost' identified by 'www.wuyujin.com';
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> exit # 退出客户端

*  重新登录 `./bin/mysql -u root -p`，回车后输入刚才重设的密码。  
    查看MySQL版本和用户信息(密码已被加密，无法查看明文)

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.19    |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> use mysql;
    Database changed
    
    mysql> select Host, User, plugin, authentication_string from user;
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | Host      | User             | plugin                | authentication_string                                                  |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    n9FtZ<lPnYyaYAuIWCXDjSuzb.fNcVDUR1B5vdVPZSjhceud8 |d | $A$005$Pf9@#K^TV
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql>

既然可以命令行启动服务器`./support-files/mysql.server start`，也可以命令行登录`./bin/mysql -u root -p`。  
下一步就该设置环境变量，以便在任意目录下都可以直接执行以上两个命令。

## 环境变量 ##

编辑`/etc/profile`，添加以下配置：

# mysql
    export MYSQL_HOME=/opt/mysql8
    export PATH=$MYSQL_HOME/bin:$MYSQL_HOME/support-files:$PATH

保存退出，重启使最新配置对所有用户生效。

## 新建用户 && 授权 ##

*  新建用户 `create user 'wuyujin'@'%' identified by 'abcd' ;`  
    用户`wuyujin`，可以在所有机器`%`上登录客户端连接，密码是`abcd`。
 *  授权 `grant all privileges on *.* to 'wuyujin'@'%' ;` 其中`privileges`可以省略。  
    格式： `grant 权限 on 数据库对象 to 用户@主机`
 *  刷新权限 `flush privileges ;`
 *  用户信息表 `select * from mysql.user where user = 'wuyujin' \G` `\G`可以改变结果集的打印方式。
 *  操作流程如下：

mysql> create user 'wuyujin'@'%' identified by 'abcd';  # 新建用户
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select host, user from mysql.user;  # 查看用户表记录数
    +-----------+------------------+
    | host      | user             |
    +-----------+------------------+
    | %         | wuyujin          |
    | localhost | mysql.infoschema |
    | localhost | mysql.session    |
    | localhost | mysql.sys        |
    | localhost | root             |
    +-----------+------------------+
    5 rows in set (0.00 sec)
    
    mysql> grant all privileges on *.* to 'wuyujin'@'%';    # 授权
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> flush privileges;                # 刷新权限
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> exit
    Bye

登录测试：`mysql -u wuyujin -p`回车后输入密码。若成功登入则以上操作成功。

## GUI客户端连接工具 ##

推荐：DBeaver，安装过程及快捷键见： [Ubuntu 数据库客户端 DBeaver 安装、使用及常用的快捷键][Ubuntu _ DBeaver]  
如图，使用DBeaver查询用户账号信息。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eXVqaW4xOTk3_size_16_color_FFFFFF_t_70]

[https_wiki.ubuntu.org.cn_MySQL]: https://wiki.ubuntu.org.cn/MySQL
[MySQL]: https://dev.mysql.com/downloads/mysql/
[20200304201827978.png]: https://img-blog.csdnimg.cn/20200304201827978.png
[Ubuntu _ DBeaver]: https://blog.csdn.net/wuyujin1997/article/details/104144075
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eXVqaW4xOTk3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200215151935297.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1eXVqaW4xOTk3,size_16,color_FFFFFF,t_70