MySQL/Postgresql/Mongo: 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

mongo-db.md

Export

mongodump --uri "mongodb+srv://db-user:[email protected]/db-name?retryWrites=true&w=majority" --out ~/mongo-db

Import

mongorestore --uri "mongodb+srv://db-user:[email protected]/?retryWrites=true&w=majority" ~/mongo-db

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 --column-statistics=0 --set-gtid-purged=OFF --no-tablespaces -v -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'@'%' 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.

reserved comment

I hope this message finds you well. I'm currently facing an issue when trying to import a PostgreSQL database backup, and I'm seeking assistance to resolve it.

The problem I'm encountering is as follows:

The error seems to indicate that I'm performing a data-only restore, and I may be missing schema objects. I would like to restore both data and schema objects, and I'm unsure how to proceed.

Any suggestions, insights, or explanations on how to address this issue would be highly valuable. Thank you in advance for your help!

@rajesh-ivd Please use stack overflow to ask your questions.