# Creating a Database MySQL is a leading open-source relational database management system (RDBMS) known for its reliability, scalability, and ease of use. Setting up a database properly in MySQL is crucial for ensuring long-term maintainability, performance, and security of applications. This guide walks through different ways to create a MySQL database: using the MySQL CLI, using Docker containers, and using the mysqladmin tool. It also emphasises best practices that should be followed at each step. ## **Creating a Database Using MySQL CLI** The most common and straightforward way to create a database is by using the MySQL command-line interface (mysql client). First, a connection must be established to the MySQL server using an account with appropriate privileges, typically the root account or a designated administrative user. #### **Connect to MySQL:** Connect to MySQL: ``` mysql -u root -p ``` To connect to a remote MySQL database using the MySQL CLI, you need to specify the host’s IP address or domain name using the `-h` flag along with the username and password: ```mysql mysql -h -P -u -p ``` You will be prompted to enter the password for the root user. Upon successful login, the MySQL shell opens where SQL queries can be executed. #### **Create a New Database** To create a database with default settings: ``` CREATE DATABASE mydatabase; ``` However, it is a best practice to explicitly define the character set and collation. This prevents potential problems with encoding and sorting, especially when dealing with multilingual data or special characters. The recommended character set for modern applications is utf8mb4, which fully supports Unicode. Create a database with specific character set and collation: ``` CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` You can verify that the database was created by listing all databases: ``` SHOW DATABASES; ``` Explicitly setting the character set ensures data consistency and minimizes future migration issues. Additionally, defining these settings at creation time avoids relying on server defaults, which can vary across different environments. ## **Creating a Database in Docker** Docker is a tool that helps run applications in isolated environments called containers. A MySQL container provides a self-contained database instance that can be quickly deployed and managed. If you are running MySQL inside a Docker container, follow these steps: #### **Access Elestio Terminal** Head over to your deployed MySQL service dashboard and head over to **Tools > Terminal**. Use the credentials provided there to log in to your terminal. [![image.png](https://docs.elest.io/uploads/images/gallery/2025-04/scaled-1680-/LoCimage.png)](https://docs.elest.io/uploads/images/gallery/2025-04/LoCimage.png) Once you are in your terminal, run the following command to head over to the correct directory to perform the next steps ``` cd /opt/app/ ``` #### **Access the MySQL Container Shell**

Instead of pulling an image or running the container manually, use Docker Compose to interact with your running container. As you are using Elestio, it will already be a Docker compose:

``` docker-compose exec mysql bash ``` Inside the container, access the MySQL shell: ``` mysql -u root -p ``` #### **Create Database** Now, to create a database, use the following command. This command tells MySQL to create a new logical database called `mydatabase`. By default, it inherits settings like encoding and collation from the template database (template1), unless specified otherwise. ``` CREATE DATABASE mydatabase; ``` ## **Creating a Database Using** `mysqladmin` The `mysqladmin` utility provides a non-interactive way to create databases. It is particularly useful for automation scripts and quick administrative tasks. To create a database: ``` mysqladmin -h -P -u root -p create mydatabase ``` One limitation of `mysqladmin` is that it does not allow specifying character set and collation at creation time. Therefore, if these settings need to be controlled explicitly (which is generally recommended), it is better to use the mysql CLI instead. Before using mysqladmin, ensure the MySQL server is running. On traditional installations, check the status: ``` sudo systemctl status mysql ``` If the service is not active, it can be started with: ``` sudo systemctl start mysql ``` ## **Best Practices for Creating Databases** **Use Meaningful Names** Choosing clear and descriptive names for databases helps in organisation and long-term maintenance. Avoid generic names like testdb or database1, as they do not convey the database’s role or content. Instead, choose names that reflect the kind of data stored or the application it supports, such as customer\_data, sales\_records, or analytics\_db. Meaningful names improve clarity for developers, DBAs, and future maintainers who need to quickly understand the purpose of each database without relying heavily on documentation. **Follow Naming Conventions** A standardized naming convention across all environments and teams simplifies database management and reduces confusion. MySQL database names are case-sensitive on Unix-based systems, so consistent use of lowercase letters is recommended. Use underscores to separate words (e.g., order\_details) rather than camelCase or spaces. This avoids the need for extra quoting in SQL queries and prevents platform-specific bugs. Additionally, avoid using reserved MySQL keywords or special characters in database names, as these can lead to parsing errors and unexpected behaviour. **Restrict User Permissions** Granting only the minimum required permissions significantly strengthens database security and reduces the likelihood of accidental damage or data leaks. Following the Principle of Least Privilege, reporting users should only be given SELECT access, while application users may require `SELECT`, `INSERT`, `UPDATE`, and `DELETE` rights. Only a few trusted administrative users should have powerful privileges like `ALTER`, `DROP`, or `GRANT`. Avoid assigning superuser access unless absolutely necessary. Creating user roles or groups with defined scopes can help standardise permission levels across teams and services. **Enable Backups** Regular backups are critical to ensure business continuity and safeguard against data loss from unexpected events such as accidental deletions, server crashes, or software bugs. MySQL provides tools like mysqldump for logical backups of individual databases, and mysqlpump or xtrabackup for more advanced use cases. It’s good practice to schedule automated backups using cron jobs or database orchestration tools. Backup files should be stored securely and regularly tested for restoration to verify that the process works as expected during emergencies. **Monitor Performance** Ongoing performance monitoring is essential to maintain the responsiveness and stability of MySQL databases. Monitoring tools like `performance_schema`, `information_schema`, or external platforms like Percona Monitoring and Management (PMM) help identify slow queries, locked transactions, and system resource bottlenecks. Use `EXPLAIN` and `ANALYZE` to understand query plans and optimize indexes. Keeping an eye on connection stats, query latency, and buffer pool usage allows for timely tuning and ensures efficient database operations at scale. ## **Common Issues and Their Solutions** Here’s a table summarizing common problems faced during database creation and how to resolve them:

Issue	Cause	Solution
ERROR 1044 (42000): Access denied for user	The connected user does not have the CREATE privilege.	Connect as a user with administrative privileges or grant necessary permissions.
ERROR 1007 (HY000): Can't create database; database exists	Attempting to create a database that already exists.	Choose a different name or drop the existing database if appropriate using DROP DATABASE.
Can't connect to MySQL server on 'localhost'	MySQL server is not running, or incorrect connection parameters are used.	Start the MySQL service and verify network and authentication parameters.
Collation or character set issues later in application	Database created without explicitly specifying character set or collation.	Always specify utf8mb4 and a collation like utf8mb4\_unicode\_ci during database creation.
Docker MySQL container refuses connections	MySQL container not ready or port mappings not correctly set.	Check container logs with docker-compose logs mysql and verify port exposure settings in docker-compose.yml.