mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
2answers
1k views

Restore data from remote server using “.sql” file

I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...
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 ...
0
votes
1answer
99 views

Mysql dump restore doing some funny stuff

I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command [root@localhost /]# mysqldump -u root -p fms < /device.sql The funny part it keep ...
0
votes
1answer
368 views

Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error. This is the format I use: /mnt/temp# mysql -u username -p -h url_of_db_master -D ...
4
votes
1answer
110 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 ...
2
votes
1answer
182 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 ...
1
vote
1answer
2k 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 ...
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
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
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 ...
4
votes
2answers
11k 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
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 ...
3
votes
1answer
2k 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 ...
1
vote
3answers
753 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
365 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 ...
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 ...
3
votes
1answer
5k 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?
6
votes
1answer
959 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
1answer
4k 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
567 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 ...
1
vote
1answer
1k 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 ...
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? ...
2
votes
2answers
155 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 ...
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
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 ...
4
votes
4answers
2k 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
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 ...
1
vote
2answers
5k 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 ...
1
vote
1answer
3k 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
2answers
905 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 ...
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
3k 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: ...
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 ...
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 ...
4
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 ...
5
votes
1answer
4k 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 ...
6
votes
3answers
2k 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 ...
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 ...
2
votes
1answer
2k views

MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this: mysql -e ...
7
votes
1answer
5k 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, ...
3
votes
1answer
6k 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
6k 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 ...