MySQL cheatsheet

mysql-cheatsheet.md

Manage databases

Create database

CREATE DATABASE database;

Delete database

DROP DATABASE database;

Manage tables

Create table

CREATE TABLE table_name;

Delete table

DROP TABLE table_name;

Manage users

Create user

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

Edit a password for a user

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

Delete user

DROP USER ‘username’@‘localhost’;

Show users

select host, user, password from mysql.user;

Edit user password

 mysqladmin -u root -p password [NewPassword]

or

sudo mysqladmin -u root password -p

Permissions

Types of permissions

• ALL PRIVILEGES
• CREATE
• DROP
• DELETE
• INSERT
• SELECT
• UPDATE
• GRANT OPTION

Grant permissions

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 permissions

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

Show permissions

SHOW GRANTS username;

Flush privileges

FLUSH PRIVILEGES;

---

Disable root login on Phpmyadmin

Edit /etc/phpmyadmin/config.inc.php

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

}

---

Allow remote connection

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