mariadb.md

MariaDB

• See also
  • MySQL cheat sheet
  • Troubleshooting connection issues
  • MariaDB performance tuning
    • InnoDB Buffer Pool
  • MySQLTuner script

Shell

• mysql -u <USERNAME> -p<PASSWORD> = Connect to a local MariaDB instance.
• mysql -h <SERVER> -u <USERNAME> -P <PORT> -p<PASSWORD> <DATABASE_NAME> = Connect to a remote MariaDB instance.
• mysqldump -u <USERNAME> -p -x -A >dbs.sql = Create database dump.
• mysqld_safe --skip-grant tables & = Launch MySQL to reset the root password.

Databases

• show databases; = Print all databases.
• use my_database; = Connect to the database called my_database.
• show tables; = Print tables of the current database.

Users

• See also:
  • Account management commands
    
    
• use mysql; select user,host from user; = Print all users.
• create user alice@localhost identified by 'password123'; = Create user alice with password password123.
  • localhost is the server alice is allowed to connect from.
• create user 'alice'@'%' identified by 'password123'; = Create user alice that is allowed to connect from any server.
  • alice@localhost and alice@% are DIFFERENT users.
• grant all privileges on my_database.* to 'alice'@'localhost'; = Give alice full permissions to my_database.
• set password for 'alice'@'localhost' = password('CorrectHorseBatteryStaple'); = Change password for alice.
• drop user 'alice'@'localhost'; = Delete user alice.

MariaDB Galera cluster

• See also:
  • Understanding Galera
  • Galera node monitoring
  • Galera node failure & recovery: 1 2

Quorum requires more than half of all nodes to be running!

• galera_new_cluster = Re-bootstrap a failed cluster. Run from bash on most up-to-date node.
  • show status like 'wsrep_last_committed'; = Show commit number. Compare across nodes to determine most up-to-date node.
• show status like 'wsrep%'; = Show cluster status.
  
  Change safe_to_bootstrap to 1 to forcibly allow bootstrapping node:

root@server# cat /var/lib/mysql/grastate.dat

# GALERA saved state
version: 2.1
uuid:    00dbfde9-51d0-11eb-8b86-02d3d6f3051a
seqno:   -1
safe_to_bootstrap: 0

• If show status like 'wsrep_cluster_size'; shows the incorrect node number, reboot the node that doesn't agree with the others -- don't just restart the service.