mysqldump : MySQL's Standard Dump/Backup Utility
43
votes
7answers
23k views
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
34
votes
14answers
48k views
How can I move a database from one server to another?
How can I move MySQL tables from one physical server to another?
Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.
I want to move it to a new server, ...
20
votes
5answers
31k views
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
16
votes
4answers
21k views
MySQL DB import/export command line in Windows
How to export / import a database in MySQL through the command line?
MySQL system configuration, WampServer server installed.
OS: Windows
The advice for my local host, not an live hosting...
9
votes
3answers
10k views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
7
votes
2answers
3k views
How can I monitor the progress of an import of a large .sql file?
I am importing a 7 GB foobar.sql to restore a table in a local database.
$ mysql -h localhost -u root 'my_data' < foobar.sql
$ mysql --version
/usr/local/mysql/bin/mysql Ver 14.12 Distrib ...
7
votes
1answer
3k views
Does mysqldump export indices, by default?
I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:
--disable-keys, -K
which suggests, ...
7
votes
2answers
12k views
Safest way to perform mysqldump on a live system with active reads and writes?
I'm not sure if this is true but I remember reading if you run the following command in linux
mysqldump -u username -p database_name > backup_db.sql
while reads and writes are being made to a ...
6
votes
5answers
5k views
slow load speed of data from mysqldump
I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
6
votes
3answers
1k views
Users complain that system runs slow when mysqldump is in progress.
The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump ...
6
votes
1answer
2k views
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
6
votes
1answer
536 views
recover a single mysql database on a busy master-slave replicated system
Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.
I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
5
votes
3answers
12k views
How do I change the DEFINER of a VIEW in Mysql?
When I run mysqldump, I get an error:
mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES
This makes sense because foobar is a legacy ...
5
votes
1answer
743 views
Error on import of mysqldump file - Illegal double value found during parsing
I am currently getting the following error while trying to import / restore a mysqldump file:
ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308'
value found during parsing
I ...
5
votes
2answers
5k views
What is the recommended way to backup a MySQL/Amazon RDS database to S3?
I have two purposes for this:
To have an offsite backup in case of a region wide problem with Amazon Web Services.
To copy production data from a production billing account to a beta billing ...
5
votes
5answers
7k views
How to import a .sql file in MySQL?
I am trying to import a .sql file using MySQL Workbench and I get this error:
ERROR 1046 (3D000) at line 28: No database selected
I have first created an empty database called with the same name as ...
5
votes
1answer
985 views
How should I migrate a large MySQL database to RDS?
I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here:
...
4
votes
1answer
2k views
mysqldump with INSERT … ON DUPLICATE
I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate ...
4
votes
4answers
560 views
Exporting\Importing a large MySQL data without mysqldump
I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy.
After running a simple --all-databases dump succeeds, but the import ...
4
votes
1answer
2k views
Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.
...
4
votes
4answers
855 views
Incomplete mysqldump
I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB.
...
4
votes
1answer
76 views
For MySQL, is there tool to dump a set of rows from one tables and all related (by foreign key) rows from all related tables?
I'm using MySQL db.
I want to dump a set of rows from one table and if these rows have foriegn keys that point to rows on some other tables, I want to dump those too.
Then I want to load these dumps ...
4
votes
2answers
656 views
Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script
I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to ...
4
votes
1answer
548 views
What's the fastest way to replicate a MySQL database from one machine to another?
What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump?
If using mysqldump, is the fastest way
$ ...
3
votes
3answers
948 views
how to use a different tmp dir for INTO OUTFILE select with MySQL?
I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). ...
3
votes
2answers
1k views
Can mysql restore a single table from a large mysqldump?
What is the best way to restore a single table from a large (~5GB) database mysqldump file?
update:
I have found solutions (posted below) using command line tools to parse the table, but is there a ...
3
votes
3answers
2k views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
3
votes
3answers
1k views
Mysql backup strategies?
We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
3
votes
1answer
1k views
Restore dropped mysql database
I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years.
This year I did not do a backup and accidentally dropped the ...
3
votes
1answer
3k views
How do I open a MySQL Database Dump?
I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
3
votes
2answers
1k views
Slow MySQL Database Import with mysqldump and USE .. SOURCE
I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.
I used this command to back things up:
mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz
...
3
votes
3answers
4k views
Restore mysql database with different name
Hello Experts,
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods.?
regards,
Praveen
3
votes
1answer
1k views
Consistent logical backup of databases that use MyISAM and InnoDB engines
I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.
The mysqldump utility supports these two options:
--single-transaction - Creates a consistent ...
3
votes
1answer
503 views
how to import mysql database structure?
I have a database on Machine A. What I want is to dump my database in such a way that when I import it on another machine (Machine B) only the structure of the tables and Database are created.
I want ...
3
votes
1answer
541 views
MySQL dump restore - how to retain comments
So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.
Some of our evil triggers had a comment lines starting with the # character.
When I ...
3
votes
3answers
209 views
How to change data structure in mysql using mysqldump without deleting files
Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, ...
3
votes
2answers
185 views
mysqldump fails to dump database
I m using following sql query for creating db backup
mysqldump -u[username] -p[password] -h[hostname] [database] | gzip > [backup file path]
This generates zipped .sql file in path specified but ...
3
votes
2answers
526 views
Quick MySQL Backup
I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack.
Or mysqldump will work ...
3
votes
1answer
3k views
DROP PROCEDURE IF EXISTS not included in mysqldump
I'm dumping my stored procedures only using the following command:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql
but the ...
3
votes
1answer
115 views
adding another table to existing MySQL replication scenario
I'm running a simple MySQL replication scenario MASTER ➙ BLACKHOLE ➙ SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE.
Now I would like to add several more ...
3
votes
1answer
2k views
MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump
while trying to restore a MySql Dump, i get the following error:
ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes
any ideas?
3
votes
2answers
2k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
3
votes
2answers
1k views
mysqldump vs LOAD DATA INFILE
Why would you ever use mysqldump to repopulate a table over load data infile?
Here's the back story to my question. We had a table that was incorrectly partitioned. It was supposed to be ...
3
votes
1answer
543 views
How does max_allowed_packet affect the backup and restore of a database?
I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
2
votes
3answers
3k views
Where does the mysqldump command store the backed up databases?
Am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful , but where does it place the backed up databases ?
2
votes
1answer
53 views
Load different date formats in mysql
I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD.
Is there a way by which I can load DD-MON-YY ...
2
votes
2answers
225 views
MySQL, import SQL dump with another database
I have an SQL dump of db1 that contains this line:
"USE db1"
If I import that file into db2 with:
mysql -D db2 -uroot -p < /var/backups/db1.sql.gz
Is there the risk that db1 will be overriden?
...
2
votes
2answers
1k views
Why is mysqldump file so large?
We have a 2GB MySQL DB. We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here?
Questions
Why is this file so ...
2
votes
1answer
121 views
How to change the create and use statement in a large sql file without opening it
I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another ...
