mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

3
votes
2answers
2k 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 ...
3
votes
1answer
8k 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
1answer
2k 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
2k views

mysql works, mysqldump doesn't

I have mysql 5.5 on my ubuntu 12.04 server. This command: mysql -u root -p works perfectly, but this gives me error: mysqldump -u root -p mydb_name > a.sql mysqldump: Got error: 2002: Can't ...
3
votes
2answers
5k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
3
votes
1answer
5k views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
3
votes
1answer
492 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?
3
votes
2answers
6k 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 ...
3
votes
3answers
2k 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
1answer
3k 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
2answers
1k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
3
votes
2answers
546 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
1answer
3k 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 ...
3
votes
2answers
3k 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
6k 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 ...
3
votes
2answers
1k 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
2answers
2k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
3
votes
2answers
8k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
3
votes
1answer
296 views

Backing up a 400-500 GB database on MySQL/Amazon RDS?

We have an Amazon RDS small instance for a MySQL datawarehousing database. Database size will be around 400-500 GB very shortly. What is the best way to backup our database? Can it be done ...
3
votes
1answer
448 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
2answers
873 views

php/mysql database redesign and migration: changing databases to tables

I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old ...
3
votes
2answers
6k views

Why is my MySQL dump file and my phpMyadmin export file different sizes?

I am looking to create a cron job to backup my MySQL database. I am using this command: $ mysqldump --user="User" --password="Password" -A --single-transaction > /home/user/Export-`date ...
3
votes
4answers
5k views

Is MySql locking table during backup and how to avoid?

I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the ...
3
votes
1answer
6k 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
1answer
352 views

how to verify mysql backup file

Is There any tool to verify the backup taken using mysqldump without manual restoration ? I have to take full backup daily so it is difficult for me to verify the backups daily by restoring them. As ...
3
votes
1answer
382 views

mysqldump freezing on a specific table

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command: mysqldump -uxx -pxx ...
3
votes
1answer
3k 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 < ...
3
votes
1answer
710 views

MySQLDump wrong dump

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for ...
2
votes
1answer
1k 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
1answer
3k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
2
votes
2answers
1k views

mysqldump: flush-privileges option

I'd like to get some clarification about the --flush-privileges option for mysqldump. Here's the description of the option from the MySQL docs: --flush-privileges Send a FLUSH PRIVILEGES ...
2
votes
2answers
507 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
3answers
61 views

How to dump only the MySQL database structure? [closed]

I am trying to take dump of MySQL database structure using the following commands: mysqldump -d -h localhost -u root -p databasename > dumpfile.sql mysqldump -d -h localhost -u root -p database ...
2
votes
2answers
2k 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. ...
2
votes
3answers
2k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
2
votes
1answer
2k views

How can I mysqldump all databases except the mysql schema?

I want to backup all databases on mysql server. I want to exclude the mysql schema. How can I do that ?
2
votes
1answer
210 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 ...
2
votes
1answer
1k views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
2
votes
3answers
1k views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
2
votes
1answer
186 views

Backup everything except one record

I want to backup my database except t1 table's one record (where option='siteurl'). I'm trying mysqldump's --where command. mysqldump -u root --skip-extended-insert --where "option='siteurl'" ...
2
votes
2answers
337 views

How does mysqldump take place when there are more than one database?

I'm writing a shell script wherein I'm doing a mysqldump of all databases. My questions are: Are the tables of all databases in the server locked during the mysqldump? If I have to do an ...
2
votes
1answer
889 views

Is there any risk associated with stopping mysqldump incomplete?

I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours. I used following command and options: mysqldump -u db -p user table --hex-blob | gzip> ...
2
votes
1answer
2k views

mysql db backup excluding specific tables

Are there any mysql scripts for taking a database backup by excluding specified tables? Also, what is the best way to take backup of a running mysql system?
2
votes
2answers
129 views

Mysqldump throwing error 'Couldn't execute 'ROLLBACK TO SAVEPOINT sp':

Can any one please explain what is happening with our DB dumps? We are executing dump as below mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX ...
2
votes
2answers
104 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
2
votes
1answer
199 views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
2
votes
1answer
532 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
2
votes
1answer
434 views

MySQL Dump/Import progress on Windows

I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd ...
2
votes
1answer
1k views

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the ...
2
votes
1answer
1k views

How to speed up a loading data into MySQL from a zipped archive

I dumped a large database to an archive with: mysqldump --compress --max_allowed_packet=500M -h myhost mydb | gzip > /tmp/mydb.sql.gz This took about 10 minutes to run, and generated a file ...