mysql

Wat het doet

Zo schrijf je het

mysql [-h HOST] [-u USER] [-p] [DB] [-e "SQL"]
mysqldump [OPTIONS] DB > FILE.sql

Belangrijkste delen

Useful SQL

SHOW DATABASES; / SHOW TABLES;

List.

CREATE USER 'u'@'host' IDENTIFIED BY 'pw';

New user.

GRANT … ON db.* TO 'u'@'host';

Give rights.

SHOW GRANTS FOR 'u'@'host';

Show rights.

SHOW PROCESSLIST;

Running queries.

SHOW VARIABLES LIKE 'x%';

Settings.

FLUSH PRIVILEGES;

Reload grants (after direct table edits).

Meest gebruikt

Niets past bij dit filter.

$ als gewone gebruiker# als root (of met sudo)

1. 1Install and secure MariaDB on RHEL

   #dnf install -y mariadb-server
   #systemctl enable --now mariadb
   #mysql_secure_installation

2. 2Create a database and an app user

   #mysql -u root -p
   MariaDB [(none)]> CREATE DATABASE shop CHARACTER SET utf8mb4;
   MariaDB [(none)]> CREATE USER 'shop'@'10.20.2.%' IDENTIFIED BY 'Ch4nge-me-2026';
   MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop'@'10.20.2.%';

3. 3Check users and rights

   $mysql -u root -p -e "SELECT user, host FROM mysql.user;"
   $mysql -u root -p -e "SHOW GRANTS FOR 'shop'@'10.20.2.%';"

4. 4Back up without locking the site

   $mysqldump -u backup -p --single-transaction --routines --triggers shop | gzip > shop-$(date +%F).sql.gz

5. 5Restore

   $gunzip < shop-2026-10-27.sql.gz | mysql -u root -p shop

6. 6Find slow or stuck queries

   $mysql -u root -p -e "SHOW FULL PROCESSLIST;" | awk '$6 > 30'

7. 7Database sizes

   $mysql -u root -p -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;"

8. 8Keep the password out of the command line

   $cat ~/.my.cnf
   [client]
   user=backup
   password=Example-Only-2026
   $chmod 600 ~/.my.cnf

9. 9Listen only on the private network

   #cat /etc/my.cnf.d/bind.cnf
   [mysqld]
   bind-address=10.20.2.10
   #systemctl restart mariadb

10. 10Run in a container for a lab

    $podman run -d --name db -e MARIADB_ROOT_PASSWORD=labonly -p 127.0.0.1:3306:3306 docker.io/library/mariadb:11.4

Niets past bij dit filter.

Bestanden

/etc/my.cnf, /etc/my.cnf.d/

Server and client settings.

/var/lib/mysql/

Data files.

/var/log/mariadb/mariadb.log

Log (RHEL).

Exitcodes