MySQL Commands

// login to mysql
// then enter db_password
mysql -u db_user -p

// create database
CREATE DATABASE db_name;

// drop database
DROP DATABASE db_name;

// drop table
DROP TABLE IF EXISTS db_name.table_name;

// drop all tables
echo "db_name"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

// MySQL 5.7
// create user
CREATE USER 'db_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'db_password';

// grant permission
GRANT ALL ON db_name.* TO 'db_user'@'localhost' IDENTIFIED BY 'db_password' WITH GRANT OPTION;

// MySQL 8
// create user
CREATE USER 'db_user'@'%' IDENTIFIED WITH mysql_native_password BY 'db_password';

// grant permission
GRANT ALL ON db_name.* TO 'db_user'@'%';

// reload grant
FLUSH PRIVILEGES;

// mysqldump database to export as gzip
// run and enter db_password
mysqldump -u db_name -p user_name | gzip > ~/db.sql.gz

// mysqldump database to export as sql
// run and enter db_password
mysqldump -u db_name -p user_name > ~/db.sql

// mysqldump database to export as gzip from remote database
// run and enter db_password
mysqldump -P 3306 -h ip_address -u db_name -p user_name | gzip > ~/db.sql.gz

// import sql format to MySQL
// run and enter db_password
mysql -u db_user -p db_name < ~/db.sql

// import gzip format to MySQL
// run and enter db_password
// no need to enter root folder
zcat ~/db.sql.gz | mysql -u db_user -p db_name