MySQL is probably the most popular relational database management system (RDBMS). A relational database management system organizes data as a relationship between columns and rows in a bunch of tables.
The columns represent the different attributes of the data, while each row records its values. This article can guide you on how to manage MySQL users and databases remotely using the command line.
It details how to:
- Create a MySQL users
- Delete a MySQL user
- Create a MySQL database
- Delete a MySQL database
- Delete a MySQL table
- Export a MySQL Database
- Import an SQL file into a MySQL database
Follow the steps below to set them up.
Creating a MySQL User
Securely log in to your server through SSH access to open the command line.
Type this command to log in to MySQL as the root user:
mysql -u root -p
Enter the MySQL root password and press Enter. Now, this will enable you to run MySQL commands as the user.
Now, to create a new user and assign privileges, then run this command from the mysql> prompt:
GRANT ALL PRIVILEGES ON *.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;
Replace username with the name of the user you are creating and password with the user’s password.
The above command will assign all privileges to the user. But you can restrict the privileges with the grant command. And here’s how:
GRANT PRIVILEGE ON *.* TO ‘username’@’localhost’;
Replace PRIVILEGE with the permission you want to grant the user. MySQL has over 50 privileges you can assign a user. You can view the full list here.
Now, to log in to MySQL as the new user, type /q to exit the program, then run this command:
mysql -u username -p
Of course, replace username with the name of the user you just created. Type the user’s password and press Enter to log in.
Deleting a MySQL User
You can delete a MySQL user by running this command:
DELETE FROM mysql.user WHERE user = ‘username’;
Replace username with the name of the user you intend to remove and press Enter.
You can view all the MySQL users with this command:
SELECT user FROM mysql.user GROUP BY user;
Creating MySQL Database
A user owns a MySQL database. So to create one, you’d need to log in first with the user account that holds the database.
Now run this command to create the database:
CREATE DATABASE databname;
Replace databname with the name of the database you want to create.
You’d need to run the USE command to access or work with the database you just created or any other databases. To do this, type the below command and press Enter.
USE databname;
Replace databname with the name of the database you want to open or work with.
Deleting a MySQL Database
Deleting a MySQL database from the command line is irreversible. So, always ensure to delete only the correct ones.
It’s advisable to list all the databases on your server and then copy the name of the database you intend to delete. Run the SHOW DATABASES command to list all your databases.
SHOW DATABASES;
Use the DROP DATABASES command to delete a MySQL database. Here’s how it should look like after the mysql> prompt:
DROP DATABASE databname;
Replace databname in the code with the name of the database you want to delete. Use the SHOW DATABASE command to confirm if the program has removed the database from the server.
Deleting MySQL Tables
You can delete a MySQL table with the DROP TABLE command, but first, you’d need to select the database where the table resides. Of course, you can do this with the USE command.
USE databname;
Replace databname with the database’s name you intend to select. Now run this command to delete a table:
DROP TABLE tablename;
Replace tablename in the code with the name of the table you want to delete.
Exporting MySQL Database
Exporting a MySQL database is essential if you intend to backup your database or transfer it to another server. You can perform this task using the mysqldump command.
To export a MySQL database into an SQL file, type this command
mysqldump -u username -p databname > ExportDatabase.sql
Replace username with the name of the database owner and databname with the name of the database you intend to export.
Also, replace ExportDatabase.sql with the name you’d want to export the MySQL file to.
Enter the user’s password, and MySQL will create the SQL file of your database.
Importing MySQL Database
You can restore a MySQL database by importing the backup file. To import an SQL file, for example, ExportDatabase.sql to your database, then run this command:
mysql -u username -p databname < ExportDatabase.sql
Replace username with the name of the database owner and databname with the name of the database you intend to import the file to.
Enter the user’s password to complete the import.
Wrapping It Up
You can manage your MySQL databases remotely with a handful of commands. This guide has described how to go about them. Follow the steps to get started.
