0

My dump:

mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql

My restore

mysql --host=localhost --user=root --password=whatever -Dtargetdatabasename < out.sql

As I am restoring a single database, I need to make sure that if it already exists it is deleted and if it does not exist I must create it (empty) in order to import my dump file without errors. How do I do this, from the cmd console with batch? Thanks

Update Solved:

mysql --host=localhost --user=root --password=whatever -e "DROP DATABASE IF EXISTS database_name";
mysql --host=localhost --user=root --password=whatever -e "CREATE DATABASE IF NOT EXISTS database_name";

if someone has a better idea to publish it, to select the answer as correct, otherwise you can vote for close

Rick James
  • 78,038
  • 5
  • 47
  • 113
acgbox
  • 157
  • 1
  • 7

1 Answers1

3

mysqldump has a --add-drop-database exists as a mysqldump.

If you use the --databases option and --add-drop-database the DROP DATABASE IF EXISTS SQL as become comes in the dump. --add-drop-database won't have an effect on a single database that is specified without the --databases option.

Example of generated output:

$ mysqldump -u root -S /var/lib/mysql/mysql.sock --add-drop-database  --databases test

-- MariaDB dump 10.19  Distrib 10.5.13-MariaDB, for debian-linux-gnu (x86_64)


-- Host: localhost    Database: test




-- Server version   10.5.11-MariaDB


/!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 utf8mb4 /;
/!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 /;


--


-- Current Database: test


/!40000 DROP DATABASE IF EXISTS test/;


CREATE DATABASE /!32312 IF NOT EXISTS/ test /!40100 DEFAULT CHARACTER SET latin1 /;


USE test;


--


-- Table structure for table t1


DROP TABLE IF EXISTS t1;
/!40101 SET @saved_cs_client     = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE t1 (
  i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/!40101 SET character_set_client = @saved_cs_client /;

So for the case asked in the question the backup becomes (as a single line):

mysqldump --host=localhost --user=root --password=whatever
     --single-transaction --routines --triggers
     --log-error=error.txt 
     --add-drop-database --databases mydatabase > out.sql

The restore becomes:

mysql --host=localhost --user=root --password=whatever < out.sql

No -Dtargetdatabasename is needed because the generated SQL will have a use mydatabase to select that database already during the restore. This also means that a restore needs to be of the same database name. If you need something else you can edit the sql file.

danblack
  • 7,719
  • 2
  • 10
  • 27
  • Your answer is very interesting, and I appreciate the explanation and I will give it a "useful". But for the purposes of my question it is not related, since your answer is for linux, and mysqldump does not import the database (it only exports) and what interests me is the import of the file in .sql format by cmd – acgbox Aug 26 '21 at 17:21
  • It outputs the DROP DATABASE IF EXISTS and CREATE DATABASE IF NOT EXISTS syntax you where looking for so it don't need to be done manually on import. – danblack Aug 26 '21 at 21:02
  • Here's the point: I'm a newbie on this topic and your answer is unintelligible to me. If you can rephrase it by giving clear examples of how I apply this in a batch script, I'll consider it. – acgbox Aug 26 '21 at 22:58