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