{"id":320374,"date":"2021-08-10T15:33:45","date_gmt":"2021-08-10T12:33:45","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/"},"modified":"2021-08-10T15:33:45","modified_gmt":"2021-08-10T12:33:45","slug":"how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/","title":{"rendered":"#How to Automate MySQL Database Migrations With Skeema \u2013 CloudSavvy IT"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2d2309c5842\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2d2309c5842\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#Getting_Started\" >Getting Started<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#Synchronizing_Schemas\" >Synchronizing Schemas<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#Using_Multiple_Environments\" >Using Multiple Environments<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#Dry_Runs_and_Lints\" >Dry Runs and Lints<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#More_Options\" >More Options<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-automate-mysql-database-migrations-with-skeema-cloudsavvy-it\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Automate MySQL Database Migrations With Skeema \u2013 CloudSavvy IT&#8221;<\/strong><\/p>\n<div id=\"article-content-area\">\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage aligncenter size-full wp-image-13653\" srcset=\"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2021\/08\/424bda25.jpeg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.cloudsavvyit.com\/p\/uploads\/2021\/08\/424bda25.jpeg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" src=\"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2021\/08\/424bda25.jpeg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Illustration showing the Skeema logo\" width=\"1602\" height=\"902\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.skeema.io\">Skeema<\/a> is an open-source CLI tool that lets you synchronize MySQL database schemas across multiple hosts. It works with plain SQL files containing <code>CREATE TABLE<\/code> statements.<\/p>\n<p>Database migrations are one of the most common challenges in the software development process. How do you version your schema and <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly changes to all your environments?<\/p>\n<p>Skeema helps alleviate this pain point by letting you manage your schema as regular SQL. There\u2019s no special syntax or configuration file format to learn. When you push your schema up to a host, Skeema will pull the definitions of the tables on that host and diff them against your local files. It\u2019ll then compute the <code>CREATE<\/code>, <code>ALTER<\/code>, and <code>DROP<\/code> statements to make the remote host match your SQL files.<\/p>\n<p>You\u2019ll need to be using InnoDB tables to get the most out of Skeema. It can work with MyISAM tables but not all features are supported and you may run into issues.<\/p>\n<h2 id=\"getting-started\"><span class=\"ez-toc-section\" id=\"Getting_Started\"><\/span>Getting Started<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Skeema is available for Linux and Mac. DEB and RPM packages are offered, as well as pre-built standalone binaries. Grab the appropriate <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.skeema.io\/cli\/download\">download<\/a> for your system and either install the package or extract the binary to a directory that\u2019s in your path.<\/p>\n<p>The first stage in using Skeema is to get your database\u2019s existing schema. If you\u2019ve already got a collection of SQL files with <code>CREATE TABLE<\/code> statements, you can use them as-is. Otherwise, run the <code>skeema init<\/code> command to get the SQL representing a live database.<\/p>\n<p>Skeema\u2019s CLI accepts the same arguments as the regular <code>mysql<\/code> command. Use the <code>-h<\/code>, <code>-u<\/code> and <code>-p<\/code> flags to specify your MySQL host, username, and password. The MySQL user should have administrative access to your schemas; otherwise, Skeema might not be able to inspect them or apply all changes. Skeema will deposit your SQL files into a new directory within your working directory; add the <code>-d<\/code> flag to choose what this is called.<\/p>\n<pre>skeema init -h example.com -u root -p -d my-sql<\/pre>\n<p>Skeema defaults to exporting the SQL for all the schemas on the host. Each schema will get its own subdirectory within the <code>my-sql<\/code> folder. Use the <code>--schema<\/code> flag to indicate a specific schema to export. It will be placed directly into <code>my-sql<\/code>, without any subdirectory nesting.<\/p>\n<h2 id=\"synchronizing-schemas\"><span class=\"ez-toc-section\" id=\"Synchronizing_Schemas\"><\/span>Synchronizing Schemas<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once you\u2019ve got your SQL, you\u2019re ready to synchronize it to another host. Use the <code>skeema push<\/code> command to diff your local SQL against another MySQL server. Skeema will automatically apply any changes it detects.<\/p>\n<pre>cd my-sql&#13;\nskeema push -h example.com -u root -p -d my-sql<\/pre>\n<p>Try editing one of the <code>CREATE TABLE<\/code> statements in your SQL. Add a new column or alter the type of an existing one. When you <code>skeema push<\/code>, Skeema will generate an <code>ALTER TABLE<\/code> statement that updates the remote table to match.<\/p>\n<p>Potentially destructive operations like dropping a table or changing a column\u2019s data type are disabled by default. This helps you avoid unintentional data loss if you run Skeema accidentally or specify an incorrect server. Destructive operations are activated by adding the <code>--allow-unsafe<\/code> flag to <code>skeema push<\/code> commands.<\/p>\n<h2 id=\"using-multiple-environments\"><span class=\"ez-toc-section\" id=\"Using_Multiple_Environments\"><\/span>Using Multiple Environments<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A common use of Skeema is keeping multiple environments in sync. If you\u2019ve got local, dev, and production servers, Skeema lets you name them so you can seamlessly push and pull between them.<\/p>\n<p>Edit the <code>.skeema<\/code> file in your schema directory to enable this functionality. This is an INI-like configuration structure where each section defines a new named environment. You\u2019ll have a single <code>production<\/code> environment, added by <code>skeema init<\/code>, to begin with.<\/p>\n<p>Specify each server\u2019s connection details so you can push and pull to keep everything in sync. Keys at the top of the file, outside any section, are global settings applied to all environments.<\/p>\n<pre>default-character-set=utf8mb4&#13;\ndefault-collation=utf8mb4_<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/general\/\" data-internallinksmanager029f6b8e52c=\"3\" title=\"General\" target=\"_blank\" rel=\"noopener\">general<\/a>_ci&#13;\ngenerator=skeema:1.5.2-community&#13;\nschema=example-db&#13;\n&#13;\n[production]&#13;\nflavor=mysql:8.0&#13;\nhost=example.com&#13;\nport=3306&#13;\npassword=example&#13;\nuser=mysql&#13;\n&#13;\n[local]&#13;\nflavor=mysql:8.0&#13;\nhost=localhost&#13;\nport=3306&#13;\npassword=example&#13;\nuser=mysql<\/pre>\n<p>Now you can easily synchronize changes between environments:<\/p>\n<pre>skeema pull local&#13;\nskeema push production<\/pre>\n<p>This command sequence first updates your on-disk SQL files to match your <code>local<\/code> server\u2019s schema. The changes are then applied to the live <code>production<\/code> environment. Alternatively, you could manually edit the on-disk SQL and then push to both <code>local<\/code> and <code>production<\/code>.<\/p>\n<p>This approach makes database migrations simple, repeatable, and less error-prone. You could incorporate Skeema into a CI pipeline to roll out database changes to production as part of your regular deployment flow.<\/p>\n<h2 id=\"dry-runs-and-lints\"><span class=\"ez-toc-section\" id=\"Dry_Runs_and_Lints\"><\/span>Dry Runs and Lints<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Sometimes you might want to check the changes an environment needs without actually applying them. In this case, use <code>skeema diff<\/code> to compare a named environment to your on-disk SQL files. Skeema will show you the differences so you can anticipate the effects of a <code>skeema push<\/code>.<\/p>\n<p>Skeema also has a built-in linting tool. Run <code>skeema lint<\/code> to analyze your SQL files and find possible issues. Several <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.skeema.io\/docs\/commands\/lint\">configurable rules<\/a> are supported. These help you ensure your SQL is consistent, supported, and compatible with modern MySQL best practices. Lint results are also shown during <code>skeema push<\/code> and <code>skeema pull<\/code> commands.<\/p>\n<h2 id=\"more-options\"><span class=\"ez-toc-section\" id=\"More_Options\"><\/span>More Options<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Skeema commands have several shared flags that let you customize operations. Here are a few of the most significant ones:<\/p>\n<ul>\n<li><strong><code>ignore-table<\/code><\/strong> \u2013 Specify a list of table names to exclude from synchronization. These tables will not be pushed to a remote host or pulled from it. The flag supports regex syntax.<\/li>\n<li><strong><code>ignore-trigger<\/code><\/strong> \u2013 <code>ignore-table<\/code> but applied to triggers.<\/li>\n<li><strong><code>temp-schema<\/code><\/strong> \u2013 The name of the temporary schema to create on the database host. This database is used by Skeema as a staging ground for inter<a href=\"https:\/\/buradabiliyorum.com\/en\/category\/social-mediaa\/\" data-internallinksmanager029f6b8e52c=\"1\" title=\"Social Media\" target=\"_blank\" rel=\"noopener\">media<\/a>te changes during push\/pull operations. It\u2019s automatically deleted after the operation completes.<\/li>\n<li><strong><code>workspace<\/code><\/strong> \u2013 Controls where the temporary schema is created. The default is to use the remote host but <code>docker<\/code> is supported as an alternative value. This will create a new MySQL Docker container on your local machine for each Skeema operation. It will only work if you have Docker installed.<\/li>\n<li><strong><code>connect-options<\/code><\/strong> \u2013 A comma-separated list of MySQL options to set on the remote database connection, such as <code>sql_mode=\"ALLOW_INVALID_DATES\",innodb_lock_wait_timeout=1<\/code>.<\/li>\n<\/ul>\n<p>These options give you more control over Skeema\u2019s operations. They let you setup the database connection to exactly match your application\u2019s MySQL configuration.<\/p>\n<h2 id=\"conclusion\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Skeema takes the pain out of database migrations by providing automation and compatibility with CI\/CD deployment pipelines. It incorporates a built-in SQL linter, diff tool, and synchronization mechanism.<\/p>\n<p>A premium version adds extra functionality including the ability to work with views and triggers. Skeema Premium is a commercial offering built atop the open-source Skeema Community core. It retails at $99\/month and also supports Windows systems.<\/p>\n<p>Adopting Skeema makes it easier for you to share database schema changes with your team, keeping all your environments in sync. It\u2019s a safer choice than manually sharing <code>ALTER TABLE<\/code> statements that could be misplaced and don\u2019t create a canonical representation of your current schema.\n<\/div>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.cloudsavvyit.com\/13652\/how-to-automate-mysql-database-migrations-with-skeema\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Automate MySQL Database Migrations With Skeema \u2013 CloudSavvy IT&#8221; Skeema is an open-source CLI tool that lets you synchronize MySQL database schemas across multiple hosts. It works with plain SQL files containing CREATE TABLE statements. Database migrations are one of the most common challenges in the software development process. How do you version&#8230;<\/p>\n","protected":false},"author":1,"featured_media":320375,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.cloudsavvyit.com\/p\/uploads\/2021\/08\/424bda25.jpeg","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-320374","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/320374","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=320374"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/320374\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/320375"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=320374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=320374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=320374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}