en:server:services:mariadb [Your Digital Privacy DIY Solutions

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MariaDB ======

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009.

MariaDB is intended to maintain high compatibility with MySQL, with library binary parity and exact matching with MySQL APIs and commands, allowing it in many cases to function as drop-in replacement for MySQL. However, new features are diverging. It includes new storage engines like Aria, ColumnStore, and MyRocks.

Its lead developer/CTO is Michael "Monty" Widenius, one of the founders of MySQL AB and the founder of Monty Program AB. On 16 January 2008, MySQL AB announced that it had agreed to be acquired by Sun Microsystems for approximately $1 billion. The acquisition completed on 26 February 2008. Sun was then bought the following year by Oracle Corporation. MariaDB is named after Widenius' younger daughter, Maria. (MySQL is named after his other daughter, My.)

===== Package =====

<code>
pacman -S mariadb
</code>

===== Create data dir =====

<code>
mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
</code>

===== Start =====

<code>
systemctl enable --now mariadb.service
</code>

===== Improve initial security =====

Set the password for the mysql root user, and most questions can be answered yes, for example, if you do not need remote access.

<code>
mysql_secure_installation
</code>

===== Create local user =====

<code>
mysql -u root -p
</code>
<code>
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;
EXIT;
</code>

===== Create remote user =====

<code>
mysql -u root -p
</code>
<code>
CREATE USER 'user_name'@'%' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;
EXIT;
</code>

===== Create database =====

<code>
mysql -u root -p
</code>
<code>
CREATE DATABASE db_name;
GRANT ALL ON db_name.* to 'user_name'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
\q
</code>
or something more detailed
<code>
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON db_name_here.* TO 'myuser'@'localhost';
</code>

===== Show and delete database =====

<code>
mysql -u root -p
SHOW DATABASES;
DROP DATABASE db_name;
FLUSH PRIVILEGES;
EXIT;
</code>

===== Show and delete user =====

<code>
mysql -u root -p
SELECT User FROM mysql.user;
DROP USER user_name@localhost;
FLUSH PRIVILEGES;
EXIT;
</code>

===== Set or change password =====

<code>
SET PASSWORD FOR 'user_name'@'localhost' = PASSWORD('password');
FLUSH PRIVILEGES;
EXIT;
</code>

===== Backup database =====

You can dump all databases with the following command ''mysqldump -u root -p %%--%%all-databases | gzip > all_databases.sql.gz'' or just one single database ''mysqldump -u root -p --databases database_name | gzip > database_name.sql.gz''.

Or use the following script, which contains:
  * separate databases (when you extract the file)
  * define after how many days the old backups should be deleted
  * email notification

<code>
#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER="my_user"             # DB_USERNAME
MyPASS="mypassword"          # DB_PASSWORD
MyHOST="localhost"           # DB_HOSTNAME

# Backup Dest directory
DEST="/path/to/backup/mysql/"

# Email for notifications
# EMAIL="email"
# How many days old files must be to be removed
DAYS="60"

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%H-%M")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
performance_schema"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
                for i in $SKIP
                do
                        [ "$db" == "$i" ] && skipdb=1 || :
                done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
        FILE="$MBD/$db.sql"
        $MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS $db > $FILE
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

#echo -e "Subject: MySQL backup $(hostname)\r\n\r\nMySQL backup is completed! Backup name is $NOW.tar.gz" | msmtp -a default $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;
</code>

Use [[en/server/services/crontab#examples|crontab]] for automation.
===== Restore database =====

<code>
mysql -u root -p db_name < database.sql
</code>

===== Check/update database =====

Upon a major version release of mariadb (for example mariadb-10.7.4-1 to mariadb-10.8.3-1), it is wise to upgrade databases: 

  - upgrade the package(s)
  - restart the daemon
  - run mariadb-upgrade

<code>
systemctl restart mariadb.service
</code>

<code>
mariadb-upgrade -u root -p
</code>

To check all tables in all databases:  
<code>
mariadb-check --all-databases -u root -p -c
</code>  

To analyze all tables in all databases:  
<code>
mariadb-check --all-databases -u root -p -a
</code>  

To repair all tables in all databases:  
<code>
mariadb-check --all-databases -u root -p -r
</code> 
 
To optimize all tables in all databases:  
<code>
mariadb-check --all-databases -u root -p -o
</code>

All "Check/update database" commands in one block
<code>
systemctl restart mariadb.service
mariadb-upgrade -u root -p
mariadb-check --all-databases -u root -p -c
mariadb-check --all-databases -u root -p -a
mariadb-check --all-databases -u root -p -r
mariadb-check --all-databases -u root -p -o
</code>