Pythian Group

The easiest way to see the differences between two schemas on a non-Windows machine is to run:

mysqldump -h server1 --no-data --all-databases > file1.sql
mysqldump -h server2 --no-data --all-databases > file2.sql
diff file1.sql file2.sql

However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.

We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.

We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and patch. MySQL workbench does both reverse engineering and “forward engineer” alter script for free in the OSS/Community version.

Step by step, here’s how to do it:

1. First, create a script with the database schema that you want to be the final result. For example, if you want to take an existing production schema and change it to be like the development schema, then your “final result” is the development schema:

Read the rest of this entry . . .

More than a month has passed since Sydney Oracle Meetup #2. We shot some video, but it took me a while to process it and publish a few interesting pieces, but I finally got it all.

Ric Van Dyke’s presentation on tuning SQL queries using 10046 trace is available on the SOM website in the Files section (you must be a member). However, there were no material from Tanel Poder’s session — it’s title started with “Zero Slides…” and Tanel demonstrated live some of his secrets of productivity in Oracle troubleshooting. Luckily, my colleague, Andrey Goryunov, managed to shoot some of it on the video and I’m publishing here a couple fragments on the visualization of Oracle performance troubleshooting.

Make sure you are watching them in HQ on YouTube to see more details and if you like these videos, make sure you rate them.

PerfSheet is a very handy solution based on Microsoft Excel scripting and let you automate extraction and charting of any data you can extract from an Oracle database (and generally speaking, any other database). The best thing is that Tanel has put great efforts in it and made it available to everyone for free. One demo is worth many words so here we go:
Read the rest of this entry . . .

Or, There is a Use for GUI Tools!

Recently I was working on a problem for a customer. They are converting a server with two InnoDB data files to innodb_file_per_table:

$ ls -l ibdata*
-rwxr-xr-x  1 mysql  mysql  2.0G Oct 14 13:10 ibdata1
-rwxr-xr-x  1 mysql  mysql  350G Oct 14 13:10 ibdata2

I honestly don’t recall ever seeing (or hearing about) so large a data file.

The method chosen for conversion boils down to this:

1. stop and start the server to enable innodb_file_per_table
2. alter all tables to myisam
3. stop server and delete ibdata file
4. restart server
5. convert tables back to InnoDB
6. add foreign keys

This post isn’t about how we did it, or about whether or not it was the best way, or anything like that. This post is really about the the last two steps.

There were over 500 InnoDB tables and something like 375 foreign keys. So I needed to somehow create the ALTER TABLE statements for these 500 servers. To drop the Foreign Keys, I used the following SELECT statement:

SELECT CONCAT( "mysql -u root -pPASS ", table_schema," -e 'ALTER TABLE ",
table_name, ' DROP FOREIGN KEY ', constraint_name,';'' &')
AS list_of_fks FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

This created a list of statements that I could then put in a batch file, edit a little, and have them run in parallel.

I used the following SELECT statement to find all InnoDB tables and prepare a command to convert them to MyISAM:

SELECT CONCAT("mysql -u root -pPASS ", TABLE_SCHEMA, " -e 'ALTER TABLE ",
TABLE_NAME, " ENGINE=MYISAM;' & ")
FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='INNODB';

Again, the output can be put in a batch file, modified a little, and run in parallel.

Once everything was converted to MyISAM, I shut down the database and removed the InnoDB log and data files and modified the my.cnf to so I had file_per_table and a much smaller InnoDB data file. That was the fairly straightforward part. Now the fun began. Read the rest of this entry . . .