MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
69 views

Unable to load mysql dump file

I recently had to deal with migrating some servers over (rather was forced to by the hosting company) and we took too long to migrate our data so they did it for us. Unfortunately they just copied ...
0
votes
2answers
147 views

Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly ...
0
votes
1answer
70 views

Copying MySQL schema, some tables not getting recognized!

From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are ...
2
votes
4answers
454 views

Moving large databases

I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space). Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need ...
1
vote
1answer
58 views

Migrating all MySQL databases from from one server another. Getting error during import

I create a mysqldump on all the databases on the source server with this command: mysqldump -h mysql-01 -u root -p --all-databases > newdump.sql This takes about an hour, but runs fine. Now, ...
1
vote
2answers
631 views

mysqldump error on common_schema

I tried to take the backup of common_schema in Server version: 5.5.44 MySQL Community Server (GPL). mysqldump -u root -p common_schema > common_schema_bkup.sql I got the following error: ...
1
vote
1answer
112 views

Mysql shutting down during a mysqldump of a specific database

I have a server which is running Centos 6.6 with Cpanel. I have been attempting to do mysqldump backups on individual databases. All of the databases are under 5MB except for one. The database that ...
3
votes
1answer
8k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
14
votes
6answers
30k 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?
12
votes
7answers
72k 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 ...
1
vote
2answers
378 views

MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

I am trying to restore multiple mysql dump (.sql) files. Data in each dump file are from the same table, but with different ID (primary key) range. For example, > 1.sql (0 < ID && ID ...
1
vote
0answers
156 views

How do I restore a mysql dump that was created in xml format?

I have a rather big XML formatted MySQL dump It was created with the --xml param (-X) param of mysqldump. I need to import it in. This article from 2010 indicates that there was no "built in" way ...
2
votes
0answers
147 views

mysqldump restore error

I am getting the error below, while restoring a dump to RDS MySQL 5.6. Dump is taken from MySQL 5.5 originally. I tried restoring twice but same error. Can anyone help ERROR 1064 (42000) at line ...
1
vote
1answer
77 views

Windows crashes on doing sql dump [closed]

Windows simply crashes and opens the blue screen for dumping physical memory on trying to import sql dump. Blue screen error: IRQL_NOT_LESS_OR_EQUAL So far I have tried 4 methods: Through workbench ...
1
vote
1answer
37 views

Backup a MySQL database -options

I'm relatively new to database driven sites. In order to backup a database I use phpMyAdmin's Export function from the server and download the output to my computer. That works, but it requires me to ...
0
votes
1answer
1k views

Is it safe to use mysqldump to backup and restore a master-master cluster

Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB). I have a master-master setup and am interested in backing up and restoring ...
2
votes
1answer
46 views

Database Dump/Backup but only to specific records?

This question is about MySQL Backup (or Dump) whereby i want to backup and store information from only information related to a person; SchoolA from the School Table in this example. So, I should not ...
3
votes
1answer
887 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
2
votes
1answer
727 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser ...
0
votes
1answer
21 views

Preserving create_time when loading a mysqldump file [duplicate]

I want to make a copy of a MySQL database but preserve the information_schema.create_time for each of the tables. I haven't been able to find an option to do that with mysqldump.
0
votes
1answer
77 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
7
votes
4answers
12k views

Where does the mysqldump command store the backed up databases?

Am trying to backup my databases through the terminal in Ubuntu using the mysqldump command and it's successful , but where does it place the backed up databases ?
1
vote
2answers
393 views

mysqldump with automatically generated export name

When I do database dumps through MySQL WorkBench it picks a filename for me which is the current date (YYYMMDD). Now, I want to do this in a batch file since I want to make weekly dumps automatically ...
165
votes
4answers
313k views

How do you mysqldump specific table(s)? [duplicate]

How can I dump a specific table or set of tables without including the rest of the db tables?
2
votes
1answer
59 views

Strategy for setting up a 60gb MySQL database from a dump?

I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been ...
3
votes
2answers
13k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql ...
3
votes
2answers
1k views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
3
votes
3answers
4k 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 ...
0
votes
1answer
58 views

Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump?

I'm new in db administration and I've looked for a faster import of a mysqldump. I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before ...
0
votes
1answer
56 views

Why does mysqldump require drop command?

I am trying to backup a large table and after reading various other answers on this site have tried mysqldump --user=user --password=password dbname1 tablename | mysql --user=user --password=password ...
0
votes
2answers
148 views

Fixing MyISAM database without losing data

My old server was crashed but I was able to recover the databases from MySQL through testdrive. I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and ...
0
votes
0answers
75 views

Best way to restore 400GB+ worth of data in MySQL 5.6

So I'm trying to figure what the best/quickest way would be to refresh a massive amount of data. We're wanting to copy 3 months worth of data from a replication server to a QA server for product ...
0
votes
2answers
65 views

understanding mysqldump auto-backup performance issues and inner functionality

Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the ...
0
votes
1answer
63 views

Backup a huge database 200gb+

i have a mysql database where i need to take a backup of the log database. the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of ...
0
votes
0answers
168 views

Export MySQL Database with version compatibility

I'm trying to move a MySQL database out of my Win 2012 server & onto a shared hosting web server (Linux). I'm having problems importing the database (which imports fine into other hosted packages ...
1
vote
1answer
455 views

Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin?

I know it's not necessary to backup the information_schema database, but what about the following databases (besides the user-defined databases)? performance_schema mysql phpmyadmin (So, in case ...
0
votes
1answer
387 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
113 views

Is there an easy way to update a mysqldump file from version 9.11 to 10.11? [closed]

We have a client that has had their servers for soo long, they were still running MySQL 4.0.24. Now that they are refreshing their servers, we took a mysqldump of their current database to put into ...
2
votes
1answer
384 views

Consolidated realtime mysql backup with master/slave setup or binlog

I have 20+ mysql servers and their have low write requirements. So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose. If I ...
3
votes
2answers
2k 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 ...
0
votes
0answers
98 views

How do I dump my DB without creating temporary files?

My system is showing disk problems, and linux turned my root folder to read-only. Before I reboot, I wanted to backup my mysql DB, just in case I can never access it again after rebooting/checking ...
1
vote
1answer
107 views

Very weird Innodb Corruption Issue

I'll try to keep this to the point. System: OPenVZ VPS, debian 6, 512 MB RAM, 50 GB HDD (25GB free) I have a mysqldump dump file (3GB), that was created when the innodb database was working fine. ...
1
vote
1answer
47 views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
0
votes
1answer
9k views

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I know this might be a duplicate of some other posts. But those solutions didn't work for me and I've lot of issues within those answers. Neither could I comment nor post in there, I'm posting a ...
0
votes
1answer
129 views

How to run a mysqldump from a .txt or .sql file inside a command line window?

If I run the follow line directly in command line window it will works good. C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql How can I run a .txt or .sql file ...
1
vote
1answer
287 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
1answer
19 views

Import lots of mysqldumps with missing CREATE DATABASE

I have a bunch of mysqldumps of my old server with one database each. Unfortunately we forgot to add CREATE DATABASE ... in all those dumps. How can I import those files in one batch?
2
votes
3answers
126 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 ...
0
votes
0answers
63 views

Import database with views and routines into Amazon RDS

I need to import a collection of MySQL DB's into Amazon RDS without downtime. One can do this by initially setting up the RDS instance as a slave. There is one problem, you're not a super user on RDS ...
0
votes
0answers
198 views

Automysqlbackup fix error “Skipping the data of table mysql.event. Specify the --events option explicitly”

I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.". I found that its because of not specifying whether or not to add ...