MySQL is one of the best ways to save data with many functionalities. Creating multiple databases and granting specific sets of privileges to users granting them privileges such as creating databases, inserting, updating, and dropping, are a few of many of what you get. One of the best things about MySQL is that you can manage it right from the command line or terminal, which is a great feature.
This guide covers all aspects of managing MySQL Databases and Users from the Command line, including the following.
Now, each of the above is explained in detail.
How to Manage MySQL Users?
The operations performed in this section are relevant to MySQL users and their management; let’s get into them.
Log in as the root user
To do the management, there’s a super-user with all the power in MySQL, known as the “root user”, and database administrators can create other super-users using the command:
$ sudo mysql -u root -p
The default password for the root user in MySQL is blank, so don’t type anything there or remove the “-p” flag if you haven’t set any password.
Make a New User Account
Users are the ones who use the databases either to view or manipulate. To create a new user account, you’re required to create one using this command.
> CREATE USER 'test'@'localhost' IDENTIFIED BY 'ASDF';
As seen in the image above, we’ve created a new user who can access only from the “local host” but if you want to create a user with the right to access from any system, use the above command like this.
> CREATE USER 'New-UserName'@'%' IDENTIFIED BY 'User-Password';
Just like that, you can create as many users as required.
Change the Login Password for a User
At some point, there’s a need to change the user password of an account which the same user can do.
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('asd');
There’s an error, as seen above, which is a version issue because this is for version 5.7.5 or older, so check your MySQL version using this command.
$ mysql -V
If your MySQL version is newer than 5.7.5, then use this command to set a new password for a user.
> sudo mysql -u root -p > SET PASSWORD = 'asd';
As seen in the above image, we’ve set a new password for a root user in MySQL.
List All Users
The database administrators might need to check the users using the database, which is done via the command.
> SELECT USER FROM mysql.user;
The above command can only be performed by the root user.
Granting or Revoking Permissions From a User Account
Granting and Revoking Permissions or Privileges is one of the main functionalities of MySQL, as you can manage what rights are to be given to which user, and it is done using this command (Granting).
> GRANT permission1, permission ON Database-Name.Table-Name TO 'user-name'@'localhost or IP';
Now, if we want to grant all privileges on testDB (database) and its tables to the “test” user, we’d use this command.
> GRANT ALL PRIVILEGES ON testDB.* TO 'test'@'localhost';
In the above image, the “testDB” is our database name while the “ * ” indicates all permissions granted to ‘test’ for more information on privileges, following this official documentation.
To revoke permissions, we’d use this command.
> REVOKE INSERTON testDB.* FROM 'test'@'localhost';
The above image indicates that we’ve revoked the “INSERT” privilege for the “test” user and then checked the privileges granted using this command.
> SHOW GRANTS FOR ‘’@’localhost’;
Delete a User Account
Deleting a user in MySQL requires using the following command (root user only).
> DROP USER 'user-name'@'localhost or IP';
For example, if we need to delete a user account called “test”, we will use this command.
> DROP USER 'test'@'localhost';
In the above command, we’ve deleted the user account of the “test” user, and to delete any other account, replace the test with the required user name.
How to Manage MySQL Databases?
All the operations performed in this section are relevant to the MySQL databases.
Create a Database
A database is a collection of tables with an organized set of data, and to create one, the following command can be used.
> CREATE DATABASE IF NOT EXISTS newDB;
The above query will only create a database if it does not exist; else, it will just bypass the operation.
Switch to a Database
We’ve created a new database called “newDB” . You need to switch to the database using the USE keyword to make operations in it.
> USE newDB;
Create Tables in a Database
When the required database is selected to add tables, use this command to create a new table.
> CREATE TABLE tab1 (first VARCHAR(50) NOT NULL);
The above image shows that we’ve created a new table named “tab1”.
To check the tables inside the currently selected database, use this command
> SHOW TABLES;
List All Databases
To see a list of all databases currently present on the system, this command is used.
> SHOW DATABASES;
Delete a Database
The following command is used to get rid of a database in MySQL.
> DROP DATABASE IF EXISTS testDB;
When we checked, the database named “testDB” was no more.
Import & Export MySQL Databases
If you wish to move your data from one server to another, there’s a way to export your database(s) and then import them to the other server. First, we need to export using this command.
$ sudo mysqldump -u root -p test1 > exportdatabase.sql
Using the above command, we’ve exported the database file named “test1” to an external file named “exportdatabase.sql” and the file is stored in the “Home” directory.
When you have copied this file to another server, create a new database, and then import the copied file using this command.
$ sudo mysql -u root -p asd < exportdatabase.sql
And finally, we can see the “asd” database in our system using this command.
> SHOW DATABASES;
That’s how you can manage the database and user from the command line.
Conclusion
In MySQL, any database administrator has to look for two basic tasks frequently, i.e., user management and database management. This post has carried out a detailed guide to demonstrate all the basic user and database management operations.
