mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
1k views

Repair with keycache in mysql runs EVERY time. Can't even log why it's happening

I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements ...
1
vote
3answers
4k 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
3answers
21k 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 ...
2
votes
1answer
1k views

Can I mysqldump an entire database while using where='condition' on individual tables?

I have used mysqldump to dump an entire database, However, when I want to use --where=condition", on individual tables, I have dumped individual tables mysqldump --where='condition' mydb table ...
3
votes
2answers
3k 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 ...
2
votes
1answer
2k 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 ...
2
votes
1answer
350 views

How can I restore a single record from a MySQL backup file?

Occasionally we have an issue where a record's previous data might get wiped while editing. We backup our database every night for issues like this, however sometimes I am not available to do a ...
3
votes
2answers
10k 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 ...
3
votes
1answer
1k 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 ...
4
votes
2answers
2k 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 ...
2
votes
1answer
354 views

How to prevent high memory, CPU and time consumption by MySQL restore?

I have a local test machine with 2 GB RAM and a dual core processor. I imported a dump on that using mysql -uuser -psecret < script.sql script.sql is a 700 MB file which contains a single ...
3
votes
2answers
2k views

Taking mysql dump from another machine

I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. Both ae Linux ...
7
votes
6answers
9k 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 ...
3
votes
2answers
4k 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 ...
2
votes
2answers
323 views

mysqldump reimport isnt working correct

I am trying to restore this database with my sql file I have...yet just get this output with it not importing anything into my db. Thoughts? [root@domU-12-31-39-0B-06-AA data]# mysqldump ...
1
vote
1answer
421 views

How mysqldump replaces special characters during dumping records?

i am trying to dump a particular row using java. So that i retrieve the data by using record sets and i manually written sql script code by appending the dynamic records. But my problem is i could ...
1
vote
3answers
693 views

“Too many connections” when running MySQLDump

MySQL 5.5.x on Windows, 4GB RAM. I have a Wordpress Multi-Site install. The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now) I am running mysqldump ...
1
vote
1answer
335 views

Good strategy to replicate MySQL data

I'm working on MySQL database, well i have a master instance and slaves, and i need to add a new slave to Master. I adopt a simple strategy to do so: Stop slave, import data to new slave, and finally ...
3
votes
4answers
3k 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 ...
2
votes
1answer
1k views

importing myisam 5.0 database into a 5.5 innodb server

I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
8
votes
2answers
10k 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 ...
3
votes
1answer
4k 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?
5
votes
1answer
3k 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. ...
3
votes
1answer
527 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
6
votes
1answer
847 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 ...
1
vote
1answer
986 views

MySQL - MySqlDump uses \n instead of null

Using Windows with MySQL 5.1. I am running a script that uses mysqldump to backup a database. The mysqldump syntax used is: mysqldump --user=root --port=3306 --password=topsecret some_database ...
2
votes
3answers
1k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
1
vote
1answer
2k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...
6
votes
3answers
4k 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 ...
56
votes
5answers
94k 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?
2
votes
2answers
147 views

mysqldump seems to copy excessively

I have an old_server with 50 databases. I want to create new_server with just 3 databases. When I try using mysqldump, it seems like all the database directories (although they are empty on ...
11
votes
4answers
24k 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 ...
1
vote
1answer
90 views

mysql server password change while restoring

I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to ...
7
votes
3answers
8k 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?
1
vote
2answers
4k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
2
votes
1answer
1k 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
119 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
1
vote
1answer
2k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
3
votes
1answer
560 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 ...
5
votes
1answer
1k 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 ...
2
votes
1answer
2k views

When are we supposed to set default-character-set for the client?

I'm trying to understand when I'm supposed to set/force default-character-set for the client. The documentation states: You can force client programs to use specific character set as follows: ...
4
votes
4answers
1k 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. ...
2
votes
2answers
536 views

MySQL corruption even after full dump and re-import

We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over. The error message seem to relate to indexes so I assume the data itself is ok and when I ...
4
votes
1answer
967 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
2answers
2k 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 ...
2
votes
1answer
4k 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
810 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 ...
4
votes
1answer
2k 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
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 ...
1
vote
2answers
3k views

How do you encrypt data while performing a mysqldump?

My OS is windows 7. How can I encrypt data while performing a mysqldump? From the command prompt, can I take a mysqldump with encrypted varchar and texts values? Please help me. Thanks