Create a MySQL database with the right character set and collation, add an application user with limited grants, verify the result and fix the usual errors.
Connect as an account that has the CREATE privilege (for example root) and run:
CREATE DATABASE shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
USE shop;
That is a complete, working answer. The rest of this page covers the parts that cause trouble later: which character set and collation to pick, creating an application user instead of connecting as root, checking the result, and undoing it.
CREATE DATABASE syntax
CREATE DATABASE [IF NOTEXISTS] db_name
[CHARACTER SET charset_name]
[COLLATE collation_name];
CREATE SCHEMA is an exact synonym. In MySQL a schema and a database are the same thing.
IF NOT EXISTS turns the "already exists" error into a warning, which is what you want in setup scripts that may run twice.
A name can be up to 64 characters. Stick to lowercase letters, digits and underscores. A name with a hyphen or a reserved word works only if you quote it in backticks everywhere, for example `my-db`.
Without IF NOT EXISTS, creating a database that exists fails:
Each database is a directory under the server's data directory, so name matching follows the file system. On Linux, shop, Shop and SHOP are three different databases by default; on Windows and macOS they are the same one. Code written on a Windows laptop that says USE Shop then fails on a Linux server with ERROR 1049 (42000): Unknown database 'Shop'. Using lowercase names everywhere avoids the problem. The setting that controls this, lower_case_table_names, can only be chosen when the data directory is first initialised.
The error points at a query that returned arbitrary values. Fix it with aggregates, primary-key grouping, ANY_VALUE() or window functions, and keep the SQL mode on.
The character set decides which characters can be stored. The collation decides how strings compare and sort. Set at database level, they are defaults for tables created afterwards; every table and column can still override them.
Use utf8mb4. It is full UTF-8, including emoji and all CJK characters, and it has been the server default since MySQL 8.0. Avoid utf8: in MySQL that name is an alias for utf8mb3, a deprecated three-byte encoding that cannot store four-byte characters.
For the collation, the common choices are:
Collation
Behaviour
Use it when
utf8mb4_0900_ai_ci
Accent-insensitive, case-insensitive. 'resume' = 'Résumé' is true. The default for utf8mb4.
General application data
utf8mb4_0900_as_cs
Accent-sensitive, case-sensitive, with language-aware ordering
Identifiers or codes where case matters but you still want sensible sorting
utf8mb4_bin
Compares by code point
Tokens, hashes, exact-match keys
utf8mb4_unicode_ci, utf8mb4_general_ci
Older, case-insensitive collations
The data must also load into MySQL 5.7 or MariaDB
The last row matters for portability. The 0900 collations were introduced in MySQL 8.0. Importing a dump that uses them into a server that lacks them fails with ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'.
To see what your server offers and what it would use by default:
Collation is hard to change once tables hold data, because unique indexes depend on it: two values that were different under a case-sensitive collation can become duplicates under a case-insensitive one. Decide before you create tables.
Verifying the database
SHOW DATABASES LIKE'shop';
SHOWCREATE DATABASE shop\G
The same information is available as a query, which is easier to use from scripts:
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name ='shop';
SHOW DATABASES lists only the databases your account has some privilege on, so a database can exist and still not appear for a restricted user.
USE shop; makes it the default database for the session. Without a default database, unqualified table names fail with ERROR 1046 (3D000): No database selected. You can also select it when connecting (mysql -u root -p shop) or qualify names as shop.orders.
Creating a user and granting access
Applications should not connect as root. Create an account that can only touch this database:
If a migration tool needs to create and alter tables, give that job its own account with wider rights on the same database only:
CREATEUSER'shop_migrate'@'localhost'
IDENTIFIED BY'replace-with-another-long-random-password';
GRANTALL PRIVILEGES ON shop.*TO'shop_migrate'@'localhost';
Points that trip people up:
An account is a user name plus a host.'shop_app'@'localhost' and 'shop_app'@'%' are two separate accounts with separate passwords and grants. localhost allows connections from the server machine itself. For an application on another host, use that host's address or a pattern such as 'shop_app'@'10.0.1.%'. '%' means any host; use it only when the network is restricted some other way.
shop.* means all tables in shop, present and future.*.* is global and is rarely what an application account should have.
_ and % in the database name of a GRANT are wildcards. A grant on `shop_eu`.* also matches shopXeu. Escape the underscore (`shop\_eu`.*) for names that contain one.
FLUSH PRIVILEGES is not needed after CREATE USER or GRANT. Those statements take effect immediately. It is only required if you edit the grant tables in the mysql system database directly, which you should not do.
You need the right privileges yourself:CREATE on the database to create it, CREATE USER to add accounts, and you can only grant privileges you hold with GRANT OPTION.
Verifying the grants
SHOW GRANTS FOR'shop_app'@'localhost';
GRANT USAGE ON *.* TO `shop_app`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost`
USAGE means "no global privileges"; every account has that line. Then test a real login from a shell:
mysql -u shop_app -p shop -e "SELECT DATABASE(), CURRENT_USER();"
CURRENT_USER() shows which account row the server matched, which is the quickest way to debug "I granted it but it still says access denied".
Troubleshooting
Error
Cause and fix
ERROR 1044 (42000): Access denied for user 'x'@'localhost' to database 'shop'
The account is authenticated but has no privilege on that database. Grant it, or connect as an account that has CREATE.
ERROR 1045 (28000): Access denied for user 'shop_app'@'10.0.1.7' (using password: YES)
Wrong password, or no account exists for that user and host combination. Check the host part.
ERROR 1049 (42000): Unknown database 'Shop'
Misspelt name, or a case mismatch on a case-sensitive server.
ERROR 1396 (HY000): Operation CREATE USER failed for 'shop_app'@'localhost'
The account already exists. Use CREATE USER IF NOT EXISTS or ALTER USER.
Authentication plugin 'caching_sha2_password' cannot be loaded (client side)
An old client library. Since MySQL 8.0 accounts use caching_sha2_password by default. Upgrade the driver. The older mysql_native_password plugin is disabled by default in MySQL 8.4 and removed in 9.0, so switching the account back to it is no longer a long-term option.
Changing or removing a database
Change the defaults for tables created from now on:
ALTER DATABASE shop COLLATE utf8mb4_0900_as_cs;
This does not convert existing tables. Each one needs ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;, which rewrites the table.
There is no RENAME DATABASE statement. To rename, create the new database and move each table with RENAME TABLE shop.orders TO shop2.orders;, or dump and reload.
To remove everything:
DROP DATABASE IF EXISTS shop;
DROPUSER IF EXISTS'shop_app'@'localhost', 'shop_migrate'@'localhost';
DROP DATABASE deletes every table in the database, cannot be rolled back and does not ask for confirmation. Take a backup first (mysqldump --databases shop > shop.sql). Dropping the database does not remove grants that refer to it; if you later create another database with the same name, the old accounts regain access. Drop or revoke them explicitly, as above.
Version notes
Everything here applies to the supported MySQL release lines (8.4 LTS and the 9.x series). MySQL 5.7 and 8.0 are end of life; on 5.7 the default character set was latin1 and the 0900 collations do not exist, which is one more reason to state the character set explicitly in scripts.
Page 1 takes 5 ms and page 5,000 takes 4 seconds. OFFSET reads and throws away every skipped row. Switch to keyset (cursor) pagination for constant-time pages.
MySQL's utf8 isn't real UTF-8. Here's why emoji break it, how to convert tables and connections to utf8mb4, and how to get past the 767-byte index limit.
