BackUp MySql Database from CommandLine

Question

I am able to backup mysql database via command line by executing the below :

C:\MySQL\MySQL Server 5.0\bin\mysqldump\" -uroot -ppassword sample > \"D:/admindb/AAR12.sql\"

But there is no DROP and CREATE database queries in my .mysql file

What should i add in the syntax to get the create info to my generated .sql file ?

    -- MySQL dump 10.11
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version   5.0.67-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `awss_red_force`
--
--

DROP TABLE IF EXISTS `awss_red_force`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `awss_red_force` (
  `int_scenario_id` int(11) NOT NULL,
  `str_entity_name` varchar(100) default NULL,
  `str_hla_type` varchar(30) default NULL,
  `str_parent_name` varchar(100) default NULL,
  `dbl_x` double default NULL,
  `dbl_y` double default NULL,
  `dbl_z` double default NULL,
  PRIMARY KEY  (`int_scenario_id`),
  KEY `awss_red_force_ibfk_1` (`int_scenario_id`),
  CONSTRAINT `awss_red_force_ibfk_1` FOREIGN KEY (`int_scenario_id`) REFERENCES `scenario` (`int_scenario_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `awss_red_force`
--

LOCK TABLES `awss_red_force` WRITE;
/*!40000 ALTER TABLE `awss_red_force` DISABLE KEYS */;
/*!40000 ALTER TABLE `awss_red_force` ENABLE KEYS */;
UNLOCK TABLES;

Pablo · Accepted Answer · 2010-06-11 07:29:17Z

2

You can use --add-drop-table and --create-options options.

--add-drop-table

Add a DROP TABLE statement before each CREATE TABLE statement.

mysqldump

Actually CREATE TABLE should be added even without any additional keys.

UPDATE:

content of backup.bat

C:\MySQL\MySQL Server 5.0\bin\mysqldump -uroot -ppassword sample >
                                                     D:/admindb/AAR12.sql
copy db_restore.sql+AAR12.sql restore.sql

Assuming that you create manually db_restore.sql and put all database-wide create/drop operations. The backup.bat should produce restore.sql with database drop/create and your table data.

edited Jun 11, 2010 at 7:29

answered Jun 11, 2010 at 6:26

Pablo

29.7k37 gold badges138 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Anuya Over a year ago

I want CREATE and DROP to be generated for database

Pablo Over a year ago

Sorry I misread the database part. There is no such built in option.

Anuya Over a year ago

@Michael, I mean to say that, the script does not have Drop Database and Create Database syntax. It has Drop Table and Create Table

Anuya Over a year ago

Pls take a look at the .sql file generated

Pablo Over a year ago

@srk: you can try using --all-databases --add-drop- database which will dump all your databases and add DROP DATABASE IF EXISTS .... However, if this is not suitable then alternatively you can just pre-create yourself those database create/drop statements and either use them as separate .sql files or concatenate in the batch file to final dump file.

|

Kerry · Accepted Answer · 2010-06-11 06:30:33Z

0

--add-drop-table

--create-options

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

answered Jun 11, 2010 at 6:30

Kerry

1862 silver badges7 bronze badges

1 Comment

Anuya Over a year ago

I want CREATE and DROP to be generated for database

thkala · Accepted Answer · 2012-01-07 01:43:56Z

private void TakeDBBackup() 
{ 
string tables = ""; 

// Displaying tables from apman database 
var cmd = 
new MySqlCommand( 
"select table_name from information_schema.tables where table_schema='apman' and table_type like '%TABLE'", 
ClientConnection); 
MySqlDataReader read2 = cmd.ExecuteReader(); 
while (read2.Read()) 

// Initializing tables into a varible string. 
tables += read2["table_name"] + " "; 
read2.Close(); 

// Displays current date and time name as folder name. 
DateTime backupTime = DateTime.Now; 
int year = backupTime.Year; 
int month = backupTime.Month; 
int day = backupTime.Day; 
int hour = backupTime.Hour; 
int minute = backupTime.Minute; 
int second = backupTime.Second; 

// Creating Dumpdata directory 
string dest = Directory.GetCurrentDirectory() + "\\Dumpdata\\"; 
Directory.CreateDirectory(dest); 
string src = dest + year + "-" + month + "-" + day + "-" + hour + "-" + minute + "-" + second + ".sql"; 
var file = new StreamWriter(src); 
string cmd1 = string.Format("--force --user=root --password=passd apman > {0} {1}", src, tables); 
var proc = new ProcessStartInfo(); 
proc.FileName = "mysqldump.exe"; 
proc.Arguments = cmd1; 
proc.RedirectStandardInput = true; 
proc.RedirectStandardOutput = true; 
proc.UseShellExecute = false; 
proc.RedirectStandardError = true; 
proc.CreateNoWindow = true; 
Process p = Process.Start(proc); 
string res = p.StandardOutput.ReadToEnd(); 
p.Start(); 
p.OutputDataReceived += (o, args) => File.AppendAllText(src, args.Data); 
file.WriteLine(res); 
//p.WaitForExit(); 
p.Close(); 
Console.WriteLine(@"Database backup is taken"); 
file.Close(); 
}

Collectives™ on Stack Overflow

BackUp MySql Database from CommandLine

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related