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 mode is used, you will get a -1406 error (data too long) when inserting a character string that is too large for the target column.
      If you don't use the STRICT_TRANS_TABLES mode, you get a -1265 warning (data truncated) when the value is too large.
    2. Blank padding of fetched CHAR data can be controlled with the PAD_CHAR_TO_FULL_LENGTH.
      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
              default character set utf8mb4
              collate utf8mb4_unicode_ci;
  7. Create the application tables.

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