ankurk91/import-sql.md

## import-sql.md

      
    Raw
  

              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

      
    Raw
  

              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

      
    Raw
  

              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

      
    Raw
  

              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

      
    Raw
  

              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 ubuntu@192.168.1.234

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.