GoAdmio

mysqlsync

Replace the MariaDB data of another GoAdmio server with a file-level copy of this server's, over SSH; MariaDB is stopped on both servers during the copy

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


Overview

/go/mysqlsync copies every MariaDB database, user, and grant from this server to another GoAdmio server. It does not export and import SQL. It stops MariaDB and copies the whole data directory, /var/lib/mysql, with rsync.

Run it on the source server. It:

[!WARNING] MariaDB is stopped on this server for the whole copy, so every site that uses a database is down until it finishes. If you interrupt the command, MariaDB stays stopped. Start it again with systemctl start mariadb.

[!WARNING] The remote server's own databases and users are replaced. Databases that exist only on the remote server are not removed from disk, but they become unusable, because the system tables and the InnoDB files come from this server. Use this command only on a new or empty target.


Quick start

# Copy MariaDB to a new server, using its root password
/go/mysqlsync 203.0.113.20 'RemoteRootPass'

Syntax

/go/mysqlsync <remote-server> <remote-password> [ssh-port]

Run the command as root.


Arguments

Position Name Required Default Notes
1 <remote-server> Yes — Hostname or IP address of the target server.
2 <remote-password> Yes — The SSH password of the target server's root user.
3 [ssh-port] No 22 SSH port of the target server.

The target must allow root login over SSH with a password (see permitrootlogin). The command does not check its arguments.


Output

The command prints one line, then the rsync progress. SSH messages are hidden, so connection and login errors do not appear.

Copying MariaDB/MySQL to 203.0.113.20...

When it finishes it writes 1 to /usr/local/goadmio/tmp/serversync/MariaDB, a flag file that records that the MariaDB copy ran. It is written even if a step failed. Every call is also appended to /var/log/go/mysqlsync.log. The exit code is 0 in nearly every case, so do not use it to detect failures.

Security

[!WARNING] The command log records the remote root password in plain text, because it saves all arguments. The password is also visible in the process list while the command runs, and this server's MariaDB root password is visible in the process list of the remote server for a moment. SSH host keys are not checked (StrictHostKeyChecking=no), so run it only between servers on a trusted network.


Examples

# Default SSH port
/go/mysqlsync 203.0.113.20 'RemoteRootPass'
# Custom SSH port
/go/mysqlsync 203.0.113.20 'RemoteRootPass' 2222
# Afterwards, on the remote server, check the databases
mariadb -e "SHOW DATABASES;"
# If the copy was interrupted, bring MariaDB back on this server
systemctl start mariadb

Troubleshooting

Symptom Likely cause Fix
The command finishes quickly with no copy, and no error The SSH login failed (wrong password, port, or root login disabled), and SSH errors are hidden. Test with ssh -p <port> root@<remote-server>, then run again.
Sites on this server report database errors MariaDB is stopped during the copy, or the copy was interrupted. Wait for the copy, or run systemctl start mariadb.
MariaDB does not start on the remote server The two servers run different MariaDB versions, or the remote configuration does not suit the copied data. Install the same version on both with installmariadb, then check journalctl -u mariadb.
Databases that were on the remote server are broken or missing The whole data directory was replaced. This is expected. Restore them from a backup.
The remote GoAdmio cannot connect to MariaDB The remote config.php does not hold the local root password, because the first step could not run. On the remote server, run mysqlreset with this server's root password.
sshpass: command not found or rsync: command not found A tool is missing. Install sshpass and rsync on this server, and rsync on the remote server.
The copy is very slow The data directory is large. Wait. Progress is shown. Running it again resumes and only sends changes, but MariaDB is stopped again each time.

Details

What changes on the remote server

What Change
MariaDB root password Set to this server's MariaDB root password, with mysqlreset. The remote config.php and /root/.my.cnf are updated to match.
/var/lib/mysql Filled with this server's data directory. Files that exist only on the remote server are kept, because the copy does not delete anything.
Ownership /var/lib/mysql is set to the mysql user and group.
Configuration files Not copied. The remote keeps its own MariaDB configuration. See my.

Requirements

Why the sites are down

Copying the data files of a running database gives an inconsistent copy, so the command stops MariaDB first. That is also why it is a quick way to move everything: no dump or import is needed. To copy a single database or table without moving everything, use copydatabase or copytable.

What it does

flowchart TD
    A["mysqlsync remote password [port]"] --> B["Read local root password"]
    B --> C["Remote: run mysqlreset with the local password"]
    C --> D["Remote: stop MariaDB"]
    D --> E["Local: stop MariaDB"]
    E --> F["rsync /var/lib/mysql to the remote server"]
    F --> G["Local: start MariaDB"]
    G --> H["Remote: chown, restart MariaDB"]
    H --> I["Write flag file"]
  1. Appends the command line, including the remote password, to /var/log/go/mysqlsync.log.
  2. Reads this server's MariaDB root password from config.php.
  3. Uses port 22 if no port was given, and creates /usr/local/goadmio/tmp/serversync.
  4. Prints Copying MariaDB/MySQL to <remote-server>....
  5. On the remote server, runs /go/mysqlreset <local root password>.
  6. On the remote server, stops MariaDB.
  7. Stops MariaDB on this server.
  8. Copies /var/lib/mysql/ to the remote server with rsync -azlh -P.
  9. Starts MariaDB on this server.
  10. On the remote server, sets mysql:mysql ownership on /var/lib/mysql and restarts MariaDB.
  11. Writes 1 to /usr/local/goadmio/tmp/serversync/MariaDB.

See also

Command Description
serversync Copy a whole GoAdmio server to another server over SSH
copydatabase Copy a MySQL/MariaDB database from this server to a remote server over SSH
copytable Copy a MySQL/MariaDB table from this server to a remote server over SSH
mysqlreset Change the MySQL root password in safe mode
mysqlbackup Back up MariaDB/MySQL to a remote server via rsync
postgresync Copy PostgreSQL data to a remote server
installmariadb Install or upgrade MariaDB from the official repositories