How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?

Question

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

Answer 1

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.