Knowledgebase : MySQL Server

This article goes over exporting a database in phpMyAdmin.

 

  1. Log into Control Panel.
  2. Once logged in, go to your domain \ databases
  3. Select the database
  4. Click on WebAdmin
  5. Click the Export tab at the top of the page for the selected database.
  6. The options to export are shown. By default, the export will create a .sql file which can be reimported into any database server. Additionally, the Save as File option at the bottom of the page is checked and you can choose to compress the database file if it is a database with a large amount of data.

How to Export or Backup or Dump A MySQL Database

 

To export a MySQL database into a dump file, simply type the following command syntax in the shell. You can use Telnet or SSH to remotely login to the machine if you don’t have access to the physical box.


mysqldump -u username -ppassword database_name > dum.sql


Replace username with a valid MySQL user ID, password with the valid password for the user

 

No space after -p and the password, else mysqldump will prompt you for password yet will treat the password as database name, so the backup will fail) and database_name with the actual name of the database you want to export. Finally, you can put whatever name you like for the output SQL dump file, here been dump.sql.

 

The while data, tables, structures and database of database_name will be backed up into a SQL text file named dump.sql with the above command.

 

How to Export A MySQL Database Structures Only

If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures. For example, the syntax is:

 

mysqldump -u username -ppassword –no-data database_name > dump.sql


How to Backup Only Data of a MySQL Database

If you only want the data to be backed up, use –no-create-info option. With this setting, the dump will not re-create the database, tables, fields, and other structures when importing. Use this only if you pretty sure that you have a duplicate databases with same structure, where you only need to refresh the data.

 

mysqldump -u username -ppassword –no-create-info database_name > dump.sql


How to Dump Several MySQL Databases into Text File


–databases option allows you to specify more than 1 database. Example syntax:

 

mysqldump -u username -ppassword –databases db_name1 [db_name2 ...] > dump.sql


How to Dump All Databases in MySQL Server

No dump all databases, use the –all-databases option, and no databases’ name need to be specified anymore.

 

mysqldump -u username -ppassword –all-databases > dump.sql


How to Online Backup InnoDB Tables

Backup the database inevitable cause MySQL server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

 

Syntax: mysqldump -u username -ppassword –all-databases –single-transaction > dump.sql


How to Restore and Import MySQL Database

You can restore from phpMyAdmin, using Import tab. For faster way, upload the dump file to the MySQL server, and use the following command to import the databases back into the MySQL server.

 

mysql -u username -ppassword database_name < dump.sql


The import and export of MySQL database not only is important to recover the data when disaster strikes, but also provides an easy way to migrate or move to another server, such as when switching web hosting providers. However, do note that one common problem – character set encoding. Newer release of mysqldump uses UTF8 as its default charset if nothing is specified, while older versions (older than 4.1 typically) use Latin1 as default characterset. If you database charset is Latin1 and dump in UTF8 collation, the data may ends up become simply rubbish, garbled, or unreadable (frequently happen with WordPress blog). If this case, use –default-character-set=charset_name option to specify the character set or convert the database to UTF8.

 

Source: http://www.mydigitallife.info/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial/

  1. Login to Control Panel https://control.DNN4Less.com/cp
  2. Under your subscription/domain go to “Databases”
  3. Click database name
  4. Click “Webadmin”
  5. Click “Import”
  6. Click “Browse” to select the database then click “Go”

You can import your backup .sql files through phpMyAdmin.

Importing:

  1. Log in to your MySQL database server via phpMyAdmin
  2. Select your database name on the left
  3. Click on the "Import" tab on top
  4. Click the "Browse" button and locate your .sql file
  5. Click the "Go" button on the bottom of the page to import the backup
Add dynamic to your site using MySQL, learn how to install PHPMyAdmin, create and administer new users and back up your database content with this quick start guide.