About

The instruction is aimed to migrate existing databases running on MySQL to preferred MariaDB version.

Migration

To reduce downtime during migration you may install MariaDB as Docker container so both MariaDB and MySQL work simultaneously.

  • create directory /var/lib/mariadb for future database files

  • create directory /var/backups for dump and backup files

  • Create dump of existing bgerp database:

/opt/bgerp/baskup.sh create_db
unzip -p /opt/bgerp/backup/<backup_file>.db.zip dump.sql > /var/backups/dump.sql

  • create simple MariaDB configuration file for Mariadb doker container (e.g. */etc/mariadb/mariadb.conf.d/server.cnf) with the following content:

 [mariadbd]
 skip-name-resolve
 sql-mode=
 innodb_file_per_table=1
 innodb_buffer_pool_size=1G (?)
 character-set-server     = utf8mb4
 character-set-collations = utf8mb4=uca1400_ai_ci
docker pull mariadb:lts

  • and run:

docker run --cap-add=sys_nice --name=mariadb -e MARIADB_ROOT_PASSWORD=12345 -v /etc/mariadb/mariadb.conf.d:/etc/mysql/mariadb.conf.d -v /var/lib/mariadb/:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro -v /var/backups:/opt/backups -p 127.0.0.1:33061:3306 -d  mariadb:lts --ssl=off

  • Tune the MariaDB configuration (*/etc/mariadb/mariadb.conf.d/server.cnf) so that it runs.

To view the MariaDB logs:
docker logs mariadb
To view the container status:
docker ps

  • Connect to the MariaDB from console client

docker exec -ti mysql mysql --ssl=0 -u root -p12345

  • Create database and fill it from dump

MariaDB [(none)]> create database bgerp;
MariaDB [(none)]> use bgerp;
MariaDB [(bgerp)]> source /opt/backups/dump.sql

Add user for the database with this script. Username and password obtained from bgerp.properties file:

#!/bin/sh

MYSQL='docker exec -ti mariadb mariadb'
CONNECT_PASSWORD='12345'
CONNECT_HOST=127.0.0.1
CONNECT_PORT=33061
FILE='/opt/BGERP/bgerp.properties'
PWD=`grep db.pswd $FILE | cut -d'=' -f2`
USER=`grep db.user $FILE | cut -d'=' -f2`
HOST_PORT=`grep db.url $FILE | cut -d'/' -f3`
HOST=`echo $HOST_PORT | cut -d':' -f1`
DB=`grep db.url $FILE | cut -d'?' -f1 | cut -d'/' -f4`

CONNECT_PARAMETERS=(--ssl=0 --host=${CONNECT_HOST} --port=${CONNECT_PORT} -uroot -p${CONNECT_PASSWORD})

MYSQL_SCRIPT=("CREATE USER '${USER}'@'${HOST}' IDENTIFIED BY '${PWD}'; GRANT ALL PRIVILEGES ON $DB.* TO '${USER}'@'${HOST}';")

args=("${CONNECT_PARAMETERS[@]}" -e "${MYSQL_SCRIPT[@]}")
echo "Adding user for BGERP database..."
${MYSQL} "${args[@]}"
echo "Done"

After successfull migrating to MariaDB in Docker you may install MariaDB on host instead Mysql and migrate to it.