MySQL Schema Initialization Note

Initiate MySQL Database

Setting up a MySQL database for a new project, you basically have three things to do:

• Create a database
• Create a standalone database user for the database for security purposes
• Grant privileges of the database to the new user

SQL
Wrap|Copy
-- Create Database
CREATE DATABASE IF NOT EXISTS `<database_name>` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- Create User
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
-- Grant Privileges
GRANT ALL PRIVILEGES ON `<database_name>`.* TO '<username>'@'%';

Restore Database from Dump File

bash
Wrap|Copy
mysql -u [username] -p -h [host] -P [port] [dbname] < [filename].sql

Dump Database to File

bash
Wrap|Copy
mysqldump -u [username] -p -h [host] -P [port] [dbname] > [filename].sql

Dump Database to File with Compression

bash
Wrap|Copy
mysqldump -u [username] -p -h [host] -P [port] [dbname] | gzip > [filename].sql.gz