MySQL cheat sheet

Cheat Sheet

# Create a new user and let remote access possible
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
#
# Show user in sql
# Show all MySQL users:
mysql> SELECT user FROM mysql.user;
# List only unique user names:
mysql> SELECT DISTINCT user FROM mysql.user;
# Show MySQL users and hosts they are allowed to connect from:
mysql> SELECT user,host FROM mysql.user;
# Show MySQL users, their passwords and hosts:
mysql> SELECT user,host,password FROM mysql.user;
# in MySQL 5.7 and higher:
mysql> SELECT host,user,authentication_string FROM mysql.user;
#
# Remove user
## Revoke all grants for a mysql user
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';
mysql> DROP USER 'username'@'localhost';
mysql> FLUSH PRIVILEGES;
# Clean a table, delete all records
mysql> DELETE FROM tableName;
# select / use a database
mysql> USE databaseName;
# remove a table
mysql> DROP TABLE tablename;
# remove a database
mysql> DROP DATABASE dbname;
# login with other user name
mysql> mysql -h localhost -P 3306 --protocol=tcp -u userName -p
mysql> mysql -h 127.0.0.1 -P 3306 --protocol=tcp -u userName -p