Schema change is one of the crucial tasks in MySQL with huge tables. Schema change can cause locks.

What is gh-ost?

                        gh-ost is a triggerless online schema change for MySQL by Github Engineering .It produces light workload on the master during the schema changes . We need online schema change to alter a table without downtime (locking) in production.pt-online schema change is the most widely used tool for making changes in the tables.gh-ost is just an alternative to pt-online schema change.

Why we have to use gh-ost?

         pt-online-schema uses triggers for migrating data from existing table to new table (shadow table ).It uses triggers like After insert,After update,After delete. It uses triggers to propagate live changes in the original table to the shadow table.pt-online schema uses synchronous approach ( All changes are applied on the temporary table immediately).

fb-online schema change uses asynchronous approach(All changes are added to change log table later applied on shadow table)

Major difference between these online schema changes and gh-ost is triggers less. Before understanding about gh-ost we need to know something about triggers and what are the disadvantages over triggers based online schema change.

Triggers

Trigger is a stored routine that get activated when a particular event occur on the table.  For example a row can be inserted by using insert statement and insert trigger activates for the newly inserted row. Triggers generally contains set of queries.They use same transaction space as the query manipulates the tables. This ensures atomicity of both original table and shadow table ( new table ).

             Query and trigger run concurrently and competes for resources.It may cause lock in production.Generally, Triggers are used to record ongoing changes to the original table. pt-osc pauses the execution when server has high currency (–critical-load) or replication lag (–max-lag ) is very high. But it never cancel the triggers in order to maintain synchronization with the original table and the duplicate table.

Triggerless

gh-ost does not use triggers. It is a triggerless.

How ghost track live changes in the original table ?

                  gh-ost uses asynchronous approach similar to fb-osc but it does not use triggers and change log table. Instead of using new change table, it uses binary logs.In order to use gh-ost online schema migration, we must enable a few variables in MySQL server.

1) log-bin=mysql-bin (gh-ost acts as fake replica and pulls the events from the binary logs)

2) binlog-format=row (gh-ost will not work if this variable is set to statement.we need to change it to the row based or let allow the ghost to change by using the option –switch-to-rbr )

3) log-slave-updates=on (if you are using gh-ost in slave, we must enable this variable in order to generate binary logs)

These variables must be enabled because gh-ost acts as fake replica and it needs binlogs to apply live changes in the table.

 
log-bin=mysql-bin
binlog-format=ROW
log-slave-updates=ON
	

How to install gh-ost ?

Steps:

1)Download the release from its official site

 
[aakash@mydbopslabs12 ~]$ wget https://github.com/github/gh-ost/releases/download/v1.0.45/gh-ost-binary-linux-20180417090238.tar.gz
	

2) Extract the file

 
[aakash@mydbopslabs12 ~]$ tar -xvf gh-ost-binary-linux-20180417090238.tar.gz
	

How it works ?

1. Connecting to replica/master In gh-ost online schema change we can avoid the –execute option by using no-op migration.It is similar to dry-run in pt-osc.
2. validate alter statement and checking privileges and existence of tables.
3. creation of ghost table which is similar to original table
4. Apply alter on ghost table
5. copy data from original table to ghost table
6. copy live changes in the original table and copied to the ghost table by reading DML events from the binlog
7. swapping the tables ghost → original and original → old
8. Dropping the older table.

Ensure proper grants for MySQL user

 
mysql>grant ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE  on *.* to 'ghost'@'192.168.33.1';
Query OK, 0 rows affected (0.01 sec)

mysql>grant super,replication slave on *.* to 'ghost'@’192.168.33.1’ ;
Query OK, 0 rows affected (0.01 sec)

mysql>flush privileges;
Query OK, 0 rows affected (0.07 sec)
	

After setting these things we need to check binlog_format. we need to set it to ROW.

 
[root@mydbopslabs11 vagrant]#  ./gh-ost --host=localhost --user=root --password='Aakash@007' --database=nlwiktionary_nousers --table=text --alter="ADD COLUMN new3 INT NOT NULL DEFAULT '0'" --chunk-size=2000 --max-load=Threads_connected=20
	

2018-05-27 11:51:50 FATAL You must be using ROW binlog format. I can switch it for you, provided –switch-to-rbr and that localhost:3306 doesn’t have replicas

                         In the above line , we have given user,password,databases,tables to alter.we also add chunk size for the background table copying process

                         After setting binlog_format=row or use –switch-to-rbr option we can proceed to the next step.In verbose mode,it gives detailed output.without –execute flag we can give dry-run.It checks for all the preliminary validations like user privileges,binary logs,connections.

 
[root@mydbopslabs11 vagrant]# ./gh-ost --host=localhost --user=root --password='Secret' --database=osm --table=users --alter="ADD COLUMN new3 INT NOT NULL DEFAULT '0'" --chunk-size=2000 --max-load=Threads_connected=20 --verbose

2018-05-27 11:59:17 INFO starting gh-ost 1.0.45
2018-05-27 11:59:17 INFO Migrating `osm`.`users`
2018-05-27 11:59:17 INFO connection validated on localhost:3306
2018-05-27 11:59:17 INFO User has ALL privileges
2018-05-27 11:59:17 INFO binary logs validated on localhost:3306
2018-05-27 11:59:17 INFO Restarting replication on localhost:3306 to make sure binlog settings apply to replication thread
2018-05-27 11:59:17 INFO Inspector initiated on mydbopslabs11:3306, version 5.7.22-log
2018-05-27 11:59:17 INFO Table found. Engine=InnoDB
2018-05-27 11:59:17 INFO Estimated number of rows via EXPLAIN: 3941
2018-05-27 11:59:17 INFO Recursively searching for replication master
2018-05-27 11:59:17 INFO Master found to be mydbopslabs11:3306
2018-05-27 11:59:17 INFO Tearing down inspector
2018-05-27 11:59:17 FATAL It seems like this migration attempt to run directly on master. Preferably it would be executed on a replica (and this reduces load from the master). To proceed please provide --allow-on-master. Inspector config=localhost:3306, user=root, applier config=localhost:3306, user=root

If we run the gh-ost in master,we have to use the flag --allow-on-master to execute on the master itself. This feature is used on Aurora RDS too.

# Migrating `osm`.`users`; Ghost table is `osm`.`_users_gho`

# Migrating mydbopslabs11:3306; inspecting mydbopslabs11:3306; executing on mydbopslabs11

# Migration started at Sun May 27 12:37:54 +0000 2018

# chunk-size: 2000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: Threads_connected=20; critical-load: ; nice-ratio: 0.000000
	

From the above output,we can understand that which table are we going to alter(users) and which table is used as temporary table(_users_gho).It also produces another table with suffix _ghc.It is used to store migration logs and status.And maximum acceptance lag is 1.5s.

 
#Copy: 3991/3991 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: mydbopslabs11-bin.000003:637475; State: migrating; ETA: due

Copy: 3991/3991 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: mydbopslabs11-bin.000003:638321; State: migrating; ETA: due

Copy: 3991/3991 100.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 1s(copy); streamer: mydbopslabs11-bin.000003:642607; State: migrating; ETA: due
	

From these we can understand,how many rows have been copied and how many events have been copied from the binary logs and also the estimated time to complete the copy of the tables.

Read binary logs from slave → Analyze tables on slave → apply changes in the master

lock the original table → after sync,rename the original table to _old → rename the ghost table to original table → Drop the old table

Advantages:

• No triggers and no additional tables are created
• configuration changes during run time.

Limitations:

• No support for foreign keys
• No support for tables having triggers

Image Courtesy: Photo by David Menidrey on Unsplash