How to manage MySQL databases and MySQL users from the command line
What is MySQL?
MySQL is popular an open source database management software that helps users store, organize and retrieve data using Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. MySQL can be used to retrieve information from databases underpinning CMS systems, forums, galleries, blogs, shopping carts and many others.
This short blog post describes how to carry out common MySQL administration tasks from the command line.
Login to MySQL shell
Once you have MySQL installed, type the following your terminal:
mysql -u root -p
You will be prompted to enter the MySQL root user password into the prompt, which will allow you to access the MySQL shell.
It is very important to end all MySQL commands with a semicolon ( ; ) otherwise the command will not execute.
MySQL commands are normally written in uppercase but databases, tables, usernames and other text is by convention written in lowercase. However, MySQL commands are not case-sensitive and you may choose to ignore these conventions if you wish.
List databases
Because we have logged into MySQL as root user, we have access to all databases. To list all MySQL databases on the system, run the following command.
SHOW DATABASES;
The output should look something like this
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
Create MySQL database
Creating MySQL database is very easy, MySQL command below creates new database called rails_project.
CREATE DATABASE rails_project;
Running the SHOW DATABASES; command again results in
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rails_project |
| test |
+--------------------+
5 rows in set (0.00 sec)
Create a MySQL database user
Once we have rails_project database created, it is a good idea to create a user with appropriate permissions to access the database. This avoids having to use MySQL root user credentials. The MySQL command below will create a user called deploy with password helloWorld
CREATE USER 'deploy'@'localhost' IDENTIFIED BY 'helloWorld';
Now we have user deploy created but without the necessary permissions. This means if we logout from MySQL and then attempt to login again with mysql -u deploy -p ,permission will be refused
Grant MySQL user database privileges
MySQL command below will grant all privileges (full access) to deploy user on rails_project database.
GRANT ALL PRIVILEGES ON rails_project . * TO 'deploy'@'localhost';
NOTE: Replacing rails_project with * would grant all privileges to all databases on your system.
Reload privileges
The last step to finalize deploy user permissions to login and interact with rails_project database is to reload all privileges.
FLUSH PRIVILEGES;
User deploy is now able to login to the MySQL shell and access rails_project database.
Select MySQL database
If deploy user wishes to interact (create tables, update records …) with rails_project database using MySQL shell, deploy user must select rails_project database.
USE rails_project;
Running command above will select rails_project database and deploy can start performing actions on this database.
List all MySQL users
If you still logged in as MySQL root user, you could be wondering how to list all MySQL users created on system. Command bellow is only privileged to MySQL root user as ordinary MySQL user will get no output.
SELECT User FROM mysql.user;
Your output might be similar to one bellow, please note user deploy user in the first row.
mysql> SELECT User FROM mysql.user;
+------------------+
| user |
+------------------+
| deploy |
| josef |
| mysql.sys |
| root |
| thomas |
+------------------+
5 rows in set (0.00 sec)
Show MySQL user privileges
While still logged in as MySQL root user, you listed all MySQL users. Now you wish to check privileges for user deploy.
SHOW GRANTS FOR 'deploy'@'localhost';
Your output might be similar to one bellow, please note deploy user in the first row.
mysql> SHOW GRANTS FOR 'deploy'@'localhost';
+-------------------------------------------------------------------+
| Grants for deploy@localhost |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'deploy'@'localhost' |
| GRANT ALL PRIVILEGES ON `rails_project`.* TO 'deploy'@'localhost' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL commands mentioned above will help you to get up and running but what if you wish remove MySQL user and MySQL database?
Delete MySQL user
Please be aware that deleting a MySQL user can’t be undone! MySQL command bellow deletes MySQL user deploy.
DROP USER 'deploy'@'localhost';
If you now run SELECT User FROM mysql.user; you should see that the deploy user is no longer listed.
mysql> SELECT User FROM mysql.user;
+------------------+
| user |
+------------------+
| josef |
| mysql.sys |
| root |
| thomas |
+------------------+
4 rows in set (0.00 sec)
Delete MySQL database
The MySQL command given below deletes the database rails_project, but again be aware that deleting MySQL database can’t be undone!
DROP DATABASE rails_project;
Executing SHOW DATABASES; command now shows that rails_project database is no longer present.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
