MySQL Schema change With Skeema – Part 1 “Basic Operations”

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

1. About the Skeema tool :
2. Internal workflow of Skeema tool:
3. Basic operations:
   1. Init :
   2. Host directory :
   3. Diff:
   4. Push :
   5. Pull :
   6. Lint :
4. Summary :

About the Skeema tool :

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.

For more information – Skeema Website

Internal workflow of Skeema tool:

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 <command> <options>

Command	Descriptions
Init	Initialize the server by using the Skeema tool. So the host directory will be created. The host directory will hold the schema directory and the metadata file called the .skeema file. The host directory will hold the table structure with a .sql extension. And .skeema file with hold the metadata information. If any changes need to be pushed via the Skeema tool, we need to change in *.sql file only.
Diff	Diff options will be used to identify the changes that have been done in the *.sql file in the host directory and the actual structure in the production.
Lint	Lint is used for parsing the changes done in the *.sql before pushing it to the server. It will parse the query by executing it in the skeem_tmp database. Once the parsing is done, it will drop the database automatically.
Push	Once the changes are confirmed publish, By using the push command. We can push the changes to the server.
Pull	If any of the changes are done directly to the server not via the Skeema tool. So the structure in the *.sql file won’t be updated. To make it an update, we can use the pull option. It will pull the latest structure from the server and place it in the *.sql file.

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.

Host directory :

root@localhost:/home/mydbops/skeema# ls -ltrh
total 8.0K
drwxr-xr-x 10 root root 4.0K Jul 26 15:04 production

root@localhost:/home/mydbops/skeema/production# ls -ltrh
total 32K
drwxr-xr-x 2 root root 4.0K Jul 26 15:06 mysqldiff
drwxr-xr-x 2 root root 4.0K Jul 26 15:06 sbtest
drwxr-xr-x 2 root root 4.0K Jul 26 15:06 test
drwxr-xr-x 2 root root 4.0K Jul 26 15:10 mydbops

root@localhost:/home/mydbops/skeema/production# cat .skeema
generator=skeema:1.7.1-community

[production]
flavor=percona:5.7
host=*.*.*.*
port=3306
user=test
password="*.*.*.*"

root@localhost:/home/mydbops/skeema/production/mydbops# ls -al
total 16
drwxr-xr-x  2 root root 4096 Jul 26 15:10 .
drwxr-xr-x 10 root root 4096 Jul 26 15:06 ..
-rw-r--r--  1 root root   80 Jul 26 15:06 .skeema
-rw-r--r--  1 root root  288 Jul 26 15:10 sbtest.sql
root@localhost:/home/mydbops/skeema/production/mydbops#
root@localhost:/home/mydbops/skeema/production/mydbops# pwd
/home/mydbops/skeema/production/mydbops
root@localhost:/home/mydbops/skeema/production/mydbops# cat sbtest.sql
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

.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.

Advertisement