|
|
 |
 |
|
 |
| |
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. |
|
 |
|
 |
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
|
| |
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 |
|
|
|
|
|
|
|
|
Customer Support Centre : Manual : MySQL Databases
