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
-- 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
mysql -u [username] -p -h [host] -P [port] [dbname] < [filename].sql
Dump Database to File
mysqldump -u [username] -p -h [host] -P [port] [dbname] > [filename].sql
Dump Database to File with Compression
mysqldump -u [username] -p -h [host] -P [port] [dbname] | gzip > [filename].sql.gz
