Looking at MySQL data changes during manual testing

This is article 3 of 3 for a MySQL test strategy.  In article 1 and article 2 we did the spadework and set up 2 instances of MySQL on the same machine and set up replication between them.

During development I've found it useful to compare the state of the database before and after a test.  This is especially true when using database tools such as Hibernate/JPA as often the complexity of these tools results in unintended side-effects.  Rather than try to predict the various effects on the database, it's best to compare the database before and after to verify the expected changes.

There are a number of tools which can help.  Redgate's MySQL Data Compare is an excellent commercial example.  I would take a dump of database, perform a test, take a second dump and compare the two.  It worked well, however it was tedious taking 2 dumps each time and loading each into the tool, particularly on larger databases this was very slow (to the point I soon gave up).

So nowadays I set up replication between 2 instances, and then:

1. Stop replication on slave instance (mysql> STOP SLAVE;)
2. Run test
3. Compare master and slave instance
4. Restart replication on slave (mysql> START SLAVE;)
5. Repeat

I currently use Toad to compare the 2 instances.  It is excellent freeware.

Running MySQL replication on a single machine

This is article 2 of 3 for a MySQL test strategy.

First up, from a redundancy point of view, you're unlikely to want to run a master and a replicated slave instance on the same machine.  If the machine goes down you probably lose both.  My reason for doing this is specifically for testing during development.

See article 1 for a guide to installing muliple MySQL instances on the same machine. We'll start with a 2 instances, a master and a slave.

1. Ensure master and slave instances have a different server-id set (see Setting the Replication Master Configuration and Setting the Replication Slave Configuration)
2. Enable binary logging on the master: log-bin=mysql-bin (see Setting the Replication Master Configuration)
3. As this is only a test instance the slave will use the root account to access the master access.  If this was a production system you'd want to create a replication account with more limited privileges (see Creating a User for Replication).
4. Flush and lock the master database:
   

       mysql> FLUSH TABLES WITH READ LOCK; 

5. Obtain the current log position information:
   

       mysql> SHOW MASTER STATUS;

6.  Take a dump of the master database (from another command window):
   

       > mysqldump -u root dbName > dbdump.sql

7. Unlock the master database:
   

       mysql> UNLOCK TABLES; 

8. Import dump into slave database (assuming your slave instance is running on port 3307):
       > mysql --port 3307 -u root dbName < dbdump.sql
9. Set up the replication link to the master:
   

       mysql> CHANGE MASTER TO MASTER_HOST='localhost',
       MASTER_USER='root',
       MASTER_PASSWORD='<password>',
       MASTER_LOG_FILE='as obtained from step 5',
       MASTER_LOG_POS=as obtained from step 5;

10. Start replication:
    

        mysql> START SLAVE;

11. Check status:
    

        mysql> SHOW SLAVE STATUS\G;

Running multiple instances of MySQL on same machine

This is article 1 of 3 for a MySQL test strategy.

Inspiration came from this blog article Installing Multiple MySQL on Same Machine.

This was done on a Windows 7 platform with MySQL 5.6, the gist of it should work on other versions and platforms.

There is no need to install the MySQL binaries again (unless of course you'd like the additional instance(s) to run a different version of MySQL).

We simply need to make a copy of the data files and ensure there are no clashes with the exisiting instance

Find the data directory.  On my machine it's C:\ProgramData\MySQL\MySQL Server 5.6.

1. Make a copy of the my.ini file and call it something different, for example, my2.ini
2. Make a copy of the data folder and call it something different, for example, data2
3. Edit the my2.ini file*:
   a) correct any paths to the folder above
   b) update the log files
   c) update server-id
   d) update port x2
4. Copy the mysql folder and its contents from original data folder to your new data folder.  Note: I don't fully understand the ramifications of this and therefore these instructions should be treated with extreme caution and not used on a production system.
5. Install a new windows service for this instance: mysqld --install MySQL56_2 --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my2.ini"

* for the record, my settings changed as followed:
[client]
port=3306 -> 3307
[mysqld]
port=3006 -> 3307
datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data2" ->  "C:/ProgramData/MySQL/MySQL Server 5.6/data2"
general_log_file="XXX.log" -> "XXX2.log"
slow_query_log_file="XXX-slow.log" -> "XXX2-slow.log"
log-error="XXX.err" -> "XXX2.err"
server-id=1 -> 2
I don't think server-id is a default setting, but it's helpful if you're going to be running replication (if not you may not need it).