In the previous blog, we have gone through the Basic operation of the Skeema tool. In this blog, we will see the production use case of the Skeema tool.
Selective schema setup :
root@localhost:/home/mydbops/skeema# skeema init -h *.*.*.* -u test -p -d production1 --schema=mydbops
Enter password:
2022-07-27 03:42:08 [INFO] Using host dir /home/mydbops/skeema/production1 for *.*.*.*:3306; skipping schema-level subdirs
2022-07-27 03:42:08 [INFO] Populating /home/mydbops/skeema/production1
2022-07-27 03:42:08 [INFO] Created /home/mydbops/skeema/production1/sbtest.sql (271 bytes)
Instead of initializing the complete server (All databases), we can initialize the server only for the particular database as well. By using the –schema option, we can able to initialize the server only with a particular schema and I kept the host directory name as production1.
All (Global) servers :
root@localhost:/home/mydbops/skeema# skeema diff
2022-07-27 03:44:54 [INFO] Generating diff of *.*.*.*:3306 mysqldiff vs /home/mydbops/skeema/production/mysqldiff/*.sql
2022-07-27 03:44:54 [INFO] *.*.*.*:3306 mysqldiff: No differences found
2022-07-27 03:44:54 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production/mydbops/*.sql
2022-07-27 03:44:54 [INFO] *.*.*.*:3306 mydbops: No differences found
2022-07-27 03:44:54 [INFO] Generating diff of *.*.*.*:3306 sbtest vs /home/mydbops/skeema/production/sbtest/*.sql
2022-07-27 03:44:54 [INFO] *.*.*.*:3306 sbtest: No differences found
2022-07-27 03:44:54 [INFO] Generating diff of *.*.*.*:3306 test vs /home/mydbops/skeema/production/test/*.sql
2022-07-27 03:44:54 [INFO] *.*.*.*:3306 test: No differences found
2022-07-27 03:44:54 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production1/*.sql
2022-07-27 03:44:54 [INFO] *.*.*.*:3306 mydbops: No differences found
If we perform the command on the base directory (/home/mydbops/skeema/), Then the SQL will execute on the production and production1 server.
Selective server execution:
Executing only on host production1
root@localhost:/home/mydbops/skeema/production1# skeema diff
2022-07-27 03:46:22 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production1/*.sql
2022-07-27 03:46:22 [INFO] *.*.*.*:3306 mydbops: No differences found
root@localhost:/home/mydbops/skeema/production1#
If we need to perform the Skeema tool only for a particular server but for all the databases of that host, then we need to go particular host directory (/home/mydbops/skeema/production1) and execute the command.
Selective schema validation of production server :
root@localhost:/home/mydbops/skeema/production/mydbops# skeema diff
2022-07-27 03:50:37 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production/mydbops/*.sql
2022-07-27 03:50:37 [INFO] *.*.*.*:3306 mydbops: No differences found
root@localhost:/home/mydbops/skeema/production/mydbops#
If we need to perform the Skeema tool only for a particular database, then we need to go particular database directory (/home/mydbops/skeema/production/mydbops) and execute the command, So it will the Skeema tool only for that database instead of all the database belongs to that host.
Concurrency and other notable options :
root@localhost:/home/mydbops/skeema# cat /etc/skeema
concurrent-instances=5
alter-algorithm=inplace
alter-lock=none
alter-wrapper="/usr/bin/pt-online-schema-change --execute --alter {CLAUSES} D={SCHEMA},t={TABLE},h={HOST},P={PORT},u={USER},p={PASSWORDX}"
alter-wrapper-min-size=1m
We can mention the options in the global file (/etc/skeema) and the individual hidden file present in the host directory. The concurrent-instances option will help to execute the alter concurrently among different hosts at the same time. The alter-wrapper will help us to add flavor to perform the alter. WHERE alter-wrapper-min-size help to set a threshold size of the table to switch between online alter and the wrapper while performing alter.
Environment based Execution:
root@localhost:/home/mydbops/skeema/production1# skeema add-environment development -h *.*.*.* -S /var/lib/mysql/mysql.sock -u test -p
Enter password:
2022-07-27 03:48:16 [INFO] Added environment [development] to /home/mydbops/skeema/production1/.skeema
root@localhost:/home/mydbops/skeema/production1# skeema diff development
2022-07-27 03:49:05 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production1/*.sql
2022-07-27 03:49:05 [INFO] *.*.*.*:3306 mydbops: No differences found
root@localhost:/home/mydbops/skeema/production1# skeema diff production
2022-07-27 03:49:12 [INFO] Generating diff of *.*.*.*:3306 mydbops vs /home/mydbops/skeema/production1/*.sql
2022-07-27 03:49:12 [INFO] *.*.*.*:3306 mydbops: No differences found
root@localhost:/home/mydbops/skeema/production1#
As a standard practice for different shards, we will have QA, PROD, and DEV environments as well. Instead of adding those servers as individual servers, we can add them as an environment to the existing servers and perform the command execution as well. But we can’t execute parallel in the environment. We can execute one at a time.
Note: If you did not mention any environment name by default it will go to production
Conclusion :
Skeema tool makes the DBA task easier by handling the DDL hustle free by performing the alter concurrently among the shards and also auto switching between online alter and wrapper based on table size.
%20vs%20systemctl%20stop%20(2).png)
.png)
.svg)
