Will Fitch’s Blog

Moving from a procedural system to object-oriented can be a daunting task. One feature to assist you is the MySQLi class, which allows for an object-oriented approach to database manipulation. This tutorial gives insight into the structure and basic usage of the MySQLi class. If PDO isn’t an option for you, then try MySQLi!

Read the rest of this entry →

Navicat MySQL is a tool produced by PremiumSoft CyberTech Ltd that allows both developers and DBAs to access, design, manipulate, report and manage data and the MySQL server. Although it’s not free, it is certainly worth taking the time to play around with.

Navicate MySQL is certainly the tool of choice for my DBA needs. As a developer/DBA, I handle large amounts of data, and am continuously altering and adding databases, tables and fields. Navicat has certainly helped me in this aspect.

This is not a tutorial for Navicat, but this will be coming soon. Right now, I want to introduce some capabilities of Navicat, and show how it differs from other database tools. First, here is a list of features for Navicat:

1. Timeout Reconnection for SQL server
2. Data and Structure Synchronization
3. New Query Builder - create query from different databases
4. Query Parameter
5. SQL Console
6. View Builder
7. Views, Stored Procedure and Triggers creation
8. Private Key for SSH Tunnel
9. Supports all MySQL versions
10. SSH Tunnel
11. HTTP Tunnel
12. Foreign Keys
13. Foreign Key Data Selection
14. Unicode and Character Set Support
15. Edit text in Blob field
16. Print Table Structure
17. Import data from ODBC
18. Import / Export data up to 18 most popular formats including MS Access, MS Excel, XML, PDF and TXT.
19. Create Schedule for Backup, Import / Export, Data Transfer, Saved Queries and Data Synchronization.
20. Create Reports with visual Report Builder
21. Report Archive - Reports can be saved as an archive file (.raf) for backup and increased portability.
22. Create Report Archive from command line

The best advantage is PremiumSoft keeps up with the MySQL versions! Yes, you heard me correctly. When MySQL 5 went into production, this tool had already supported stored procedures, triggers, views, and other MySQL 5 features! Keep in mind that I have tried quite a few tools in my time to include SQLYog (decent, but just not enough features), phpMyAdmin (too slow, but is web based and free), MySQL Query Browser (crashes all the time, but is free), MySQL Administrator (is okay, but doesn’t do a whole lot), MySQL Maestro (just boring and useless IN MY OPINION!), as well as others.

If you want to download a trial version of Navicat, go to http://www.navicat.com/detail.html. They have different licenses and it works on Windows, Linux, and Mac OS X.

If you do download the trial, please post comments on here for your experience.

*NOTE* I am in NO WAY affiliated with PremiumSoft CyberTech Ltd, so don’t cry that I work for them.

This tutorial will cover using the mysqldump executable for backing up databases. If you are an average user of MySQL, and are not interested in administering a MySQL server, then this tutorial might be useless to you.

The mysqldump tool is a backup program that is distributed with every MySQL download. If you aren’t familiar with the bin directory within your MySQL distro, then it’s time to start playing around with the tools inside it.

According to MySQL AB, a man named Igor Romanenko was the original author of the mysqldump tool. As with all software, especially open source, it has been hacked, smacked, slapped around and rewritten 17 times since then (those aren’t actual statistics). When using the mysqldump tool, it produces SQL statements to create table structure, populate tables, or both.

I will assume you are using Linux command line to practice using this tool, but the same principles apply to Windows. Please do not play with production data if this is your first time using this application. You can screw things up if you aren’t careful.

The first step is to get into the MySQL bin directory. If your installation of MySQL is in /usr/local/mysql, then let’s get there by typing the following:

cd /usr/local/mysql/bin

Now that we are inside our bin directory, let’s list the files within and see what we’ve got.

ls

Now you should see the mysqldump tool along with a bunch of other stuff that will eventually get covered in other tutorials. Although we are only going to cover the commonly used and mostly basic options, let?s look at all that can be used with mysqldump. Click here to view them.

The command to backup an entire database is simple:

mysqldump database_name > filename_to_create.sql

Keep in mind that you will likely be required to use the ?p (password) and/or ?u (username) to backup a database. That user must have permissions to access that particular database. For instance, if we needed to backup the mysql database, and were using the root user, we would type the following:

mysqldump mysql > mysql_backup.sql ?u root ?p

At this point, MySQL will prompt us to input a password for the user root. After doing so, the backup process will begin, and a new file filled with all of the SQL creations and inserts for the mysql database will be available. Pretty handy, huh? What if we need to backup ALL databases? This is actually just as simple!

With the list of options provided with the link above, one of the option was ?–all-databases?. This will dump all databases into the file you command. Let?s try it!

mysqldump ?all-databases > alldbs.sql ?u root ?p

This will dump all databases into an SQL file called alldbs.sql. Now that we have finally backed up some databases, what do we need to do to restore them?

This can actually be a little tricky at first. You won?t be using mysqldump to do this, but rather mysql itself. There are two possible ways to do this. The first is to restore the data directly (using brute force). I don?t like this way, and if the SQL is formatted perfectly, then you could run into some issues. At all costs, here is the format:

mysql db_name < file.sql

Again, permissions will be a factor here. Here is my favorite method:

mysql ?e ?source backup_file.sql? db_name

That?s it! That is way too easy.

Before closing this tutorial, I want to show you how to copy databases and their data from one server to another. This is extremely handy if you have a development, test, and production area serving different databases:

mysqldump ?opt db_name | mysql ?host=remote_host -C db_name

The mysqldump tool is just too cool!  If you have any questions, feel free to email me.