Graceful MySQLD control — move_sql.sh

move_sql.sh is BOA's graceful MySQLD orchestrator. Despite the name it does not move databases between hosts and has no per-site logic. It cleanly stops, starts, or restarts the local Percona/MySQL server, preparing InnoDB for a quick, dirty-page-free shutdown first.

It exists so BOA's own InnoDB log-resize and Percona upgrade/restart paths can bring the DB server down and back up without losing time on a slow shutdown or a stale socket. Operators rarely run it by hand.

Whole-host or per-Octopus database migration is not provided by this script. For whole-host migration use xmass; for per-Octopus migration use xoct. See Migration & cloning.

The script lives at /var/xdrago/move_sql.sh (188 lines).

Invocation

Its only interface is a single positional verb:

/var/xdrago/move_sql.sh restart   # stop then start (also the default)
/var/xdrago/move_sql.sh stop      # graceful shutdown
/var/xdrago/move_sql.sh start     # start + wait for the socket
/var/xdrago/move_sql.sh           # no argument -> restart

There are no flags — no --site, no --target-db-host, no --help. Any unrecognised argument falls through to restart. On a proxy host (/root/.proxy.cnf present) the script exits immediately and does nothing.

What stop does

stop brings the whole web/PHP/DB stack down so MySQLD can shut down cleanly:

1. Create lock pidfiles under /run (see "Locks" below).
2. Stop Nginx, wait for it to exit, then killall nginx.
3. Stop every PHP-FPM instance (PHP 8.5 … 5.3), rotating each
   php<NN>-fpm-error.log into /var/backups/php-logs/<timestamp>/,
   then pkill -9 any survivors.
4. Prepare InnoDB for a fast, clean shutdown:
     SET GLOBAL innodb_max_dirty_pages_pct           = 0;
     SET GLOBAL innodb_change_buffering              = 'none';
     SET GLOBAL innodb_buffer_pool_dump_at_shutdown  = 1;
     SET GLOBAL innodb_io_capacity                   = 3000;
     SET GLOBAL innodb_io_capacity_max               = 6000;
   On Percona 5.7 only, it also forces an immediate buffer-pool dump
   (innodb_buffer_pool_dump_pct = 100; innodb_buffer_pool_dump_now = ON;).
5. SET GLOBAL innodb_fast_shutdown = 1;  then `service mysql stop`.
6. Remove the stale /run/mysqld/mysql* sockets.
7. Wait until no mysqld process remains, then remove the locks.

Stopping Nginx and all PHP-FPM first is deliberate: it removes the clients that would keep writing to the database, so the dirty-page flush converges quickly and the shutdown stays fast. The InnoDB statements use the root client implicitly via /root/.my.cnf — no password file is read.

What start does

start is the inverse, kept minimal:

1. Create lock pidfiles under /run.
2. If mysqld is already running, do nothing and exit.
3. Remove any stale /run/mysqld/mysql* sockets.
4. `service mysql start`.
5. Loop until both a mysqld process and /run/mysqld/mysqld.sock
   exist ("Waiting for MySQLD graceful start…").
6. Remove the locks.

It does not start Nginx or PHP-FPM back up — the caller (or BOA's normal monitor cycle) brings the web tier back. start is purely about the DB server and its socket.

What restart does

restart (the default) chains the two: _stop_sql then _start_sql, sharing one set of locks, then exits. This is the form BOA uses when it just needs the DB server bounced after a config change.

Locks

While running, the script creates four marker pidfiles under /run, which other BOA components honour as "the DB is being restarted, hold off":

  • /run/boa_wait.pid
  • /run/fmp_wait.pid
  • /run/restarting_fmp_wait.pid
  • /run/mysql_restart_running.pid

If mysql_restart_running.pid already exists when the script starts, it assumes another restart is in progress, prints "MySQLD restart procedure in progress?" and exits without touching anything — so two restarts never race. All four markers are removed on completion. Each lock cycle also runs a sync + drop_caches to free memory.

Where BOA calls it

move_sql.sh is plumbing: several BOA components call it to bounce the DB server cleanly, but the surrounding logic — not move_sql.sh — owns whatever they are doing. The stop/start verbs bracket on-disk surgery; the bare restart is used wherever a plain clean bounce is enough.

  • InnoDB log resize (lib/functions/sql.sh.inc, _innodb_log_file_size_update): calls move_sql.sh stop, swaps the ib_logfile* / redo-log files and edits innodb_log_file_size / innodb_redo_log_capacity in /etc/mysql/my.cnf, then move_sql.sh start.
  • Post-my.cnf restart: a bare move_sql.sh (default restart) bounces the server after a my.cnf update on the Percona setup/upgrade path.
  • Self-healing monitors (monitor/check/mysql.sh, monitor/check/system.sh, mysql_backup.sh): a bare restart when the stack finds MySQLD wedged, busy, or down (the high-load _sql_restart path).
  • Whole-host migration (xmass): the migration tool runs move_sql.sh restart locally and stop/start over SSH on the target to checkpoint each end cleanly. Note the direction — move_sql.sh does no migration itself; it is only the clean-restart building block xmass calls.

Where these paths bracket on-disk changes, the surrounding BOA code sets innodb_fast_shutdown and synchronises directives first; move_sql.sh only performs the clean stop/start.

Manual use

There is rarely a reason to run it directly, but it is safe when you need a clean DB bounce — for example before a manual InnoDB redo-log change, or to recover from a server that left a stale socket behind:

bash /var/xdrago/move_sql.sh restart     # clean restart of the local MySQLD

bash /var/xdrago/move_sql.sh stop        # bring the DB down for offline maintenance
# … do maintenance on /var/lib/mysql …
bash /var/xdrago/move_sql.sh start       # … then back up

Remember that stop also stops Nginx and all PHP-FPM instances, so the whole stack is offline between a manual stop and the next start (or until BOA's monitor restarts the web tier). Do not run stop on its own and walk away from a production box.

Related