MySQL and MariaDB

This page prepares a MySQL or MariaDB server for myDBR. The driver is mysqli (see Requirements). In the examples the reporting database is mydbr, the account is mydbr and your data is in the database mydatabase. The wizard can do these steps for you with its Create User and Create Database buttons, see Quick Start.

Create the user

$ mysql -uroot -p --default-character-set=utf8mb4
Enter password: ******
mysql> create user 'mydbr'@'localhost' identified by 'mydbr_password';
mysql> exit

If the web server and the database are on different computers, replace localhost with the host name or address of the web server.

Create the reporting database and grant the rights

$ mysql -uroot -p --default-character-set=utf8mb4
Enter password: ******
mysql> create database mydbr character set = 'utf8mb4';
mysql> grant all on mydbr.* to 'mydbr'@'localhost';
mysql> grant select on mydatabase.* to 'mydbr'@'localhost';
mysql> exit

The account needs all privileges on the reporting database (including creating and running stored procedures) and select on every database that your reports read. Add a grant select for each of them. A report that updates data (see Editable Reports) also needs the matching insert, update or delete rights.

Create the myDBR objects

The creation script is in the install/db_creation directory of the myDBR package:

$ cd install/db_creation
$ mysql -umydbr -p --default-character-set=utf8mb4 mydbr < mydbr_create_mysql.sql
Enter password: ******

The script creates the tables and the stored routines of myDBR. It can be run again on an existing installation.

note

If the script fails with You do not have the SUPER privilege and binary logging is enabled, the server writes a binary log and does not allow non-privileged users to create stored routines. Ask the administrator to set log_bin_trust_function_creators=1, or run the script with an administrator account.

Connection settings

The wizard writes the connection to mydbr_config.php. Besides the host, port, database, user and password, it can store a Unix socket and the SSL settings for an encrypted connection:

define( "DB_VENDOR", 'mysql' );
define( "DB_HOST", 'localhost' );
define( "DB_PORT", 3306 );
define( "DB_NAME", 'mydbr' );
define( "DB_USER", 'mydbr' );
define( "DB_PASSWORD", '********' );
define( "SETUP_DONE", true );

MariaDB uses the same settings and the same DB_VENDOR.