Tuesday, May 6, 2014

Database Cheat Sheet






This is a summary of the SQL, MySQL and SQLite commands that I use the most. I use it as my own Database Cheat Sheet but could be useful for you too. Feel free to copy & paste!

Open Database

Assign values to the variables or replace them with the values in the command. In this case MySQL will ask for password.
MySQLSQLite
USER=
DB=
mysql -u $USER -p $DB
DBFile=
sqlite3 $DBFile

Create User in MySQL

Assign values to the variables or replace them with the values in the commands. MySQL will ask for the root password.

In these examples I'm using 'localhost' to grant access from localhost only but if you want to grant remote access you can use the wildcard '%' to grant access from any server or specify the IP or Host name or a combination of both like in: '%.domain.com' or '127.0.0.%'

USER=
PASSWD=
HOST=localhost
mysql -u root -p   -t<<EOSQL
CREATE USER '$USER'@'$HOST' IDENTIFIED BY '$PASSWD';
GRANT ALL PRIVILEGES ON * . * TO '$USER'@'$HOST';
FLUSH PRIVILEGES;
EOSQL

If required to restrict the access replace the permissions, database or table in this line:

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

Where:
type_of_permission: could be ALL PRIVILEGES, CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, GRANT OPTION. If you want to grant more than one permission, execute one SQL GRANT command per permission.
database_name: is the database name in the table specified. Use * (asterisk) for any database.
table_name: is the table to grant the permission where the table is. Use * (asterisk) for any table.

Made a mistake? Revoke the permission or drop the user.

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

DROP USER ‘$USER’@‘localhost’;

After any change, remember to flush the changes with:

FLUSH PRIVILEGES;
Post a Comment