mysqldump over SSH

user_4444824 0
Viewed 869

I need to do a mysqldump directly on a remote server with SSH. The main reason is that I do not have enough space on server to do it normally then copy it over with SSH.

Somehow I need to pipe the output of mysqldump command to SSH.

Ideally this would be a one line command.

Thanks

mariadb ssh mysql
2 Answers

You can try:

ssh -t user@server \
    "mysqldump \
        -B database \
        --add-drop-table \
        --ignore-table database.logs" > ~/mydatabase.sql

Notice that in this example, you don't need to login into mysql. And you do not need sudo permissions.

I also add the --add-drop-table and --ignore-table options, since these are pretty common.

You can change > ~/mydatabase.sql into | gzip -9 > ~/mydatabase.sql.gz to compress the file.

user_12932440

You wish to store dumpfile remote system not on the server running mysql. If you execute the MysqlDump command through ssh, the dump file will eventually remain on the server connected to ssh. You can consider to connect to mysql to execute mysqldump command via ssh tunneling.

I am not sure any tools support ssh tunneling on linux but Putty can make ssh tunneling on windows.

https://www.linode.com/docs/databases/mysql/create-an-ssh-tunnel-for-mysql-remote-access/

user_129932450
Related