JIYIK CN >

SkeemaIs an open source CLI tool that allows us to synchronize MySQL database schemas across multiple hosts. It works on plain SQL files containing CREATE TABLE statements.

Database migration is one of the most common challenges during software development. How can we version the schema and apply the changes to all environments?

Skeema helps alleviate this pain point by letting us manage our schema like regular SQL. There is no special syntax or configuration file format to learn. When we push our schema to a host, Skeema will extract the definitions of the tables on that host and compare them to the local file. It will then calculate the CREATE, ALTER, and DROPstatements to match the remote host to our SQL file.

We need to use InnoDBtables to take full advantage of Skeema. It can be MyISAMused with tables, but not all features are supported and you may encounter issues.

getting Started

Skeema is available for Linux and Mac. DEB and RPM packages are available, as well as pre-built standalone binaries. Get the appropriate download for our system and then install the package or unpack the binary to a directory in our path accordingly.

The first step in using Skeema is to get an existing schema for your database. If we already have CREATE TABLEa collection of SQL files containing statements, we can use them as is. Otherwise, run skeema initthe command to get the SQL representing the live database.

Skeema's CLI accepts mysqlthe same arguments as regular commands. Use the -h , **-u**, and -p flags to specify our MySQL host, username, and password. The MySQL user should have administrative access to our schemas; otherwise, Skeema may not be able to check them or apply all changes. Skeema will place our SQL files in a new directory in the working directory; add the -d flag to specify its name.

skeema init -h example.com -u root -p -d my-sql

Skeema exports the SQL for all schemas on the host by default. Each schema will get its own subdirectory in the my-sql folder. Use --schemathe -d flag to indicate a specific schema to export. It will be placed directly inside my-sql without any subdirectories nesting.

Synchronous Mode

Once you have the SQL, you can sync it to another host. Use skeema pushthe command to compare your local SQL with another MySQL server. Skeema will automatically apply any changes it detects.

$ cd my-sql
$ skeema push -h example.com -u root -p -d my-sql

Try editing one of the statements in SQL CREATE TABLE. Add a new column or change the type of an existing column. When we push skeema, Skeema will generate a ALTER TABLEstatement to update the remote table to match.

Potentially destructive operations, such as dropping a table or changing a column's data type, are disabled by default. This can help us avoid accidental data loss if Skeema is accidentally run or an incorrect server is specified. --allow-unsafeDestructive operations are activated by adding the -p flag to the skeema push command.

Using multiple environments

A common use for Skeema is keeping multiple environments in sync. If we have local, development, and production servers, Skeema lets us name them so that we can push and pull between them seamlessly.

Edit the .skeema file in the schema directory to enable this feature. This is an INI-like configuration structure where each section defines a new named environment. To begin with, we will have a skeema initproduction environment added by .

Specify the connection details for each server so that we can push and pull to keep everything in sync. The keys at the top of the file, outside of any section, are global settings that apply to all environments.

default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
generator=skeema:1.5.2-community
schema=example-db

[production]
flavor=mysql:8.0
host=example.com
port=3306
password=example
user=mysql

[local]
flavor=mysql:8.0
host=localhost
port=3306
password=example
user=mysql

Now we can easily sync changes between environments:

$ skeema pull local
$ skeema push production

This sequence of commands first updates the SQL file on disk to match the local server's schema. The changes are then applied to the live production environment. Alternatively, we can manually edit the SQL on disk and then push to both the local and production environments.

This approach makes database migrations simple, repeatable, and less error-prone. We can incorporate Skeema into our CI pipeline to roll out database changes to production as part of our regular deployment process.

Test run and Lint

Sometimes we may want to check for changes needed to an environment without actually applying them. In this case, use skeema diffSkeema to compare a named environment with a SQL file on disk. Skeema will show us the differences so we can predict skeema pushthe effects.

Skeema also has a built-in lint lintingtool. Run lint skeema lintto analyze our SQL files and identify possible issues. It supports several configurable rules. These help us ensure that our SQL is consistent, supported, and compatible with modern MySQL best practices. Lint results are also displayed during the execution of the lint skeema pushand skeema pulllint commands.

More options

Skeema commands have several shared flags that allow us to customize their operation. Here are some of the most important ones:

• ignore-table – Specifies a list of table names to exclude from synchronization. These tables will not be pushed to or pulled from the remote host. This flag supports regular expression syntax.
• ignore-trigger – Ignore tables but apply to triggers.
• temp-schema – The name of a temporary schema to be created on the database host. Skeema uses this database as a staging area for intermediate changes during push/pull operations. It is automatically deleted after the operation is complete.
• workspace – controls where temporary schemas are created. The default is to use a remote host, but docker is supported as an alternative. This will create a new MySQL Docker container on your local machine for each Skeema operation. This will only work if you have Docker installed.
• connect-options – A comma-separated list of MySQL options to set on the remote database connection, sql_mode='ALLOW_INVALID_DATES',innodb_lock_wait_timeout=1e.g.

These options give us more control over how Skeema operates. They let us set up the database connection to exactly match our application's MySQL configuration.

Summarize

Skeema takes the pain out of database migrations by providing automation and compatibility with CI/CD deployment pipelines. It combines a built-in SQL linter, diff tool, and synchronization mechanism.

The premium version adds additional functionality, including the ability to use views and triggers. Skeema Premium is a commercial product built on the open source Skeema Community core. It also supports Windows systems.

Adopting Skeema has made it easier for us to share database schema changes with our own team, keeping all of our environments in sync. ALTER TABLEThis is a safer option than manually sharing statements, which can get misplaced and don't create a canonical representation of the current schema.