mnestorov/mysql-commands

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

• Change MySQL Password Policy Level

• 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

• 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

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

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

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;

SELECT * FROM mysql.user;

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

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

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;

FLUSH PRIVILEGES;

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

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

DROP USER 'user'@'localhost';

CREATE DATABASE [database_name];

SHOW DATABASES;

USE [database_name];

SHOW TABLES;

DROP DATABASE [database_name];

DROP TABLE [table_name];

SELECT * FROM [table_name];

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.

mysqldump -u root -p database_name > database_name.sql

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

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

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

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;

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

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

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