Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from serverfault.com Jan 29 '13 at 17:34

This question came from our site for system and network administrators.

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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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