Export and import MySQL databases

Overview

This article will show you how to export a copy of your MySQL database, and how to import an SQL database backup file to your database server. This is useful for making personal backups of your site, and for importing backups from other servers.

READ ME FIRST

This article is provided as a courtesy. Installing, configuring, and troubleshooting third-party applications is outside the scope of support provided by (mt) Media Temple. Please take a moment to review the Statement of Support.

Instructions

Method 1 - phpMyAdmin

You can use phpMyAdmin to import and export your database, as long as it is relatively small (10MB or less). If your database is larger than 10MB, please skip to Method 2 - command line.

First, log into phpMyAdmin. For instructions, please see: Managing a MySQL database with phpMyAdmin.

Once you're logged into phpMyAdmin, follow the steps outlined below.

Export

1. In phpMyAdmin, select your database from the list on the left.
   
2. Click on Export from the top set of tabs.
   
3. Click the radio button for "Custom" to view all available options. Select the tables from the list that you would like to back up. If you want to back up the entire database, click Select All.
   
4. By default, your database name will be used to name the exported database file. You can change this by entering your desired file name in the File name template box. You can also select a compression option (such as "gzipped") if you want to compress the backup before downloading it from the server.
   
5. Make sure radio button for "structure and data" is selected from the "Dump Table" option list. This should be the default setting.
   In the Structure section, check the box for "Add DROP TABLE / DROP VIEW / PROCEDURE / FUNCTION / EVENT statement" if you want this export to be able to replace existing tables of the same name. If you want to merge this backup with another database, do not select this.
   
6. Click the Go button to save the file to your local computer.
   

Import

1. Make sure the database you need has already been created. If it has not, please first create the database:
   • How do I create a database?
   CAUTION:

   If you import a backup file to a database that already has content, it will replace the existing content.
2. In phpMyAdmin, select your database from the list on the left.
   
3. Click on "Import" from the top set of tabs.
   
4. Click on the "Choose File" button.
   
5. Browse to your local SQL file and click "Open." If it is a zipped file, be sure to unzip the file first.
6. Click the "Go" button towards the bottom of the page. Wait while your database imports. Depending on the size, this can take a few minutes.
   
   You should get a message like this:
   "Import has been successfully finished, X queries executed."
   If you instead receive an error, please try the command line method below.

Method 2 - command line

This method works for all database sizes, including very large ones.

You must be able to log into your server with SSH. Please see this article for details: Connecting via SSH to your server.

Export

1. Log into your server via SSH.
2. Use the command cd to navigate to a directory where your user has write access. For example (replace00000 with your site number):

   cd /home/00000/data/

3. Export the database by executing the following command:

   
   mysqldump --add-drop-table -h internal-db.s00000.gridserver.com -u username -p dbname > dbname.sql
   

   Once you execute this command, you will be prompted for your database password. Type in the password and hit enter. Your database will now start exporting. When it is done, you will see the command prompt again. If it is a large database, this may take a few minutes.
   NOTE:

   The following variables need to be replaced with your own information:

   • -u username specifies the database username.
   • -p designates that you will be entering a password.
     • Database users on the (gs) Grid-Service shows you how to add a database username and password. The username will be formatted like db00000_name.
   • dbname is the name of the database you are trying to export. On the (gs) Grid-Service, the database name will be formatted like db00000_database.
   • dbname.sql is the name you want to give your backup file, and can be whatever you want.
   • -h specifies a host. The internal host name must be specified on the (gs) Grid-Service. It will be formatted like internal-db.s00000.gridserver.com.
   • Omit the --add-drop-table argument if you plan to merge this backup with an existing database when you import it. This option means the backup will totally replace the old database when it is imported.
4. You can now download the resulting SQL file. Connect to your server with FTP, navigate to the directory where you created the dump file, and download it.
5. If you created the SQL file in a web-accessible directory, such as your html folder, you should delete it after downloading a copy. Otherwise, anyone can download it from the web.

If you get an error that looks like this:

Got Error: 1045: Access denied for user 'db00000@internal-db.s00000.gridserver.com' (using password: YES) when trying to connect

This means you have entered an incorrect password. Please retype it carefully, or reset your password via the AccountCenter Control Panel. See Database users on the (gs) Grid-Service for instructions.

Import

1. Make sure the database you need has already been created. If it has not, please first create the database:
   • How do I create a database?
   CAUTION:

   If you import a backup file to a database that already has content, it will replace the existing content.
2. Use FTP to upload your SQL file to your server. You can upload it to your default FTP directory. Or, see Step 1 in the "Export" instructions above for another suggestion. Alternately, you can use scp to upload your file via SSH.
3. Log into your server via SSH.
4. Use the command cd to navigate into the directory where you uploaded your backup file in Step 1. If you uploaded the backup to your data directory, go here (replace 00000 with your site number):

   cd /home/00000/data/

5. Import the database by executing the following command:

   mysql -h internal-db.s00000.gridserver.com -u username -p dbname < dbname.sql

   OR:

   mysql -h internal-db.s00000.gridserver.com -u username -p dbname -e 'source dbname.sql'

   Once you execute this command, you will be prompted for your database password. Type it in and hit enter. Your database will now import. It may take a few minutes if you have a large database. When the import is done, you will be returned to the command prompt.
   
   
   NOTE:

   • Variables are the same as in Step 3 from the Export section above. Please check Step 3 in the "Export" section to make sure you are correctly replacing the example code with your own information.

   • dbname.sql is the actual name of your SQL file.

   • If you have a gzipped backup of your database, you can use this line instead:

   gunzip < dbname.gz | mysql -h internal-db.s00000.gridserver.com -u username -p dbname

   You can enter in your own username, database name, and backup file name, as before. dbname.gz is the name of your gzipped backup file. Use "unzip" instead of "gunzip" for zipped files.
6. Remove the SQL file from your web-accessible directory, if you uploaded it to a public folder. Otherwise, anyone can download it from the web.

If you get an error that looks like this:

Got Error: 1045: Access denied for user 'db00000@internal-db.s00000.gridserver.com' (using password: YES) when trying to connect

You have entered an incorrect password. Please retype it carefully, or reset your password via the AccountCenter Control Panel. See Database users on the (gs) Grid-Service for instructions.

If you get an SQL error during the import, you can force it to finish by adding "-f" to the command, which stands for "force." For example:

mysql -f -h internal-db.s00000.gridserver.com -u username -p dbname -e 'source dbname.sql'

This can help you finish an import if you have a few corrupt tables, but need to get the database as a whole imported before you do anything else.

Additional Resources

To automate your database backups, you may want to consider following this guide written especially for (mt) Media Temple database backups. (mt) Media Temple is not affiliated with this third-party website and takes no responsibility for its content.

Connecting via SSH to your server

Introduction

Secure Shell (SSH) is a UNIX-based command interface and protocol for securely getting access to a remote computer. SSH is actually a suite of three utilities - slogin, ssh, and scp - that are secure versions of the earlier UNIX utilities, rlogin, rsh, and rcp. SSH commands are encrypted and secure in several ways. Both ends of the client/server connection are authenticated using a digital certificate, and passwords are protected by being encrypted.

SSH allows you to connect to your server securely and perform Linux command-line operations.

Instructions

Enable SSH access

For security reasons, your (gs) Grid-Service comes with SSH access disabled by default. Here are instructions for enabling SSH access:

1. Log into your AccountCenter and click on your primary domain.
2. Click on Server Administrator.
   
3. Set the SSH option to Enabled and click Save.
   

NOTE:

This option enables SSH for the Server Administrator user only. This is the only (gs) Grid-Service SSH user with wide-reaching permissions. For information regarding SSH for sub-ftp users, please see this article.

Connect to the server

You will first need to choose an SSH client.

• Mac OS X includes the SSH client Terminal by default.
• Windows-based Operating Systems do not come with an SSH client by default. We recommend PuTTY. For information, please see: Using SSH in PuTTY (Windows).
• Linux distributions include support for SSH by default as well. Simply start up a terminal.

Login credentials

You should use the following information to log into the server via SSH (replace 00000 with your site number):

• Server: s00000.gridserver.com
• Username: example.com
• Password: This is the same as your Server Administrator FTP password. Read this article if you need to reset this password.

NOTE:

You can use "serveradmin@example.com" as your username if you want to, but it isn't necessary. If you do decide to use the longer version of the username, be sure to replace the @ symbol with % instead in the first part of the username, if you are using Terminal to connect. Otherwise, Terminal will misinterpret everything after the first @ as the name of the server.

So, if you are using Terminal, you should type:

ssh serveradmin%example.com@example.com

This syntax also works for sub-FTP SSH users.

If you are new to command-line interfaces, you may want to get familiar with some of the commands first.

It is standard to hit "Enter" after each command, to execute it after you finish typing.

PuTTY is a free software application for Windows 95, 98, XP, Vista, and 7 which can be used to make an SSH connection to your server. You can download the application at http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html.

For detailed instructions on using PuTTY, please read our article on SSH in PuTTY (Windows).

1. Open your SSH client.
2. Type:

   
   ssh example.com@s00000.gridserver.com
   
   

   OR

   
   ssh example.com@example.com
   
   

3. If this is your first time connecting to the server from this computer, you will see the following output. Accept the connection by typing "yes."

   
   The authenticity of host 'example.com (12.33.45.678)' can't be established.
   RSA key fingerprint is 3c:6d:5c:99:5d:b5:c6:25:5a:d3:78:8e:d2:f5:7a:01.
   Are you sure you want to continue connecting (yes/no)?
   
   

   
   yes
   
   

4. You will now be prompted to enter your password. Please note that you will NOT see your cursor moving, or any characters typed (such as ******), when typing your password. This is a standard Terminal security feature. Hit enter.
   
   
5. You can also copy and paste, using Command+V to paste.

   
   Password:
   
   

6. You are now logged into your (gs) Grid-Service via SSH. You should see output like this:

   
   The programs included with the Debian GNU/Linux system are free software;
   the exact distribution terms for each program are described in the
   individual files in /usr/share/doc/*/copyright.
   
   Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
   permitted by applicable law.
   example.com@n11:~$
   
   

   You can begin typing commands at the prompt.

10 JQUERY CAPTCHA PLUGINS WITH TUTORIALS

CAPTCHA is designed to recognize the difference between a bot and human and sometimes even malicious software to provide the security to your system. If you want to prevent the entry of bots into your website then you can start using the CAPTCHA as it is the smartest way to stop it.

By today i have collected 10 useful example captcha plugin using jquery, php, asp.net with tutorials and available Domo interface and source free download.

i hope this example will you to a create your website security, please kindly check out bellow:

1. JQUERY CAPTCHA SYSTEM WITH JQUERY & JQUERY UI

It is an easy-to-use because it no need to type the text security, it needs human action instead of to read a hard text and it is a very lightweight jQuery plugin.

DEMO

2. MOTIONCAPTCHA – DRAWING-BASED CAPTCHA WITH JQUERY

Captchas are usually hard to use and boring. However they help a lot in minimizing headaches on the application-side by making sure that “an action is performed by a human”.

DEMO

3. JQUERY FANCY DRAGGABLE CAPTCHA

Here is a jQuery example for a captcha in a registration form. In order to pass the “Are you human” test, the user has to drag the numbers from 1 to 6 into order.

DEMO

4. JQUERY ADVANCED AJAX VALIDATION WITH CAPTCHA

jQuery offers a plugin called validate which will allow you to create stunningly user friendly sign up forms, login addresses, contact forms and much more. The plugin is written and maintained by Jörn Zaefferer, a member of the jQuery team, lead developer on the jQuery UI team and maintainer of QUnit. In this tutorial I’m going to teach you how to create a signup form with advanced AJAX validation and CAPTCHA.

DEMO

5. RECAPTCHA STYLE CAPTCHA WITH JQUERY AND PHP

his tutorial is about to creating a captcha same as recaptcha. I used CSS and PHP for this. You can find few tutorials about creating and integrating captcha/recaptcha in php over 99points, but now you can create your own recaptcha style captcha with php and jquery.

DEMO

6. JQUERY VALIDATE PLUGIN PLUS PHP EQUALS CAPTCHA

When I build forms, my go-to validation is 9 out of 10 times its jQuery plugin: Validation. Keeping with that and their built in functionality to basically = something to something. I used that to my advantage and use PHP to create a simple captcha.

DEMO

7. PHP CAPTCHA CODE

In this tutorial you will be explained how to create a Captcha in PHP. You will be using some of the features available in PHP for creating an image. This is very simple and basic tutorial and we are not using any custom fonts for generating captcha image.

DEMO

8. JQUERY REAL PERSON

This plugin is designed to help overcome automated form submission by requiring a “real person” to identify text made up of dots. The entered value is compared on the server with the generated value to determine whether processing should continue.

DEMO

9. EASY CAPTCHA SETUP USING JQUERY/AJAX/PHP

it simple puts a human readable words which the use has to type into a box to prove they are human. There are many free ones out there and I have used a few and found the Google reCaptcha to be pretty easy to install and use.

DEMO

10. ASP.NET MVC CAPTCHA

MVC application that I have been working on required a CAPTCHA today. The problem is that all of the solutions out there, that I could find for ASP.NET, are control based and I wanted a more MVC approach.