Database Version Control with MySQL Utilities
Posted on April 13, 2014 • 3 minutes • 504 words
Table of contents
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