MariaDB

From Mark Furneaux's Wiki
Jump to: navigation, search

MariaDB is an open source drop-in replacement for MySQL.

Installation

Use the repository configuration tool to add the repository to the server and install the server core.

To use MariaDB with PHP and Apache, several packages must be installed: # apt install libapache2-mod-php7.0 libapache2-mod-php php7.0-mysql

phpMyAdmin

The easiest way to administer the MariaDB instance is with the web frontend phpMyAdmin.

Installation

Manual

Download and extract the current version archive from the phpMyAdmin website into your web directory.

Repository

You can also install phpMyAdmin from the official repositories by running:
# apt install phpmyadmin

If installing with this method, be sure to change the alias in /etc/apache2/conf-enabled/phpmyadmin.conf.

Configuration

The fastest configuration is to rename the file config.sample.inc.php to config.inc.php in the root directory.

Uncomment the following lines, adding a username and password for the phpMyAdmin control user, and adding a random string for the blowfish secret.

/*
 * This is needed for cookie based authentication to encrypt password in
 * cookie
 */
$cfg['blowfish_secret'] = '1234567'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

/* User used to manipulate with storage */
 $cfg['Servers'][$i]['controlhost'] = 'localhost';
 $cfg['Servers'][$i]['controlport'] = '';
 $cfg['Servers'][$i]['controluser'] = 'pma';
 $cfg['Servers'][$i]['controlpass'] = 'pmapass';

/* Storage database and tables */
 $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
 $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
 $cfg['Servers'][$i]['relation'] = 'pma__relation';
 $cfg['Servers'][$i]['table_info'] = 'pma__table_info';
 $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
 $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
 $cfg['Servers'][$i]['column_info'] = 'pma__column_info';
 $cfg['Servers'][$i]['history'] = 'pma__history';
 $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
 $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
 $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
 $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
 $cfg['Servers'][$i]['recent'] = 'pma__recent';
 $cfg['Servers'][$i]['favorite'] = 'pma__favorite';
 $cfg['Servers'][$i]['users'] = 'pma__users';
 $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
 $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
 $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
/* Contrib / Swekey authentication */
// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';

Navigate to the new phpMyAdmin installation and login. You will receive several error messages which will be resolved later.

Import sql/create_tables.sql into your database using phpMyAdmin.

Create a user which matches the credentials entered in config.inc.php and grant all privileges on the phpmyadmin database that the script above created.

You must logout and back in to clear the errors.

Migration

Eventually a time will come where a database server will need to be migrated to new hardware or a new installation.

Stop the server:
# service mariadb stop

Copy the datadir and config to the desired location, using an archive like tar if necessary.

You can choose to delete the InnoDB log files (ib_logfile0 and ib_logfile1) as they will be created on startup.

Ensure that the configuration is equivalent/compatible in /etc/mysql/my.cnf.

Allow MariaDB access to the new datadir from AppArmour by modifying the line in /etc/apparmor.d/tunables/alias:

alias /var/lib/mysql/ -> /tank/mariadb/

OR

Disable the profile entirely by renaming the profile by running:
# mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/usr.sbin.mysqldbak

Restart AppArmour:
# service apparmour restart

Start MariaDB:
# service mariadb start