How to Create, Drop, and View Databases on MySQL in Ubuntu 24.04 LTS

MySQL is a widely-used relational database management system, known for its robustness and ease of use. Managing databases is one of the fundamental tasks for database administrators and developers. In this article, we’ll cover how to create, drop, and view databases in MySQL on Ubuntu 24.04 LTS. Each command will be explained with simple and clear instructions.

Prerequisites

Before proceeding, ensure:

1. MySQL is installed and running on Ubuntu 24.04 LTS.
   If you not installed Please follow steps of How to Install MySQL 8 on Ubuntu 24.04 LTS to install MySQL.
2. You have administrative access to MySQL (root user or a user with sufficient privileges).

Log in to MySQL

Access the MySQL command-line interface using the root account or an account with appropriate privileges.

sudo mysql -u root -p

• -u root: Specifies the root user.
• -p: Prompts you to enter the root password.

Create a Database

To create a new database, use the CREATE DATABASE command. Replace database_name with your desired database name.

CREATE DATABASE database_name;

Example:

CREATE DATABASE my_first_database;

• CREATE DATABASE: This command initializes a new database.
• my_first_database: This is the name of the database being created. You can choose any name as long as it’s unique.

To verify the creation, view the list of databases (explained in Step 4).

Drop a Database

To delete an existing database, use the DROP DATABASE command. Replace database_name with the name of the database you want to delete.

DROP DATABASE database_name;

Example:

DROP DATABASE my_first_database;

• DROP DATABASE: This command removes a database permanently. Use it with caution as it cannot be undone.

View All Databases

To see a list of all databases on your MySQL server, use the SHOW DATABASES command.

SHOW DATABASES;

Example Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• SHOW DATABASES: This command lists all databases available on the server.

Additional Commands

Select a Database

Before performing operations like creating tables or inserting data, you need to select a database.

USE database_name;

Example:

USE my_first_database;

• USE: This command sets the specified database as the active one.

Rename a Database (Optional)

MySQL does not provide a direct command to rename a database. Instead, you can create a new database and export/import data.

1. Create a new database:

CREATE DATABASE new_database_name;

2. Export the old database from the terminal (ensure you are outside the MySQL shell):

sudo mysqldump -u root -p old_database_name > old_database_name.sql

If you encounter the error:

mysqldump: Got error: 1698: Access denied for user 'root'@'localhost' when trying to connect

Use the following command to resolve it:

sudo mysql -u root -p

Then, grant access privileges:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
EXIT;

Retry the mysqldump command with the updated privileges.

If you encounter the error:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Resolve it by updating the MySQL password policy or setting a stronger password:

SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.length = 6;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongP@ss1';
FLUSH PRIVILEGES;

• validate_password.policy = LOW: Lowers the password policy requirements.
• validate_password.length = 6: Sets a minimum password length of 6 characters.
• Replace StrongP@ss1 with a secure password.

Retry the mysqldump command with the updated privileges.

1. Import into the new database:

mysql -u root -p new_database_name < old_database_name.sql

• Ensure these commands are run outside the MySQL shell (on your terminal) to avoid syntax errors like ERROR 1064. If you’re inside the MySQL shell, type EXIT; to leave it before running these commands.

1. Drop the old database:

DROP DATABASE old_database_name;

Conclusion

In this article, you learned how to create, drop, and view databases in MySQL on Ubuntu 24.04 LTS. Database management is a fundamental skill for working with MySQL, and mastering these commands ensures you can efficiently handle database operations.

If you have any questions or need further assistance, feel free to contact us at:

Email: devopsbyrushi@gmail.com