First lets look at what is mySQL database?
MySQL is a relational database management system (RDBMS) which has more than 11 million installations. The program runs as a server providing multi-user access to a number of databases. MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now a subsidiary of Sun Microsystems, which holds the copyright to most of the codebase. The project's source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements.
Why use import or export of sql dump file (scenario)?
I have two MySQL databases located on a server somewhere. I connect via secure shell. I don't know all of the details about the configuration of this particular server but it obviously has MySQL installed/configured properly and you can assume that any other 'very likely' items would also be resident. I need to completley copy one database into the other (one is currently quite large, the second is empty).
And the easiest way to do this is use sql export to dump file and sql import of dump file to mySQL database.
How to create mySQL dump file (export database to sql file)?
The easiest way to export is use next syntax in command prompt (cmd):
mysqldump -u USER -p PASSWORD DATABASE > filename.sql
For example we have database with next parameters:
database username
baseu01
database password
h4z56s3
database name
database01
sql export file name
export.sql
Appropriate command line for export is:
mysqldump -u baseu01 -p h4z56s3 database01 > filename.sql
After executing export command you will have file "export.sql" in your folder.
Example how sql export dump file looks like:
-- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 26, 2007 at 07:14 AM
-- Server version: 4.1.21
-- PHP Version: 4.4.2
--
-- Database: `optimumd_search`
--
-- --------------------------------------------------------
--
-- Table structure for table `PLD`
--
CREATE TABLE `PLD` (
`ID` int(11) NOT NULL auto_increment,
`TITLE` varchar(255) NOT NULL default '',
`CACHE_TITLE` text,
`TITLE_URL` varchar(255) default NULL,
`CACHE_URL` text,
`DESCRIPTION` longtext,
`PARENT_ID` int(11) NOT NULL default '0',
`STATUS` int(11) NOT NULL default '1',
`DATE_ADDED` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
...
INSERT INTO `PLD` VALUES (1, 'Arts', 'Arts', 'Arts', 'index.php?c=1', '', 0, 2, '2007-, '');
INSERT INTO `PLD` VALUES (2, 'Business', 'Business', 'Business', 'index.php?c=2'01', 264,);
INSERT INTO `PLD` VALUES (3, 'Computers and Internet', 'Computers aernet', 'Compd_I ...);
INSERT INTO `PLD` VALUES (4, 'Games', 'Games', 'Games', 'index.php1', 88, 0, 0, '', '');
INSERT INTO `PLD` VALUES (5, 'Health', 'Health', 'Health', 'index.php?c=5'', 170, 0,, '');
INSERT INTO `PLD` VALUES (6, 'Home', 'Home', 'Home', 'index.php?c=6', '', 0, 0, '', '');
INSERT INTO `PLD` VALUES (7, 'Kids and Teens', 'Kids and Teens', 'Kids_and_Tee2-22 12:46 ...);
INSERT INTO `PLD` VALUES (8, 'News', 'News', 'News', 'index.php?c=8, 53, 0, 0, '', '');
INSERT INTO `PLD` VALUES (9, 'Recreation', 'Recreation', 'Recreati007-02-25 151', 118, ...);
...
So what to do with sql dump file? Simple... Use it as an archive if some disaster happends and you loose your database or windows server crashes - you can allways import sql dump file back in mySQL database.
How to import sql dump file to mySQL database?
The scenario: server crashes and you got mysql dump file stored on your hard drive. First you install mySQL database - then create database, database user and database password and then use next command line:
mysql -u username -p password database_name < filename.sql
If we use the same example as we used for export command line for export is:
mysql -u baseu01 -p h4z56s3 database01 < export.sql
Advanced options for exporting or importing a database
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.
Syntax:
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
To 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
mysql database import-export usage and linking to us
This tutorial can be used with windows server 2003, windows server 2000, windows XP and windows XP proffessional or even unix wervers - as long as mySQL database is installed on your computer.
If you find this tutorial to be usefull please add link to it import-sql-dump-file-to-mysql-database so other people will also find this tutorial page.
