MySQL

Database Version Control With MySQL Utilities

The following steps are meant to capture a snapshot of your development database structure and your staging/production database structure, compare the two, and provide SQL to consolidate the differences.

This article relates to one of many methods for database version control in a singular or team-based environment.

MySQL Workbench

Having MySQL Workbench is not mandatory for this process. But it’s a nice interface to consolidate the suite of tools that MySQL offers.

Download http://dev.mysql.com/downloads/tools/workbench/

MySQL Utilities

MySQL Utilities will install the required programs to finish the fowllowing steps.

Download http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

Verify MySQL Utilities

Open MySQL Workbench.

Start MySQL Utilities from MySQL Workbench.

The available MySQL Utilities programs can be seen in the console output below with the “help” command executed.

~$ mysqluc -e "help utilities"
Launching console ...

Utility           Description
----------------  ---------------------------------------------------------
mysqlauditadmin   audit log maintenance utility
mysqlauditgrep    audit log search utility
mysqldbcompare    compare databases for consistency
mysqldbcopy       copy databases from one server to another
mysqldbexport     export metadata and data from databases
mysqldbimport     import metadata and data from files
mysqldiff         compare object definitions among objects where the
                  difference is how db1.obj1 differs from db2.obj2
mysqldiskusage    show disk usage for databases
mysqlfailover     automatic replication health monitoring and failover
mysqlfrm          show CREATE TABLE from .frm files
mysqlindexcheck   check for duplicate or redundant indexes
mysqlmetagrep     search metadata
mysqlprocgrep     search process information
mysqlreplicate    establish replication with a master
mysqlrpladmin     administration utility for MySQL replication
mysqlrplcheck     check replication
mysqlrplshow      show slaves attached to a master
mysqlserverclone  start another instance of a running server
mysqlserverinfo   show server information
mysqluserclone    clone a MySQL user account to one or more new users

The same can be done by entering mysqluc -e "help utilities" into your console.

If you receive the following error.

Traceback (most recent call last):
  File "/bin/mysqluc", line 23, in <module>
  from mysql.utilities.common.options import license_callback, UtilitiesParser
File "/Library/Python/2.7/site-packages/mysql/utilities/common/options.py", line 34, in     <module>
from mysql.connector.conversion import MySQLConverter
ImportError: No module named connector.conversion

ImportError: No module named connector.conversion

MySQL Utilities assumes that the MySQL Connector for Python has been installed.

Download it here http://dev.mysql.com/downloads/connector/python/

Compare Databases with mysqldbcompare

Run mysqldbcompare.

~$ mysqldbcompare --server1=<user>:<password>@<host> --server2=<user>:<password>@localhost dwh_production:dwh_development --run-all-tests --skip-data-check --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases dwh_production on server1 and dwh_development on server2
#

If you can’t connect to your database directly, but can SSH into the platform that the database server is located on, run:

~$ ssh -f -N -L 3307:localhost:3306 <user>@<server>

Run mysqldbcompare.

~$ mysqldbcompare --server1=<user>:<password>@localhost:3307 --server2=<user>:<password>@localhost dwh_production:dwh_development --run-all-tests --skip-data-check --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases dwh_production on server1 and dwh_development on server2
#

TIP: Generate a file with the above output.

~$ mysqldbcompare --server1=<database_user>:<database_password>@localhost:3307 --server2=<user>:<password>@localhost dwh_production:dwh_development --run-all-tests --skip-data-check --difftype=sql >> mysqldbcompare.sql

Now you have all the SQL to ALTER your production database structure so that it matches up with your development database structure.

Import

Import database ALTER statements.

~$ mysqlimport -u <user> -p<password> dwh_production mysqldbcompare.sql

Verify

Get a DIFF of the two databases.

~$ mysqldbcompare --server1=<database_user>:<database_password>@localhost:3307 --server2=<user>:<password>@localhost dwh_production:dwh_development --run-all-tests --skip-data-check

Resources

http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html