MySQL / MariaDB Commands

Overview

This is a command reference for the MySQL and MariaDB database engines.

Password Policy

• Change MySQL Password Policy Level

Users

• Show All Database Users
• Create New Database User
• Change The Name Of A Single Database User
• Grant Privileges On A Single User
• Update Database Permissions Or Privilages
• Change A User Password From MySQL Prompt
• Change A Users Password From Unix Shell
• Delete A Single DB user

Databases

• Create A Database On The SQL Server
• List All Databases On The SQL Server
• Switch To A Database
• List All The Tables In The Database
• Delete A Single Database
• Delete A Single Database Table
• Show All Data In A Database Table

Backup

• Backing Up A Single Database
• Backing Up Multiple Databases
• Backing Up All Databases
• Back Up MariaDB Database With Compression

Restore

• Restore A Single Database
• Restore Multiple Databases
• Restore All Databases

Password Policy

Change MySQL Password Policy Level

To change the default password policy level, we can change the settings at runtime using the command line or in the config file my.cnf/mysqld.cnf permanently.

Login to MySQL command prompt and execute below query to view current settings of validate_password.

SHOW VARIABLES LIKE 'validate_password%';

The default level is MEDIUM, we can change it to LOW by using the below query. The LOW level required only passwords length to min 8 characters.

SET GLOBAL validate_password.policy=LOW;

Users

Show All Database Users

SELECT * FROM mysql.user;

Create New Database User

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Change The Name Of A Single Database User

RENAME USER 'user'@'localhost' TO 'newuser'@'localhost';

Grant Privileges On A Single User

Option 1

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

Option 2

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password'

Option 3

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Update Database Permissions Or Privilages

FLUSH PRIVILEGES;

Change A User Password From MySQL Prompt

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');

Change A Users Password From Unix Shell

[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'newpassword'

Delete A Single DB User

DROP USER 'user'@'localhost';

Databases

Create A Database On The SQL Server

CREATE DATABASE [database_name];

List All Databases On The SQL Server

SHOW DATABASES;

Switch To A Database

USE [database_name];

List All The Tables In The Database

SHOW TABLES;

Delete A Single Database

DROP DATABASE [database_name];

Delete A Single Database Table

DROP TABLE [table_name];

Show All Data In A Database Table

SELECT * FROM [table_name];

Backup

Backup And Restore MariaDB Databases Using The mysqldump Utility

mysqldump - is the utility that we will use to back up our MariaDB database. It’s designed specifically for backup purposes. The cool thing about mysqldump is that you don’t need to stop MariaDB service to make a backup. It can be used to back up a single database, multiple databases, and all databases. By default, it will create a dump file that contains all the statements needed to re-create the database.

Backup A Single Database

mysqldump -u root -p database_name > database_name.sql

Backup Multiple Databases

mysqldump -u root -p --databases db_name1 db_name2 ...  > multi_database.sql

Backup All Databases

mysqldump -u root -p --all-databases > all-databases.sql

Backup MariaDB Database With Compression

mysqldump -u root -p database_name | gzip > database_name.sql.gz

Restore

Restore A Single Database

Option 1 - From unix shell

mysql -u root -p database_name < database_name.sql

Option 2 - From within mysql

USE [database_name];
SOURCE backup-file.sql;

Restore Multiple Databases

mysql -u root -p < multi-databases.sql

Restore All Databases

mysql -u root -p < all-databases.sql

---

License

This repository is unlicense[d], so feel free to fork.