MySQL Commands for Laravel

MySQL.md

MySQL CLI

To login into mysql, 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 []' {}"

Create user in MySQL 5.7

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;

Create user in MySQL 8

CREATE USER 'db_user'@'%' IDENTIFIED WITH mysql_native_password BY 'db_password';

Grant permission

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

Reset root password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Reload grant

FLUSH PRIVILEGES;

mysqldump database to export as gzip

mysqldump -u db_user -p db_name | gzip > ~/db.sql.gz

mysqldump database to export as sql

mysqldump -u db_user -p db_name > ~/db.sql

mysqldump database to export as gzip from remote database

mysqldump -P 3306 -h ip_address -u db_user -p db_name | gzip > ~/db.sql.gz

mysqldump database to export as gzip from Docker MySQL server

docker exec -i mysql_container_id mysqldump -u db_user -p"db_password" db_name | gzip > ~/db.sql.gz

Import sql format to MySQL

mysql -u db_user -p db_name < ~/db.sql

Import gzip format to MySQL

zcat ~/db.sql.gz | mysql -u db_user -p db_name

Import gzip format to Docker MySQL server

gunzip ~/db.sql.gz | docker exec -i mysql_container_id mysql -u db_user -p"db_password" db_name

Import gzip format to Remote MySQL server

pv ~/db.sql.gz | gunzip | mysql -u db_user -p"db_password" -h db_host -P db_port db_name

Create SSH tunnel to connect remote MySQL

ssh -fNg -L 3307:127.0.0.1:3306 user_name@ip_address

// next connect MySQL in command line
mysql -h 127.0.0.1 -P 3307 -u db_user -p db_name

// for Laravel, change .env file
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3307
DB_DATABASE=db_name
DB_USERNAME=db_user
DB_PASSWORD=db_password

.