mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

6
votes
1answer
2k views

How should I migrate a large MySQL database to RDS?

I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here: ...
3
votes
1answer
2k 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 ...
0
votes
4answers
1k views

How to do a mysqldump with a use database in the dump

So the command I normally use to get a database sent to another server is: mysqldump -u user -p --add-drop-tables database *file* But I always have to go into the file and add a line at the top: use ...
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
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
151 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
7
votes
2answers
3k views

Slow MySQL Database Import with mysqldump and USE .. SOURCE

I have about 12 tables in MySQL innoDB, one of them has 11 million records in it. I used this command to back things up: mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz ...
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 ...
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
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 ...
1
vote
0answers
81 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
1
vote
1answer
126 views

How export a database based in a primary key of a table?

So, I'm trying to export all the DB structure of my MySQL db. Attached is my DB structure, I have more then this, but I want these tables only. Basically Tools relate to Factsheets and Factsheets ...
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
3answers
2k views

how to use a different tmp dir for INTO OUTFILE select with MySQL?

I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). ...
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 ...
1
vote
2answers
1k views

Database table data transfer between different servers using bcp.exe and mysqldump

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY. Using MSSQL on two different servers I am using the following command ...
1
vote
1answer
157 views

Creating dump file with use command

I am taking backup of 2 tables from different schema. I need to create the "use dbname" statement at the beginning of each table. mysqldump --databases company --tables loadme --databases new_company ...
3
votes
2answers
646 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
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 ...
1
vote
2answers
138 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB ...
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 ...
4
votes
1answer
760 views

How does max_allowed_packet affect the backup and restore of a database?

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
4
votes
4answers
1k views

Exporting\Importing a large MySQL data without mysqldump

I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy. After running a simple --all-databases dump succeeds, but the import ...
1
vote
1answer
722 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 ...
1
vote
1answer
235 views

Mysql 5.1.X How to upload data from dump file without the partitioning commands?

I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning. How can I tell the mysql command line to ignore the partitioning ...
1
vote
2answers
806 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
876 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
93 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
335 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
98 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
168 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
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 ...
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 ...
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 ...
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
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
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 ...
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 ...
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
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?
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 ...
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 ...
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 ...
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
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 ...
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 ...