mac4u

Basic MySQL usage

Michael -

I been asked for some basic code to use in MySQL database management. Its hard to say what is basic usage, as every time I need to get something out of a db, if it is MySQL or T-SQL I need to use different methods and code elements, but I came up with some few that are pretty much a must know to event create a db for the purposes of installing Ghost or WordPress CMS's.

Lets get into the source:

1. Login into a db under Linux CLI:

mysql -u user_name -p
  • -u if your user name, if the db has just been installed the user here will need to be root
  • -p if a parameter and says that you are trying to log into the db using a password, if you would not put the -p parameter the system will try to log in without a password.

2. Creating a new user:

When you log into the db and want to create a new user type this code in:

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'your_strong_password';

The code is self explanatory, but just to say that after the @ you can use localhost which will limit the user ability to log in only via localhost or % which is a wildcard and allows the user to log into the db from outside the machine itself.

3. Granting privileges to users:

This one is more complex, as you need to know to whom you want to grant privileges, what type of privileges and which db:

GRANT type PRIVILEGES ON db_name.* TO 'user_name'@'localhost';

The word type must be replaced with specific privileges that you want to give to the user:

  • SELECT Ability to perform SELECT statement on the table.
  • INSERT Ability to perform INSERT statement on the table.
  • UPDATE Ability to perform UPDATE statement on the table.
  • REFERENCES Ability to create a constraint that refers to the table.
  • ALTER Ability to perform ALTER TABLE statements to change the table definition.
  • ALL ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES.

The db_name.* must be replaced with the name of the database you want to grant access to, or is to all replace with *.*

The same goes to the localhost on the end, depending on if the user has permission to access the the only via localhost or via wildcard %

4. How to revoke permision givet to a user.

The same logic applies as when you would be grating access to a db:

REVOKE type PRIVILEGES ON db_name.* FROM 'user_name'@''localhost';

The same as for granting access, you will need to specify what type of privileges are you revoking:

  • SELECT Ability to perform SELECT statement on the table.
  • INSERT Ability to perform INSERT statement on the table.
  • UPDATE Ability to perform UPDATE statement on the table.
  • REFERENCES Ability to create a constraint that refers to the table.
  • ALTER Ability to perform ALTER TABLE statements to change the table definition.
  • ALL ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES.

If you do not know what was grated to the user, adn you just want to check type:

SHOW GRANTS;

You will see a list of GRANTS for all users.

5. Show the list of all databases.

This one is simple, just type:

SHOW DATABASES;

6. To use a database.

You know which db you wanna work with, to add something or update etc. But how to select a db, type this:

USE db_name;

Where the db_name is the actual name of the db you want to use.

7. How to exit the db mode in console.

Simple, just type:

exit;