MariaDB Installation

Installation

• sudo pacman -S mariadb
• sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
• sudo systemctl enable --now mariadb

Enter in the database

• sudo mariadb

Create a user

• CREATE USER 'username'@'%' IDENTIFIED BY 'password';

• "%" means that all ip's is allowed to login with this credentials

Create a database

• CREATE DATABASE 'yourDB';

Give privilegies to that database

• GRANT ALL PRIVILEGES ON yourDB.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

To a specific ip

• "GRANT ALL PRIVILEGES ON . TO 'username'@'127.0.0.1' IDENTIFIED BY 'password' WITH GRANT OPTION;"

Backup

External backup from a usb drive

Mount the devices

• mount /dev/sdxy /mnt

Use the same datadir as configurated before

• mariadbd-safe
  --datadir=/mnt/var/lib/mysql
  --socket=/tmp/mysql.sock
  --skip-networking

The daemon database should be running now, open another tty, enter the database to check if everthing is ok

• mariadb -u root --socket=/tmp/mysql.sock

If is ok just exit the database
Now dump the entire database

mariadb-dump \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --socket=/tmp/mysql.sock \
  -u root \
  > /path/to/backup/mariadb_backup.sql

To restore from dump just use:

• mariadb < mariadb-backup.sql

• If is a fresh mariadb install you need to install mariadb system tables: mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

• Unfurtunnally this dump from previous command does not save users passwords correctly, you will need to setup users again