Let's say that I have database1 and database2.
database1 - contains data
database2 - is empty.
I want to copy all data from database1 to database2 via SSH - duplicate database1.
What command should I use?
I have tried
mysqldump -u user -p database1 > database1.sql
Enter password: mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: NO) when trying to connect
Duplicate a MySQL database over SSH in just one command:
mysqldump -u <local-user> -p <local-db> | gzip | ssh user@hostname \
"gunzip | mysql -u <remote-user> -p<password> <remote-db>"
Note that you must create the remote database first.
More advanced version:
mysqldump -u <local-user> -p <local-db> | xz | pv -W | ssh user@hostname \
"tee remote-dump.sql.xz | unxz | mysql -u <remote-user> -p<password> <remote-db>"
The advanced version:
has better compression using xz/unxz (Though take care that compression speed doesn't become a bottleneck - if xz is at 100% CPU then it has probably become a bottleneck and you might be better off with gzip)
shows a progress indicator using pv
saves a copy of the dump using tee
Only one problem I haven't solved is how to avoid specifying the password in the remote command. It would be really nice to be able to enter this password interactively on the command line – if anyone knows how, please chime in.
$ ssh remotehost mysql -e "'create database dbname'" $ mysqldump dbname | ssh remotehost mysql dbname Note that the silly double quotes are needed, one pair for each host involved. Also note that I have the needed bits in my ~/.my.cnf file to not require putting username/passwd on the command line.This will copy database from S1 to S2
mysqldump --opt <database> | gzip -c | ssh user@wherever 'cat > /tmp/yourfile.sql.gz'
Unzip file
gunzip /tmp/yourfile.sql.gz
Next you'll have to import on S2
mysql -h<host> -u<user> -p<password> < /tmp/yourfile.sql
enjoy
cat is a linux command for display on screen content of a file (try to read manuals: man cat )Use the following 2 comands:
mysqldump -u customers -p customers > customers.dump
mysql -u new_customers -p new_customers < customers.dump
(you may get access denied because you can not write to the directory so you may have to change directory using cd)
First thing you need to do is to ssh into server from which you want to duplicate or copy the database to another server.
The command which worked for me was:
mysqldump -u <database-user> -p<database-password> <database-name> | xz | ssh <target-ssh-username>@<target-hostname> \ "tee remote-dump.sql.xz | unxz | mysql -u <target-database-user> -p<target-database-password> <target-database-name>"
Note:- you can use .cnf file to store username and password of the database. Because my execution was small, I didn't used it.
Link for doing that: https://www.techiecorner.com/1619/how-to-setup-mysqldump-without-password-in-cronjob/
After entering your command. It will prompt you for
**The authenticity of host 'w01b3a99.kasserver.com (85.13.164.141)' can't be established.**
Are you sure you want to continue connecting (yes/no)?
type yes
Then it will prompt you to enter
*target-user@target-hostname* ssh password
Enter the password of target-user password
It will be silent execution running. After some time your command execution will be completed.
I haven't used pv -W because I was using the Cloudways Server. pv wasn't installed already. They don't allow root access to install any package.
mysql duplicate databasegives me everything necessary, e.g. rubyrobot.org/article/duplicate-a-mysql-database