en:server:services:mariadb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
en:server:services:mariadb [2022/05/30 07:36] – created danen:server:services:mariadb [2023/02/14 21:42] (current) – [Check/update database] switched to mariadb-* instead of mysql* dan
Line 33: Line 33:
 </code> </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>
  • en/server/services/mariadb.1653896217.txt.gz
  • Last modified: 2022/10/24 08:24
  • (external edit)