As a Database Engineer, One of the biggest challenges in day-to-day activity is performing DDL on high-traffic and transaction-intensive tables. It will become overhead when handling a large number of servers/shards.
As a standard process, we will first deploy the changes in DEV and QA before deploying them in production.
In the sharded environment, It will become a heavy overhead to maintain the schema changes in DEV, QA, and PROD servers. Since we will have multiple servers in the sharding.
To overcome this deployment supervision, the Skeema tool will help to deploy the changes in QA, DEV, and PROD in a safe and parallel ( for Shards ) as well.
This blog focuses on the basic operation of Skeema, will have a series of blogs on Skeema
Skeema tool is written in the Golang language and it is an actively developing tool as well. It supports only DDL (Create, Drop, Alter) on multiple servers. It won’t support Rename and Truncate in DDL.
The internal workflow of the Skeema tool is described in the below flowchart
After installing the Skeema tool, We can’t directly use it. We need to first initialize the Skeema tool. Once initialized, we can perform the activity by using options.
Syntax : Skeema
Basic operations:
Init :
root@localhost:/home/mydbops/skeema# skeema init -h *.*.*.* -u test -p -d production
Enter password:
2022-07-26 15:06:21 [INFO] Using host dir /home/mydbops/skeema/production for *.*.*.*:3306
2022-07-26 15:06:21 [INFO] Populating /home/mydbops/skeema/production/mysqldiff
2022-07-26 15:06:21 [INFO] Created /home/mydbops/skeema/production/mysqldiff/mysqldiff_test.sql (191 bytes)
2022-07-26 15:06:21 [INFO] Populating /home/mydbops/skeema/production/mydbops
2022-07-26 15:06:21 [INFO] Created /home/mydbops/skeema/production/mydbops/sbtest.sql (271 bytes)
2022-07-26 15:06:21 [INFO] Populating /home/mydbops/skeema/production/test
2022-07-26 15:06:21 [INFO] Created /home/mydbops/skeema/production/test/message.sql (16649 bytes)
We are initializing the server by using the Skeema tool. So the host directory is getting created as “production“, We can decide the directory name by using the -d option or the directory will be created based on the hostname.
Inside the host directory, the *.sql file and the .skeema file will be created.
.Skeema file has the metadata and the *.sql file has the table structure. If we need to perform DDL on any of the tables using the Skeema tool, we need to modify it in the *.sql file.
Diff:
Diff is used to identify the modification we made on the *.sql file by comparing the structure in the *.sql file with the current structure in the server.
root@localhost:/home/mydbops/skeema# skeema diff
2022-07-26 15:55:59 [INFO] Generating diff of *.*.*.*:3306 mysqldiff vs /home/mydbops/skeema/production/mysqldiff/*.sql
2022-07-26 15:55:59 [INFO] *.*.*.*:3306 mysqldiff: No differences found
2022-07-26 15:55:59 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production/mydbops/*.sql
-- instance: *.*.*.*:3306
USE `mydbops`;
\! /usr/bin/pt-online-schema-change --execute --alter 'ADD KEY `c` (`c`)' D=mydbops,t=sbtest,h=*.*.*.*,P=3306,u=test,p=XXXXX
2022-07-26 15:55:59 [INFO] *.*.*.*:3306 mydbops: diff complete
2022-07-26 15:55:59 [INFO] Generating diff of *.*.*.*:3306 sbtest vs /home/mydbops/skeema/production/sbtest/*.sql
2022-07-26 15:55:59 [INFO] *.*.*.*:3306 sbtest: No differences found
2022-07-26 15:55:59 [INFO] Generating diff of *.*.*.*:3306 test vs /home/mydbops/skeema/production/test/*.sql
2022-07-26 15:55:59 [INFO] *.*.*.*:3306 test: No differences found
I have done the modification in sbtest.sql, So the diff commands identify it and share the sample command for execution like dry-run.
Push :
Once the changes are confirmed deploy them on the server. we need to use the push command to perform the DDL on the server.
root@localhost:/home/mydbops/skeema/production# skeema push
2022-07-26 16:32:51 [INFO] Pushing changes from /home/mydbops/skeema/production/mysqldiff/*.sql to *.*.*.*:3306 mysqldiff
2022-07-26 16:32:51 [INFO] *.*.*.*:3306 mysqldiff: No differences found
2022-07-26 16:32:51 [INFO] Pushing changes from /home/mydbops/skeema/production/mydbops/*.sql to *.*.*.*:3306 mydbops
-- instance: *.*.*.*:3306
USE `mydbops`;
\! /usr/bin/pt-online-schema-change --execute --alter 'ADD KEY `c` (`c`)' D=mydbops,t=sbtest,h=*.*.*.*,P=3306,u=test,p=XXXXX
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LC_TERMINAL_VERSION = "3.4.15",
LC_TERMINAL = "iTerm2",
LANG = (unset)
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Found 2 slaves:
warehouse-b2b-db-playground-none-8491856
warehouse-b2b-db-playground-none-8491857
Will check slave lag on:
warehouse-b2b-db-playground-none-8491856
warehouse-b2b-db-playground-none-8491857
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
Altering `mydbops`.`sbtest`...
Creating new table...
Created new table mydbops._sbtest_new OK.
Altering new table...
Altered `mydbops`.`_sbtest_new` OK.
2022-07-26T16:35:05 Creating triggers...
2022-07-26T16:35:05 Created triggers OK.
2022-07-26T16:35:05 Copying approximately 1972656 rows...
2022-07-26T16:35:29 Copied rows OK.
2022-07-26T16:35:29 Analyzing new table...
2022-07-26T16:35:29 Swapping tables...
2022-07-26T16:35:29 Swapped original and new tables OK.
2022-07-26T16:35:29 Dropping old table...
2022-07-26T16:35:29 Dropped old table `mydbops`.`_sbtest_old` OK.
2022-07-26T16:35:29 Dropping triggers...
2022-07-26T16:35:29 Dropped triggers OK.
Successfully altered `mydbops`.`sbtest`.
2022-07-26 16:35:29 [INFO] *.*.*.*:3306 mydbops: push complete
2022-07-26 16:35:29 [INFO] Pushing changes from /home/mydbops/skeema/production/sbtest/*.sql to *.*.*.*:3306 sbtest
2022-07-26 16:35:29 [INFO] *.*.*.*:3306 sbtest: No differences found
2022-07-26 16:35:29 [INFO] Pushing changes from /home/mydbops/skeema/production/test/*.sql to *.*.*.*:3306 test
2022-07-26 16:35:29 [INFO] *.*.*.*:3306 test: No differences found
Now changes are pushed to the server one by one. I have used pt-osc as a flavor to perform alter.
Pull :
The pull command is used to pull the latest table and database structure from the server. It will be more useful when anyone has performed the DDL directly on the server not via the Skeema tool. To make sure that the Skeema tool is referring to the correct structure we need to run pull and collect the updated structure.
root@localhost:/home/mydbops/skeema# skeema pull
2022-07-26 16:40:01 [INFO] Updating /home/mydbops/skeema/production/mysqldiff to reflect *.*.*.*:3306 mysqldiff
2022-07-26 16:40:01 [INFO] Updating /home/mydbops/skeema/production/mydbops to reflect *.*.*.*:3306 mydbops
2022-07-26 16:40:01 [INFO] Wrote /home/mydbops/skeema/production/mydbops/sbtest.sql (271 bytes)
2022-07-26 16:40:01 [INFO] Updating /home/mydbops/skeema/production/sbtest to reflect *.*.*.*:3306 sbtest
2022-07-26 16:40:01 [INFO] Updating /home/mydbops/skeema/production/test to reflect *.*.*.*:3306 test
Lint :
Lint is for parsing the changes applied on the .sql file by executing it in the temporary schema called _skeema_temp, Once the parsing is done. The schema will be dropped automatically.
root@warehouse-b2b-db-playground-none-8448763:/home/mydbops/skeema/production# skeema lint
2022-07-29 18:38:40 [INFO] Linting /home/mydbops/skeema/production
2022-07-29 18:38:40 [INFO] Linting /home/mydbops/skeema/production/mysqldiff
2022-07-29 18:38:40 [INFO] Linting /home/mydbops/skeema/production/mydbops
"2022-07-29 18:38:40 [ERROR] /home/mydbops/skeema/production/mydbops/sbtest.sql:9: SQL syntax error:
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ') ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 9"
2022-07-29 18:38:40 [INFO] Linting /home/mydbops/skeema/production/sbtest
2022-07-29 18:38:51 [INFO] Linting /home/mydbops/skeema/production/test
2022-07-29 18:38:54 [ERROR] Found 1 error and 0 warnings
root@warehouse-b2b-db-playground-none-8448763:/home/mydbops/skeema/production#
Summary :
In this blog, we have covered the basic operation and the skeema tool to get an overall idea of it. In the upcoming blog, we will see how to use the Skeema tool based on the production case and how to add a flavor to perform alter as well.