GoAdmio

copydb

Copy an existing MySQL/MariaDB database into a new database and user for a hosting account on this server

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


Overview

/go/copydb clones a database on the same server into a new database that belongs to a hosting account. It:

Nothing is sent to another server. To copy a database to a remote server, use copydatabase. To copy a single table to a remote server, use copytable.


Quick start

# Copy shop_db into a new database shop_copy for the account example.com
/go/copydb example.com shop_copy shop_user 'S3cureP4ssw0rd' shop_db

Syntax

/go/copydb <domain> <new-database> <user> <password> <source-database>

All five arguments are positional and all are needed. Only a completely empty argument list is rejected (see Validation); a missing argument is not caught.


Arguments

# Argument Required Notes
1 domain Yes An existing GoAdmio account. The new database is created under it.
2 new-database Yes Name of the database to create. It must not already be registered in GoAdmio.
3 user Yes Database user for the new database. Not root, go, mysql, horde, or roundcube.
4 password Yes Password for the database user. See Security.
5 source-database Yes The existing database to copy from. It isn't checked against the account, so any database on the server can be used.

Use plain names (letters, numbers, and underscores). The command does not validate names itself, and the values are inserted into SQL and shell commands without quoting. Avoid spaces and shell special characters in the password for the same reason. For any naming rules on the new database or user, see mysql.


Output

Messages and bare percentage numbers, each on its own line. The percentages are meant to drive a progress bar. The output of mysql appears first, then:

Exporting Database shop_db...
99
Importing Database shop_db to shop_copy...
<percentage numbers, one per line>
Database import completed successfully...
100

During the export the dump size isn't known in advance, so the command prints 99 when the export ends. The import step reports real percentages.

Outcome What you get
Copy attempted The messages above.
No arguments No output, exit status 1.
Validation failure A message from Validation and exit status 1.

[!WARNING] Database import completed successfully... is printed whether or not the import worked. The command does not check the result of creating the database, the export, or the import. Check the new database yourself (see Examples).

Security

[!WARNING] The database user's password is passed as a command-line argument, so it can be visible in the process list and saved in your shell history. The command also passes the MariaDB root password on the command line to mariadb-dump and mariadb, so it is briefly visible in the process list while the copy runs. The log hides the user password but not the root password's exposure in the process list.


Examples

# Clone a database into a new one for an account
/go/copydb example.com shop_copy shop_user 'S3cureP4ssw0rd' shop_db
# Verify the copy
mariadb -e 'SHOW TABLES FROM shop_copy;'
# Compare row counts for one table in the source and the copy
mariadb -e 'SELECT COUNT(*) FROM shop_db.orders; SELECT COUNT(*) FROM shop_copy.orders;'

Troubleshooting

Symptom Likely cause Fix
No output, exit status 1 No arguments were given. Pass all five arguments.
MYSQL: MySQL Database Username <user> already exists. The user is one of the reserved names: root, go, mysql, horde, roundcube. The wording says "already exists", but it is a reserved-name check. Choose another user name.
Account Domain <domain> not exists The domain isn't a hosted account on this server. Check the domain, or create the account with create.
Database '<name>' already exists. The name is already registered in GoAdmio. Choose another name for the new database.
"completed successfully" but the new database is empty The source database name is wrong, or an argument is missing. The dump fails, but the command carries on. Drop the empty database, check the source name with mariadb -e 'SHOW DATABASES;', and run again.
Import errors, or the new database doesn't exist Creating the database with mysql failed (for example an invalid name or user), and the command doesn't check. Create the database by hand or fix the name, then run again.
A password with spaces or special characters fails The password isn't quoted when passed on internally. Use a password without spaces or shell special characters.
pv: command not found pv isn't installed. Install pv.
The copy differs from the source The source was being written to during the dump. The dump doesn't use a consistent snapshot. Run it when the source is idle.
Other databases of the account stopped accepting their old password The final step may change the default MySQL password for all the account's databases (see Final step). Confirm what phpmyadmin does with an account argument before using this on a live account.
A .sql file is left in /usr/local/goadmio/tmp The command was interrupted before cleanup. Delete it. It contains the full database.

Details

Requirements

Validation

Check Failure
At least one argument Silent exit with status 1.
user is not root, go, mysql, horde, or roundcube MYSQL: MySQL Database Username <user> already exists. and exit 1.
domain exists in GoAdmio's site list Account Domain <domain> not exists and exit 1.
new-database isn't registered in GoAdmio's database list Database '<name>' already exists. and exit 1.

The last check uses GoAdmio's own list of databases, not MariaDB itself, so a database that exists in MariaDB but isn't registered in GoAdmio isn't caught. Missing arguments, a nonexistent source, and an existing database user are not checked.

How it works

flowchart TD
    A["copydb ..."] --> B["Write log line"]
    B --> C{"Arguments, user, domain, name OK?"}
    C -- "No" --> X["Message, exit 1"]
    C -- "Yes" --> D["Create database and user with mysql"]
    D --> E["Dump source to tmp/new-database.sql"]
    E --> F["Import into the new database"]
    F --> G["Print completed, delete tmp file"]
    G --> H["Run phpmyadmin for the account"]

The dump

The source is dumped with mariadb-dump --skip-lock-tables into /usr/local/goadmio/tmp/<new-database>.sql. With the tool's default options, the dump contains table structure, data, and triggers, but not users, grants, stored routines, or events. --skip-lock-tables means the source isn't locked, but no single-transaction option is used, so the copy is not a consistent snapshot if the source changes during the dump.

The dump has no CREATE DATABASE statement, so it imports into whichever database you name, which is how the copy ends up under the new name.

The temporary file is deleted after the import. It is named after the new database.

Final step

The last line runs /go/phpmyadmin <domain>. The script's own comment describes it as changing the default MySQL password for all databases of the domain. This page doesn't describe that behavior independently. See phpmyadmin and test on a non-production account first.

The exit status of the command is the exit status of that last step. Earlier failures don't change it.

Log file

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

[Nov 19 12:00:00]# copydb example.com shop_copy shop_user ******** shop_db

See also

Command Description
mysql Create a MySQL database
copydatabase Copy a MySQL/MariaDB database from this server to a remote server over SSH, optionally under a different name
copytable Copy a MySQL/MariaDB table from this server to a remote server over SSH, optionally into a different database name
sql Create a MySQL database and import an SQL file into it
import Import a SQL dump into a MySQL/MariaDB database
phpmyadmin Install the latest phpMyAdmin on a GoAdmio server
dbls List all databases and their sizes