On-line schema changes to MySQL / MariaDB large database tables: 3 options

1-sql.md

Easiest Method: SQL

Simply let MySQL know you want to avoid table locks when doing the schema change.

ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

You will probably want to detach it from your session as it might take hours/days.

nohup mysql -u root -p foo -e "ALTER TABLE bar ADD ip_address VARBINARY(16) NULL DEFAULT NULL, algorithm=inplace, lock=none;" &

You might have issues if it's a high-write table or you're using foreign keys.

2-percona-toolkit.md

https://www.percona.com/blog/2019/06/07/how-pt-online-schema-change-handles-foreign-keys/

https://www.percona.com/downloads/percona-toolkit/LATEST/

wget https://.....deb
sudo dpkg -i ___.deb

Command to run migration

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

pt-online-schema-change \
--user=root \
--ask-pass \
--host=127.0.01 \
--statistics \
--print \
--alter-foreign-keys-method=auto \
--alter "ADD COLUMN ip_address VARBINARY(16) NULL DEFAULT NULL" \
--dry-run \
D=bar,t=foo

--execute

root@server:/home/deploy/percona# ./run.sh 
Enter MySQL password: 
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
There is an error in MySQL that makes the server to die when trying to rename a table with FKs. See https://bugs.mysql.com/bug.php?id=96145
Since pt-online-schema change needs to rename the old <-> new tables as the final step, and the requested table has FKs, it cannot be executed under the current MySQL version

3-gh-ost.md

gh-ost MySQL migrations

Our tables are too big to be making column changes at this size. We have to use a migrations tool like gh-ost.

https://github.com/github/gh-ost/

1. Make sure it's an https://github.com/github/gh-ost/blob/master/doc/shared-key.md#examples-allowed-and-not-allowed alter

2. Make sure you have a MySQL config file with a [client] block: https://github.com/github/gh-ost/blob/master/doc/cheatsheet.md#cnf-file

3. Enable MySQL binary log

Check to see if the binary log is on, and set to ROW.

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
+---------------------------------+--------------------------------+

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog%';
+-----------------------------------------+--------+
| Variable_name                           | Value  |
+-----------------------------------------+--------+
| binlog_annotate_row_events              | OFF    |
| binlog_cache_size                       | 32768  |
| binlog_checksum                         | NONE   |
| binlog_commit_wait_count                | 0      |
| binlog_commit_wait_usec                 | 100000 |
| binlog_direct_non_transactional_updates | OFF    |
| binlog_format                           | ROW    |
| binlog_optimize_thread_scheduling       | ON     |
| binlog_row_image                        | FULL   |
| binlog_stmt_cache_size                  | 32768  |
+-----------------------------------------+--------+

If not, (or wrong format) add the following config to /etc/mysql/[conf.d]/10-gh-ost.cnf

You can read more here: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
; 24hrs: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds
;binlog_expire_logs_seconds=86400

You will also need to set the server_id manually in the config which you can do in the same [mysqld] block above. Without this MySQL still claims it's set, but it is not really.

[mysqld]
server_id=1

4. Check the config is valid

The following will validate the configs without starting MySQL

$ mysqld --help

5. Test changes

You can see the flags here: https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md

  
gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--max-lag-millis=1500 \
--conf=my.cnf \
--host=127.0.0.1 \
--allow-on-master \
--database="bar" \
--table="foo" \
--verbose \
--alter="ADD COLUMN ip_address VARBINARY(16) NULL DEFAULT NULL" \
--assume-rbr \
--cut-over=default \
--exact-rowcount \
--discard-foreign-keys \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \

--allow-master-master \

6. LIVE: Run changes

add the following

-- execute

7. Disable binary logs

They take up a lot of space. Alternatively purge old logs manually instead of waiting until binlog_expire_logs_seconds.

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

Be warned, all three of these solutions have issues with foreign keys.