MySQL cheatsheet

Manage databases

CREATE DATABASE database;

DROP DATABASE database;

CREATE TABLE table_name;

DROP TABLE table_name;

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

ALTER USER 'username'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

DROP USER ‘username’@‘localhost’;

select host, user, password from mysql.user;

 mysqladmin -u root -p password [NewPassword]

sudo mysqladmin -u root password -p

GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';

GRANT type_of_permission ON database_name.table_name TO ‘username’@'localhost’;

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

REVOKE type_of_permission ON database_name.table_name FROM ‘username’@‘localhost’;

SHOW GRANTS username;

FLUSH PRIVILEGES;

Edit /etc/phpmyadmin/config.inc.php

if (!empty($dbname)) {
   $cfg['Servers'][$i]['auth_type'] = 'cookie';
+  $cfg['Servers'][$i]['AllowRoot'] = false;   

}

nano /etc/mysql/my.cnf

Or other files who MariaDB/MySQL is registered.

- bind-address = 127.0.0.1
+ bind-address = 0.0.0.0

After that, restart the service.

systemctl restart mariadb

You can verify if the process listening to the good IP and port with netstat :

netstat -ant | grep 3306