gh-ost简单使用
(站在巨人的肩膀上)
gh-ost介绍与原理性的东西这里就不多做介绍，因为好雨云发布一篇文章，针对gh-ost原理写的已经非常详细，括号里之所以写(站在巨人的肩膀上)，也是对好雨云的感谢与崇拜。
1. gh-ost工作模式
gh-ost有三种工作模式：
a:
连接到从库，在主库做迁移。
b:
连接到主库，迁移过程所有操作都在主上操作，包括读取binlog等等。
c:
在从库做迁移测试。
三种方法各有优缺点，但我只关心缺点，先说a的缺点，a会在从上面读取binlog,但数据库主从数据为什么会造成不一致，一个很重要的原因是主库的binlog没有完全在从库执行。所以个人感觉a方法有丢失数据的风险。b方法任何操作都会再主库操作，或多或少会对主库负载造成影响，但是可以通过调整一些参数降低和时刻关注这些影响，所以个人推荐使用b方法。至于c方法是偏向测试用的，这里不做过多介绍，但是c方法里有一个细节，cut-over阶段有会stop slave一个操作,其实这个操作风险特别高，有时stop slave 时间会很长，务必会对线上数据库使用造成影响，所以如果使用c方法做测试也要在线下数据库。
2.参数介绍。
gh-ost参数很多，但我使用到的有限，这里只对使用到的参数进行介绍，再强调一下，这里测试的是b方法。
–max-load
迁移过程中，gh-ost会时刻关注负载情况，负载阀值是使用者自己定义，比如数据库的最大连接数，如果超过阀值，gh-ost不会退出，会等待到负载在阀值以下继续执行。
–critical-load
这个指的是gh-ost退出阀值，当负载超过这个阀值，gh-ost会停止并退出
–chunk-size
迁移过程是一步步分批次完成的，这个参数是指事务每次提交的行数，默认是1000。
–max-lag-millis
会监控从库的主从延迟情况，如果延迟秒数超过这个阀值，迁移不会退出，等待延迟秒数低于这个阀值继续迁移。
–throttle-control-replicas
和–max-lag-millis参数相结合，这个参数指定主从延迟的数据库实例。
–initially-drop-ghost-table
gh-ost执行前会创建两张xx_ghc和xx_gho表，如果这两张表存在,且加上了这个参数，那么会自动删除原gh表，从新创建，否则退出。xx_gho表相当于老表的全量备份，xx_ghc表数据是数据更改日志，理解成增量备份。
–initially-drop-socket-file
gh-ost执行时会创建socket文件，退出时不会删除，下次执行gh-ost时会报错，加上这个参数会删除老的socket文件，重新创建。
–ok-to-drop-table
go-ost执行完以后是否删除老表，加上此参数会自动删除老表。
–host
数据库实例地址。
–port
数据库实例端口。
–user
数据库实例用户名。
–password
数据库实例密码。
–database
数据库名称。
–table
表名。
-verbose
执行过程输出日志。
–alter
操作语句。
–cut-over
自动执行rename操作。
–debug
输出详细日志。
–panic-flag-file
这个文件被创建，迁移操作会被立即终止退出。
–execute
如果确定执行，加上这个参数。
–allow-on-master
整个迁移所有操作在主库上执行，也就是数的b方法。
–throttle-flag-file
此文件存在时操作暂停，删除文件操作会继续。
3.执行命令
./gh-ost–max-load=Threads_connected=3000 –critical-load=Threads_connected=5000–chunk-size=1000 –max-lag-millis=2000-throttle-control-replicas=”192.168.1.200:3316″ -initially-drop-ghost-table –initially-drop-socket-file–ok-to-drop-table –host=”192.168.1.216″ –port=3316–user=”developer” –password=”developer_eloandb”–database=”test” –table=”d_funds_info” -verbose –alter=”add column rrrchar(11)” –cut-over=default –panic-flag-file=/tmp/ghost.panic.flag –execute –allow-on-master-throttle-flag-file /tmp/1.log
执行日志如下:
2016-09-3014:22:31 INFO starting gh-ost 1.0.8
2016-09-3014:22:31 INFO Migrating `test`.`d_funds_info`
2016-09-3014:22:31 INFO connection validated on 192.168.1.216:3316
2016-09-3014:22:31 INFO User has ALL privileges
2016-09-3014:22:31 INFO binary logs validated on 192.168.1.216:3316
2016-09-3014:22:31 INFO Restarting replication on 192.168.1.216:3316 to make sure binlogsettings apply to replication thread
2016-09-3014:22:31 INFO Table found. Engine=InnoDB
2016-09-3014:22:31 DEBUG Estimated number of rows via STATUS: 1123798
2016-09-3014:22:31 DEBUG Validated no foreign keys exist on table
2016-09-3014:22:31 INFO Estimated number of rows via EXPLAIN: 1123798
2016-09-3014:22:32 DEBUG Potential unique keys in d_funds_info: [PRIMARY(auto_incrmenet): [id]; has nullable: false idx_uuid: [uuid]; has nullable:true]
2016-09-3014:22:32 DEBUG Looking for master on 192.168.1.216:3316
2016-09-3014:22:32 INFO Master found to be testCore:3316
2016-09-3014:22:32 INFO connection validated on 192.168.1.216:3316
2016-09-3014:22:32 DEBUG Streamer binlog coordinates: mysql-bin.000037:322475392
2016-09-3014:22:32 INFO Registering replica at 192.168.1.216:3316
2016-09-3014:22:32 INFO Connecting binlog streamer at mysql-bin.000037:322475392
2016-09-3014:22:32 DEBUG Beginning streaming
2016-09-3014:22:32 INFO connection validated on 192.168.1.216:3316
2016-09-3014:22:32 INFO rotate to next log name: mysql-bin.000037
2016-09-3014:22:32 INFO connection validated on 192.168.1.216:3316
2016-09-3014:22:32 INFO Droppping table `test`.`_d_funds_info_gho`
2016-09-3014:22:32 INFO Table dropped
2016-09-3014:22:32 INFO Droppping table `test`.`_d_funds_info_ghc`
2016-09-3014:22:32 INFO Table dropped
2016-09-3014:22:32 INFO Creating changelog table `test`.`_d_funds_info_ghc`
2016-09-3014:22:32 INFO Changelog table created
2016-09-3014:22:32 INFO Creating ghost table `test`.`_d_funds_info_gho`
2016-09-3014:22:33 INFO Ghost table created
2016-09-3014:22:33 INFO Altering ghost table `test`.`_d_funds_info_gho`
2016-09-3014:22:33 DEBUG ALTER statement: alter /* gh-ost */ table`test`.`_d_funds_info_gho` add column rrr char(11)
2016-09-3014:22:34 INFO Ghost table altered
2016-09-3014:22:34 DEBUG Waiting for tables to be in place
2016-09-3014:22:34 DEBUG Tables are in place
2016-09-3014:22:34 DEBUG Received state TablesInPlace
2016-09-3014:22:34 DEBUG Potential unique keys in _d_funds_info_gho: [PRIMARY(auto_incrmenet): [id]; has nullable: false idx_uuid: [uuid]; has nullable:true]
2016-09-3014:22:34 INFO Chosen shared unique key is PRIMARY
2016-09-3014:22:34 INFO Shared columns areid,uid,nocode,type,income,outgo,balance,description,cdate,tid,areaid,txSerialnumber,txBackSerialnumber,uuid
2016-09-3014:22:34 INFO Listening on unix socket file: /tmp/gh-ost.test.d_funds_info.sock
2016-09-3014:22:34 DEBUG Reading migration range according to key: PRIMARY
2016-09-3014:22:34 INFO Migration min values: [22920136]
2016-09-3014:22:34 DEBUG Reading migration range according to key: PRIMARY
2016-09-3014:22:34 INFO Migration max values: [29164887]
2016-09-3014:22:34 DEBUG Operating until row copy is complete
#Migrating `test`.`d_funds_info`; Ghost table is `test`.`_d_funds_info_gho`
#Migrating testCore:3316; inspecting testCore:3316; executing on localhost
#Migration started at Fri Sep 30 14:22:31 +0800 2016
2016-09-3014:22:34 DEBUG Getting nothing in the write queue. Sleeping…
#chunk-size: 1000; max-lag-millis: 1000ms; max-load: Threads_connected=3000;critical-load: Threads_connected=5000; nice-ratio: 0.000000
#throttle-flag-file: /tmp/1.log
#throttle-additional-flag-file: /tmp/gh-ost.throttle
#panic-flag-file: /tmp/ghost.panic.flag
# Servingon unix socket: /tmp/gh-ost.test.d_funds_info.sock
Copy:0/1123798 0.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 0s(copy);streamer: mysql-bin.000037:322478765; ETA: N/A
Copy:0/1123798 0.0%; Applied: 0; Backlog: 0/100; Time: 3s(total), 1s(copy);streamer: mysql-bin.000037:322481397; ETA: throttled, 192.168.1.200:3316replica-lag=5.000000s
Copy:0/1123798 0.0%; Applied: 0; Backlog: 0/100; Time: 4s(total), 2s(copy);streamer: mysql-bin.000037:322484089; ETA: N/A
2016-09-3014:22:36 DEBUG Issued INSERT on range: [22920136]..[22921136]; iteration: 0;chunk-size: 1000
Copy:1000/1123798 0.1%; Applied: 0; Backlog: 0/100; Time: 5s(total), 3s(copy);streamer: mysql-bin.000037:322570795; ETA: N/A
.
.
.
.
Copy:1122625/1123798 99.9%; Applied: 0; Backlog: 0/100; Time: 2m36s(total), 2m32s(copy);streamer: mysql-bin.000037:446823993; ETA: throttled, 192.168.1.200:3316replica-lag=5.000000s
2016-09-3014:26:25 INFO Setting RENAME timeout as 3 seconds
2016-09-3014:26:25 INFO Session renaming tables is 195099
2016-09-3014:26:25 INFO Issuing and expecting this to block: rename /* gh-ost */ table`test`.`d_funds_info` to `test`.`_d_funds_info_del`, `test`.`_d_funds_info_gho`to `test`.`d_funds_info`
2016-09-3014:26:25 INFO Found atomic RENAME to be blocking, as expected. Double checkingthe lock is still in place (though I don’t strictly have to)
2016-09-3014:26:25 INFO Checking session lock: gh-ost.195104.lock
2016-09-3014:26:25 INFO Connection holding lock on original table still exists
2016-09-3014:26:25 INFO Will now proceed to drop magic table and unlock tables
2016-09-3014:26:25 INFO Dropping magic cut-over table
2016-09-3014:26:25 INFO Releasing lock from `test`.`d_funds_info`,`test`.`_d_funds_info_del`
2016-09-3014:26:25 INFO Tables unlocked
2016-09-3014:26:25 INFO Tables renamed
2016-09-3014:26:25 INFO Lock & rename duration: 802.769696ms. During this time,queries on `d_funds_info` were blocked
2016-09-3014:26:25 INFO Looking for magic cut-over table
[MySQL]2016/09/30 14:26:25 statement.go:27: Invalid Connection
2016-09-3014:26:25 INFO Droppping table `test`.`_d_funds_info_ghc`
2016-09-3014:26:26 INFO Table dropped
2016-09-3014:26:26 INFO Droppping table `test`.`_d_funds_info_del`
2016-09-3014:26:26 INFO Table dropped
2016-09-3014:26:26 INFO Done migrating `test`.`d_funds_info`
2016-09-3014:26:26 INFO Done
4.服务器负载执行前和执行中对比：
执行前：
14:21:39up 6 days, 9:09, 2 users, load average: 0.03, 0.13, 0.16
执行中：
14:25:29up 6 days, 9:13, 2 users, load average: 0.73, 0.45, 0.28
5.动态调整参数
在执行过程中还可以动态配置参数，假如想把最大连接数调整到200，迁移就暂时等待，命令如下：
echomax-load=Threads_connected=200 | socat -/tmp/gh-ost.test.d_funds_info.sock
首先要安装socat工具。
以上算是我对gh-ost 简单使用笔记吧，还请各位大牛多多提出宝贵意见。帮助鄙人更正。
![[原创] MYSQL 事务隔离级别实验](https://blog.75271.com/wp-content/themes/com75271/timthumb.php?src=https://blog.75271.com/wp-content/themes/com75271/css/img/pic/6.jpg&h=110&w=185&q=90&zc=1&ct=1)
