Managing MySQL or MariaDB in CLI has some annoying moments.

  1. One is when the version of the application has different commands.
  2. Another is when you forget to type the semicolon at the end.
  3. Do not forget that MySQL and MariaDB are not exactly the same.

Before starting check the version of the application:

mysql --version

OR

mysql> SELECT VERSION();

If you have MySQL 5.7.6+ or MariaDB 10.1.20+ the commands may be different than if they are older than that.

Log-in the MySQL as root:

mysql -u root -p

Managing databases:

mysql> SHOW DATABASES;
mysql> CREATE DATABASE databaseName;
mysql> CREATE DATABASE IF NOT EXISTS databaseName;
mysql> DROP DATABASE databaseName;
mysql> DROP DATABASE IF EXISTS databaseNname;

Managing users:

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';

For newer:

mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';

For older:

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

Listing and deleting users:

mysql> SELECT user, host FROM mysql.user;
mysql> DROP USER 'user'@'localhost';
mysql> DROP USER IF EXISTS 'user'@'localhost';

Managing privileges:

mysql> GRANT ALL PRIVILEGES ON databaseName.* TO 'user'@'localhost';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
mysql> GRANT ALL PRIVILEGES ON databaseName.tableName TO 'user'@'localhost';
mysql> GRANT SELECT, INSERT, DELETE ON databaseName.* TO [email protected]'localhost';
mysql> REVOKE ALL PRIVILEGES ON databaseName.* TO 'user'@'localhost';
mysql> SHOW GRANTS FOR 'user'@'localhost';

If you forgot your root password:

sudo systemctl stop mysql
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &
mysql -u root

For newer:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

In case ALTER USER does not work try:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

For older:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;

Restart the service:

mysqladmin -u root -p shutdown
sudo systemctl start mysql
sudo systemctl start mariadb

Try to enter as root now:

mysql -u root -p