GoAdmio

copytable

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

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


Overview

/go/copytable pushes one table from a database on this server to a remote server. It:

The copy goes one way, from the server you run the command on to the remote server. Only the named table is copied, with its structure and data. Other tables, views, and database users are not.

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


Quick start

# Copy the table shop_db.orders to a remote server, keeping the same database name
/go/copytable shop_db orders 203.0.113.10 'RemoteRootPassword'

Syntax

/go/copytable <database> <table> <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/copytable shop_db orders 203.0.113.10 'RemoteRootPassword' "" 2222

Arguments

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

Use plain names (letters, numbers, and underscores) for the database, table, and target database. 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.orders Database Table 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.... A start line is also printed first and includes your arguments with the password hidden:

[Nov 19 12:00:00]# copytable shop_db orders 203.0.113.10 ******** 
Outcome What you get
Copy attempted The progress line, then exit status 0.
Another instance holds the lock Instance (<name>) already running. and exit status 1.
Table doesn't exist locally Table <database>.<table> doesn't exist. and exit status 1.

[!WARNING] The command does not check whether the dump, upload, or import worked, and SSH errors are hidden. Exit status 0 and a finished progress line do not prove the table arrived. Check the table 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 start line and the log hide 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/copytable shop_db orders 203.0.113.10 'RemoteRootPassword'
# Import into a different database on the remote server
/go/copytable shop_db orders 203.0.113.10 'RemoteRootPassword' shop_copy
# Custom SSH port, same database name (note the empty fifth argument)
/go/copytable shop_db orders 203.0.113.10 'RemoteRootPassword' "" 2222
# Different database name and custom SSH port
/go/copytable shop_db orders 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 'SELECT COUNT(*) FROM shop_copy.orders;'"
# Delete the dump files afterwards (run on both servers)
rm -f /root/shop_db-orders.sql

Troubleshooting

Symptom Likely cause Fix
Instance (<name>) already running. Another command holds the lock file. Wait for it to finish. A lock older than one hour is treated as stale and ignored.
Table <database>.<table> doesn't exist. Wrong database or table name, or the local check couldn't run (database server down, or the root password in GoAdmio's config doesn't work). Check the names with mariadb -e 'SHOW TABLES FROM <database>;'.
The command finishes but the table isn't on the remote server The SSH login, upload, or import failed, and the errors are hidden. Test ssh -p <port> root@<remote-server> by hand. Check the password and that root login is allowed (see permitrootlogin).
The SSH port seems to be ignored The port is the sixth argument, so it was passed in the fifth position and treated as a database name. Use "" for the fifth argument, then the port.
The remote table was replaced The dump drops and recreates the table, so an existing table with the same name is overwritten. Use a different target-database, or back up the remote table first.
.sql files left in /root The command doesn't delete them. Delete them on both servers. See the last example.
The log only shows the last run The log file is overwritten on each run. Keep your own copy if you need the history.
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["copytable ..."] --> B["Print start line, write log"]
    B --> C{"Lock held in the last hour?"}
    C -- "Yes" --> X1["Instance already running, exit 1"]
    C -- "No" --> D["Create lock"]
    D --> E{"Table exists locally?"}
    E -- "No" --> X2["Table doesn't exist, exit 1"]
    E -- "Yes" --> F["Dump to /root/database-table.sql"]
    F --> G["Upload with scp (port 22)"]
    G --> H["Upload with rsync (chosen port)"]
    H --> I["Create target database on remote if missing"]
    I --> J["Import dump on remote"]
    J --> K["Remove lock"]

Lock file

The command writes its name to /usr/local/goadmio/lock/backup.lock and removes the file when it exits, including when you press Ctrl+C. The file is named backup.lock, so a running backup, or any other command that uses the same lock, makes copytable exit instead of wait.

The lock is not refreshed while the copy runs. If one copy takes longer than an hour, another instance can start.

The dump

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

The dump files stay in /root on both servers after the command finishes. They contain the table's data, so delete them when you no longer need them.

The upload

The dump is sent twice:

  1. scp always connects on port 22, because it is not given the port.
  2. rsync connects on the port you pass (or 22).

With the default port both steps use the same connection. With a custom port the scp step fails silently and rsync does the real transfer.

The import

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

Log file

The start line is written to /var/log/go/copytable.log, with the password replaced by ********. The file is overwritten on every run. The target database (argument 5) is not logged.


See also

Command Description
copydb Copy a MySQL/MariaDB database from one server to another
copydatabase Copy a MySQL/MariaDB database from one server to another
mysqlsync Copy MySQL databases from a local to a remote server
table Import a single table from a dump file in MySQL/MariaDB
import Import a SQL dump into a MySQL/MariaDB database
csv Export a MySQL/MariaDB table to a CSV file
permitrootlogin Allow root SSH login from a specific IP
port Change the default SSH port (22) to another port