MySQL/Postgresql: Import database via command line

import-sql.md

Import large database to MySql ⚡

cd /path/to/backups
mysql -u root -h 127.0.0.1 -p --default-character-set=utf8
# Switch to database 

USE database_name;
SET names 'utf8';
SET autocommit=0;
SOURCE backup.sql;
COMMIT;

• Note: database_name should exists
• Source

mysql-import-export-all.md

Import/Export all databases in one command

Export

mysqldump -u root -h 127.0.0.1 -p --all-databases > all_db.sql

Import

mysql -u root -h 127.0.0.1 -p < all_db.sql

• Source

Import/Export single database

mysqldump --set-gtid-purged=OFF --no-tablespaces -h 127.0.0.1 -u root -p database_name_here > backup_file.sql

mysql -h 127.0.0.1 -u root -p database_name_here < backup_file.sql

mysql-user.md

Create new user for specific database

CREATE DATABASE my_database;
CREATE USER 'my_user'@'%' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'%' IDENTIFIED BY 'my_password' WITH GRANT OPTION;

Links

• https://stackoverflow.com/questions/1659848/mysql-create-user-with-rights-only-for-specific-db
• https://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address

postgresql-docker.md

Import export postgresql database running inside docker

Export

# get the container id
docker container ls

# Specify the container id in next command, for example 95bf1f75af9c
docker exec 95bf1f75af9c pg_dump -U postgres database_name > backup.sql

Import

docker exec -i 95bf1f75af9c psql -U postgres database_name < /full/path/to/backup.sql

Extras

• rSync from remote to local

postgresql-rds.md

Import database dump into PostgreSQL AWS RDS

Assumptions

• You have postgres installed on your local machine
• You have a database dump *.sql file created with psql command
• You can SSH to EC2 instance
• You have an EC2 instance running and EC2 machine has access to RDS instance

Steps

• Create a tunnel to EC2

# Syntax
ssh -L <local free port>:<RDS instance host>:<RDS port> <ec2-user>@<ec2-ip-address>

# Example command
ssh -L 5433:db-name.c13mzxjj9hpa.us-east-1.rds.amazonaws.com:5432 [email protected]

• Import the database to RDS (in new terminal window)

psql -U postgres -p 5433 -h 127.0.0.1 -d db_name < ./path/to/db_backup_file.sql

• Close both terminal window when done.