Is it possible to keep mysql migration running without keeping connection open?

Question

ALTER TABLE can easily take a few days - and during this time there's a non-negligible chance of connection getting terminated due to network problems. Is it possible to start ALTER TABLE (or CREATE TABLE ... SELECT ...; or some other very long running query) and leave it running without keeping connection open all the time?

(the obvious solution of screen + console mysql client won't easily work as there's no ssh running on that server, only mysqld).

Answer 1

Put your alter table command in a script that you will call from a shell script like this

#!/usr/bin/ksh

muser=username
mpass=password
mhost=hostname
mdb=databasename

mysql --user=$muser --password=$mpass --host=$mhost $mdb < alter.sql

then call the shell script with nohup

nohup myscript.ksh >logfile 2>&1 &

You can log out of the shell session immediately afterwards. You may get a warning message that you have running jobs, but it is safe to ignore.

You can probably skip the sql script and put the commands in the shell script. I just don't recall the syntax