Dealing with data in MySQL 5.0 can require extensive coding and patience. Take a helping hand from MySQL expert Mike Hillyer, an IT pro with MySQL Professional Certification and the top-ranked MySQL expert at Experts-Exchange.com.
In this tip, Hillyer explains how to connect to backup remote databases without using GUIs, recommends an open source solution database management solution for companies interested in superior parsing of MySQL databases and describes a workaround for a bug found in reproducing binary values in BIT columns.
Is there an open source application that companies could use to better parse MySql databases? What should a company that would like to be able to scan, search and sort log files down to a single event using keyword search function, as well as exporting data into Excel or a comparable format look for?
Mike Hillyer: MySQL Administrator does provide functionality for searching the log files for keywords and saving to a text format which could be imported into Excel.
You may also find the Practical Query Analyzer project to be of use. It is a multi-database query log analyzer that can identify trends in your logfiles.
Is there a way to write a SQL statement that will override (set to null) all foreign keys for loading a test development database?
Hillyer: When loading test data, you can issue the following statement to shut off foreign key checks in your session:
When you are finished loading your data, issue the following statement to turn foreign key checking back on:
Remember to have all foreign key requirements met before turning foreign key checks back on.
How can I set up my MySQL 5.0 database so that I get e-mail notification each time a record is added? Is there a way to do is using a shell or PHP script?
Hillyer: The simplest way is to add an e-mail send to the application adding the data. In MySQL 5.0, you could use a combination of a trigger and a user-defined function (UDF) to do this, with the UDF calling some form of C function for sending email.
I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE to load data into a table with large BIT columns. How do I load data so the value is correct with reproduceable binary values in the BIT columns?
Hillyer:This is being looked into. In the meantime, it appears that using a function such as PHP's pack() function to write binary values for the bit data is a workaround. You may wish to keep an eye on the bug tracking system to find out what progress is being made. Try searching for 'load data bit'.
What is the best method of backing up MySQL databases on Red Hat Enterprise servers without using a GUI for this process?
Hillyer: This depends on server activity and other needs. For servers with small tables and low activity, the mysqldump utility should work fine.
Users with larger tables may want to look at the mysqlhotcopy utility
Users with very active servers may want to look at solutions such as LVM snapshots or configuring a replication slave for backup purposes.
How do I connect to the remote database from a local VB6.0/MySQL application?
Hillyer:Odds are good that your Web hosting provider has protected the MySQL server with a firewall, so you may need to contact your provider and ask about SSH access to the server. Assuming you can be granted SSH access, look at this article for information on connecting via SSH tunnels.
Examples of connecting can be found in the various sample codes here.
This was first published in March 2006