Customer Support Centre : Manual : MySQL Databases
 
Welcome to the MySQL section of the Efficient Hosting Support Manual where you will find information about all aspects of working with MySQL.  
The links in this section are as follows;
Introduction To MySQL : Creating A MySQL Database : Using phpMyAdmin To Manage MySQL Databases
Accessing MySQL Through Encrypted Telnet (SSH) : Associating Users With MySQL Databases
Accessing MySQL Databases From PHP Scripts : Accessing MySQL Databases From Perl Scripts
Importing Data Into MySQL Database Tables : Understanding MySQL Database Passwords
Backing Up MySQL Databases : Further Reading And Related Links
 
  Introduction To MySQL
MySQL is a very fast and robust SQL ("Structured Query Language") database management system. Structured Query Language is the most common standardized language used to access databases.

A database is simply a structured collection of data. It may be anything from a simple shopping list to a vast amount of information in a corporate network. To add, modify, access and process data stored in a database, you need a database management system such as MySQL.

The following tutorial shows how to create one or more MySQL database(s) and gives a basic overview of the various ways you can manage and access MySQL databases at Efficient Hosting.
 
  Creating A MySQL Database
You can create a MySQL database using the MySQL Databases option in your Control Panel.
Simply enter a name for the new database in the Db: field and click Add Db.
A database will be created named with your username as a prefix in the form username_name.
Please avoid using characters other than ordinary letters and numbers when naming databases. In particular, we advise against using additional underscores.

The number of MySQL databases you may create depends on which web hosting plan you have. Please refer to our Plans page for details.

Return To Top of Page
 
  Using phpMyAdmin To Manage MySQL Databases
After you have created at least one MySQL database you may begin working with it straight away using a database management system called "phpMyAdmin". phpMyAdmin is a powerful and user-friendly system for managing MySQL databases which can be conveniently accessed by clicking on the phpMyAdmin link near the bottom of the MySQL Databases menu. phpMyAdmin allows common operations such as the running of queries and the adding, deleting and modifying of tables and records to be performed using simple web-based forms.

As well as offering a user-friendly web based interface for managing your databases, phpMyAdmin allows "raw" MySQL commands to be conveniently typed into the Run SQL Query/Queries command box for processing. This feature allows more advanced users to run almost any "raw" MySQL statement without needing to establish a direct Encrypted Telnet (SSH) connection with the MySQL server.

Please refer to our separate phpMyAdmin documentation for detailed instructions on using phpMyAdmin to manage your MySQL database(s).

Return To Top of Page
 
  Accessing MySQL Through Encrypted Telnet (SSH)
More expert users may prefer to access MySQL by establishing a direct connection through Encrypted Telnet (SSH). Unlike when using the user-friendly phpMyAdmin web-based interface, this requires a knowledge of raw MySQL commands.

Once you have established a Encrypted Telnet (SSH) connection to your account (this can most easily be done through your Control Panel) you will need to enter the following command at the bash$ shell prompt:

/usr/bin/mysql username_databasename -u username -ppassword

(replacing the items in italics with your own account username, the MySQL database password and the name of the database you wish to access, as appropriate)

Return To Top of Page
 
  Associating Users With MySQL Databases
Your MySQL databases can always be accessed using your main account user name and password.
However, when accessing databases from PHP or Perl/CGI scripts, it is good practice to instead associate a "User" with any database(s) you need to access. You can then access the database(s) using the User name and password of the associated User. This has two advantages over simply using your main account user name and password.
Firstly, and most importantly, doing it this way avoids the need to include your main account password in any scripts. Although your password should get stripped from any properly coded PHP or Perl/CGI files by the server before they reach the Internet, there is obviously an increased security risk from having your main account password included in unecrypted form within script files in your account space.
Secondly, as explained in detail in the Understanding MySQL Database Passwords section below, changing your main account password can cause any scripts which make use of this password to fail until they too are manually updated to include the new password.

You can create a MySQL User using the MySQL Databases option in your Control Panel. Simply enter a User name and password for the new User in the appropriate boxes in the Users section of the menu, and click the Add User button. For security reasons, you should use a different password to your main account password.
As with database names, when you create a User the system will automatically prefix the User name with your main account user name, giving a User name of the form accountusername_name. If you are unsure, you can easily check the full names of any databases or Users you have created, as they will be displayed on the main database menu screen.
Before you can make use of this new User you need to associate it with one or more MySQL databases. To do this, return to the main database menu and select this User in the User: drop-down box in the Databases section of the menu. Then select the database you wish to be able to access through this User in the Db: drop-down box and click the Add User to Db button.
If all goes well, you will see a message saying that the User has been added to the specified database. When you return to the main database menu you should now see the User listed beneath the associated database, along with sample code for connecting to the database through this User from PHP and Perl/CGI scripts.

As an example, suppose that your account user name were myaccount and you created a User named myuser and associated this User with a database named mydatabase.

Then you could access this database either using your main account details...

Host Name: localhost
Database: myaccount_mydatabase
User Name: myaccount
Password: Your main account password

Or, by connecting through the associated User, using...

Host Name: localhost
Database: myaccount_mydatabase
User Name: myaccount_myuser
Password: The password you specified when you created the User


Return To Top of Page
 
  Accessing MySQL Databases From PHP Scripts
All Efficient Hosting accounts support the coding of web pages in the popular and powerful PHP4 scripting language. PHP4 has been designed to interact seamlessly and smoothly with MySQL databases, making it the natural choice when coding the "front end" for MySQL database driven sites.

The coding necessary to access a MySQL database from a PHP script is best illustrated with an example. In order to merely display the information in your database you might use code something like the following:
<HTML><BODY>
<P>Test output from my database:</P>
<TABLE BORDER=1>
<?
mysql_connect("localhost", "myaccount_myuser", "userpassword") or die('Unable to connect to the database.');
$result = mysql("myaccount_mydatabase", "select * from tablename");
$num = mysql_numrows($result);
$i = 0;
while($i < $num) {
echo "<TR><TD>";
echo mysql_result($result,$i,"nameoffield1");
echo "</TD><TD>";
echo mysql_result($result,$i,"nameoffield2");
echo "</TD><TD>";
echo mysql_result($result,$i,"nameoffield3");
echo "</TD></TR>\n";
$i++;
}
?>
</TABLE>
</BODY></HTML>
This example code assumes that you have associated a User with your database, as explained above. You will need to replace myaccount with your own account user name, and replace myuser and userpassword with the name and password of the associated User.

Please be advised that we do not allow the use of persistent connections to MySQL databases. Persistent connections can give a small speed improvement for some scripts, especially for very busy sites. But, in a shared hosting environment, this advantage is outweighed by the risk of MySQL connections not getting released when they should, due to scripting or other errors.
Conveniently, most third party scripts will default to not using persistent connections unless you specifically configure them to do so.

For more on PHP please refer to the PHP Homepage at http://www.php.net.
For more on accessing MySQL from PHP scripts please refer to the various resources in the Related Links section below.

Return To Top of Page
 
  Accessing MySQL Databases From Perl Scripts
All Efficient Hosting accounts support the coding of web pages using the CGI scripting interface. CGI scripts are usually written in the powerful Perl programming language.

The coding necessary to access a MySQL database from a Perl CGI script is best illustrated with an example. In order to merely display the information in your database you might use code something like the following:
#!/usr/local/bin/perl

use DBI;

$dbh = DBI->connect("DBI:mysql:myaccount_mydatabase:localhost", "myaccount_myuser", "userpassword") or die "Unable to connect to the database, $!";
$sql = "select * from tablename";
$sth = $dbh->prepare($sql);
$sth->execute;

print "Content-type: text/html\n\n";
print "<HTML><BODY>\n";
print "<P>Test output from my database:</P>\n";
print "<TABLE BORDER=\"1\">\n";
while (@res = $sth->fetchrow_array) {
print "<TR><TD>$res[0]</TD><TD>$res[1]</TD><TD>$res[2]</TD></TR>\n";
}
print "</TABLE>\n";
print "</BODY></HTML>\n";
This example code assumes that you have associated a User with your database, as explained above. You will need to replace myaccount with your own account user name, and replace myuser and userpassword with the name and password of the associated User.

As when using PHP scripts, we do ask that you avoid the use of persistent connections to MySQL databases.

For more on Perl and CGI please refer to our Perl/CGI documentation.
For more on accessing MySQL from Perl/CGI scripts please refer to the various resources in the Related Links section below.

Return To Top of Page
 
  Importing Data Into MySQL Database Tables
When you first add tables to a MySQL database these tables will, of course, be empty of any data.
There are many different ways of adding data to tables, depending on the purpose of the table.

Records may be added manually, one at a time, using phpMyAdmin, or even using raw MySQL commands in an Encrypted Telenet (SSH) session.
Data may be written to the table dynamically by a PHP or Perl/CGI script, such as a MySQL database driven forum script.

In other cases, there may already exist a list of records which you need to import into a database table. This pre-existing list of records may have been created using another application such as a database or spreadsheet program running on your local computer.
Such a list of records may be imported into a MySQL table by reading them in from a text file. The values for each field should be listed on one line of the file per record.

The simplest way to import data into a MySQL table from a text file is to use phpMyAdmin. Once logged in to phpMyAdmin, click on the appropriate table name and then on the Insert data from a textfile into table link, which you will find about half way down the right-hand frame.
Use the Browse... button to locate the text file on your local computer which contains the data to be added to the database table.
Check the Replace check box if you wish to overwrite any matching records already in the MySQL table.
Set the Fields terminated by entry to whatever character is used in your text file to separate neighbouring field values. Common characters for separating fields are tabs (which should be specified as \t) and commas.
If your text file uses quotes (or some other character) around text values (or all fields) you will need to enter this character in the Fields enclosed by box. This can often be left blank (deleting any character suggested by phpMyAdmin).
In most cases, the Fields escaped by box should be left blank (deleting any character suggested by phpMyAdmin).
If you are connecting from a Windows based machine, the Lines terminated by entry should normally be set to \r\n (the default suggested by phpMyAdmin). Windows uses both a carriage return character (\r) and a newline character (\n) to terminate the lines of text files.
If your text file contains values for every field, listed in the same order as the fields are defined in the MySQL table, then the Column names box can be left blank.
Finally, click Submit to upload the data file to the server and the records will be automatically appended to the table.

Using phpMyAdmin for importing text files does have the disadvantage that the text file must be uploaded to the server at the same time as it is decoded and stored. This is fine for small data files but for very large text files of data it can be better to use an FTP program to upload the text file to your account space first, as a separate step. Be sure to upload the file using text/ASCII mode as otherwise the lines may not be terminated correctly which can cause problems when the data is imported from the file.

For example, if you upload a file called mydata.txt to the root folder of your account then you can copy the data into a table using the following MySQL command:

LOAD DATA LOCAL INFILE "/home/username/mydata.txt" REPLACE INTO TABLE tablename FIELDS TERMINATED BY ',';

(replacing username with your own account user name and tablename with the name of your MySQL table)

Drop the word REPLACE if you don't want to overwrite any matching records already in the MySQL table.
This example assumes that your fields are separated by commas, that values are given for all fields in the same order as they are defined in the table, and that text values are not enclosed in quotes (or any other characters).
If your fields are separated by tabs (for example), simply replace the TERMINATED BY ',' clause with TERMINATED BY '\t' (where \t is the standard escape code for a tab character).
If all fields are not included, or they are not included in the correct order, simply add a clause of the form (fieldname1, fieldname2, fieldname3, etc.) before the closing semi-colon of the command.
If some or all of your text fields are enclosed in " style quotes (for example), simply add the clause ENCLOSED BY '"' after the TERMINATED BY ',' clause. This will cause the quotes to be stripped out before the text values are stored in the database.

The above LOAD DATA command can be most conveniently typed into the "Run SQL query/queries" text box within phpMyAdmin. When running the command through phpMyAdmin, the "dump" file will need to be world readable (see Changing Permissions).
Alternatively, the command may be keyed directly into a MySQL command prompt after connecting to the MySQL database through an Encrypted Telnet (SSH) session.

Full documentation on using the LOAD DATA INFILE command is available from the MySQL home page.

Return To Top of Page
 
  Understanding MySQL Database Passwords
When a MySQL database is first created, the password for this database will automatically be set the same as your current account password.

If you should later change your account password (using the Change Password option in your Control Panel), all of your MySQL database passwords will automatically be changed to match the new account password. Any PHP scripts configured to use the old password will then not be able to access the database until they are updated to connect using the new password.
For this reason (and also for improved security) we recommend always associating a User with a database and using this User's password in any PHP or Perl scripts which need to access the database. Unlike the main database passwords, the passwords for Users associated with databases are not affected by changes to your account password. See Associating Users With MySQL Databases above.

Return To Top of Page
 
  Backing Up MySQL Databases
We backup all of our clients accounts on a daily basis and also on a weekly and monthly cycle. All MySQL databases are also backed up on daily/weekly/monthly cycles. However, please be aware that these automated backups are intended to supplement your own local backups rather than replace them. We strongly encourage you to keep an up to date copy of both the files in your account space and your MySQL databases (if any) on your local computer and to regularly backup your site (including any critical databases) to a storage medium such as floppy disk, ZIP disk, CD-RW, etc.

You can read more about backing up the files in your account space here.

MySQL databases are normally backed up to a "dump" file which contains the necessary MySQL commands to re-create from scratch both the structure of the database and the data it contains.

The easiest way to backup a MySQL database is to use phpMyAdmin. After entering phpMyAdmin (using the link at the bottom of the main MySQL Databases menu in your Control Panel) click on the name of the database you wish to backup in the left hand frame. Then check the "boxes" for Structure and data and Save as file in the View dump (schema) of database section of the menu in the right hand frame. Finally, click the Go button to download a "dump" file of the database to your local computer.

You can choose to backup only some of the tables in the database by selecting them in the list provided. If no tables are specifically selected then the entire database is backed up.

It can also be a good idea to select the Add 'drop table' option when backing up. This allows you to overwrite any existing (corrupt?) database tables when restoring from the backup file, without needing to manually delete any pre-existing tables first.

If you should need to restore a database from a backup, this can also be done using phpMyAdmin. If the database doesn't already exist you should first create it in the normal way. If you didn't select the Add 'drop table' option when the "dump" file was created you will need to delete any pre-existing tables of the same name as tables in the backup, in order to avoid errors occurring. If necessary, you can delete all existing tables from a database by selecting the box next to every table and then clicking the Drop button.

As when backing up, begin by entering phpMyAdmin and clicking on the name of the database you wish to restore. Then click the Browse... button for the Location of the text file in the Run SQL query/queries section of the right hand frame menu, and locate the backup file on your local computer. Finally, click the Go button to re-create the database structure and contents from the backup. phpMyAdmin will let you know whether the restore was successful.

Using phpMyAdmin for backing up (and, if necessary, restoring) databases does have the disadvantage that the "dump" file must be transferred from (or to) the server at the same time as it is retrieved from (or stored in) the database. This is fine for small databases but for very large database backups it can be better to use an FTP program to download (or upload when restoring) the "dump" file as a separate step.
Database "dump" files are really a kind of text file (containing a list of MySQL commands) so when downloading or uploading a "dump" file, be sure to transfer the file using text/ASCII mode, as otherwise the lines may not be terminated correctly.

For example, you can backup a database to a "dump" file called dbdump.sql, to be created in the root directory of your account, by keying the following command while connected to the server through an Encrypted Telnet (SSH) session:

mysqldump username_databasename -u username -ppassword > /home/username/dbdump.sql

(replacing username and password in the above command with your main account user name and password)

You can then download the dbdump.sql database "dump" file from your account space to your local computer using your favourite FTP program.

If you want to be more ambitious, you can even use the mysqldump command in a Cron job to automatically back up your database to a "dump" file within your account space on a regular basis. For security reasons, you should avoid using your main account user name and password in a Cron job or any other script. Instead, we recommend that you associate a User with the database and use the password for this User in any scripts, as explained above.

You can, of course, also restore a database from a backup file following a similar two step process, using FTP to transfer the backup file from your local computer back to the server, and then reading the data back into the MySQL database from the "dump" file.
The database should already exist and, unless your "dump" file includes 'drop table' commands, it should ideally be empty.

For example, if you upload a database backup file called dbdump.sql to the root directory of your account then you can restore a database from this backup by keying the following command while connected to the server through an Encrypted Telnet (SSH) session:

/usr/bin/mysql username_databasename -u username -ppassword < /home/username/dbdump.sql

(replacing username and password in the above command with your main account user name and password)

Return To Top of Page
 
  Further Reading And Related Links
Our documentation on using phpMyAdmin to administer your databases:
phpMyAdmin

Official MySQL site:
http://www.mysql.com

Setting up a MySQL Based Website (using Perl):
http://www.linuxplanet.com/linuxplanet/tutorials/1046/1/

Complete List Of MySQL Related PHP Commands:
http://www.php.net/manual/en/ref.mysql.php

Return To Top of Page
 
Home : Order : Plans & Prices : Domains : Demo : FAQ : Support : Feedback : Contact Us : Affiliates : Terms

Copyright © 2000-2004 Efficient Hosting. All rights reserved.
Reproduction in whole or in part is strictly prohibited.