• 05 Jun, 2026
• 0 Comments
• 4 Mins Read

MySQL Managing Databases

MySQL Managing Databases: A Complete Guide for Beginners and DBAs

Introduction

Database management is one of the most important responsibilities of a MySQL Database Administrator (DBA). Whether you are developing applications, managing enterprise systems, or learning database administration, understanding how to create, modify, and maintain databases is essential.

MySQL provides a simple yet powerful set of commands for managing databases. This guide covers everything from creating databases to modifying and deleting them, along with best practices used in production environments.

What is a Database in MySQL?

A database is a structured collection of data stored electronically. In MySQL, a database acts as a container that holds tables, views, stored procedures, triggers, and other database objects.

For example:

• Company Database
  • Employees Table
  • Departments Table
  • Payroll Table
• E-commerce Database
  • Customers Table
  • Products Table
  • Orders Table

A single MySQL server can host multiple databases.

Viewing Existing Databases

To display all databases available on the MySQL server:

SHOW DATABASES;

Example Output:

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

Understanding System Databases

When MySQL is installed, several system databases are created automatically.

1. mysql

Stores:

• User accounts
• Privileges
• Authentication details

2. information_schema

Contains metadata about:

• Tables
• Columns
• Indexes
• Views

3. performance_schema

Used for:

• Performance monitoring
• Query analysis
• Resource usage tracking

4. sys

Provides simplified performance reports based on performance_schema.

Creating a Database

The CREATE DATABASE statement is used to create a new database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE companydb;

Verify creation:

SHOW DATABASES;

Creating Database with Character Set

When working with multilingual applications, define a character set during creation.

Example:

CREATE DATABASE companydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Benefits:

• Supports Unicode characters
• Handles emojis
• Better international language support

Selecting a Database

Before creating tables or running queries, select the database.

Syntax:

USE database_name;

Example:

USE companydb;

Output:

Database changed

Display Current Database

To check which database is currently selected:

SELECT DATABASE();

Example Output:

+------------+
| DATABASE() |
+------------+
| companydb  |
+------------+

Viewing Database Information

Display creation details of a database:

SHOW CREATE DATABASE companydb;

Example Output:

CREATE DATABASE `companydb`
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Renaming a Database

MySQL does not support direct database renaming.

Instead:

1. Create a new database.
2. Export old database.
3. Import into new database.
4. Drop old database.

Example:

mysqldump -u root -p olddb > backup.sql

mysql -u root -p -e "CREATE DATABASE newdb"

mysql -u root -p newdb < backup.sql

Modifying a Database

You can change database attributes such as character set and collation.

Syntax:

ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Example:

ALTER DATABASE companydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Deleting a Database

To permanently remove a database:

Syntax:

DROP DATABASE database_name;

Example:

DROP DATABASE companydb;

Warning:

This operation deletes:

• All tables
• All data
• Views
• Stored procedures
• Triggers

Recovery is impossible unless backups exist.

Creating a Database Only if it Doesn’t Exist

Avoid errors when creating databases repeatedly.

CREATE DATABASE IF NOT EXISTS companydb;

Example Output:

Query OK, 1 row affected

or

Database exists

Dropping Database Only if it Exists

Prevent errors during deletion.

DROP DATABASE IF EXISTS companydb;

Useful in automation scripts.

Database Backup Before Management Tasks

Always create backups before making major changes.

Using mysqldump:

mysqldump -u root -p companydb > companydb_backup.sql

Restore backup:

mysql -u root -p companydb < companydb_backup.sql

Checking Database Size

Find database storage usage:

SELECT
table_schema AS Database_Name,
ROUND(SUM(data_length + index_length)/1024/1024,2)
AS Size_MB
FROM information_schema.tables
GROUP BY table_schema;

Sample Output:

+---------------+---------+
| Database_Name | Size_MB |
+---------------+---------+
| companydb     | 250.45  |
+---------------+---------+

Database Management Best Practices

1. Use Meaningful Database Names

Good:

salesdb
hrdb
inventorydb

Avoid:

db1
test123
abc

2. Implement Regular Backups

Schedule:

• Daily backups
• Weekly full backups
• Monthly archive backups

3. Apply Least Privilege Principle

Grant only required permissions.

Example:

GRANT SELECT, INSERT
ON companydb.*
TO 'appuser'@'localhost';

4. Monitor Database Growth

Track:

• Table sizes
• Index sizes
• Disk consumption

This helps prevent storage issues.

5. Use UTF8MB4 Character Set

Recommended for modern applications:

utf8mb4

Benefits:

• Supports all Unicode characters
• Future-proof design

6. Test Before Production Changes

Always validate:

• Schema modifications
• Character set changes
• Deletion operations

in a development environment first.

Common Database Management Commands Cheat Sheet

Task	Command
Show databases	SHOW DATABASES;
Create database	CREATE DATABASE dbname;
Select database	USE dbname;
Current database	SELECT DATABASE();
Database structure	SHOW CREATE DATABASE dbname;
Modify database	ALTER DATABASE dbname;
Delete database	DROP DATABASE dbname;
Safe create	CREATE DATABASE IF NOT EXISTS dbname;
Safe delete	DROP DATABASE IF EXISTS dbname;

Conclusion

Managing databases is a fundamental skill for every MySQL administrator and developer. From creating databases and configuring character sets to backing up and safely deleting databases, proper database management ensures performance, security, and reliability.

By following best practices such as regular backups, privilege management, database monitoring, and proper naming conventions, organizations can maintain healthy and scalable MySQL environments.

Mastering MySQL database management is the first step toward becoming a successful MySQL DBA and handling real-world production systems with confidence.

Learnomate Technologies – Empowering Future Database Administrators through Practical Training and Real-World Experience.