We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
405
votes
4answers
754k 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?
206
votes
11answers
169k 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 5.0....
173
votes
7answers
207k 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 ...
137
votes
13answers
248k 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, ...
79
votes
6answers
133k 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 ...
68
votes
4answers
92k views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
51
votes
3answers
66k 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 ...
46
votes
5answers
66k views

Why is DROP DATABASE taking so long? (MySQL)

New CentOS installation. I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to ...
41
votes
6answers
82k views

Restore mysql database with different name

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?
39
votes
6answers
232k 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 ...
38
votes
8answers
194k 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...
37
votes
7answers
66k 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 ...
35
votes
4answers
109k 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 ...
31
votes
3answers
41k 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 account. ...
31
votes
1answer
39k 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?
25
votes
1answer
14k views

How can I determine the size of a dump file prior to dumping?

How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
23
votes
1answer
16k views

Cannot locally import UTF-8 encoded SQL database on Mac

I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through: An error occurred when reading the file, as it could not be read in the ...
23
votes
8answers
51k views

Any option for mysqldump to ignore databases for backup?

We have 40 databases in our server. We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore ...
23
votes
2answers
29k views

Best practices for backing up a MySQL DB

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience ...
21
votes
6answers
33k 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 ...
20
votes
1answer
31k views

How can I mysqldump all databases except the mysql schema? [duplicate]

I want to backup all 200+ databases on a MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that?
20
votes
1answer
24k 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 ...
20
votes
7answers
32k views

How to convert a MySQL database to PostgreSQL?

Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.
19
votes
1answer
14k 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, ...
18
votes
5answers
36k views

mysqldump Error 2013

I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the 'maia_mail' table # mysqldump -u root -p maia > maia.sql mysqldump: Error 2013:...
17
votes
3answers
9k views

mysqldump vs mysqlpump

I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
16
votes
1answer
18k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
14
votes
1answer
21k views

What are the optimal mysqldump settings? [closed]

After some search I ended up with the following settings: mysqldump\ --host=localhost\ --port=3306\ --databases ****\ --user=****\ --password=****\ --default-character-set=...
14
votes
8answers
51k views

Error while restoring a Database from an SQL 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: $ >mysql -u root -p -h localhost -D database -...
14
votes
2answers
19k views

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES I am able to loging properly and able to take full backup from SQLyog Tool ...
14
votes
4answers
29k views

Mysqldump tables excluding some fields

Is there a way to mysqldump a table without some fields? Let me explain: I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of ...
14
votes
1answer
20k views

How to dump whole database from remote server on sequel pro

I have a database connection to a remote host on SequelPro Mac OSX. How can i dump whole database. I need a dump like what mysqldump command does: $ mysqldump --opt -u [uname] -p[pass] [dbname] > [...
14
votes
4answers
50k views

Taking MySQL dump from another machine

I have a MySQL db on Machine A and I want to access the mysqldump from machine B. Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What ...
12
votes
4answers
14k 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 ...
12
votes
2answers
7k views

Tool to export data with all relational data? [closed]

Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design? The purpose is to ease migrations of bits of data ...
11
votes
5answers
14k 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. ...
10
votes
4answers
28k views

Where does the mysqldump command store the backed up databases?

I am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful. Where does it place the backed up databases? I run the command like this: $ ...
10
votes
1answer
17k views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
10
votes
4answers
31k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
10
votes
1answer
3k 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 ...
9
votes
3answers
40k views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
9
votes
3answers
5k 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 --...
9
votes
1answer
5k 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 ...
9
votes
2answers
28k views

MySQL replication: 'Duplicated entry for PRIMARY key'

Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync. Basically 'mysqldump' was running almost whole night and then the ...
9
votes
1answer
30k views

Dump of a mysql table on import replaced existing records

I took a dump using mysqldump.. mysqldump -u... -p... mydb t1 > mydb_table.sql Then I imported the dump in another database having same table, but different records.. mysql -u...-p... mydb < ...
9
votes
2answers
11k views

mysql.proc keeps crashing, cannot do a mysqldump?

Due to some problems with InnoDB, I'm going to dump all databases to a new server: mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server The dump process stopped with ...
9
votes
2answers
15k views

How do I show warnings when loading data created from mysqldump?

I've got a large .sql file with large insert into ... values ... statements. Many of these statements are generating warnings during execution. How can I get mysql to print the warnings? If it hit ...
9
votes
1answer
4k views

Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin?

I know it's not necessary to backup the information_schema database, but what about the following databases (besides the user-defined databases)? performance_schema mysql phpmyadmin (So, in case of ...
9
votes
2answers
59k views

How to log verbose output from mysqldump?

You typically save the verbose output of a Linux command like this: # command > output.txt But when you use a command like mysqldump, the > option outputs the dump of the database tables to ...
9
votes
2answers
21k 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. ...