Create A Bash Function To Copy MySQL Or PostgreSQL Database To Another Database Locally And Remotely

Posted By Weston Ganger

I have multiple development machines and I usually have one database that has way more data in it. Heres how to copy it to another database locally and remotely.

Firstly the database must be created or exist on the destination server. Then run one of these commands from the destination computer.

For MySQL:


# Locally without a root database user password
mysqldump -u root my_database | mysql -u root my_new_database

# Locally with a root database user password
mysqldump -u root -pmypassword my_database | mysql -u root -pmypassword my_new_database

# Remotely without a root database user password
ssh myuser@192.168.1.7 -C -o CompressionLevel=9 mysqldump -u root my_database > my_database.sql && mysql -u root my_database < my_database.sql && rm my_database.sql

# Remotely without a root database user password
ssh myuser@192.168.1.7 -C -o CompressionLevel=9 mysqldump -u root my_database > my_database.sql && mysql -u root my_database < my_database.sql && rm my_database.sql

# From a rails app folder without an existing database
rake db:create && ssh myuser@192.168.1.7 -C -o CompressionLevel=9 mysqldump -u root -pmypassword my_database > my_database.sql && mysql -u root -pmypassword my_database < my_database.sql && rm my_database.sql

For PostgreSQL:


# Locally without a root database user password
pg_dump -U your_user my_database | psql -U your_user my_new_database

# Locally with a root database user password
pg_dump -U your_user -pmypassword my_database | psql -U your_user -pmypassword my_new_database

# Remotely without a root database user password
ssh myuser@192.168.1.7 -C -o CompressionLevel=9 pg_dump -U your_user my_database > my_database.sql && psql -U your_user my_database < my_database.sql && rm my_database.sql

# Remotely without a root database user password
ssh myuser@192.168.1.7 -C -o CompressionLevel=9 pg_dump -U your_user my_database > my_database.sql && psql -U your_user my_database < my_database.sql && rm my_database.sql

# From a rails app folder without an existing database
rake db:create && ssh myuser@192.168.1.7 -C -o CompressionLevel=9 pg_dump -U your_user -pmypassword my_database > my_database.sql && psql -U your_user -pmypassword my_database < my_database.sql && rm my_database.sql

This is much easier if you set it up as a bash function like so:


# ~/.bash_aliases
update-mysql-db(){
  rake db:create && ssh $2 -C -o CompressionLevel=9 mysqldump -u root $1 > $1.sql && mysql -u root $1 < $1.sql && rm $1.sql
}

update-postgresql-db(){
  rake db:create && ssh $2 -C -o CompressionLevel=9 pg_dump -U root $1 > $1.sql && psql -U root $1 < $1.sql && rm $1.sql
}

I would use this function like this on the destination computer:


update-mysql-db my_database myuser@192.168.1.7

update-postgresql-db my_database myuser@192.168.1.7

Article Topic:Software Development - Linux

Date:August 16, 2016