Adding mysql columns to a large table

Changing a large Mysql table has become much easier. I just wanted to document using the pt-online-schema-change command. This following example adds a new column to a Users table.

# pt-online-schema-change  h=api.rds.amazonaws.com,t=Users,u=xxx
 --database xxx  --ask-pass --alter "ADD COLUMN (last_ip varchar(40))"   
--nocheck-replication-filters --critical-load Threads_running=100  --execute

Enter MySQL password:
Found 7 slaves:
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
  ip-10-xxx
Will check slave lag on:
  ip-10-xxx
  ip-10-xxx	
	....

# 8 software updates are available:
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.
#   * The current version for MySQL Community Server (GPL) is 5.6.24.

Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `xxx`.`Users`...
Creating new table...
Created new table xxx._Users_new OK.
Waiting forever for new table `xxx`.`_Users_new` to replicate to ip-xxxx...
Altering new table...
Altered `xxx`.`_Users_new` OK.
2016-03-30T13:55:11 Creating triggers...
2016-03-30T13:55:11 Created triggers OK.
2016-03-30T13:55:11 Copying approximately 3827686 rows...
Copying `xxx`.`Users`:  10% 04:18 remain

Leave a Reply

Your email address will not be published. Required fields are marked *


eight − = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>