Thesqldump not generating USE statement

backupMySQLmysqldump

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!

Best Answer

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.

Related Solutions

Mysql – Slow MySQL Database Import with thesqldump and USE .. SOURCE

For your dump command, you could use the following

mysqldump --single-transaction --extended-insert -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Extended inserts are better.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction The Single Transaction option is better for dumping InnoDB tables.

In your my.cnf file, make the following changes temporarily

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite

innodb_doublewrite=0

and also

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 2

Restart MySQL and then remove these options after your import and restart MySQL again.

Memory Tables

If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.

ALTER TABLE name_of_table ENGINE=InnoDB;

mysqlimport command

http://linux.die.net/man/1/mysqlimport

I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.

Dump the database using mysqldump

mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]

Import the data into your database.

mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.