Common MySQL Commands

Logging into a mysql

mysql -u (user) -p

Type your password at the prompt

Creating a Database

e.g. test_database

CREATE DATABASE test_database;

Show or List Databases

SHOW DATABASES;

Example of results

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test_database  |
+--------------------+
4 rows in set (0.00 sec)

Creating a News and Grant Permission in MySQL

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

Granting Privileges

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

The asterisks in this command refer to the database and table (respetively) that they can access - this specific command allows the user to read, edit, execute and perform al tasks accross all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to relaod all privileges.

FLUSH PRIVILEGES;

Your changes will be in effect now.

About these Permissions:

• ALL PRIVILEGES - (grants user all access to database or system)
• CREATE - (grants user to create new tables or databases)
• DROP - (allows user to delete tables or databases)
• DELETE - (allows user to delete rows from tables)
• INSERT - (allows user to insert rows into tables)
• SELECT - (allows user to select command to read through databases)
• UPDATE - (allows user to update table rows)
• GRANT OPTION - (allows users to grant or remove other users' privileges)

Example

GRANT [type of permission] ON [database name].[table name] TO '[USERNAME]'@'localhost'';

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name

Each time you update or change a permission be sure to use the flush privileges command.

If you need to revoke permission, the structure is almost identical to granting it.

REVOKE [type of permission] ON [database name].[table] FROM '[username]'@'localhost';

Just as you can delete databases with DROP, you can use DROP to delete a user altogether.

DROP USER 'demo'@'localhost';