反馈已提交

网络繁忙

Configure MySQL 8 external database

  • Author:mt6666
  • Edit Times:13次
  • Recent Updates:FRInternational 于 2022-08-31
  • 1. Overview

    1.1 edition

    Bi server versionFunction change
    5.1.8
    -
    5.1.15
    When the external database is migrated and "migrate data to the database to be enabled" is not checked, it will automatically detect whether the project versions corresponding to the new and old databases are consistent

    1.2 Function introduction

    After the BI system is configured with an external database, it can ensure the stability of the BI system in case of a cluster environment or a large amount of data.

    The user can configure the external database in "initialization" or configure the MySQL 8 version external database in "platform".

    Note: if an external database is configured, do not easily modify the user name and password of the external database, otherwise the project will fail to start.

           For modification, please refer to:Modify the external database account password .

    2. Database configuration

    2.1 Database version:

    The types and versions supported by the external database are shown in the following table:

    Database typeBi server versionSupport database version
    MySQL85.1.8

    8.0.11,8.0.16,8.0.20

    2.2 hardware configuration

    Configuration typeMinimum configurationRecommended configuration:
    Memory1GB2GB
    CPU4 core8 core
    Network speed50Mbps100Mbps
    Occupied space1GB2GB
    Disk read / write speed50MB/s100MB/s

    Note 1: the occupied space needs to be adjusted in combination with the usage. It can be configured according to the size of the webapps / Webroot / WEB-INF / embedded / finedb folder, and the future use size can be estimated according to the actual size, increasing by 1-2 times.

    Note 2: the recommended memory is the memory reserved for the engineering configuration library.

    Note 3: the external database shall be in the same network segment as the Bi project as much as possible to avoid network fluctuation and other problems.

    2.3 New database

    Create a new database to store platform related data.

    Standard database building statement:CREATE DATABASE `finedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

    Note: different finebi projects cannot share the same external database, otherwise the data may be disordered.

    It is recommended to set the default character set of the database to utf8 and the default collation to utf8_ bin。 As shown in the following figure:

    Database name:

    Only numbers, letters, underscores and "." are allowed

    Character set:

    1)When the platform is externally connected to the MySQL database, you need to set the collation of the character set (case sensitive).

    2)Utf8 – UTF-8 Unicode encoding is recommended.

    Collation:

    There are various utf8 encoded sorting rules in mysql, of which the two most common are shown in the following table:

    1)utf8_bin(recommend)

    Definition: store each character in the string with binary data, case sensitive

    Example: Alex and Alex are considered to be two different values

    1)utf8_general_ci

    Definition: CI is the abbreviation of case insensitive, that is, case insensitive, and is the default encoding of utf8

    Example: Alex and Alex are considered to be the same value

    2.4 Replace the drive

    Finebi 5.1 has built-in MySQL 5 driver. If you use MySQL 8 external database, you need to manually replace the driver.

    1)Close Bi project

    2)Download the corresponding version of MySQL8.0 drive,And upload it to the project webapps\webroot\WEB-INF\libdirectory.

    3)Delete mysql-connector-java-5.1.39-bin.jar under the project webapps \ Webroot \ WEB-INF \ lib directory.

    4)Restart the Bi project.

    3. Method 1: configure external database during initialization

    This method is applicable to configuring external database when logging in data decision system for the first time.

    3.1 Enter the initialization interface

    The user can configure the external database when entering the platform for the first time, as shown in the following figure:

    3.2 Configure external database

    Click "configure database" to enter the external database configuration interface, select the database type as mysql, and enter the corresponding information of the actual database. As shown in the following figure:

    The description of each setting item is shown in the following table:

    Setting itemExplain
    Database typeSelect MySQL
    drive

    Manually modify tocom.mysql.cj.jdbc.Driver

    Please ensure that the driver has been replaced according to Section 2.4, and there are no MySQL drivers of other versions in the project

    Database name

    Section 2.3 new database name

    It is forbidden to share the database with other projects. It is recommended to create a new database

    The database name can only contain numbers, letters, underscores and "."

    User name / host / password / portFill in according to the actual situation of the local database

    The host name can only contain numbers, letters, underscores, "-" and "."

    Users need to have create, delete, alter, update, select and insert permissions

    Database connection URL

    Manually modify it in the following format, where IP: port number / database name needs to be adjusted

    jdbc:mysql://IP:Port number / database name?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL

    3.3 Configuration succeeded

    Wait for some time, and you will be prompted that Successfully enabled the new database . As shown in the following figure:

    Note: do not refresh the page during migration.

    4. Method 2: configure external database in the platform

    This method is applicable to configuring external database for the first time after entering the data decision system.

    4.1 Access platform

    The administrator logs in to the data decision system and clicks "manage> System  > General > external database > to be configured", as shown in the following figure:

    4.2 Configure external database

    Input the corresponding information of the actual database and configure the external database. As shown in the following figure::

    The description of each setting item is shown in the following table:

    Setting itemExplain
    Database typeSelect MySQL
    Drive

    Manually modify it to com.mysql.cj.jdbc.Driver

    Please ensure that the driver has been replaced according to Section 2.4, and there are no MySQL drivers of other versions in the project

    Database name

    Section 2.3 new database name

    It is forbidden to share the database with other projects. It is recommended to create a new database

    The database name can only contain numbers, letters, underscores and "."

    User name / host / password / portFill in according to the actual situation of the local database

    The host name can only contain numbers, letters, underscores, "-" and "."

    Users need to have create, delete, alter, update, select and insert permissions

    数据库连接URL

    Manually modify it in the following format, where IP: port number / database name needs to be adjusted:

    jdbc:mysql://IP:Port number / database name?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL

    4.3 Configuration succeeded

    After waiting for a period of time, a pop-up prompt will pop up: the target database has been successfully enabled. Click OK. As shown in the following figure:

    Note 1: do not refresh the page during migration.

    Note 2: there is no need to restart after the database configuration is successful.

    5. Method 3: platform secondary migration database

    This method is applicable to migrating to a new database after the external database has been configured.

    5.1 Access platform

    The administrator logs in to the data decision system and clicks "manage> System  > General > external database > configured", as shown in the following figure:

    5.2 Configure external database

    Input the corresponding information of the actual database and configure the external database. As shown in the following figure:

    The description of each setting item is shown in the following table:

    Setting itemExplain
    Database typeSelect MySQL
    Drive

    Manually modify it to com.mysql.cj.jdbc.Driver

    Please ensure that the driver has been replaced according to Section 2.4, and there are no MySQL drivers of other versions in the project

    Database name

    Section 2.3 new database name

    It is forbidden to share the database with other projects. It is recommended to create a new database

    The database name can only contain numbers, letters, underscores and "."

    User name / host / password / portFill in according to the actual situation of the local database

    The host name can only contain numbers, letters, underscores, "-" and "."

    Users need to have create, delete, alter, update, select and insert permissions

    Database connection URL

    Manually modify it in the following format, where IP: port number / database name needs to be adjusted:

    jdbc:mysql://IP:port number / database name?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL

    Description of the "migrate data to the database to be enabled" button:

    1)Check "migrate data to the database to be enabled"

    • When the database is switched, the data of the original external database will be imported into the new database, and the historical data backup file will not be generated under the project.

    • If platform data already exists in the new database, the original platform data will be cleared before importing data. The prompt is shown in the following figure:

    2)Uncheck "migrate data to the database to be enabled"

    • If the new external database is empty, it prompts that the current data must be migrated to the target database.

    • If the new external database is not empty

           Before migration, the configuration information of the original database will be automatically backed up, and the db.properties.bak file will be generated in the webapps \ Webroot \ WEB-INF \ config directory. As shown in the following figure:

           5. 1.15 and later Bi projects will automatically detect whether the jar package version corresponding to the data in the target database is consistent with the jar package version of the current project.

          If it is inconsistent, a prompt will pop up: unable to enable! Project version required to enable the new database: XXX. Current project version: XXX. Please ensure the project version is consistent!

         If the jar package version is consistent, directly enable the new database. After the migration is successful, the prompt is as shown in the following figure:

    5.3 Migration succeeded

    After waiting for a period of time, a pop-up prompt will pop up: successfully switched to the target database! If there is data difference between the old and new databases, the system operation may be affected. It is recommended to restart the project to ensure normal use.

    Click "OK" and restart the Bi project. The migration is successful. As shown in the following figure:

    Note: do not refresh the page during migration.

    Tips:

    6. matters needing attention

    If utf8 is not set for the database collation at the beginning_ Bin, but it has been migrated, so there are two tables fine_ conf_ entity、 fine_ conf_ The classname needs to be modified to utf8_ Bin, followed by fine_ conf_ Entity as an example:

    1)Query the current character set collation

    show full columns from fine_conf_entity

    2)Set the collation of character set to utf8_ bin

    • Set the default verification rules for columns

    ALTER TABLE fine_conf_entity MODIFY id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin;
    • Set the verification rules of the table

    ALTER TABLE fine_conf_entity DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

    3)Check whether the modification is successful

    show full columns from fine_conf_entity

    4)Use the same steps as 1) 2) 3) above to add fine_ conf_ Entity is replaced with fine_ conf_ The classname table is set as shown in the following figure:

    Attachment List


    Theme: 管理平台应用
    Already the First
    Already the Last
    • Helpful
    • Not helpful
    • Only read

    售前咨询电话

    400-811-8890转1

    在线技术支持

    在线QQ:800049425

    热线电话:400-811-8890转2

    总裁办24H投诉

    热线电话:173-1278-1526

    文 档反 馈

    鼠标选中内容,快速反馈问题

    鼠标选中存在疑惑的内容,即可快速反馈问题,我们将会跟进处理。

    不再提示

    10s后关闭