phpMyAdmin & MySQL, pt. 3

Now that you have a backup of your database, how do you import it in case of data loss/corruption? That question will be answered here and it’s easier than you may think.

These steps can be used for importing any SQL file through phpMyAdmin. To perform an import of your SQL file:

1. Connect to your phpMyAdmin site.
2. In the left pane, click the name of the database.
3. In the right pane, click the tab labeled SQL.
4. You should see a text box under Location of the text file:. Click the Browse button.
5. Browse to the SQL file you wish to import.
   • The file may end in .sql, .sql.zip, .sql.gz or .sql.bz. It depends on what compression type you chose during the backup of your database.
   • If your file is larger than the allowed size, you will have to break it apart by using a text editor to transfer some of the SQL query to another text file. The new text file would need to be saved with the .sql extension.
6. Under Compression:, leave it set to Autodetect.
7. The Character set of the SQL file needs to be set to what type of encoding you are using on the database. Unless you changed it from the default, leave it set to utf8.
8. Click the Go button. Import time depends on the speed of your connection and the size of the SQL file.

If you are running your own web server, and the file size limit for the import file is showing 2,048 KB (2MB), you can increase by changing a setting in your php.ini file. The setting you want to change is upload_max_filesize. By default, it is set to 2M. Some web host have set the limit between 2 and 8 MB (8M).

What do you do if you get an error? As much as I’d like to have info on every error listed for your quick search, it’s not happening. There is just too much that can go wrong to be able to really be effective in writing it up. You can ask me about it (in the comments section) or go to either TechDiscussions or Computer Help Forum, create an account and ask away. Whichever way you choose, you’ll get the help you need.

20.Aug.05 Tech Tip, phpMyAdmin Comments (3)

phpMyAdmin & MySQL, pt. 2

Part 1 covered configuring and installing (uploading) phpMyAdmin. Part 2 will cover exporting (backup) data from the MySQL database.

This, by far, is what I think is the most important function. A good backup is what stands between you and total data loss. With the backup function in phpMyAdmin, you can backup individual tables or the entire database. Web hosting providers perform their own backups, but I like to make my own.

To perform an export/backup:

1. On the left side of the page, click the name of your database.
   • If you have multiple databases being managed, you will have a drop down menu to select which database you want to manage.
2. On the right side of the page, click the tab labeled Export.
3. Choose the table(s) you wish to backup from the list under Export.
   • If you want to backup the entire database, click Select All.
   • If you want to backup certain tables, click on the table name. To choose multiple tables, hold the Ctrl key while clicking the table name(s).
4. Choose what format you want the exported file to be in. You can choose:
   • SQL: The dumped file will have a .sql extension and can be imported into almost any standard SQL database. This will be the format to use the majority of the time. It can easily be imported using phpMyAdmin or editted with just about any text editor (not Notepad, very garbled).
   • LaTeX: This one I’m not very familiar with but I’m open to explanations. Google fails to give me any good answers on what the format really is about.
   • Microsoft Excel 2000: Why? I guess if you really want it in a format that can be relatively easy to pull into an MS Access database, this would be the choice to go with.
   • Microsoft Word 2000: WHY? I really don’t know why you would want this format. Honestly.
   • CSV for Excel: I guess you could use this for the same reasons as you would the Excel 2000 format.
   • CSV
   • XML
5. Other choices you may want for a basic backup:
   • Structure
     • Add DROP TABLE : During an import, this will delete any table that may currently exist in the database that matches a table name in the backup file.
     • Add IF NOT EXISTS: Definetly check this one. If you don’t, and you import the file but a table name isn’t in the database that is in the backup, your data won’t get restored. This forces the table to be created if one doesn’t exist.
     • Add AUTO_INCREMENT value: Yes and no. If your data has an auto_increment number, like WordPress’ post ID in the wp_post table, you can check this one if you want that number to stay with the data assigned to it. I’d say go ahead and do it if you are mainly going to be importing into an empty database. Beware that it can cause conflicts if you import a table with auto_increment numbers into a table that may already have matching numbers (you can’t have duplicates in the auto_increment column). This is not going to kill your backup if you don’t check this one.
     • Enclose table and field names with backquotes: Check this one. It doesn’t really cause any overhead but it is essential if you have special characters in the table title or field names.
     • SQL export compatibility: If you only plan on importing this back into the MySQL database you are backing up from, leave it set to None. Otherwise, choose what type of database server you plan on importing this into (i.e. MS SQL, Oracle, PostgreSQL, etc.).
   • Data (choose one, not both)
     • Complete inserts: This will include the column name for every INSERT, which can make the file easy to read if you manually edit it.
     • Extended inserts: This is the one I’d choose because the INSERT variable and the table name are only used once so the backup file is smaller.
6. Place a check mark next to Save as file.
   • If you don’t do this, the text of the dump (export/backup) will be displayed in the browser and you will have to copy/paste it into a text file.
7. Leave the File name template set to __DB__ and the saved file will take on the name of the database.
8. Change the compression to either zipped or gzipped. If the server supports it, bzipped will be a choice.
   • Why use compression? If you have a large database, the dump file can be quite large. If it is over the 8MB limit, you will not be able to import it via phpMyAdmin unless you do some editting of the file. This just cuts out that step and increases the chances that you will be able to import the file without editting in the future. The import function supports compressed SQL files.
9. Click Go and save the file to your local machine.

Yes, I know there are a few more options available for the export process. Those options are not necessary for a basic, yet still fully functional, backup of a database.

If you do just want the structure of your database backed up, but not the data, remove the check mark next to Data but leave it next to Structure (step 5). Also, if you just want the data but not the structure, remove the check mark next to Structure and leave it next to Data.

Part 3 will cover importing your backup or any other SQL script.

30.Jul.05 Tech Tip, phpMyAdmin Comments (2)

phpMyAdmin & MySQL, pt. 1

phpMyAdmin is a great web interface for people to use to administer a MySQL database. Thousands of websites and web hosts use MySQL as a database backend for storing data and creating dynamic websites. PHP supports MySQL connections by default. In this series, I am going to explain how to install phpMyAdmin and use some basic functions to administer your MySQL database.

Installing phpMyAdmin
Some web host give you access to phpMyAdmin in the control panel. If yours does, you can skip this part of the tutorial. This part is for those of you that either:

1. have a web host that does not have phpMyAdmin
2. have a web host with phpMyAdmin available but it is an older version
3. have MySQL installed on your local machine, develop web pages on your machine and want an easy to use interface to admin your MySQL install
• This assumes you have IIS or Apache and PHP installed on your local machine.

As of this writing, the latest stable release of phpMyAdmin is 2.6.3-pl1. You may want to download and install this release on your website, even if your host offers phpMyAdmin, because it offers many security updates, PHP 5 support and an increased file size upload of 8,192 Kb (8 Mb). Versions previous to 2.6.x had an upload size limit of 2,048 Kb (2 Mb).

1. Download the file you can use according to your OS. They offer three file types:
   • bzip2
   • gzip
   • zip
2. The files will extract into a directory named phpMyAdmin-2.6.3-pl1
3. Edit the config.inc.php file with a text editor.
   • In Windows, Notepad will make php text run together. Use Wordpad or try out ConTEXT (free).

The following are the basic variables you will need to configure to use phpMyAdmin.

Note: All variables should start and end with a single quote (‘) except for TRUE and FALSE variables.

1. $cfg['PmaAbsoluteUri']
   • Set this to the URI used for your phpMyAdmin install. If your website is http://www.mywebsite.com, and you upload the files into a directory called myadmin, the absolute URI will be http://www.mywebsite.com/myadmin. This is important because you may experience errors in phpMyAdmin if it is not set.
2. $cfg['Servers'][$i]['host']
   • Set this to the hostname or IP address of your MySQL server. If it is running on your local machine, you can leave it set to the default ‘localhost’. Your web host can tell you the web address, or IP number, of the MySQL database install used for your hosting.
3. $cfg['Servers'][$i]['port']
   • Only change this if your host, or local install, has MySQL server running on a port other than the default port (3306).
4. $cfg['Servers'][$i]['compress']
   • Set this to True and communications to/from the MySQL server will be compressed. This may or may not increase the transfer rate.
5. $cfg['Servers'][$i]['auth_type']
   • Leave this as config if you want to store the username/password in the config.inc.php file and configure the following:
     • $cfg['Servers'][$i]['user']
       • The username used to connect to your MySQL database server.
     • $cfg['Servers'][$i]['password']
       • The password corresponding to the username to connect to the MySQL database server.
   • Set this to http if you want to prompt for the DB username/password. If so, leave $cfg['Servers'][$i]['user'] and $cfg['Servers'][$i]['password'] empty.
6. $cfg['Servers'][$i]['only_db']
   • If you only have one database on the MySQL server, you can put the name of the database here. This is optional. If you have multiple databases, all database names will show up in phpMyAdmin if this is left blank. If you type in a database name, only that database will show up in phpMyAdmin.
7. $cfg['Servers'][$i]['AllowRoot']
   • Set this to FALSE to prevent the username ‘root’ from being able to use phpMyAdmin to access the MySQL database.
     • It is much wiser, from a security standpoint, to never use root (or sa in MS SQL) as the user for your app/website to connect to your DB server. You should only use those accounts to create a user with just enough privileges to perform hat needs to be done in the database.

All other variables can be left to their default settings. There are settings to allow you to control several MySQL servers in a single phpMyAdmin interface but this tutorial only covers a single MySQL installation.

Once all variables have been set, save the file. Upload all the files into the directory you specified as the absolute URI. Be sure to password protect this directory. If you don’t, anyone hitting this directory will be able to access your MySQL database!

Part 2 will cover using phpMyAdmin to perform a backup.

27.Jul.05 Tech Tip, phpMyAdmin Comments (2)