I have database on a server with 120 tables.

I want to clone the whole database with a new db name and the copied data.

Is there an efficient way to do this?

Clone MySQL database

When I run a mysqldump command on my database and then try to import it, it fails as it attempts to create the tables alphabetically, even though they may have a foreign key that references a table later in the file.  There doesn&#39;t appear to be anything in the [documentation](http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html) and I&#39;ve found answers like [this](https://stackoverflow.com/questions/1382583/foreign-key-constraints-while-dumping-data/1382610#1382610) that say to update the file after it&#39;s created to include:

    set FOREIGN_KEY_CHECKS = 0;
    ...original mysqldump file contents...
    set FOREIGN_KEY_CHECKS = 1;

Is there no way to automatically set those lines or export the tables in the necessary order (without having to manually specify all table names as that can be tedious and error prone)?  I could wrap those lines in a script, but was wondering if there is an easy way to ensure I can dump a file and then import it without manually updating it.

Can you automatically create a mysqldump file that doesn&#39;t enforce foreign key constraints?

I wrote a script to backup my MySQL databases using:

    mysqldump --opt --all-databases -u user -pmypassword &gt; myDump.sql

A cron launches it every night and scp the result to another server.
`mypassword` appears in clear in my script, everyone can see it with the appropriate rights. I have been told about /proc issues too (where the cmd run can be seen).

MySQL documentation says:

&gt; Specifying a password on the command line should be considered insecure. See Section 7.6, &quot;Keeping Your Password Secure&quot;.

I have not found this magic 7.6 sections anywhere.

 What is the good practice to deal with automatic mysqldump and password security?

Mysqldump launched by cron and password security

I have a large SQL file with one database and about 150 tables.  I would like to use `mysqlimport` to import that database, however, I would like the import process to ignore or skip over a couple of tables.  What is the proper syntax to import all tables, but ignore some of them?  Thank you.

MySQL import database but ignore specific table

I need to do a mysqldump of a database on a remote server, but the server does not have mysqldump installed. I would like to use the mysqldump on my machine to connect to the remote database and do the dump on my machine.

I have tried to create an ssh tunnel and then do the dump, but this does not seem to work. I tried:

    ssh -f -L3310:remote.server:3306 user@remote.server -N

The tunnel is created with success. If I do 

    telnet localhost 3310

I get some blurb which shows the correct server mysql version. However, doing the following seems to try to connect locally

    mysqldump -P 3310 -h localhost -u mysql_user -p database_name table_name



how to mysqldump remote db from local machine

with the following statement:
    
    mysqldump --complete-insert --lock-all-tables --no-create-db 
    --no-create-info --extended-insert --password=XXX -u XXX 
    --dump-date yyy &gt; yyy_dataOnly.sql
I get INSERT statements like the following:

    INSERT INTO `table` VALUES (1,&#39;something&#39;),(2,&#39;anything&#39;),(3,&#39;everything&#39;);

What I need in my case is something like this:

    INSERT INTO `table` VALUES (1,&#39;something&#39;);
    INSERT INTO `table` VALUES (2,&#39;anything&#39;);
    INSERT INTO `table` VALUES (3,&#39;everything&#39;);


Is there a way to tell &quot;mysqldump&quot; to create a new INSERT statement for each row?
Thanks for your help!

MySQLDump one INSERT statement for each data row

I am trying to load a small sample of records from a large database into a test database.

How do you tell mysqldump to only give you n records out of 8 million?

Thanks

Limiting the number of records from mysqldump?

How can I access the MySQL command line with XAMPP for Windows?

Is there a way to restrict certain tables from the mysqldump command?

For example, I&#39;d use the following syntax to dump *only* table1 and table2:

    mysqldump -u username -p database table1 table2 &gt; database.sql

But is there a similar way to dump all the tables *except* table1 and table2? I haven&#39;t found anything in the mysqldump documentation, so is brute-force (specifying all the table names) the only way to go?

Skip certain tables with mysqldump

I know questions with this title have been answered before, but please do read on. I&#39;ve read thoroughly all the other questions/answers on this error before posting.

I am getting the above error for the following query:

    CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
      `menu_id` varchar(32) NOT NULL,
      `parent_menu_id` int(32) unsigned DEFAULT NULL,
      `menu_name` varchar(255) DEFAULT NULL,
      `menu_link` varchar(255) DEFAULT NULL,
      `plugin` varchar(255) DEFAULT NULL,
      `menu_type` int(1) DEFAULT NULL,
      `extend` varchar(255) DEFAULT NULL,
      `new_window` int(1) DEFAULT NULL,
      `rank` int(100) DEFAULT NULL,
      `hide` int(1) DEFAULT NULL,
      `template_id` int(32) unsigned DEFAULT NULL,
      `alias` varchar(255) DEFAULT NULL,
      `layout` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`menu_id`),
      KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Does anyone have idea why and how to fix it?
The catch is - this same query works perfectly on my local machine, and worked as well on my previous host. Btw.it&#39;s from a mature project - phpdevshell - so I&#39;d guess these guys know what they are doing, although you never know.

Any clue appreciated.

I&#39;m using phpMyAdmin.

#1071 - Specified key was too long; max key length is 1000 bytes

Here&#39;s the information I have:

I am working with a Linux based system using MySQL and PHP5.  I need to be able to generate a `mysqldump` from within a .php file, and then have that dump be stored in a file on the server in a location I would specify.

As I&#39;m a PHP nooblet, I&#39;d like someone to give me some assistance, guidance, or code, that would do what I require. This would have to be run remotely from the Internet.

Using a .php file to generate a MySQL dump

I am  extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:

    $ &gt;mysql -u root -p -h localhost -D database -o &lt; dump.sql
    ERROR: ASCII &#39;\0&#39; appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII &#39;\0&#39; is expected. Query: &#39;SQLite format 3&#39;.

I have tried putting `--binary-mode` in the ini file but it still gives the same error. What should I do? Please help. 

**UPDATE**

As suggested by Nick in his comment I tried `$ &gt; mysql -u root -p -h localhost -D database --binary-mode -o &lt; dump.sql` but it gave me the following `ERROR at line 1: Unknown command &#39;\☻&#39;.` 
It is a 500 Mb dump file, and when I view its contents using gVIM, all I can see is expressions and data which is not comprehensible. 

Enable binary mode while restoring a Database from an SQL dump

I am trying to do a mysql dump of a few rows in my database. I can then use the dump to upload those few rows into another database. The code I have is working, but it dumps everything. How can I get mysqldump to only dump certain rows of a table?

Here is my code:

&lt;pre&gt;&lt;code&gt;
mysqldump --opt --user=username --password=password lmhprogram myResumes  --where=date_pulled=&#39;2011-05-23&#39; &gt; test.sql

&lt;/code&gt;&lt;/pre&gt;



MYSQL Dump only certain rows

I want to copy a table&#39;s schema as well as the data within that table to another database table in another database on a live server.  How could I do this?


I want to copy table contained from one database and insert onto another database table

I&#39;m trying to export my database, using MySQL Workbench 6.0 on Windows, to send to my db instance in Amazon RDS, but i&#39;m getting this error:

    Operation failed with exitcode 7
    11:34:40 Dumping clubbin (taxicompanies)
    Running: &quot;C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe&quot;
    -defaults-extra- file=&quot;c:\users\selene\appdata\local\temp\tmp6o0hno.cnf&quot;
    --max_allowed_packet=1G --delayed-   insert=FALSE --host=localhost --user=root
    --port=3306 --default-character-set=utf8 &quot;clubbin&quot;   &quot;taxicompanies&quot;
    mysqldump: [ERROR] unknown variable &#39;delayed-insert=FALSE&#39;

Does anybody have any idea that can help me?

Export Data from mysql Workbench 6.0

I am having problems with getting a `crontab` to work. I want to automate a MySQL database backup.

The setup:

 - Debian GNU/Linux 7.3 (wheezy) 
 - MySQL Server version: 5.5.33-0+wheezy1(Debian) 
 - directories user, backup and backup2 have 755 permission
 - The user names for MySQL db and Debian account are the same

From the shell this command works

	mysqldump -u user -p[user_password] [database_name] | gzip &gt; dumpfilename.sql.gz


When I place this in a crontab using crontab -e 

	* * /usr/bin/mysqldump -u user -pupasswd mydatabase | gzip&gt; /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz &gt;/dev/null 2&gt;&amp;1

A file is created every minute in /home/user/backup directory, but has 0 bytes.
 
However when I redirect this output to a second directory, backup2, I note that the proper mysqldumpfile duly compressed is created in it. I am unable to figure what is the mistake that I am making that results in a 0 byte file in the first directory and the expected output in the second directory.

	* * /usr/bin/mysqldump -u user -pupasswd my-database | gzip&gt; /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz &gt;/home/user/backup2/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz 2&gt;&amp;1

I would greatly appreciate an explanation.

Thanks



mysqldump &amp; gzip commands to properly create a compressed file of a MySQL database using crontab

I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. `mysqldump --no-data` would almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?

mysqldump - Export structure only without autoincrement

Is it possible to import a single database from an --all-databases mysqldump? I guess I can modify the file manually but wondering if there are any command line options to do this.

I am moving servers and have a lot of databases, most of which I don&#39;t currently need or want at the moment but would like to have the option to restore a single one if need be.

Import single database from --all-databases dump

I have this huge, messy database I am cleaning up. It houses 500+ tables, which is the result of combining Magento Enterprise with Joomla in one single DB.

To make things worse, there is a set of 70+ Joomla tables that are not in use at all. These are all prefixed with `bak_`.

Just deleting these `bak_` tables will be easy, but I want to &#39;bak&#39; them up first (see what I did there?). In my mind I can picture a command like this:

    mysqldump -u username -p mydatabase bak_*

But this doesn&#39;t work. What would be the best way to do this? Thanks!

EDIT: Yes, I could explicitly list the 70 tables to include, or the ~430 tables to exclude, but I am looking for a better way to do this, if possible.

Mysqldump only tables with certain prefix / Mysqldump wildcards?

I was using mysqldump to export the database, like this:

    mysqldump -u root -ppassword my_database &gt; c:\temp\my_database.sql

Somehow, it only exports one table. Is there something I&#39;m doing wrong?

mysqldump exports only one table

I&#39;m confused how to import a SQL dump file.  I can&#39;t seem to import the database without creating the database first in MySQL.

This is the error displayed when `database_name` has not yet been created: 

`username` = username of someone with access to the database on the original server.  
`database_name` = name of database from the original server 

&lt;!-- language: none --&gt; 

    $ mysql -u username -p -h localhost database_name &lt; dumpfile.sql   
    Enter password:  
    ERROR 1049 (42000): Unknown database &#39;database_name&#39; 

 

If I log into MySQL as root and create the database, `database_name` 
 
    mysql -u root  
    create database database_name;  
    create user username;# same username as the user from the database I got the dump from.  
    grant all privileges on database_name.* to username@&quot;localhost&quot; identified by &#39;password&#39;;  
    exit mysql

then attempt to import the sql dump again:  

    $ mysql -u username -p database_name &lt; dumpfile.sql  
    Enter password:  
    ERROR 1007 (HY000) at line 21: Can&#39;t create database &#39;database_name&#39;; database exists

How am I supposed to import the SQL dumpfile?

Error importing SQL dump into MySQL: Unknown database / Can&#39;t create database

I&#39;m trying to create a simple structure only dump of my database. Using `mysqldump` gives me a result like:

    /*!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=&#39;NO_AUTO_VALUE_ON_ZERO&#39; */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

    DROP TABLE IF EXISTS `foo`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;

No matter what I try, I just can&#39;t seem to get rid of those comments.

I&#39;m currently using: `mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql`

**Edit:** I do however wish to retain other comments, such as `-- MySQL dump 10.13  Distrib 5.1.41, for Win32 (ia32)`

How can I get rid of these comments in a MySQL dump?

I have a MySQL user called *dump* with the following perms:
    
    GRANT USAGE ON *.* TO &#39;dump&#39;@&#39;%&#39; IDENTIFIED BY ...
    GRANT SELECT, LOCK TABLES ON `mysql`.* TO &#39;dump&#39;@&#39;%&#39;
    GRANT SELECT, LOCK TABLES ON `myschema`.* TO &#39;dump&#39;@&#39;%&#39;

I want to dump all data (included triggers and procedures) using the *dump* user. I call mysqldump in the following way:

    mysqldump -u dump -p --routines --triggers --quote-names --opt \
        --add-drop-database --databases myschema &gt; myschema.sql

Everything is OK with the dumped file except for the **triggers, they are missing**!!

The triggers are dumped correctly if I try mysqldump with _root_ MySQL user:

    mysqldump -u root -p --routines --triggers --quote-names --opt \
        --add-drop-database --databases myschema &gt; myschema.sql


So, I guess it is a perms issue... what are the **extra grants my _dump_ MySQL user needs for doing the full dump correctly?** 


Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

How do I instruct mysqldump to backup with column names in insert statements?  
In my case I didn’t a normal back up with insert sql’s resulting in  

    LOCK TABLES `users` WRITE;
    /*!40000 ALTER TABLE `users` 
    INSERT INTO `users` VALUES (1

structure.  

Now I went ahead and **removed a column** from the schema in users. After this when I run the backup sql’s I get a **column number mismatch error**.  

To fix this how do I go about instructing mysqldump to write column names too? 
Here is how I do it now

    mysqldump --host=${dbserver} --user=${dbusername} --password=${dbpassword} \
              --no-create-db --no-create-info --extended-insert --single-transaction \
              --compress tablename  

On a broader level, what’s the best practice to manage these schema changes? 




Mysqldump: create column names for inserts when backing up

While doing a MySQL dump is easy enough, I have a live dedicated MySQL server that I am wanting to setup replication on. To do this, I need dumps of the databases to import to my replication slave.

The issue comes when I do the dumps, MySQL goes full force at it and ties up resources to the sites that connecting to it. I am wondering if there is a way to limit the dump queries to a low priority state to which preference is given to live connections? The idea being that the load from external sites is not affected by the effort of MySQL to do a full dump...

How can I slow down a MySQL dump as to not affect current load on the server?

How can I backup a mysql database which is running on a remote server, I need to store the back up file in the local pc.

How to use MySQL dump from a remote machine

I&#39;m in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have the create database db_name including in the file, that way I just have to import the file to mysql without having to manually create the databases. Is there a way to do that?

mysqldump with create database line

So I can export only a table like this:

    mysqldump -u root -p db_name table_name &gt; table_name.sql

Is there any way to export only a portion of a table with mysqldump? For example, 0 - 1,000,000 rows, 1,000,000 - 2,000,000 rows, etc.

Should I do this with **mysqldump** or a query?

How to use mysqldump for a portion of a table?

I have an existing MySQL instance (test), containing 2 databases and a few users each having different access privileges to each database.

I now need to duplicate one of the databases (into production) **and the users** associated with it.

Duplicating the database was [easy][1]:

Export:

    mysqldump --no-data --tables -u root -p secondb &gt;&gt; secondb_schema.sql

Import:

    mysql -u root -p -h localhost secondb &lt; secondb_schema.sql


I didn&#39;t find, however, a straightforward way to export and import **users**, from the **command line** (either inside or outside mysql).

How do I export and import a [user][2], from the command line?

---------

**Update**: So far, I have found manual (and thus error prone) steps for accomplishing this:

    -- lists all users
    select user,host from mysql.user;

Then find its grants:

    -- find privilege granted to a particular user
    show grants for &#39;root&#39;@&#39;localhost&#39;; 


Then **manually** create user with the grants listed in the result of the &#39;show grants&#39; command above. 

I prefer a safer, more automated way. Is there one?

  [1]: https://stackoverflow.com/a/21091197/1864054
  [2]: https://stackoverflow.com/a/1135251/1864054




How to Export &amp; Import Existing User (with its Privileges!)

My application download mails over IMAP and stores them in a MySQL database. Earlier I was supporting mails size upto 10 MB and hence a &#39;mediumtext&#39; column to store the mail content was enough. Now I need to support mails upto 30MB. So I changed the datatype for the column to &#39;largetext&#39;. Yesterday a mail with size 25 MB was stored. After that whenever I execute mysqldump command it throws error:
&lt;pre&gt;
mysqldump: Error 2020: Got packet bigger than &#39;max_allowed_packet&#39; bytes when dumping table `ib_mailbox_backup` at row: 3369
&lt;/pre&gt;

Row 3369 contains the 25 MB mail.

In MySQL config I increased the &#39;max_allowed_packet&#39; from 64M to 512M and it still fails with the same error. Executing the mysqldump command on the same machine where MySQL server is running. How do I solve this? 

mysqldump error: Got packet bigger than max_allowed_packet&#39;

I&#39;m using mysqldump in a cron job to backup a database with over 2 million rows.

It creates a text file which can be used to restore the datalog from the command line.

I thought it would be useful to edit the dump before a restore as a *quick* way of changing values and table or column names - at least until I learn more and become confident about doing it with ALTER and UPDATE.

Editing large text files does not bother me, but I was surprised to find that in a **250 megabyte** dump of my database, **there were only about 300 lines**. Each line was something like 800k characters long.

Is there another way of generating dumps with more control over line length?

Or should I post-process the dump with tools like sed or Perl?



How to deal with enormous line lengths created by mysqldump

I am looking for the syntax for dumping all data in my mysql database. I don&#39;t want any table information.

mysqldump data only

I run the following command from the Windows command line to backup my database:

    ...\right_path\mysqldump --add-drop-database --databases my_database_name
                             --defaults-extra-file=d:\1.cnf

where `d:\1.cnf` contains the following:

    [client]
    user=&quot;my_user&quot;
    password=&quot;my_password&quot;

Unfortunately, I got the following error message:

    mysqldump: unknown variable &#39;defaults-extra-file=d:\1.cnf&#39;

If I do:

    ...\right_path\mysqldump --add-drop-database --databases my_database_name
                             --user=&quot;my_user&quot; --password=&quot;my_password&quot;

it works as expected.

What am I doing wrong ?

Problem with mysqldump: &quot;--defaults-extra-file&quot; option is not working as expected

This has been asked a few times but I cannot find a resolution to my problem. Basically when using mysqldump, which is the built in tool for the MySQL Workbench administration tool, when I dump a database using extended inserts, I get massive long lines of data. I understand why it does this, as it speeds inserts by inserting the data as one command (especially on InnoDB), but the formatting makes it REALLY difficult to actually look at the data in a dump file, or compare two files with a diff tool if you are storing them in version control etc. In my case I am storing them in version control as we use the dump files to keep track of our integration test database.

Now I know I can turn off extended inserts, so I will get one insert per line, which works, but any time you do a restore with the dump file it will be slower.

My core problem is that in the OLD tool we used to use (MySQL Administrator) when I dump a file, it does basically the same thing but it FORMATS that INSERT statement to put one insert per line, while still doing bulk inserts. So instead of this:

    INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES (887,&#39;0.0000&#39;),191607,&#39;1.0300&#39;);

you get this:

    INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES 
     (887,&#39;0.0000&#39;),
     (191607,&#39;1.0300&#39;);

No matter what options I try, there does not seem to be any way of being able to get a dump like this, which is really the best of both worlds. Yes, it take a little more space, but in situations where you need a human to read the files, it makes it MUCH more useful.

Am I missing something and there is a way to do this with MySQLDump, or have we all gone backwards and this feature in the old (now deprecated) MySQL Administrator tool is no longer available?

All Mysqldump Solutions on Gang of Coders

Total of 34 Mysqldump Solutions