-
-
Save git-e-001/bac6c303de039febef81b6557666575e to your computer and use it in GitHub Desktop.
Revisions
-
eliyas5044 renamed this gist
Mar 5, 2021 . 1 changed file with 55 additions and 26 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,62 +1,90 @@ # 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 ``` > 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 ``` > 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 @@ -66,3 +94,4 @@ DB_PORT=3307 DB_DATABASE=db_name DB_USERNAME=db_user DB_PASSWORD=db_password ``` -
Mar 5, 2021 . 1 changed file with 3 additions and 3 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -33,15 +33,15 @@ FLUSH PRIVILEGES; // mysqldump database to export as gzip // run and enter db_password mysqldump -u db_user -p db_name | gzip > ~/db.sql.gz // mysqldump database to export as sql // run and enter db_password mysqldump -u db_user -p db_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_user -p db_name | gzip > ~/db.sql.gz // import sql format to MySQL // run and enter db_password -
Aug 25, 2020 . 1 changed file with 17 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -49,4 +49,20 @@ mysql -u db_user -p db_name < ~/db.sql // import gzip format to MySQL // run and enter db_password zcat ~/db.sql.gz | mysql -u db_user -p db_name // create SSH tunnel to connect remote MySQL // run and enter password ssh -fNg -L 3307:127.0.0.1:3306 user_name@ip_address // next connect MySQL in command line // run and enter db_password 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 -
Aug 22, 2020 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,52 @@ // 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 zcat ~/db.sql.gz | mysql -u db_user -p db_name