4

I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump.

Currently this does not include the USE 'db_name' statement in the output file, something which I need to be included.

Here's an example the dump command I'm using:-

mysqldump -u root db_name tbl_1 tbl_2 tbl_3 > output.sql

I found this question here, which describes the same issue I'm having, however the provided answer does not work for me. The USE 'db_name' statement is simply not written to the outputted SQL file.

Here's everything from one of my dumped SQL files, upto where it starts the DROP/CREATE table commands:

-- MySQL dump 10.13  Distrib 5.5.16, for Win64 (x86)
--
-- Host: localhost    Database: db_name
-- ------------------------------------------------------
-- Server version   5.5.16-log

/*!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 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

If anyone knows a solution to this, or why the solution in the other question isn't working for me, I'd really appreciate it.

Thanks!

1
  • 1
    ... the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.
    – Zoredache
    Jan 29, 2013 at 17:34

1 Answer 1

6

Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.

Your Answer

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

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