mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

0
votes
1answer
180 views

Why does importing a MySQL dump cause so many I/O?

I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many ...
2
votes
1answer
134 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'" ...
1
vote
3answers
547 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
35
votes
3answers
16k 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 ...
0
votes
0answers
30 views

mysqldump: Got error: 0: when retrieving data from server

I get a the error in the header when the server tries to replicate itself after the backup completes. I have looked at error indexes, but failed to see an explanation for the error code 0. I was ...
0
votes
0answers
124 views

mysqldump & upload to mysql speed impact due to SSD and innoDb?

I am testing an AWS Medium Instance with CentOS (AWS AMI) and comparing it with Hetzner's EX40-SSD (http://www.hetzner.de/hosting/produkte_rootserver/ex40ssd). Software setup on both is comparable( ...
0
votes
0answers
39 views

taking backup of database on remote server gives error

I am currently on a Ubuntu machine (IP is 192.168.1.3) I have databases here I want to export all these databases to another machine in my network I used following command mysqldump --all-databases ...
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 ...
0
votes
0answers
54 views

Accidentally I had deleted a database from production server working with MySQL

I have its backup as a full and an incremental backup took using enterprise backup. Is it possible to restore a specific database or else what should I do as an immediate action?
3
votes
1answer
255 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 ...
5
votes
2answers
2k 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 ...
1
vote
2answers
2k views

How can I synchronize data between two RDS MySQL databases

I have 2 Amazon RDS MySQL instances, one is MySql v5.5 and the new one is 5.6. I want to upgrade the 5.5 to the 5.6 but you cannot do upgrades at this time, so we are trying to migrate the data. I ...
1
vote
1answer
68 views

Where Mysqldump gets data from

How mysqldump is processing backup? From which all files is it reads data for creating dump? Is the process differs if storage engines are different?
0
votes
0answers
45 views

Drupal database differential restore mysql

I have several Drupal sites hosted on my shared hosting server. Each site has a dev, test and production instances, with separate Docroots and Apache Vhosts and separate mysql db instances. ...
-3
votes
1answer
59 views

How can I move one table from one database to other database [closed]

I want to move one table in one database to other database using mysql dump on linux terminal How can I go about it ?
1
vote
1answer
1k views

passing --single-transaction in mysqldump command line tool

I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup. I have however, noticed that if I backup using command line ...
0
votes
1answer
279 views

Deadlock from mysqldump --single-transaction, is it possible?

Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ? If it is used to backup a live site with many other querys running at the ...
7
votes
6answers
11k 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 ...
2
votes
2answers
636 views

Tool to export data with all relational data?

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 ...
1
vote
1answer
284 views

mysqldump―output details

I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So ...
0
votes
1answer
100 views

MySQL data retained after OS reinstall

Something strange happened today. I reinstalled my OS and then when I reinstalled MySQL workbench some of my old data was still there. I think the actual local db is empty, but all the connections, ...
0
votes
1answer
86 views

Optimizing and maintaining test databases for a web development team

Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process ...
0
votes
2answers
273 views

How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

By data integrity, I mean: Schema integrity Data (record level) integrity Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be ...
1
vote
1answer
117 views

mysql: how to copy database and required dependencies

I need to set up an automated copy of a single mysql database (not the whole DBMS). If I use mysqldump to dump and recreate a DB, the copy may be unusable because the user accounts need to also be ...
0
votes
0answers
524 views

How to speed up a mysql restore from large dump file

I have a 350MB msyql dump file that takes over 3 minutes to load. I'd like to speed it up. I've tried using --opt with mysqldump (shaved off 30 seconds). The best I can achieve is 42 seconds faster. ...
1
vote
1answer
735 views

mysqldump: replace drop table in output with delete?

I am using mysqldump to dump some records in a table: mysqldump mydatabase mytable --where="field=value" this emits script that includes drop table mytable statement. Since I use the --where ...
0
votes
1answer
256 views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
0
votes
1answer
1k views

mysql duplicate entry error 1062 when restoring backup

Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this. Requirement: To create an exact replica 'db4' of an existing DB 'db3'. Procedure ...
2
votes
1answer
1k 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 ...
1
vote
0answers
291 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
1
vote
1answer
2k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
5
votes
7answers
28k 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 ...
0
votes
0answers
299 views

Trouble converting MySql Database to SQL Server

I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one ...
-1
votes
1answer
125 views

MySQL Migration from Slave to Master

I recently had a problem with our server. It required us to install a new hard drive, cpanel, and setup the old hard drive as a slave. How can I migrate the mysql database from the slave drive to the ...
2
votes
1answer
381 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
4k 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 ...
2
votes
1answer
276 views

Table optimize command crashed MySQL server

I have a large 5GB table on one of my MySQL databases that I wanted to optimize. I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a ...
0
votes
1answer
87 views

How we take dump with batch of records option?

we have 30 GB database.we want to take backup of this database. here the question is we want to take the backup batch of every 10k records. How we can take batch of records backup using mysqldump
3
votes
2answers
4k 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
334 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 ...
0
votes
1answer
391 views

Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
1
vote
1answer
277 views

Using a mysqldump export to create data with a different database name

I am using the following to import database using a mysqldump export: mysql -u root -p example_db < path/to/export.sql I would like to import the database to a database with a different name to ...
1
vote
1answer
194 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
1
vote
2answers
3k views

Issue on mysqldump in MySQL5.6

I did upgrade the MySQL server from 5.5 to 5.6 I am trying to take dump for a table like this mysqldump -uroot -p dba admin > D:\admin.sql But it gets following error mysqldump: Couldn't ...
1
vote
1answer
208 views

Unable to use mysql_upgrade:

I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says- mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
1
vote
1answer
2k views

mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command: mysqldump -uroot -p drupaSite > drupaSite.sql I get the following error: mysqldump: Got ...
2
votes
1answer
817 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 ...
3
votes
3answers
1k 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, ...
1
vote
2answers
1k views

how to import mysql system “mysql” database [closed]

This question might be already answered but it's almost impossible to google it. When I perform full backup of mysql server using mysqldump --all-databases then reinstall it from scratch I want to ...
10
votes
2answers
13k 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 ...