To create a database in MySQL, use the `CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name]` statement. This straightforward command establishes a new database container where you’ll store your tables and data. For example, if you’re building a WordPress site with custom functionality, you might run `CREATE DATABASE IF NOT EXISTS my_website CHARACTER SET utf8mb4` to create a database that properly handles international characters and emoji. The `IF NOT EXISTS` clause prevents errors if the database already exists, making the command safe to run repeatedly.
Creating tables and users follows a similar pattern but with more configuration options. Tables are created with `CREATE TABLE` statements that define columns, data types, and constraints, while users are created with `CREATE USER ‘username’@’host’ IDENTIFIED WITH authentication_plugin BY ‘password’`. Once users exist, you assign them permissions using `GRANT privilege ON database.table TO ‘username’@’host’`. Together, these three operations form the foundation of any MySQL setup, whether you’re managing a small project site or a large multi-user application. This guide walks through each operation with practical examples, security considerations, and best practices you should know before deploying to production.
Table of Contents
- Creating MySQL Databases—Syntax, Character Sets, and Database Initialization
- Creating MySQL Tables—Defining Columns, Data Types, and Constraints
- Creating MySQL Users—Authentication Methods and Host Configuration
- Granting Permissions—Assigning Privileges and Following the Principle of Least Privilege
- Security Considerations—Default Users, Password Policies, and Access Control
- Managing Multiple Users and Environment-Specific Access
- The Evolving MySQL Landscape and Database Evolution
- Conclusion
- Frequently Asked Questions
Creating MySQL Databases—Syntax, Character Sets, and Database Initialization
Creating a MySQL database is the first step in any database project, and the syntax is intentionally simple. The basic command structure is `CREATE DATABASE database_name`, but you should almost always add the `IF NOT EXISTS` modifier to avoid errors in automated scripts: `CREATE DATABASE IF NOT EXISTS my_database`. Character set selection matters more than many developers initially realize. By default, MySQL may use latin1, which cannot properly store modern emoji, accented characters, or languages beyond Western European text. Specify `CHARACTER SET utf8mb4` when creating databases that will store international content, user-generated text, or any modern web application data. UTF-8 has become the standard for the web, and utf8mb4 is MySQL’s full implementation of UTF-8 that handles four-byte characters correctly.
The `IF NOT EXISTS` clause makes database creation idempotent—running the same script multiple times won’t fail. This is critical when using configuration management tools, Docker containers, or deployment automation. Without it, your deployment script fails the second time it runs. Similarly, you can add `DEFAULT COLLATE utf8mb4_unicode_ci` to specify how MySQL should sort and compare text, with utf8mb4_unicode_ci being appropriate for case-insensitive, language-aware comparisons. A limitation to be aware of: while you can change a database’s character set after creation, converting existing tables requires explicit `ALTER` commands on each table, making it better to choose the correct settings upfront. If you inherit a WordPress installation with latin1 databases storing emoji-filled comments, you’ll need careful migration procedures to avoid data corruption.
Creating MySQL Tables—Defining Columns, Data Types, and Constraints
Tables define the actual structure where your data lives, and the `CREATE TABLE` syntax specifies each column’s name, data type, and constraints. A minimal example looks like `CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)`. This creates a users table with an auto-incrementing primary key, a unique username field, an email field, and an automatic timestamp showing when each record was created. Data type selection directly impacts performance and storage: `INT` uses 4 bytes, `BIGINT` uses 8 bytes, `VARCHAR(255)` uses variable space up to 255 bytes, and `TEXT` can store much larger content without a predefined limit. One common mistake is creating tables without primary keys or proper constraints. Every table should have a primary key—usually an auto-incrementing ID—which ensures row uniqueness and enables efficient lookups.
Without it, you’ll eventually face data integrity problems and performance degradation as the table grows. Another important consideration is choosing between `VARCHAR` and `TEXT` for string data. VARCHAR requires you to specify a maximum length but is more space-efficient for smaller strings, while TEXT has no predefined limit but uses more disk space. For usernames and emails, `VARCHAR(255)` is standard. For user comments or article content, `TEXT` or `LONGTEXT` is appropriate. A significant limitation: changing a table’s structure after creation requires `ALTER TABLE` commands, which can lock the table during execution on large datasets. On production systems with millions of rows, adding a new column can cause significant downtime, so planning your schema carefully upfront pays dividends.
Creating MySQL Users—Authentication Methods and Host Configuration
MySQL users are separate from database users; they’re authentication credentials that connect to the MySQL server. Create users with `CREATE USER ‘username’@’host’ IDENTIFIED WITH authentication_plugin BY ‘password’`. The host portion is crucial: `’username’@’localhost’` allows connections only from the same server (ideal for local development), while `’username’@’%’` allows connections from any host (dangerous for production). For a staging environment where your web server and database run on the same machine, `CREATE USER ‘wordpress_user’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘strong_password_here’` creates a user that can only connect locally. Authentication plugins determine how passwords are validated.
The `mysql_native_password` plugin is the traditional method, widely supported by older libraries and frameworks. Newer MySQL installations default to `caching_sha2_password`, which is more secure but requires compatible connection libraries—some older php versions or legacy applications may not support it. If you’re integrating with systems you don’t control, verify their MySQL driver compatibility before choosing caching_sha2_password. The password itself should be strong and random; never use default passwords like ‘password’ or ‘admin’. A practical example: when setting up a WordPress installation on a hosting platform that provides the database for you, the provider often creates a user automatically with a generated password. If you’re managing your own MySQL installation, you generate these credentials yourself, and the security of those passwords directly affects your application’s security.
Granting Permissions—Assigning Privileges and Following the Principle of Least Privilege
Once users exist, grant them permissions using `GRANT privilege ON database.table TO ‘username’@’host’`. The basic syntax is straightforward, but permission management requires careful thought. `GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO ‘app_user’@’localhost’` grants four specific permissions on all tables in the production_db database to app_user. This follows the principle of least privilege: the user has only the permissions needed for their specific role, not blanket access. By contrast, `GRANT ALL PRIVILEGES ON *.* TO ‘app_user’@’localhost’` grants everything on everything, which is almost never appropriate outside development environments.
The tradeoff between convenience and security appears here clearly. Granting broad permissions makes development and testing faster because you don’t worry about missing permissions during testing, but it creates security risks. If the app_user credentials are compromised, an attacker has minimal access if you’ve granted only SELECT and INSERT permissions, but maximum damage if you’ve granted ALL PRIVILEGES. For WordPress specifically, the application needs SELECT, INSERT, UPDATE, and DELETE on the wp_ tables, but typically doesn’t need CREATE TABLE or DROP privileges. A practical approach: create one user for your web application with minimal required permissions, a different user for running backups (SELECT only, or with specific backup privileges), and an admin user only for management tasks. Different environments should have different privilege sets—your development database user can have broad permissions, while your production user should be tightly restricted to only what the application legitimately uses.
Security Considerations—Default Users, Password Policies, and Access Control
MySQL installations come with default accounts, particularly the ‘root’ user with no password. This is a critical security issue: immediately after installation, run `mysql -u root -p` and set a strong password. Many production server compromises stem from attackers finding MySQL running with default or no password. Additionally, delete the default ‘test’ database and anonymous users that exist in fresh MySQL installations by running the `mysql_secure_installation` script, which walks through these security basics. The warning here is straightforward: every day that your MySQL server runs with default credentials is a security incident waiting to happen. Password policies are configurable but often overlooked.
The `validate_password` plugin enforces minimum password length and complexity, helping prevent weak credentials. Enable it with `INSTALL PLUGIN validate_password SONAME ‘validate_password.so’`, then set policy levels. A limitation to consider: if you enforce strict password policies, you may need to update connection scripts and documentation to reflect the new requirements. Additionally, never store MySQL user credentials in your application code as plain text or with minimal obfuscation. If you’re running Docker containers or managing multiple environments, use environment variables or secure credential management systems to pass credentials at runtime. When granting permissions, never create a user with credentials that anyone can guess or that are visible in your codebase.
Managing Multiple Users and Environment-Specific Access
Real-world applications typically require different database users for different environments and purposes. Your production environment might have one user for the web application, another for backup scripts, and another for administrative tasks. Development environments can be more permissive. A practical setup: create `wordpress_prod@production_server_ip` with minimal permissions for your production application, `wordpress_dev@localhost` with broader permissions for local development, and separate users for analytics tools or reporting applications that only need SELECT access to specific tables.
The host specification in the username@host format is your primary access control mechanism. `’app_user’@’192.168.1.5’` only allows connections from that specific IP, preventing unauthorized access even if credentials are leaked. If you’re using a managed database service like AWS RDS, similar network-based access controls apply. A specific example: many WordPress hosting providers create database users in the format `account_production` that can only connect from that hosting provider’s web servers, not from the open internet. This architecture, combined with strong passwords, makes credential leakage far less damaging.
The Evolving MySQL Landscape and Database Evolution
MySQL remains widely used, but the database landscape is shifting. PostgreSQL ranked as the #1 most widely used database among professional developers in Stack Overflow’s 2025 Developer Survey, overtaking MySQL for the first time. Approximately 65% of developers surveyed indicated preference for PostgreSQL for new projects in 2025-2026, citing better JSON support, advanced indexing options, and more sophisticated query capabilities. If you’re starting a new project, evaluate whether MySQL or PostgreSQL better fits your needs rather than defaulting to MySQL out of habit.
On the MySQL side, version 9.6.0 was released on December 23, 2025, bringing new features and improvements. More significantly, MySQL 8.0 reaches End of Life in April 2026—if you’re still running MySQL 8.0.42 (the last bug-fix release from April 2025) or earlier, plan your upgrade soon. New installations should target MySQL 9.x or seriously evaluate PostgreSQL as an alternative. The choice between these databases has real implications: MySQL excels in simplicity and is deeply integrated into WordPress and traditional web hosting stacks, while PostgreSQL offers more sophisticated features at the cost of additional complexity. Understanding your application’s specific needs—JSON handling, complex queries, full-text search, geospatial data—helps guide this decision.
Conclusion
Creating MySQL databases, tables, and users is straightforward in syntax but requires careful attention to character sets, security, and permissions. Start with `CREATE DATABASE IF NOT EXISTS` using UTF-8 character sets, define tables with appropriate data types and constraints, create users with restricted host access, and grant only the minimum permissions each user needs. These foundations prevent common security issues, data corruption problems, and future migration headaches.
Your next steps: if you’re setting up a new system, use `mysql_secure_installation` immediately, create application-specific users with limited permissions, and test your backup and restore procedures with the credentials you’ve configured. If you’re inheriting an existing installation, audit current users and permissions, remove default accounts, and plan for MySQL 8.0’s end-of-life in April 2026. Whether you continue with MySQL or evaluate PostgreSQL depends on your specific application needs, but either way, investing time in proper database setup and security practices pays dividends throughout your application’s lifecycle.
Frequently Asked Questions
Can I change a MySQL database’s character set after creation?
Yes, but it requires caution. You can alter the database itself with `ALTER DATABASE dbname CHARACTER SET utf8mb4`, but this only affects new tables. Existing tables retain their original character set and may have corrupted data if you’ve stored utf8mb4 characters in a latin1 table. The safest approach is to migrate data to a new database with the correct character set, verify it, then switch to the new database.
What’s the difference between a MySQL user and a WordPress user?
A MySQL user is a credential that connects to the MySQL server itself—it’s managed at the database level. A WordPress user is an account within WordPress, stored in the wp_users table, which has login credentials and roles within WordPress. They’re separate systems that happen to use the same database.
Should I use the root user for my application?
Never. Always create a dedicated, limited-permission user for your application. If application credentials are compromised, an attacker with a root user can access or delete all databases. With a restricted user, they can only access what that specific user has permission to access.
How often should I rotate MySQL user passwords?
Regular password rotation is security best practice, though the frequency depends on your risk assessment. For production systems, quarterly rotation is reasonable. For applications where you don’t control the password (like shared hosting), check if your provider rotates them. Automated password rotation through tools like HashiCorp Vault is ideal for high-security environments.
Can multiple applications share the same MySQL user?
Technically yes, but it’s poor practice. If one application is compromised, the attacker has access to all applications sharing that user. Create separate users for separate applications, each with permissions only to the databases they need.
What’s the performance impact of strict password policies?
The validate_password plugin has minimal runtime performance impact—it only validates passwords when creating or altering users, not during authentication. The main impact is on your operational procedures, not on database performance.
