MySQL Databases

MySQL Workbench is a GUI for MySQL – it can be downloaded from http://wb.mysql.com. It provides a complete set of drag-and-drop tools to visually build, analyze and manage queries.

Plus, the integrated environment provides:

Query Toolbar to easily create and execute queries and navigate query history

Script Editor giving you control to manually create or edit SQL statements

Results Window so you can also easily compare and work with multiple queries

Object Browser enabling you to manage your databases, bookmarks, and history using a Web Browser like interface

Database Explorer where you can select tables and fields to query, as well as create and delete tables

Table Editor allows you easily create, modify and delete tables

Inline Help giving you instant help access to selected objects, parameters, and functions

The MySQL service for your site runs on the same server as your website, so use your domain (or the IP of your webserver) as the hostname, port 3306, your database name as the username and the corresponding database password as the password.

If your account type includes MySQL databases, then log in to your Web Hosting Plus Control Panel and select “MySQL Databases”, in the “Web Tools” section.

The database name will be the same as the username.

Backing up your MySQL database can be done in several ways:

1. Using the backup tool from the Web Tools section.
2. Using phpMyAdmin web interface
3. Using command line through shell access
4. Using MySQL Workbench

(1) Using our backup tool

Log into your Web Hosting Plus Control Panel and click on Web Tools. Click to configure MySQL Databases and click ‘Now’ under backup database. Save this file to your local machine.

(2) Using phpMyAdmin

Log into your Web Hosting Plus Control Panel and click on Web Tools. Click to configure MySQL Databases and choose the database that you want to manage. This will start phpMyAdmin. PhpMyAdmin can export your entire database by selecting the “Export” page.

1. Select all tables
2. Select “Structure and data”
3. Select “Add drop table”
4. Select “Enclose table and field names with backquotes”
5. Select “Save as file”
6. Then click “Go” button

You will be prompted to save the dump file on your computer.

(3) Using MySQL command line tools

To use MySQL command line tools, you will need SSH access to be enabled on your hosting account. You can perform backup using the following command:

mysqldump -h servername -u username -p –add-drop-table –create-options –skip-add-locks -e –set-charset –disable-keys -Q databasename > databasedump.sql

To backup and compress your dump file:

mysqldump -h servername -u username -p –add-drop-table –create-options -e –set-charset –skip-add-locks –disable-keys -Q databasename | gzip > databasedump.sql.gz

The commands above will create the dump file in your current directory. You can download this backup file using scp or ftp for your archival purposes. If you perform this command in the public_html directory (or if you move the file into the public_html directory), you will be able to download this file from the web.

(4) You can download MySQL Workbench from http://wb.mysql.com – this will allow you to connect to the MySQL process on the server directly and create a local backup of the data.

There are numerous ways to upload a MySQL database, we would strongly recommend trying methods 2 and 3.

Method 1 – phpMyAdmin

You may use phpMyAdmin to load your database by using the Import link to upload a .sql file or by pasting the SQL script into a query window and executing it. For the latter method –

1. Login to phpMyAdmin with a MySQL user that has been given DBA rights to the MySQL database you wish to load.
2. Then click on the SQL tab.
3. Paste the SQL script content into the text area, and click Go.

If the SQL script contains a lot of data, you might still get away with this by pasting it bit by bit. If the data is simply too large, see the other sections.

Method 2 – Web Hosting Plus control panel restore

In the Web Hosting Plus control panel go to the MySQL Databases page.
On this page is the text “To restore a database which you’ve previously backed up, click here.”
If you follow the link, you can upload a larger SQL file than would be possible in phpMyAdmin.

If the SQL file is very large, please consider one of the other methods below

Method 3 – MySQL Administrator

MySQL offer free software to manage, backup and restore MySQL databases. The software can restore very large files, and also comes packaged with MySQL Query Browser, which can be used a faster alternative to phpMyAdmin
You can download the software at https://dev.mysql.com/downloads/workbench/

Method 4 – MySQL command line client

This requires you to have SSH Access, which may or may not be available on your particular package.
Due to the limitations imposed by PHP, phpMyAdmin is unable to load a large MySQL dump data.

You can upload the MySQL dump file created by mysqldump –opt -Q dbname to your home directory, and then execute the MySQL client to load it.

To load your sql dump using MySQL, you can use the following command:

mysql -h mysqlhost -u mysqlusername -p databasename < dumpfile.sql or cat dumpfile | mysql -h mysqlhost -u mysqlusername -p databasename Where: mysqlhost – The name of the server where your MySQL database is located. You can find this out from the Control Panel in the MySQL Database area.
mysqlusername – Your MySQL User name as created through the Control Panel.
databasename – This is the name of your database
dumpfile.sql – The file created by mysqldump

You can also choose to use Perl and the DBI Perl Module to access your database. A commented example:

#!/usr/bin/perl

use DBI;

# Connect To Database
# * The DBI interface to MySQL uses the method “connect” to make a
# * connection to the database. It takes as it’s first argument
# * the string “DBI:mysql:database:hostname”, where database is equal
# * to the name of your database, and hostname to the server that it’s
# * located on. The second and third arguments, respectively, should
# * be your account username and password. The connection is assigned.
# * to a variable that is used by most other methods in the module.
$database = “your database name”;
$username = “your database username”;
$password = “your database password”;
$hostname = “your database hostname”;
$db = DBI->connect(“DBI:mysql:$database:$hostname”, $username, $password);

# Execute a Query
# * executing a query is done in two steps. First,
# * the query is setup using the “prepare” method.
# * this requires the use of the variable used to
# * initiate the connection. Second, the “execute”
# * method is called, as shown below.
$query = $db->prepare(“SELECT * FROM test”);
$query->execute;

# How many rows in result?
# * the “rows” method using the variable name the
# * query was executed under returns the number
# * of rows in the result.
$numrows = $query->rows;

# Display Results
# * the fetchrow_array method executed on the
# * query returns the first row as an array.
# * subsequent calls return the other rows in
# * sequence. It returns zero when all rows have
# * been retrieved.
while (@array = $query->fetchrow_array) { ($field1, $field2, $field3) = @array; print “field1 = $field1, field2 = $field2, field3 = $field3 \n”; }

# Cleaning Up
# * with the DBI module, it is a good idea to clean up by
# * explicitly ending all queries with the “finish” method,
# * and all connections with the “disconnect” method.
$query->finish;
$db->disconnect;

exit(0);

The DBI Module provides other methods you might find useful. More information is available by running “perldoc DBI” while logged on to your account via SSH.

MySQL is a Relational Database Management System. A relational database adds speed and flexibility, by storing data in separate tables rather than putting all the data in one area. These tables are linked by defined relations making it possible to combine data from several tables upon request. Using a RDMS means it is possible to add, access, and process the data stored in your database. ‘SQL’ stands for “Structured Query Language” – the most common standardised language used to access databases.

Why use MySQL?

MySQL is very fast, reliable, and easy to use. MySQL also has a very practical set of features developed in close cooperation with its users. It is also Open Source and therefore freely accessible. MySQL is used to access databases on the internet due to its connectivity, speed and security. It was originally developed to manage large databases at a much faster speed than the solutions that previously existed. MySQL has for several years, been thriving in the challenging areas of production.