Mysql how to dump and restore

MySql dumps

// restore
gunzip < [dump_file].sql.gz | mysql -u[uname] -p[pass] [target_dbname]
mysql -u[uname] -p[pass] [target_dbname] < [dump_file].sql

// dump
mysqldump -u[uname] -p[pass] [dbname] > [dump_file].sql
mysqldump -u[uname] -p[pass] [dbname] | gzip -9 > [bump_file].sql.gz

// dump from remote server to local
ssh -C [user]@[remote_host] "mysqldump --opt --compress -u[uname] -p[pass] [database_name] | gzip -9 -c" > dump.sql.gz

// restore to remote server from local, raw sql
ssh -C [user]@[remote_host] "mysql -u[uname] -p[pass] [database_name]" < dump.sql

// restore to remote server from local, sql.gz
ssh -C [user]@[remote_host] "gunzip | mysql -u[uname] -p[pass] [database_name]" < dump.sql.gz

--opt

This option is shorthand. It is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling a subset of the options affected by --opt.

// Создать базу не заходя в mysql
echo "CREATE DATABASE [dbname];" | mysql -uroot -proot

// Перелить дамп из одной базы в другую
mysqldump -u[uname1] -p[upass1] [db_name1] | mysql -u[uname2] -p[upass2] [db_name2]

Развернуть дамп локально с удаленной базы
ssh -C [user]@[host] "pg_dump -U [pg_user] [database] | gzip -9 -c" | gunzip | psql -U [local_pg_user] [local_database]