MySQL is the most widely used database management language. With MySQL even inexperienced programmers can create and modify databases consisting of multiple tables. For example, a database for a personnel-staffing organization might contain several tables including a table of available jobs, a table of available employers, and a table of staffing consultants. Databases that drive real-life applications often consist of dozens and dozens of interrelated tables. MySQL does what it takes to generate such databases and manage them efficiently. Combined with PHP and Apache or alternative software MySQL runs on the Internet. The personnel staffing organization could provide many of its services on the Internet relying on MySQL.
Don't let anyone tell you that learning MySQL is a piece of cake. Like any other programming language, mastering MySQL is not a simple task. In spite of promises that abound, you won't become a master in 48 hours. Furthermore, most MySQL solutions also involve PHP. So let's take a closer look at why you should make the commitment to learn MySQL. But first a word of encouragement, you won't have to master this extensive language to see the benefits.
Let's look briefly at what you need to develop and test MySQL programs. After all, you can't learn to program without running and debugging (finding the errors in) real programs. The good news is that MySQL is free. You can download it along with other useful applications for free. We recommend that you start with MySQL4 unless you are an experienced programmer. Later on you can move up to MySQL5. The rest of this article talks mostly about MySQL4. A companion article will discuss MySQL5.
MySQL can run on old computers. For example, I downloaded MySQL, copied it to a USB pen drive, and then copied the files, less than 60 Megabytes, into a Pentium computer purchased new in August, 1999. I couldn't run MySQL under Windows 98 because the system didn't recognize my USB drive. But I ran MySQL under Windows 2000 on this computer, which had an Internet browser but no live Internet connection. Moral of the story: You can do MySQL even on outdated computers.
So you can do MySQL. Why do MySQL? First of all, it is open source. Free. Why pay big bucks for Microsoft or other competitive products? You won't be alone in this decision; MySQL and its friends are the most popular kids on the block. And it's popular with a wide range of users from strict beginners to hard-nosed professionals whose careers depend on performance. MySQL4 provides all the functionality needed to develop a wide range of small to medium database management applications.
MySQL is fast and flexible. Many claim that for technical reasons it runs faster than its competitors including the Microsoft offerings. MySQL programs can be moved from the Windows environment to the Linux environment and still run. Word to the wise: If you are ever thinking of going Linux make sure to pay strict attention to capitalization. Linux treats File1 and file1 as two different files while Windows treats them as the same file. If you are totally strict in the use of lower-case and capital letters, it's very easy to move your web pages from Windows to Linux servers.Read More
One of the more common themes present on the various MySQL forums and mailing lists is that of data migration. Typically requests are made by users of Microsoft® Access and Microsoft SQL Server who are looking to migrate their data (and client applications) to a MySQL database. Developers often ask for tools that can be used to convert an Access database to MySQL (or convert an MSSQL database to MySQL), without realizing that there is more to migrating an application to MySQL than simply converting data.
In this article I will cover the basics of migrating an application from an Access or SQL Server database to MySQL. We’ll start with various reasons why you should (or should not) migrate your existing Access or SQL Server database to MySQL, then cover the planning stages of an application migration. Next we will look at the tools and methods for migrating your actual data from Access/MSSQL to MySQL, followed by some general guidelines for modifying your client application from a Microsoft database to MySQL. Finally, we’ll look at some considerations to make when deploying your new MySQL database and application.
Why You Should Migrate to MySQL
Chances are good that if you are reading this article you already have an interest in migrating your application from Access or SQL Server to MySQL, or at least add support for MySQL to your existing Windows® application. The reasons for migrating an application vary, but let’s look at a few of them.
MySQL is Cross-Platform
One great advantage of using MySQL is its cross-platform capabilities. You can develop your database on a Windows laptop and deploy on Windows Server 2003, a Linux server, an IBM mainframe, or an Apple XServe, just to name a few potential platforms. This gives you a lot of versatility when choosing server hardware. You can even set up replication using a master on a Windows platform with Linux slaves. It’s incredibly easy to move between platforms: on most platforms you can simply copy the data and configuration files between servers and you are ready to go!
MySQL is Fast
An independent study by Ziff Davis found MySQL to be one of the top performers in a group that included DB2, Oracle, ASE, and SQL Server 2000. MySQL is used by a variety of corporations that demand performance and stability including Yahoo!, Slashdot, Cisco, and Sabre. MySQL can help achieve the highest performance possible with your available hardware, helping to cut costs by increasing time between server upgrades.
MySQL is Free
MySQL is Open Source software. As such you are free to examine the source code and make any changes you wish. As per its GPL license, you are free to redistribute those changes as long as your software is also Open Source. If you do not wish to make your software Open Source, you are free to do so as long as you do not distribute your application externally. If you adhere to the requirements of the GPL, MySQL is free for you to use at no cost. If you wish to distribute your closed-source application externally, you will find that the cost of a MySQL commercial license is extremely low (MySQL licenses start at only $249 US). MySQL AB also offers well priced commercial support that is significantly less expensive than some of its counterparts.
Customers Want Open Source
While the Open Source nature of MySQL may not be your driving reason for migrating, I have encountered multiple users who have moved to MySQL because their customers demanded it. Many customers want lower costs and the freedoms that come with using MySQL and other Open Source technologies in their infrastructure. Open Source software such as MySQL gives them freedom from future licensing and upgrade costs and gives them a future that is free from the surprises that can come when dealing with proprietary software.
Why You Should Not Migrate to MySQL
While converting an Access or SQL Server database to MySQL offers many benefits, it may not be the ideal solution for your application. Let’s look at a few scenarios where a conversion to MySQL may not be ideal for you.
Single-User Portable Applications
There are many applications out there that use a combination of Microsoft Access and JET to manage data. These applications are only used by a single user, and often are used in situations where the data file is simply copied to a new machine when it needs to be moved. The reality is that there is not much benefit to using MySQL in such a situation. MySQL is designed as a multi-user server and is ideally suited to situations where concurrent access by anywhere from a few users to several hundred users is a priority. MySQL does offer an embedded server which can be useful when you wish to incorporate the database directly into an application, but it requires a specialized API which is not easily migrated to when an application is based on technologies such as ADO.
Applications With Incompatible Features
MySQL AB is constantly adding new features to MySQL, but there are always some features that SQL Server or Access will offer that are currently unavailable in MySQL. If you are using MySQL 4.0 you may find that a lack of prepared statements, stored procedures, subselects, and views affect the ease with which you can migrate an application to MySQL. This will of course depend on how extensively you have used such features in your application. In MySQL 4.1 we see the introduction of prepared statements and subselects. In MySQL 5.0 stored procedures and views have been introduced, although the stored procedure syntax will undoubtedly vary in some degree from Microsoft’s T-SQL language.
You can often work around the differences between MySQL and MSSQL/Access. If your existing application uses stored procedures but you need to use MySQL 4.0 or 4.1 you can always move the logic that was present in your stored procedures to functions within your application. The difficulty of this will of course depend on the number of stored procedures you use and their complexity.
Large Applications Without Database Abstraction
One piece of advice I would give to any aspiring application developer would be to abstract database access. If your application uses proper database abstraction you will find that converting that application from Access or SQL Server can be done fairly smoothly. If your application is small and lacks abstraction you may also find conversion to be relatively simple as you will not have much code to convert. That being said, the complexity of your migration and the time required to perform the migration will increase as your application grows in size. This is not to say that it will eventually become impossible to convert a large application, but time and costs will possibly increase to the point that the costs of switching to MySQL outweigh the benefits.
Planning For Your Migration
It is very important to plan ahead when migrating a database application to MySQL, as you will want a solid strategy in place before you begin your conversion. In your planning, you will need to consider changes to your data such as modification of data types, as well as modification of the actual data that may be required. You will also want to look at the changes that will need to be made to your client application(s) including such things as cursor use, functions, stored procedures, and internal data types. You will also want to take a look at your current maintenance strategies and make any modifications necessary to continue maintenance under MySQL. Finally, you will want to look at the strengths and weaknesses of MySQL, SQL Server and/or Access and ensure that you will be using MySQL to its fullest.
While SQL Server and MySQL have a fair amount of overlap as far as data types go, there are still some differences to be accounted for. Make sure to spend some time looking at the various data types you use in your tables and plan to migrate those tables to the MySQL data types that best match. Be careful when planning this: you want to match data types by capacity and not necessarily by name. For example: a MySQL
VARCHAR can hold up to 255 characters, whereas a SQL Server
VARCHAR can hold up to 4000 characters. In this case you would need to use a MySQL
TEXT column type instead of
Some data types do not have a direct correlation between SQL Server or Access and MySQL. One example would be the
CURRENCY data type: MySQL does not (yet) have a
CURRENCY data type, but creating a column with the definition
DECIMAL(19,4) serves the same purpose. While MSSQL defaults to Unicode character types such as
nVARCHAR, MySQL does not so tightly bind character sets to field types, instead allowing for one set of character types which can be bound to any number of character sets, including Unicode.
You can find the latest list of MySQL column types in the MySQL Reference Manual. You can use this table of mappings between Visual Basic datatypes and MySQL column types as a quick reference of the basic MySQL column types, their capacities, and their VB6 equivalents. MSDN also provides a list of SQL Server data types.
Sometimes you will need to modify the data itself when doing a data conversion. One example of this would be columns that hold date information. MySQL stores date information in a standard format of
YYYY-MM-DD, while Microsoft databases are often in a
MM-DD-YYYY format. It is very likely that your conversion tool will automatically take care of this, but if you are creating your own conversion tools you will need to keep this in mind. Both MSSQL and MySQL use single quote characters to wrap date information (i.e.
'2000-12-13'), but Access uses hash marks to accomplish the same task (i.e.
#23-11-2001#). If converting from Access to MySQL you will need to change your queries accordingly. Other data modifications might include schema changes to normalize your tables while performing data conversions. For more on database normalization, read the article "An Introduction to Database Normalization".
Many of the built-in MySQL functions are the same as SQL Server built-in functions, though sometimes there are naming differences. One example is the MSSQL
ISNULL() function. MySQL’s equivalent is the
IFNULL() function, which uses the same syntax. Conversely, the
ISNULL() function in Access uses a different syntax, and returns only a boolean instead of a substituted value. MySQL has more built-in functions than its Microsoft counterparts so there should be MySQL equivalents for any built-in functions your existing queries use.
Typical Windows applications will use server-side dynamic or keyset cursors when accessing data through APIs such as ADO. The Connector/ODBC driver does not support keyset-driven cursors and server-side cursor support is very limited in any case. You will want to evaluate the cursor types and cursor locations used in your application to determine if changes need to be made. You may benefit from reading the article "CursorTypes, LockTypes, and CursorLocations".
User Defined Functions
User Defined Functions (or UDFs) are not the same between SQL Server and MySQL. SQL Server functions are very similar to stored procedures, allowing you to encapsulate a series of queries into a callable function that can then be incorporated into a query. MySQL UDFs, on the other hand, are compiled C code that can be assigned to a function name and used in queries. One example would be using a C function that converts a color photo to black and white within a MySQL query to return images stored in color in BLOB columns as black and white images. Once your C code is compiled you can then incorporate it into the server and call it from a query.
MySQL does not currently offer an equivalent for the SQL Server style User Defined Functions, and the functionality of any UDFs present in your database will need to be converted to client-side application code.
MySQL has recently implemented stored procedures in version 5 of its database server. While MySQL is committed to following standard SQL conventions, this is no guarantee that a T-SQL will work in MySQL unchanged. If you will not be using MySQL 5, you will need to rewrite your stored procedures to use client-side code.
In addition to planning your data and application conversions, you will also need to look at converting your database maintenance strategies and tools. Some major backup vendors do provide backup tools for MySQL, so you may want to check with your existing vendor to determine if they provide an equivalent tool for MySQL. Backup strategies for MySQL are very similar to those of SQL Server: regular full backups should be done, with log files backed up in the interval.
There are a wide variety of tools available to help you migrate a SQL Server or Access database to MySQL. We’ll look at several different tools so you can choose the one that best suits your needs. The tools we will look at will include the following:
- Microsoft DTS
- Access Export
- Text Import/Export
SQLYog and the Microsoft DTS wizard offer graphical interfaces that can be used with both MSSQL and Microsoft Access to import tables into MySQL. MSSQL2MYSQL is a script by Michael Kofler that can convert not only the table structure and data, but converts the index information as well. If you use Microsoft Access you may not have access to the above tools, but you can use the data export features of Access.
MSSQL2MYSQL is a creation of Michael Kofler, author of The Definitive Guide to MySQL by Apress. MSSQL2MYSQL is a Visual Basic script that can be executed using either a Microsoft Visual Basic 6 installation or an application that supports VBA such as Microsoft Word or Excel.
Details on usage can be found at the author’s web site, which also includes a listing of GUI front-ends that can be used to make MSSQL2MYSQL a bit more user-friendly for non-programmers.
To use MSSQL2MYSQL with VB6, simply copy the text located at http://www.kofler.cc/mysql/mssql2mysql.txt and paste it into the code section of a VB form. You will need to change the constants at the beginning of the code to match your SQL Server and MySQL installations, and you can then proceed to run the VB6 application and your conversion will take place. MSSQL2MYSQL does not provide any visual feedback on the progress of your conversion, and provides a simple messagebox upon completion.
A nice feature of MSSQL2MYSQL is the ability to dump all statements into a text file, which you can then review and edit before executing on the MySQL server.
Microsoft Data Transformation Services
Microsoft DTS is a data manipulation tool that is included with Microsoft SQL Server. DTS is excellent for moving data between various formats and systems such as databases, spreadsheets, and even HTML. The Microsoft Data Transformation Service can be very complex, but most of us will only ever need to use the Import/Export Wizard that is included with DTS.
Using DTS is fairly straightforward, you choose an ODBC data source to read data from, and then select an ODBC data source to convert the data to. You are then given a list of tables to convert, with an option of renaming the destination table and even performing basic transformations on the data before it is inserted into the target database. These transformations are performed using Visual Basic scripting. In addition, you are given control over the table creation statements to be used, allowing you to fine-tune the MySQL table definitions to add parameters such as table handler (InnoDB, BDB, etc) to the script that will be executed.
DTS also has the ability to perform scheduled data transformations, something that can be very useful when you are using MySQL for analysis of SQL Server data or when you just want the latest data available as you work on your application migration.
SQLyog is a third-party commercial tool available to help administrators manage MySQL in a GUI environment. SQLyog is provided by by webyog, a MySQL partner, and a thirty day trial of the tool is provided. SQLyog provides an ODBC import tool that is similar to DTS, offering a straightforward interface that is perhaps even simpler to use than DTS.
SQLyog is capable of scheduled imports of data, and can also be used to synchronize both data and schema between multiple MySQL servers.
If you are a Microsoft Access user but do not have access to Microsoft DTS or SQLyog, you may want to use the export capability of Microsoft Access. Access can export its tables to a variety of formats, including ODBC. This allows you to export an Access table to MySQL by way of the Connector/ODBC ODBC driver provided by MySQL AB.
To export an Access table to MySQL, right-click on the table in question and choose the ‘Export’ option. After several steps your data will be exported to MySQL. The column-type choices made by Access may need to be modified, and you should be aware that Access will not export index information with the data, meaning that you will need to implement indexes on your tables after exporting them.
One final way to import data is to export the data from MSSQL/Access in a text format and import it directly into MySQL. When exporting, common formats such as tab-delimited or comma-delimited will work fine for later import into MySQL.
When taking this approach, you will need to manually create the MySQL tables, then import the data with the
LOAD DATA command in the mysql command-line client. Additional information on the
LOAD DATA command can be found in the "LOAD DATA INFILE syntax" section of the MySQL Reference Manual.
While perhaps the most labor-intensive and time-consuming, this approach gives you the highest level of control over table schema as you manually create the tables before importing data.
Every database application is different, and as such there are no hard and fast rules that will apply to every application migration. Below we will discuss some of the areas that most developers will need to consider when migrating an Access or SQL Server database to MySQL.
Many Windows applications use integrated Windows NT security to provide access control to their databases (also known as SSPI). This functionality is not currently available in the MySQL server and such authentication will have to be moved to the client application. Additionally, MySQL offers a high level of granularity when specifying database privileges, which can help increase application security when properly implemented.
Although server-side cursors are a pending feature for MySQL, true server-side cursors are not currently implemented. If your application currently uses server-side cursors you may need to evaluate your application and determine whether the simulated server-side cursors provided by Connector/ODBC are adequate, or whether similar functionality can be achieved with client-side cursors in your application.
Stored Procedures And Views
Stored procedures and views are newly implemented features in MySQL 5. If your application relies heavily on either of these features it is recommended that you base your migration on the MySQL 5 server or find a way to move your stored procedures into the client application. MySQL is basing stored procedure syntax on the ANSI SQL standard, which will result in some incompatibilities between T-SQL syntax and MySQL stored procedure syntax. You should plan to perform rewrites on all but the most trivial of T-SQL stored procedures to bring then into conformance with the ANSI SQL standard.
General SQL Syntax
Almost all relational database systems deviate from the SQL standard in one way or another, often to add enhancements and other special features that were not addressed in the original standards. One key to a successful application migration will be to identify SQL queries and statements used in your application that will be incompatible with MySQL.
One area of concern is quoting of table names; While Access uses square brackets (i.e.
SELECT myfield FROM [my table]), MySQL instead uses back-ticks (i.e.
SELECT myfield FROM `my table`). When possible it is best to avoid using table names that require quoting. When this is not possible you will need to change your queries accordingly.
When migrating an application, it is important to note that MySQL has certain advantages that can be exploited in your application. MySQL is often faster at creating and destroying connections within an application than its counterparts, which can affect how you go about creating and destroying connections when developing. In addition, there are specialized functions available from within MySQL that can cut down on the amount of client-side programming needed. Finding and utilizing these advantages can help improve application performance and simplify client-side development.
The migration of a database and client application is not a trivial undertaking. Not only does such a procedure take time to complete, but it often has to be performed on production systems with a minimum tolerance for downtime. The following are a few recommendations to take into consideration when migrating and deploying a database application.
Give Yourself Enough Time
Even the best planned migration can take longer than expected. When budgeting your time be sure to factor in unexpected delays and external interruptions. It is better to over-estimate on a migration project and be done early than to overshoot your schedule.
Perform Trial Migrations
Be certain to perform trial runs of your data migrations before doing any final work. I would recommend giving yourself at least a week leeway before any deadline to allow time to fix any problems that crop up during trial runs. Be careful about making changes between your last successful trial run and your production migration as even the most trivial of changes to your migration tool/script or client application can spell disaster during a production conversion run.
Perform A Limited Rollout Where Possible
If it is feasible in your situation, consider performing a limited deployment of your new system. Perhaps you have one or two customers/branches who are willing to try your new MySQL powered version in a beta program before you roll the update to all your customers. If this is not possible, you may be able to run one or two terminals in a test environment with live data pulled from your existing system using the scheduling capabilities of the migration tools mentioned earlier. In either case this would allow you to test the system with real data and real users before pushing your changes to all users as a whole.
Have Maintenance Plans In Place
Before beginning a production migration you need to ensure that you have an effective disaster recovery plan in place. Ensure that your backup hardware will be compatible with your new MySQL database, and that you have scheduled backups in place with a tested plan to recover your data. Because of the nature of data migrations, you may wish to perform backups with increased regularity in the initial weeks after conversion.
Increasing numbers of developers are migrating applications from Microsoft SQL Server and Microsoft Access to MySQL due to the increased performance, cross platform capability, and open nature of MySQL. When migrating it is important to plan ahead, determining if migration is the best solution in your situation and taking into account the various factors that could delay or impede a migration. There are various tools available to you to help with the migration of your data, and different factors to consider when converting a client application. Once conversion is complete, it is important to take time with deployment, performing trial runs and ideally performing a limited rollout, while ensuring that adequate disaster recovery measures are in place.
Finally, keep in mind that you can benefit from the experience of those who have gone before you. MySQL AB offers services and resources that can assist you in the migration of your data and applications. MySQL AB provides deployment consulting for companies looking to migrate to MySQL, and there are also a series of migration related forums available for you to review and post questions in.Read More
Query optimization is the often overlooked part of applications. Development schedules being what they are, getting the right results and getting the application working are the main priorities. So thoroughly testing, and benchmarking queries is often left as an afterthought.
With our short introduction to query optimization in MySQL, we hope to encourage at least some attention to these issues up front. We’ll also help you identify some of the more common optimizations you may run across.
1. Optimizing Data Types
There are a lot of different data types to choose from in MySQL, and sometimes it can be overwhelming. If you’re a DBA you’re looking at data types strictly in terms of optimizations, and that is your criteria for choosing. But developers may be looking at them in terms of elegant solutions, speed of coding, or simply the most obvious choice. So as a DBA you are likely to be faced with non-optimal choices here. When you can, try to influence these decisions using some general guidelines.
a. Try to avoid NULLs
Nulls are a special case in most databases. MySQL is no exception to this rule. They require more coding to handle internally, more checking, special index logic, and so on. Some developers simply aren’t aware, and when NULL is the default, that is the way tables are created. However it’s better to use NOT NULL in most cases, and even use a special value, such as 0 or -1 for your default value.
b. Use smaller fields where possible
When MySQL reads data from disk, it stores it in memory, and uses cpu cycles and disk I/O to read it. That means smaller data types, which take up less space will be read from disk and packed into memory more efficiently. That said; don’t go so crazy with setting these data types small that you don’t have room for unexpected changes in the application later. Alterations of the table will require restructuring, which is overhead, and potentially code changes, which is also a headache down the line. So strike a balance, but don’t make fields larger than they need to be either.
2. Beware of Character Set Conversions
The character set that your client or application is using may be different from that of the table itself. This will require MySQL to implicitly convert it on the fly. In addition, certain character sets such as UTF8 support multi-byte characters, so they make storage requirements greater.
3. Optimizing COUNT (my_col) and COUNT (*)
If you’re using MyISAM tables, count(*) with no where clause is very fast because the statistics on rowcounts is exact. So MySQL doesn’t have to look at the table at all to get the count. The same can be said for count(my_col) if that column is NOT NULL.
If you are doing count() with a where clause, there’s not much you can do to optimize it further, beyond the obvious of indexing the column in the where clause. It may be possible that a covering index will help you in this case, for a more complex where clause.
Short of the above suggestions, you might go with summary tables. These can allow you to keep up to date information about the contents of your table. You can use triggers, or application logic to keep the summary table always up to date, or you can run a batch job periodically to fill it with up to date information. If you do the latter, your information will be close, but not exact, depending on how often the batch job runs. Weigh your application’s need for precise information against the overhead of keeping this data up to date, and strike a balance.
4. Optimizing Subqueries
MySQL’s query optimization engine isn’t always the most efficient when it comes to subqueries. That’s why it is often a good idea to convert a subquery to a join. Joins have already been handled properly by the optimizer. Of course, be sure the column you’re joining on in the second table is indexed. On the first table MySQL usually does a full table scan on against the subset of rows from the second table. This is part of the nested loops algorithm, which MySQL often engages to perform join operations.
5. Optimizing UNION
UNION has an interesting optimization that exists across a few different databases. It’s obvious when you think about how it works. UNION gives you the rows from two tables that don’t exist in the other. So implicitly, you are removing duplicates. To do this the MySQL database must return distinct rows, and thus must sort the data. Sorting, as we know is expensive, especially for large tables.
UNION ALL can very well be a big speedup for you. What if you already know that your data does not contain duplicates in either row, or what if you don’t care about duplicates? In either case, UNION ALL is for you. Further, there may be other ways you can avoid the duplicates in your rows using some application logic, so you know that UNION ALL will provide the results you want, without the heavy overhead of sorting the data.
This list of query optimizations is by no means exhaustive, but should set you on the right track to optimizing the SQL in your application. Whenever possible, try a few different solutions, look at the query plans, test on large datasets, benchmark those results, and see what works in the real world. Also, be proactive by keeping an eye on your slow query log, and identify further queries that may need tuning as early as possible.Read More