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

2
  • 1
    The title does not agree with the "Update Solved" -- for the case where it starts out existing.
    – Rick James
    Aug 26, 2021 at 6:15
  • if you think it is not suitable, you can edit it. I would not know which title best suits the content
    – acgbox
    Aug 26, 2021 at 17:25

1 Answer 1

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.

3
  • 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, 2021 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, 2021 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, 2021 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.