Shulou(Shulou.com)05/31 Report--

This article is about how to use DDL gh-ost in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Background:

As a DBA, most of the changes to the DDL of a large table are pt-online-schema-change using Percona. This article explains the use of another tool, gh-ost: it does not rely on triggers because it obtains incremental changes from the library in row binlog through simulation, and then applies them asynchronously to the ghost table. Before using gh-ost, you can read the GitHub open source MySQL online change Schema tool article or the official website to learn about its features and principles. This article only explains how to use it.

Description:

1) download and install: https://github.com/github/gh-ost/tags

2) Parameter description: gh-ost-- help

Usage of gh-ost:-- aliyun-rds: whether to execute on Ali cloud database. True-- allow-master-master: whether to allow gh-ost to run in a dual-master replication architecture. It is generally used with the-assume-master-host parameter-allow-nullable-unique-key: the only key that allows gh-ost to depend on for data migration can be NULL, and the default is the unique key that does not allow NULL. If the unique key on which data migration (migrate) depends allows null values, the data may be incorrect and should be used with caution. -- allow-on-master: allows gh-ost to run directly on the main library. The slave library of the default gh-ost connection. -- alter string:DDL statement-- approve-renamed-columns ALTER: if you change the name of a column, gh-ost will recognize and need to provide a reason for renaming the column. By default, gh-ost does not continue to execute unless-approve-renamed-columns ALTER is provided. -- ask-pass:MySQL password-- assume-master-host string: specify a main library for gh-ost in the format "ip:port" or "hostname:port". It is useful in this master architecture, or when gh-ost does not find the master. -- assume-rbr: when confirming the binlog_format=ROW of the database instance to which gh-ost is connected, you can specify-assume-rbr, which prevents stop slave,start slave from running from the library, and users who execute gh-ost do not need SUPER permission. -- check-flag-- chunk-size int: the number of rows processed in each iteration (allowable range: 100-100000). The default value is 1000. -- concurrent-rowcount: if this parameter is True (default), after row-copy, estimate the number of statistical rows (using explain select count (*) method), and adjust the ETA time, otherwise, gh-ost first estimates the number of statistical rows, and then starts row-copy. -- the configuration file path of conf string:gh-ost. -- critical-load string: a series of comma-separated status-name=values. When the status in the MySQL exceeds the corresponding values,gh-ost, it will exit. -critical-load Threads_connected=20,Connections=1500, which means that gh-ost will stop and exit due to the heavy load of the database when the state value Threads_connected > 20 received connections > 1500 in the MySQL. Comma delimited status-name=threshold, same format as-- max-load. When status exceeds threshold, app panics and quits-- critical-load-hibernate-seconds int: when the load reaches critical-load, gh-ost goes into sleep within a specified period of time. It does not read / write anything from any server. -- critical-load-interval-millis int: when the value is 0, exit immediately when-critical-load,gh-ost is reached. When the value is not 0, when-critical-load,gh-ost is reached, it will be checked again after-critical-load-interval-millis seconds, and the check will still reach-critical-load,gh-ost will exit. -- cut-over string: select the cut-over type: cut-over of type atomic/two-step,atomic (default) is github's algorithm, and two-step uses facebook-OSC 's algorithm. -- cut-over-exponential-backoff-- the maximum lock wait time for cut-over-lock-timeout-seconds int:gh-ost in the cut-over phase. When the lock times out, the cut-over of gh-ost will retry. (default: 3)-- database string: database name. -- debug:debug mode. -- default-retries int: the number of times various operations are retried before panick. (default is 60)-- discard-foreign-keys: this parameter is for a table with foreign keys, and when gh-ost creates the ghost table, no foreign keys are created for the ghost table. This parameter is suitable for deleting foreign keys, but otherwise, use it with caution. -- dml-batch-size int: apply the batch size of DML events in a single transaction (range 1-100) (default is 10)-- exact-rowcount: accurately count the number of rows in the table (using select count (*)) to get a more accurate estimate of time. -- execute: actually execute the alter&migrate table. Default is noop. Do not execute, just test and exit. If you want the ALTER TABLE statement to be implemented in the database, you need to specify-execute-- exponential-backoff-max-interval int-- force-named-cut-over: if it is true. Then the 'unpostpone | cut-over' interactive command must name the migrated table-- force-table-names string: the table name prefix-- heartbeat-interval-millis int:gh-ost heartbeat rate value used on the temporary table. The default is 500-- help-- hooks-hint string: any message is injected into the hook-- hooks-path string:hook file storage directory via GH_OST_HOOKS_HINT (default is empty, that is, hook is disabled). Hook will look in this directory for hook files named in accordance with the convention to execute. -- host string: MySQL IP/hostname-- before the initially-drop-ghost-table:gh-ost operation, check and delete the existing ghost table. This parameter is not recommended. Please deal with the existing ghost table manually. This parameter is not enabled by default, and gh-ost exits the operation directly. Check and delete old tables that already exist before the initially-drop-old-table:gh-ost operation. This parameter is not recommended. Please deal with the existing ghost table manually. This parameter is not enabled by default, and gh-ost exits the operation directly. -- initially-drop-socket-file:gh-ost forces the deletion of existing socket files. This parameter is not recommended and may delete a running gh-ost program, causing DDL to fail. -- master-password string: MySQL master password-- master-user string:MysQL master account-- max-lag-millis int: the maximum delay time for master-slave replication. When the master-slave replication delay time exceeds this value, gh-ost will take throttle measures. Default is 1500s. -- max-load string: comma separated status name = threshold, such as: 'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes-- migrate-on-replica:gh-ost 's data migration (migrate) runs on the slave library, not the master library. -- nice-ratio float: dormancy time for each chunk period, range [0.0 … 100.0] . 0: no hibernation for each chunk period, that is, one chunk followed by chunk execution; 1: for every 1 millisecond of row-copy, another 1 millisecond for dormancy; 0. 7: for every 10 milliseconds of row-copy, another 7 milliseconds for dormancy. -- after the ok-to-drop-table:gh-ost operation ends, delete the old table. The default state is not to delete the old table, and there will be a _ tablename_ deltable. -- panic-flag-file string: when this file is created, gh-ost will exit immediately. -- password string: MySQL password-- port int: MySQL port, preferably using slave library-- postpone-cut-over-flag-file string: when this file exists, the cut-over phase of gh-ost will be delayed and the data will still be copied until the file is deleted. -- quiet: silent mode. -- replica-server-id uint: server_id of gh-ost-- replication-lag-query string: deprecate-- absolute path of serve-socket-file string:gh-ost 's socket file. -- serve-tcp-port int:gh-ost uses the port, which is closed by default. -- skip-foreign-key-checks: when you are sure that there are no foreign keys on your table, set to 'true', and want to skip the time of gh-ost validation-- skip-renamed-columns ALTER-- skip-renamed-columns ALTER: if you change the name of a column (such as change column), gh-ost will identify and need to provide a reason for renaming the column, and gh-ost does not continue by default. This parameter tells gh-ost to skip the data migration for the column, telling gh-ost to treat the renamed column as an insignificant column. This operation is dangerous and you will lose all the values of the column. -- stack: add an error stack trace. -- switch-to-rbr: have gh-ost automatically convert the binlog_format from the library to ROW format. -- table string: table name-- test-on-replica: test the gh-ost on the slave library, including data migration (migration) on the slave library. After the data migration is completed, stop slave, the original table and the ghost table are exchanged immediately and then exchanged back immediately. Keep stop slave so that you can compare the two tables. -- test-on-replica-skip-replica-stop: when-test-on-replica is executed, this parameter indicates that stop slave is not used in the process. -- throttle-additional-flag-file string: when the file is created, the gh-ost operation stops immediately. This parameter can be used to create a file when multiple gh-ost operations are operated at the same time, to stop all gh-ost operations, or to delete the file to restore all gh-ost operations. -- throttle-control-replicas string: lists all slave libraries that need to be checked for master-slave replication delays. -- throttle-flag-file string: when the file is created, the gh-ost operation stops immediately. This parameter is suitable for controlling a single gh-ost operation. -throttle-additional-flag-file string is suitable for controlling multiple gh-ost operations. -- throttle-http string-- throttle-query string: throttling query. It is executed every second. Throttling is not required when the return value is 0, and throttling operation is required when the return value is greater than 0. The query operates on the data Migration (migrated) server, so make sure that the query is lightweight. -- timestamp-old-table: use a timestamp in the old table name. This results in a unique and conflict-free cross-migration of old table names-tungsten: tells gh-ost that you are running a tungsten-replication topology. -- user string: MYSQL user-verbose-- version

3) instructions: the condition is that the binlog mode required on the MySQL of the operation is ROW. If you are testing on a top-to-bottom test, you must also be in ROW mode and turn on log_slave_updates. Adjust according to the requirements according to the above parameters.

Environment: master library: 192.168.163.131; slave library: 192.168.163.130

DDL process:

① checks for foreign keys and triggers. ② checks the primary key information of the table. ③ checks whether master library or slave library is enabled, and binlog information ④ checks whether temporary tables ending with gho and del exist tables ending with ⑤ creating ghc, storing data migration information, and binlog information, etc.-⑥ initializes the connection to stream in the above verification phase, adds binlog listening-- the following migration phase ⑥ creates a temporary table ending with gho. Execute DDL to start the transaction with ⑦ on the temporary table at the end of gho, write the source table data to the table at the end of gho according to the primary key id, then commit, and binlog apply. -the following cut-over stage ⑧ lock source table, rename table: rename source table to source _ del table, gho table to source table. ⑨ cleans up the ghc table.

1. DDL on a single instance: a single instance is equivalent to the main database and needs to be enabled-- allow-on-master parameter and ROW mode.

Gh-ost-user= "root"-password= "root"-host=192.168.163.131-database= "test"-table= "T1"-alter= "ADD COLUMN cc2 varchar (10), add column cc3 int not null default 0 comment 'test'"-allow-on-master-- execute

two。 Master / Slave DDL:

There are two options: one is to perform synchronization to slave directly on the master according to 1, and the other is to connect to the slave database and make migration in the master database (as long as the binlog of the slave database is ROW, the master database does not need to be guaranteed):

Gh-ost-user= "root"-password= "root"-host=192.168.163.130-database= "test"-table= "t"-initially-drop-old-table-alter= "ADD COLUMN y1 varchar (10), add column y2 int not null default 0 comment 'test'"-execute

The operation at this time is roughly as follows:

Row data is read and written on the main database

Read the binary log of the slave library and apply the changes to the master library

Collecting table format, field & index, number of rows and other information from the library

Read internal change events (such as heartbeat events) from the library

Switch tables in the main library

In the execution of DDL, the slave library executes the stop/start slave once, and you can add the parameter:-- assume-rbr if it is determined that the slave binlog is ROW. If the binlog of the slave library is not ROW, you can convert it to ROW with the parameter-- switch-to-rbr. At this point, it is important to note that after execution, the binlog mode will not be converted to the original value. -- assume-rbr and-- switch-to-rbr parameters cannot be used together.

3. Do DDL testing on the:

Gh-ost-user= "root"-password= "root"-host=192.168.163.130-database= "test"-table= "t"-alter= "ADD COLUMN abc1 varchar (10), add column abc2 int not null default 0 comment 'test'"-test-on-replica-switch-to-rbr-execute

Parameter-test-on-replica: test the gh-ost on the slave library, including data migration (migration) on the slave library. After the data migration is completed, stop slave, the original table and ghost table are exchanged immediately and then swapped back immediately. Keep stop slave so that you can compare the two tables. If you don't want to stop slave, you can add another parameter:-- test-on-replica-skip-replica-stop

The above three are gh-ost operation modes. In the above operation, the temporary table will not be cleaned up in the end and needs to be cleaned manually. If the temporary table still exists before the next execution, the execution will fail, and you can delete it through parameters:

Check and delete the existing ghost table before the initially-drop-ghost-table:gh-ost operation. This parameter is not recommended. Please deal with the existing ghost table manually. This parameter is not enabled by default, and gh-ost exits the operation directly. Check and delete old tables that already exist before the initially-drop-old-table:gh-ost operation. This parameter is not recommended. Please deal with the existing ghost table manually. This parameter is not enabled by default, and gh-ost exits the operation directly. -- initially-drop-socket-file:gh-ost forces the deletion of existing socket files. This parameter is not recommended and may delete a running gh-ost program, causing DDL to fail. -- after the ok-to-drop-table:gh-ost operation ends, delete the old table. The default state is not to delete the old table, and there will be a _ tablename_ deltable.

There are other parameters, such as-- exact-rowcount,-- max-lag-millis,-- max-load and so on. You can see the above instructions. Most of the commonly used parameter commands are as follows:

Gh-osc-user=-password=-host=-database=-table=-max-load=Threads_running=30, chunk-size=1000 serve-socket-file=/tmp/gh-ost.test.sock exact-rowcount allow-on-master/--test-on-replica initially-drop-ghost-table/--initially-drop-old-table/--initially-drop-socket-file max-lag-millis= max-load='Threads_running=100,Threads_connected=500' ok-to-drop-table

4) additional instructions: termination, suspension, speed limit

Gh-ost-user= "root"-password= "root"-host=192.168.163.131-database= "test"-table= "T1"-alter= "ADD COLUMN O2 varchar (10) Add column o1 int not null default 0 comment 'test' "- exact-rowcount-- serve-socket-file=/tmp/gh-ost.t1.sock-- panic-flag-file=/tmp/gh-ost.panic.t1.flag-- postpone-cut-over-flag-file=/tmp/ghost.postpone.t1.flag-- allow-on-master-- execute

The ① marking file stops running:-- panic-flag-file

Create a file to stop running. In the example, create a / tmp/gh-ost.panic.t1.flag file to terminate the running gh-ost. Temporary file cleaning needs to be done manually.

② indicates that the file forbids cut-over, that is, table name switching is prohibited, and data replication occurs normally. -- postpone-cut-over-flag-file

The creation of files is delayed by cut-over, that is, the switching operation is postponed. When the / tmp/ghost.postpone.t1.flag file is created in the example, gh-ost will complete the row copy, but will not switch the table, it will continuously synchronize the data update operation of the original table to the temporary table.

③ uses socket to listen for requests, and the operator can change the corresponding parameters after the command runs. -- serve-socket-file,--serve-tcp-port (off by default)

Create a socket file to listen and adjust the parameters through the API. When the load and delay increase during the operation, you have to terminate the operation, reconfigure the parameters, such as chunk-size, and then re-execute the operation command, which can be adjusted dynamically through the scoket API. Such as:

Pause the operation:

# pause echo throttle | socat-/ tmp/gh-ost.test.t1.sock# restore echo no-throttle | socat-/ tmp/gh-ost.test.t1.sock

Modify the speed limit parameters:

Echo chunk-size=100 | socat-/ tmp/gh-ost.t1.sockecho max-lag-millis=200 | socat-/ tmp/gh-ost.t1.sockecho max-load=Thread_running=3 | socat-/ tmp/gh-ost.t1.sock

4) comparison test with pt-online-schema-change

1. When the table is not written and the parameter is the default, the DDL operation time is about the same, after all, both are copy row operations.

two。 In the case of a large number of table writes (sysbench), because pt-osc is multithreaded and can be executed quickly, while gh-ost simulates single-threaded applications, in extreme cases, DDL operations are very difficult to complete.

Conclusion: although pt-osc does not need triggers and has much less impact on the pressure and performance of the main database, the efficiency of DDL for high concurrency scenarios is still lower than that of pt-osc, so it still needs to be handled at a low business peak. You can see the performance comparison between gh-ost and pt-osc for related tests.

5) encapsulate the script:

Environment: 192.168.163.131 (ROW), 192.168.163.130MB 132

Encapsulation script: gh-ost.py

#! / bin/env python#-*-encoding: utf-8-*-#-# Purpose: gh-ost# Created: 2018-06-16 copyright- -import MySQLdbimport reimport sysimport timeimport subprocessimport osfrom optparse import OptionParserdef calc_time (func): def _ deco (* args * * kwargs): begin_time = time.time () func (* args, * * kwargs) cost_time = time.time ()-begin_time print 'cost time:% ss'% round (cost_time,2) return _ decodef get_table_count (conn,dbname,tbname): query =' 'SELECT count (*) FROM% s% s''% (dbname) Tbname) cursor = conn.cursor () cursor.execute (query) row_nums = cursor.fetchone () cursor.close () conn.close () return row_numsdef online_ddl (conn,ddl_cmd): cursor = conn.cursor () cursor.execute (ddl_cmd) conn.commit () cursor.close () conn.close () # @ calc_timedef run_cmd (cmd): P = subprocess.Popen (cmd, shell=True) return pmenp.piddef drop_ghost_table (conn Ghost_name_list): try: cursor = conn.cursor () query = 'DROP TABLE IF EXISTS% s ''% (ghost_name_list) cursor.execute (query) conn.commit () cursor.close () conn.close () except Exception,e: print eif _ name__ = = "_ _ main__": parser = OptionParser () parser.add_option ("- P", "- Port", help= "Port for search", dest= "port") parser.add_option ("- D", "--Dbname", help= "the Dbname to use" Dest= "dbname") parser.add_option ("- T", "--Table", help= "the Table to use", dest= "tablename") (options Args) = parser.parse_args () if not options.port: print 'params port need to apply' exit () if not options.dbname: print' params dbname need to apply' exit () if not options.tablename: print 'params tablename need to apply' exit () gh_ost_socket =' / tmp/gh-ost.%s.%s.sock'% (options.dbname Options.tablename) # termination flag panic_flag ='/ tmp/gh-ost.panic.%s.%s.flag'% (options.dbname,options.tablename) # postpone_flag ='/ tmp/gh-ost.postpone.%s.%s.flag'% (options.dbname,options.tablename) # pause flag throttle_flag ='/ tmp/gh-ost.throttle.%s.%s'% (options.dbname Options.tablename) # socket='/ data/%s/tmp/mysql.sock'% (options.port) socket='/ var/run/mysqld/mysqld.sock' get_conn = MySQLdb.connect (host='192.168.163.131', port=int (options.port), user='root', passwd='root', db=options.dbname, unix_socket=socket,charset='utf8') conn = MySQLdb.connect (host='192.168.163.131', port=int (options.port), user='root', passwd='root', db=options.dbname Unix_socket=socket,charset='utf8') (table_count,) = get_table_count (get_conn,options.dbname,options.tablename) print ("\ 033 [0 Number of 32m%s\ 033 [0m "%" tables:% s "% table_count) DDL_CMD = raw_input ('Enter DDL CMD:'). Replace ('',') gh_command_list = re.split ('[] +', DDL_CMD) if gh_command_list [0] .upper () = 'CHANGE' and gh_command_list [1]! = gh_command_list [2]: print ("\ 033 [0]) 31m%s\ 033 [0m "%" renamed columns' data will be lost,pt-osc exit... ") Exit () if table_count

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.