Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
451
votes
4answers
830k 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?
239
votes
14answers
200k 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....
184
votes
7answers
231k 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 ...
148
votes
13answers
267k 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, ...
90
votes
5answers
121k 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. ...
89
votes
6answers
146k 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 ...
56
votes
3answers
68k 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 ...
51
votes
5answers
76k 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 ...
46
votes
6answers
96k 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?
45
votes
6answers
284k 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 ...
40
votes
8answers
211k 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...
38
votes
7answers
74k 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 ...
37
votes
4answers
121k 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 ...
34
votes
1answer
47k 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?
33
votes
3answers
45k 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. ...
28
votes
2answers
13k views

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql , but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) ...
27
votes
1answer
16k 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?
25
votes
1answer
36k 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?
25
votes
3answers
12k 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? ...
24
votes
1answer
19k 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 ...
24
votes
2answers
25k 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 ...
24
votes
1answer
28k 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 ...
24
votes
2answers
32k 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 ...
23
votes
9answers
59k 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 ...
21
votes
6answers
35k 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 ...
21
votes
7answers
37k 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.
21
votes
1answer
16k 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, ...
19
votes
5answers
42k 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
1answer
20k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
16
votes
4answers
58k 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 ...
16
votes
4answers
32k 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 ...
15
votes
1answer
22k 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
1answer
23k 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
57k 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 -...
12
votes
4answers
16k 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
3answers
45k 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. ...
11
votes
1answer
6k 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 ...
11
votes
2answers
68k 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 ...
11
votes
5answers
16k 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. ...
11
votes
1answer
30k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it....
11
votes
4answers
34k 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
4answers
32k 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
2answers
19k 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
1answer
6k views

Which are the minimum privileges required to get a backup of a MySQL database schema?

Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data. Are SELECT privileges required?
10
votes
2answers
27k 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. ...
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
4answers
25k views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
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
2answers
30k 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 ...

1
2 3 4 5
14