plugai_updsrv/deploy/数据库部署要求.md

部署步骤

1. 安装MySQL8

1.1 方案一 物理机安装MySQL8

• 操作系统

Ubuntu20.04LTS

如果操作系统不是Ubuntu20则需要更新MySQL官方源信息

1.1.1 apt安装MySQL

$ sudo apt update && sudo apt install mysql-server

1.1.2. 检查安装后是否启动成功

$ ps -ef | grep mysqld
mysql    65492     1  0 17:46 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

1.1.3. 修改MySQL监听地址和端口

$ sudo netstat -alnt | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

# 编辑mysqld.cnf文件将bind-address对应的值由127.0.0.1改成0.0.0.0或局域网IP地址(端口视情况决定是否修改)
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

# 重启MySQL服务
$ sudo service mysql restart

1.1.4. 创建oss用户和权限

# MySQL安装完会在/etc/mysql目录下有一个debian.cnf文件可用于本地登录并修改root密码或创建用户
$ sudo cat /etc/mysql/debian.cnf 

[client]
host     = localhost
user     = debian-sys-maint
password = xgf1OdcBzRy0LaEP
socket   = /var/run/mysqld/mysqld.sock

# 本地登录MySQL，执行下面的命令行
$ mysql -udebian-sys-maint -pxgf1OdcBzRy0LaEP mysql

mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+-------------------------------------------+
| host      | user             | plugin                | authentication_string                     |
+-----------+------------------+-----------------------+-------------------------------------------+
| localhost | root             | auth_socket           |                                           |
| localhost | mysql.session    | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | mysql_native_password | *22CC5F671040F19FF9FB1E5A9B94D2576C4A1A24 |
| %         | node             | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-----------------------+-------------------------------------------+

4 rows in set (0.00 sec)

# 创建一个admin账户并允许远程登录, 密码是123456(生产环境请设置复杂密码)
mysql> create user 'admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

# 赋予oss所有权限
mysql> grant all on *.*  to 'admin'@'%';
mysql> flush privileges;

# 修改root密码和口令加密方式并开启远程登录(视实际情况而定，如果无必要可以只修改密码不开启远程登录)
# host='%'表示开启远程访问，如果不开启就不要这个SQL字句
mysql> update user set plugin='mysql_native_password', authentication_string='', host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 重置root账户登录密码并刷新权限
mysql> alter user 'root'@'%' IDENTIFIED BY '123456';  #适用于8.x版本修改密码(设置不成功可能是需要复杂密码) 
mysql> flush privileges;

# 查看账户信息
mysql> select host,user,plugin,authentication_string from user;
+-----------+------------------+-----------------------+-------------------------------------------+
| host      | user             | plugin                | authentication_string                     |
+-----------+------------------+-----------------------+-------------------------------------------+
| %         | root             | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session    | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | mysql_native_password | *22CC5F671040F19FF9FB1E5A9B94D2576C4A1A24 |
| %         | admin            | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

1.2 方案二 docker安装MySQL8

# 创建本地数据库目录
$ sudo mkdir -p /data/mysql/{mysql-files,conf,logs,data}

# 启动容器(设置root初始密码为123456)
$ docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --restart always \
        -e TZ=Asia/Shanghai \
        -v /data/mysql/mysql-files:/var/lib/mysql-files \
        -v /data/mysql/conf:/etc/mysql \
        -v /data/mysql/logs:/var/log/mysql \
        -v /data/mysql/data:/var/lib/mysql \
        --name mysql -d  mysql:8.0.23   

# 登录mysql终端(手动输入初始密码123456登录MySQL控制台)
$ docker exec -it mysql mysql -uroot -p mysql

Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

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.

# 更改数据库root账户密码为123456并开启远程访问(密码可以自行修改成其他也可以保持原密码，主要是通过%符号开启root远程访问)
mysql> USE mysql;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> FLUSH PRIVILEGES;
mysql> exit

2. 初始化数据表

将oss-manager.sql和init-data.sql文件上传到服务器/tmp目录

# 服务器登录MySQL命令行终端执行sql文件
mysql> source /path/to/intent-system.sql

3. 解除MySQL分组查询限制

# 打开mysqld.cnf文件并在[mysqld]选项范围内加一行下面的参数(如果sql_mode已存在则去掉ONLY_FULL_GROUP_BY)
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# 重启mysql服务
$ sudo service mysql restart

4. 更改服务器/容器时区

MySQL运行服务器或容器时区改为UTC+8 (北京时间)