Install Oracle MySQL/MariaDB and create a database - database configuration/design tasks

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. Install the Oracle® MySQL Server (or MariaDB) on your computer.
  2. Configure the server with the appropriate storage engine.

    In order to have transaction support by default, you must use a storage engine that supports transactional tables, such as INNODB. In recent versions of MySQL, this is the default storage engine.

  3. Consider setting the sql_mode configuration parameter to get the appropriate behavior of the MySQL server:
    1. When the STRICT_TRANS_TABLES option is set in the sql_mode parameter, numeric data truncation/overflow will produce SQL error -1264, and character strings too large for the target column will produce SQL error -1406.
      Without the STRICT_TRANS_TABLES option, MySQL will only produce the SQL warning -1265 (data truncated) when a character string is too large. However, numeric data truncation/overflow is also relaxed and produces warning -1264, when you might expect to get an SQL error and avoid invalid numeric values in your database. Therefore, it is recommended to use the STRICT_TRANS_TABLES option.
    2. Blank padding of fetched CHAR data can be controlled with the PAD_CHAR_TO_FULL_LENGTH option of the sql_mode parameter.
      You can use this parameter to get CHAR values padded with blanks to their full length, but the result of the SQL LENGTH() function will be different since trailing blanks are significant for that function in MySQL.
  4. The mysqld process must be started to listen to database client connections. See MySQL documentation for more details about starting the database server process.
  5. Create a database user dedicated to your application, the application administrator.
    Connect as the MySQL root user, create the application administrator user and grant all privileges to this user:
    $ mysql -u root -p
    ...
    mysql> create user 'mysuser'@'localhost' identified by 'password';
    mysql> grant all privileges on *.* to 'mysuser'@'localhost';
  6. Connect as the application administrator and create a MySQL database with the CREATE DATABASE statement, and specify the character set to be used for this database:
    $ mysql -u mysuser -p
    ...
    mysql> create database mydatabase
                  character set utf8mb4
                  collate utf8mb4_0900_as_cs;
    Note: MySQL collation names include modifiers such as _ai/_as and _ci/_cs, to indicate if you want accent and character case sensitivity in your database. Consider to use the right modifiers, to have for example WHERE 'é'='É' evaluate to true or false. Use the _bin modifier (as in utf8mb4_bin), to distinguish any character, and when the sort order can be based on the binary value of characters. To check the client charset, database charset and collation:
    mysql> SELECT @@character_set_client, @@character_set_database, @@collation_database;
  7. Create the application tables.

    Convert Informix® data types to MySQL data types. See Data type conversion table: Informix to MySQL for more details.