How to sync your local MySQL schema with Server schema Quickly

I have been using MySQL for many years. It is always a challenge to safely make changes to your production database schema during each release.

The Problem:
Normally what we used to do is to create an SQL script ready and during the release, we will run the SQL script. The issue is by doing this is during our busy development we may miss some columns & it will break our code :(

Solution:
The solution to this problem is to use any MySQL Sync tools like SQL Examiner, dbForge, etc. And most of the tools are PAID.

MySQL Scheman:
MySQL schema, a simple MySQL schema synchronization utility tool which is built in PHP and it is open source. This will create an UPDATE/ADD query automatically based on the schema difference in the database and schema file and it will update schema accordingly.

Installation:

If you use Composer, you can install MySQL Scheman with the following command: composer require smart-php/mysql-scheman

Or alternatively, include a dependency for MySQL Scheman in your composer.json file. For example

{
"require-dev": {
"smart-php/mysql-scheman": "dev-master"
}
}

Basic Usage:

The first step is to create a config file either in JSON or XML where you can put it to database configuration. Next, you can create a schema file and add your required table and field information.

config.xml

<config> 
<hostname>127.0.0.1</hostname>
<username>root</username>
<password>mypassword</password>
<database>test</database>
<driver>pdo</driver>
</config>

myscheman.xml

<?xml version="1.0"?>
<database name="shcool">
<table name="students">
<columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
<columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
</table>
<table name="teachers">
<columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
<columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
</table>
</database>

Sync

By running the sync command MySQL Shcheman will find the changes required in your server and create an SQL Query and it will execute. In the above example imagine if your database doesn't have table teachers. With sync, it will create a table in the database with fields mentioned in the schema file.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

Reverse Engineering

For existing database structures MySQL Scheman supports reverse engineering of your current database schema. By using command export you can create myscheman.xml file with your existing database.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

--

--

--

Software developer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Flutter Is The New Native

ARK Ecosystem Bot — Developers GitHub Assistant

Hands-On Gitpod.io Online IDE

Index in database, theory and practice

Completing the section on Getting Started with Drupal for GCI students!

Headfull Puppeter running in docker without GUI

Practical Steps to Achieve Enterprise Digital Transformation

14 Rules That Every Developer Should Stick To

Laptop displaying a page of code and a smartphone sitting on a wooden desk

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Shabeer

Shabeer

Software developer

More from Medium

CS371p Spring 2022: Sarunas Budreckis — Entry #10

How to connect to a MySQL database using C# and WindowsForms

What is JWT

GitFlow: Quick Guide