GoAdmio

copydatabase

Copy a MySQL/MariaDB database from this server to a remote server over SSH, optionally under a different name

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · See also


Overview

/go/copydatabase pushes one whole database from this server to a remote server. It:

The copy goes one way, from the server you run the command on to the remote server. To copy a single table instead, use copytable.

The remote server must allow SSH login as root with a password. See Requirements.


Quick start

# Copy the database shop_db to a remote server, keeping the same name
/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword'

Syntax

/go/copydatabase <database> <remote-server> <remote-password> [target-database] [ssh-port]

Arguments are positional. To set the SSH port you must also fill the target-database position. Pass an empty string ("") to keep the same database name.

/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword' "" 2222

Arguments

# Argument Required Default Notes
1 database Yes — The local database to copy.
2 remote-server Yes — IP address or hostname of the remote server.
3 remote-password Yes — The remote server's root SSH password. See Security.
4 target-database No Same as database Database to import into on the remote server. It is created if it doesn't exist.
5 ssh-port No 22 SSH port of the remote server.

The command does not validate its arguments. Use plain names (letters, numbers, and underscores) for the databases, because they are inserted into shell and SQL commands without quoting.


Output

The command prints a single progress line that updates in place:

Copying shop_db Database to 203.0.113.10 | Importing...

When you use a target database, the last step reads Importing to <target-database>....

The stages shown are Dumping..., Copying..., and Importing.... Unlike some other commands, the start line with your arguments is written to the log only and isn't printed.

Outcome What you get
Copy attempted The progress line, then exit status 0.
Any failure after the start Usually an error message from the failing tool, and still exit status 0.

[!WARNING] The command does not check whether the dump, upload, or import worked. Exit status 0 and a finished progress line do not prove the database arrived. A wrong database name is not caught: the dump fails, but the command carries on and creates an empty database on the remote server. Check the result on the remote server (see Examples).

Security

[!WARNING] The remote password is passed as a command-line argument and used with sshpass -p, so it can be visible in the process list and saved in your shell history. The log hides it, but nothing else does. SSH host key checking is also turned off, so the remote server's identity is not verified.


Examples

# Same database name on the remote server
/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword'
# Import into a different database name on the remote server
/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword' shop_copy
# Custom SSH port, same database name (note the empty fourth argument)
/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword' "" 2222
# Different database name and custom SSH port
/go/copydatabase shop_db 203.0.113.10 'RemoteRootPassword' shop_copy 2222
# Verify the result on the remote server
ssh -p 22 root@203.0.113.10 "mariadb -e 'SHOW TABLES FROM shop_copy;'"
# Delete the dump file afterwards (run on both servers)
rm -f /root/shop_db.sql

Troubleshooting

Symptom Likely cause Fix
An empty database appears on the remote server The local database name is wrong or the dump failed, but the command continued. Check the name with mariadb -e 'SHOW DATABASES;', drop the empty remote database, and run again.
The command finishes but nothing is on the remote server The SSH login, upload, or import failed. Test ssh -p <port> root@<remote-server> by hand. Check the password and that root login is allowed (see permitrootlogin).
sudo: command not found or an upload error mentioning sudo The upload runs sudo rsync on the remote server, and sudo isn't installed there. Install sudo on the remote server.
The SSH port seems to be ignored The port is the fifth argument, so it was passed in the fourth position and treated as a database name. Use "" for the fourth argument, then the port.
Tables on the remote database were replaced The dump drops and recreates each table, so existing tables with the same names are overwritten. Use a different target-database, or back up the remote database first.
Extra tables are still in the remote database Tables that aren't in the dump are not removed. Drop them by hand if you want an exact copy.
Stored procedures or events are missing on the remote server The dump doesn't include them with default options. Copy them separately.
A .sql file is left in /root The command doesn't delete it. Delete it on both servers. See the last example.
Strange characters when the output is redirected to a file The progress line uses backspaces to overwrite itself. Run the command in a terminal, or filter the control characters.

Details

Requirements

How it works

flowchart TD
    A["copydatabase ..."] --> B["Write log line"]
    B --> C["Dump to /root/database.sql"]
    C --> D["Upload with rsync (chosen port)"]
    D --> E["Create target database on remote if missing"]
    E --> F["Import dump on remote"]

There is no lock and no pre-check. The command starts immediately and does not stop on errors.

The dump

The database is dumped with mariadb-dump <database> into /root/<database>.sql. With the tool's default options, the dump:

The dump doesn't contain a CREATE DATABASE statement, which is why the command creates the target database separately.

The dump file stays in /root on both servers after the command finishes. It contains all the database's data, so delete it when you no longer need it. It is overwritten on the next run for the same database.

The upload

The dump is sent once, with rsync over SSH on the port you pass (or 22). The remote side runs sudo rsync.

The import

On the remote server, the command runs CREATE DATABASE IF NOT EXISTS for the target database (or for the original name if you gave no target), then imports the dump into it.

Log file

Each call appends a line to /var/log/go/copydatabase.log, with the password replaced by ********:

[Nov 19 12:00:00]# copydatabase shop_db 203.0.113.10 ******** shop_copy

The target database (argument 4) is logged. The SSH port (argument 5) is not.


See also

Command Description
copytable Copy a MySQL/MariaDB table from one server to another
copydb Copy a MySQL/MariaDB database from one server to another
mysqlsync Copy MySQL databases from a local to a remote server
import Import a SQL dump into a MySQL/MariaDB database
mysqlbackup Back up MariaDB/MySQL to a remote server via rsync
permitrootlogin Allow root SSH login from a specific IP
port Change the default SSH port (22) to another port