MariaDB cheatsheet

mariadb-cheatsheet.md

MariaDB (MySQL) cheatsheet

Create database and add user with access

# Create db
CREATE DATABASE 'testDB';

# Create user on localhost
CREATE USER 'user1'@localhost IDENTIFIED BY 'password1';
# for all hosts
CREATE USER 'user1'@'%' IDENTIFIED BY 'password1';

# Grant all privileges on all dbs
GRANT ALL PRIVILEGES ON *.* TO 'user1'@localhost IDENTIFIED BY 'password1';
# Grant all privileges on single db
GRANT ALL PRIVILEGES ON testDB.* TO 'user1'@localhost IDENTIFIED BY 'password1';
# Important to flush
FLUSH PRIVILEGES;

# Check grants for user
SHOW GRANTS FOR 'user1'@localhost;

# Remove user
DROP USER 'user1'@localhost;

# Check remote users
SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

# User to access from local lan
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;