Set up MySQL database

The following information provides configuration guidance to use MySQL databases with B2Bi, including:

About the MySQL database

The B2Bi installer supplies the driver that is required to work with this database. You do not have to supply a database driver. After the B2Bi installation, you can find database drivers at:
<B2Bi_install_directory>\Interchange\corelib

Important: You must create the external database before starting B2Bi for the first time.

MySQL tuning guidelines

Set the following global variables for MySQL in the my.ini (Windows) or my.cnf (Linux) file:

Notes on the settings:

  • transaction-isolation=READ-COMMITTED: MySQL must run in read-committed transaction isolation mode. The B2Bi Server will not start unless the database is running in this mode.
  • lower_case_table_names=1: This forces all table names and columns to be lower case on creation and subsequent queries.
  • Use this setting whether you run MySQL on UNIX, Linux or Windows. This also is recommended for Windows to allow transferring data between MySQL databases installed on Unix or Linux and Windows.
  • max_connections=800: The database must allow a minimum value of 800 for the “maximum connections” attribute.
  • If you run MySQL on a UNIX or Linux computer, the database server name is case sensitive.
  • default-storage-engine=innodb: B2Bi only supports MySQL when MySQL has the InnoDB Storage Engine. The InnoDB Storage Engine is a transactional database engine.

User privileges

The MySQL user who is administering the database for B2Bi use requires the following minimum privileges.

For run-time administration:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • EXECUTE

For B2Bi installations, upgrades and startup, the user requires:

  • CREATE
  • DROP
  • ALTER
  • INDEX
  • CREATE TEMPORARY TABLES
  • CREATE VIEW
  • ALTER ROUTINE
  • CREATE ROUTINE

Example MySQL command for attributing privileges to a MySQL user:

GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,CREATE VIEW,ALTER ROUTINE,CREATE ROUTINE

ON <DB>.*

TO <USER>@<CLIENTHOST>

MySQL database setup procedure

To setup the MySQL database:

  1. Set database variables. See MySQL tuning guidelines.
  2. Create a schema for the B2Bi Server in the MySQL database. Set a user name and password for it. Make sure the user has adequate privileges.
  3. Install B2Bi (if it is not already installed).
  4. Check the database connection settings before you start B2Bi.
  5. See Check database connections.
  6. Start B2Bi. The server connects to the database and generates the required tables.
  7. You can use your database application to confirm that the tables have been added.

Related Links