Coder Social home page Coder Social logo

diffdbschemascomparsion's Introduction

MySQL Database Schemas/Structures Comparison Script based on 'Python' language.

Usecase :
A) 'db_master1' database is your perfect database which covered schemas & structures like tables, indexes, foreign keys, triggers, routines etc. as per product requirements.
B) 'db_testing1' database is your raw database which covered schemas like tables, indexes, foreign keys, triggers, routines etc. as per product requirements, but raw 'db_testing1' database is missing some schemas as compared to perfect 'db_master1' database due to some reasons.
C) Missing schemas on raw 'db_testing1' database might be :

  1. New columns on existing tables
  2. Need to change existing columns definition on existing tables
  3. Need to change existing columns datatype on existing tables
  4. New tables on existing database
  5. New indexes on existing tables
  6. Need to change existing indexes definition on existing tables
  7. New triggers on existing tables
  8. Need to change existing triggers definition on existing tables
  9. New functions/routines/events on database
  10. Need to change existing functions/routines/events definition on database
  11. New views on database
  12. Need to change existing views definition on database

To overcome from above mentioned problem i have developed an script to solve problem automatically within shorter period of time.

Script Compare :

  1. Table Attributes
  2. Table Structure [Column data-type / definition, indexes, foreign keys constraints]
  3. Table Columns
  4. Table Indexes
  5. Table Foreign Key Constraints
  6. Table Triggers
  7. DB Routine / Event / Procedure / Function
  8. DB Views
  9. Above All Options

Script Benefits :

  1. User-friendly
  2. Eliminate mistakes
  3. Deploy changes from dev, to test, to production
  4. Find and fix errors caused by differences between databases
  5. Generate SQL scripts that can be manually edited before running
  6. Speed up the deployment of new database schema updates
  7. DB schemas can synchronize between local to local, local to remote, remote to remote.

Check Sample Videos on Youtube Link : https://www.youtube.com/channel/UC30lKncvpa8kKtPsIE3WrrQ

If you like videos then please share it to your friends too. If you / your friends want to use / run this script then put your email id in comment box. I'll share the installation and procedure steps. Don't forget to Like or Subscribe to my youtube channel to get more videos.

Installation process on ubuntu OS version <= 14 [for python2.7] :

  1. Install 'Lampstack' i.e (php, apache2, mysql version <= 5.5)
    Link : https://www.digitalocean.com/community/tutorials/how-to-install-lamp-on-ubuntu-14-04-quickstart
  2. Install 'Python' version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pip
  3. Install python 'PyMySQL' for python version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pymysql
  4. Create 'bin' folder in your home directory level & set 774 permission to 'bin' created folder recursively.
  5. Copy script for python version 2.7 into created 'bin' directory.
    Download file name : diffDB2.py
  6. Open terminal and goto created bin directory level. Type 'diffDB2.py' and press 'ENTER' button.
  7. Go ahead steps-wise with script.

Installation process on ubuntu OS version 18.0 [for python2.7] :

  1. Install 'Lampstack' i.e (php, apache2, mysql version >= 5.7)
    Link : https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-ubuntu-18-04
  2. After installed mysql and phpmyadmin.
    Remove 'ONLY Full Group BY' option permanently.
    Remove 'zero in default date' & 'zero in default datetime' option permanently.
  3. Install 'Python' version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pip
  4. Install python 'PyMySQL' for python version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pymysql
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 2.7 into created 'bin' directory. Download file name : diffDB2.py
  7. Open terminal and goto created bin directory level. Type 'diffDB2.py' and press 'ENTER' button.
  8. Go ahead steps-wise with script.

Installation process on ubuntu OS version 18.0 [for python3.6]:

  1. Install 'Lampstack' i.e (php, apache2, mysql version >= 5.7)
    Link : https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-ubuntu-18-04
  2. After installed mysql and phpmyadmin.
    Remove 'ONLY Full Group BY' option permanently.
    Remove 'zero in default date' & 'zero in default datetime' option permanently.
  3. Install 'Python' version 3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3-pip
  4. Install python 'PyMySQL' for python version 3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3-pymysql
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 3.6 into created 'bin' directory. Download file name : diffDB3.py
  7. Open terminal and goto created bin directory level. Type 'diffDB3.py' and press 'ENTER' button.
  8. Go ahead steps-wise with script.

Installation process on linux/debian OS version 9 [for python2.7] :

  1. Install 'Lampstack' i.e (php, apache2, mysql)
    Link : https://www.linuxbabe.com/debian/install-lamp-stack-debian-9-stretch
  2. Install phpmyadmin
    Run command via terminal : sudo apt-get install phpmyadmin
    Link : https://www.youtube.com/watch?v=p08xghuzBwc
  3. Install 'Python' version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pip
  4. Install python 'PyMySQL' for python version 2.7
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python-pymysql
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 2.7 into created 'bin' directory.
    Download file name : diffDB2.py
  7. Open terminal and goto created 'bin' directory & press 'ENTER' button.
  8. Type 'python diffDB2.py' and press 'ENTER' button.
  9. Go ahead steps-wise with script.

Installation process on linux/debian OS version 9 [for python3.6] :

  1. Install 'Lampstack' i.e (php, apache2, mysql)
    Link : https://www.linuxbabe.com/debian/install-lamp-stack-debian-9-stretch
  2. Install phpmyadmin
    Run command via terminal : sudo apt-get install phpmyadmin
    Link : https://www.youtube.com/watch?v=p08xghuzBwc
  3. Install 'Python' version 3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3-pip
  4. Install python 'PyMySQL' for python version 3.6
    Run command via terminal : sudo apt-get update
    Run command via terminal : sudo apt-get install python3-pymysql
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 3.6 into created 'bin' directory.
    Download file name : diffDB3.py
  7. Open terminal and goto created 'bin' directory & press 'ENTER' button.
  8. Type 'python3 diffDB3.py' and press 'ENTER' button.
  9. Go ahead steps-wise with script.

Installation process on centos OS version 6 [for python2.7] :

  1. Install 'Lampstack' i.e (php, apache2, mysql)
    Link : https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-centos-6
  2. Install 'Python' version 2.7
    Link : https://tecadmin.net/install-python-2-7-on-centos-rhel/
  3. Install 'Pip' for python version 2.7
    Run command via terminal : curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
    Run command via terminal : python2.7 get-pip.py --user
  4. Install python 'PyMySQL' for python version 2.7
    Link : https://unix.stackexchange.com/questions/254294/make-python-2-7-the-default-python-in-centos-making-an-alias-didnt-work Run command via terminal : sudo rm -r /usr/bin/python
    Run command via terminal : sudo ln -s /usr/local/bin/python2.7 /usr/bin/python
    Run command via terminal : pip install pymysql --user
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 2.7 into created 'bin' directory.
    Download file name : diffDB2.py
  7. Open terminal and goto created bin directory level. Type 'python2.7 diffDB2.py' and press 'ENTER' button.
  8. Go ahead steps-wise with script.

Installation process on centos OS version 6 [for python3.6] :

  1. Install 'Lampstack' i.e (php, apache2, mysql)
    Link : https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-centos-6
  2. Install 'Python' version 2.7
    Link : https://tecadmin.net/install-python-3-6-on-centos/ Link : https://danieleriksson.net/2017/02/08/how-to-install-latest-python-on-centos/
  3. Install 'Pip' for python version 3.6
    Run command via terminal : wget https://bootstrap.pypa.io/get-pip.py
    Run command via terminal : python3.6 get-pip.py --user
  4. Install python 'PyMySQL' for python version 2.7
    Link : https://unix.stackexchange.com/questions/254294/make-python-2-7-the-default-python-in-centos-making-an-alias-didnt-work Run command via terminal : sudo rm -r /usr/bin/python
    Run command via terminal : sudo ln -s /usr/local/bin/python3.6 /usr/bin/python
    Run command via terminal : pip3.6 install pymysql --user
  5. Create 'bin' folder in your home directory level & set 774 permission to created 'bin' folder recursively.
  6. Copy script for python version 3.6 into created 'bin' directory.
    Download file name : diffDB3.py
  7. Open terminal and goto created bin directory level. Type 'python3.6 diffDB3.py' and press 'ENTER' button.
  8. Go ahead steps-wise with script.

Installation process on windows OS version 10 [for python2.7] :

  1. Install 'XAMPP'
    Link : https://pureinfotech.com/install-xampp-windows-10/
  2. Install 'Python' version 2.7
    Link : https://datascience.com.co/how-to-install-python-2-7-and-3-6-in-windows-10-add-python-path-281e7eae62a
  3. Open command prompt and Goto directory 'c:/python27/script' and press 'ENTER' button
  4. Type this 'pip install pymysql' command and press 'ENTER' button
  5. Start 'XAMPP' server
  6. Copy mysql diff db script for python version 2.7 into your 'Downloads' directory
    Download file name : diffDB2.py
  7. Open command prompt and goto 'Downloads' directory and type 'diffDB2.py' command and press 'ENTER' button
  8. Go ahead steps-wise with script.

Installation process on windows OS version 10 [for python3.6] :

  1. Install 'XAMPP'
    Link : https://pureinfotech.com/install-xampp-windows-10/
  2. Install 'Python' version3.6
    Link : https://datascience.com.co/how-to-install-python-2-7-and-3-6-in-windows-10-add-python-path-281e7eae62a
  3. Open command prompt and Goto directory 'c:/python36/script' and press 'ENTER' button
  4. Type this 'pip install pymysql' command and press 'ENTER' button
  5. Start 'XAMPP' server
  6. Copy mysql diff db script for python version 3.6 into your 'Downloads' directory
    Download file name : diffDB2.py
  7. Open command prompt and goto 'Downloads' directory and type 'diffDB3.py' command and press 'ENTER' button
  8. Go ahead steps-wise with script.

Any suggestions, improvements, facing issues etc. Contact on given below details.
Author Name : Chirag D Jain
Mobile & What's App : 91+ 9975967186
Email : [email protected]
Country : India

diffdbschemascomparsion's People

Contributors

chiggyjain avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.