MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

3
votes
2answers
316 views

How to merge MySQL backup files (*.sql)

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
1
vote
0answers
18 views

How to merge MySQL backup files (*.sql) [duplicate]

We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one ...
0
votes
0answers
99 views

mysql optimize table and mysqldump

I have a dbserver with about 1.000 databases. This year (2015) we didn't do anything about table optimizing at all. Also our had an uptime for almost a year (338 days). Our backup script with ...
0
votes
0answers
24 views

Dumping data to a CSV file

I am trying to export mysql data to a csv file and before I execute the command, I'd like to make sure the outcome as intended. If I run, mysqldump -u user -ppassword --no-create-info --tab=c:/temp ...
0
votes
0answers
22 views

Dreadful encoding issue

I've got several Drupal sites, and all their tables are latin1 and collations are latin1_swedish_ci. Recently, I wanted to shrink my ibdata1 so I proceeded to read this answer and I dropped my tables (...
1
vote
3answers
5k 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
0
votes
1answer
159 views

Mysqldump.exe Unknown option --no-beep

When I try to run the command : "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" -u root -p******* my_database > backups/my_database_backup_20151221121320.sql it says ...
6
votes
4answers
18k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
4
votes
2answers
2k views

ERROR 1166 (42000) at line 65203: Incorrect column name ''

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message: $ mysql -u root < mydata.sql ERROR 1166 (42000) at line 65203: Incorrect ...
1
vote
1answer
21 views

Move data from mysql 4.0 to 5.6 without corrupting encoding

I have a old mysql database that I would like to move to a recent version of mysql and optimize for SELECT ... WHERE ... statements. It is going to be a SELECT only database. Original database uses ...
0
votes
1answer
63 views

How to reset slave without over write of master dump in mysql

My master having position is File Position mysql-bin.000046 430674598 I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both ...
3
votes
2answers
89 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
0
votes
1answer
38 views

Point in time restore MySQL

I want to perform a point in time restore on MySQL. I want to change log_bin value and ensure that log files are created. I am following this article and am working on a Windows platform. Log ...
4
votes
2answers
17k views

Problem with mysqldump and view

When using mysqldump to backup MySQL, I got following error. mysqldump --all-databases --routines >> all.sql mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
0
votes
1answer
29 views

Continuously backup/restore selected tables

I'm backing up production data locally to perform experiments and investigate production bugs. I need only a handful of tables from product DB. I'm using generic mysqldump and now facing a problem ...
0
votes
1answer
37 views

Restore of database using Mysql DUmp

How would i verify that whatever the backup i have taken is correctly restored or not. EG: I am taking backup on live system where inserts are going on.backup size is around 50 GB.I have started the ...
0
votes
0answers
48 views

My DB restarts again and again

MySQL keeps restarting and logs keep giving this errors InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd ...
13
votes
6answers
14k 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.
0
votes
2answers
57 views

Unexpected behaviour when got error in the middle of mysqldump backup file import

i am using mysql 5.5.40 version. i am importing a large .sql file having around 36000 lines using a script. The script contains below statements. mysql -uxx -p < dumpfile.sql 2 > error.log cat ...
5
votes
1answer
112 views

How does mydumper's ''--use-savepoints" option reduce metadata locking

MyDumper 0.6.1 add a new option --use-savepoints. From the manual, it means: Use savepoints to reduce metadata locking issues, needs SUPER privilege I don't understand. How does it "reduce ...
0
votes
0answers
30 views

Taking a mysqldump from a specific point in time

Is there a way I can take mysql dump from a specific point in time until the latest? Let me explain the need of such requirement, maybe someone could suggest a better option: I took a mysqldump (...
2
votes
2answers
39 views

How can I generate the code-containing comments like mysqldump?

I'm needing to duplicate the mysqldump command in Python, and I am trying to figure out how automatically generate the code comments like mysqldump generates. If you're wondering why I am doing such ...
0
votes
2answers
28 views

How to run mysqldump from windows command prompt

I'm using non-install version of mysql5.1 on Windows. I have placed "mysql" folder under c:\ and it seems to work fine. But when I try C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\>...
0
votes
1answer
37 views

FLUSH LOGS command introduces a long sleep

I am using Percona mysql 5.0-92-log on Centos 6.6 final. I have logged in as a privileged user to mysql. If I issue command mysql> FLUSH LOGS; The terminal goes for a long sleep Below is the ...
114
votes
7answers
115k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
0
votes
1answer
21 views

mysqldump - does it have non-repeatable DMLs?

We are migrating databases using mysqldump, and since this is the first time I'm doing it, I have a couple of questions. If in the middle of mysqldump the utility crashes(for some reason- latest was ...
0
votes
0answers
43 views

Is it safe to run parallel innodb single-transaction of individual tables

I can't seem to find the internals for --single-transaction mysqldump option for InnoDB tables, but is it possible to use --single-transaction for single tables in a parallel dump so that the --single-...
1
vote
1answer
276 views

MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is ...
2
votes
1answer
64 views

How to mysqldump to a subset of rows in specific table during a complete backup

I have a database (myDB) that holds some tables. During my backup process I have one of those tables that should be filtered. So I want to backup only a subset of rows inside table1, when I backup the ...
0
votes
0answers
61 views

mysqldump slows down to a crawl

I have a table with ~10mln records that I want to dump. Running: mysqldump --opt --skip-extended-insert database table > table.sql While monitoring progress I have noticed that first 9.8mln ...
1
vote
2answers
87 views

What are the minimum persmission required to restore mysql dump

I'm trying to create a cronjob which will be populating the data on the server from a local machine. What are the best permission which are required to restore mysql Dump I want to restore the dump ...
6
votes
2answers
2k 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 ...
1
vote
1answer
73 views

Export and Import large database from one rds of amazon to newly created amazon rds

We are facing some issue. We would like to export and import mysql database "xyz" from old rds say xyz-rds to newly created rds say abc-rds (mysql). We are approaching common method taking full ...
0
votes
1answer
58 views

Will a mysqldump ever break my database?

I tried to backup my database with mysqldump. I've done this on a live website also. Is there any chance that this command could ever break a website/database?
3
votes
3answers
8k views

What is the best way to recover from a mysql replication fail?

Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to ...
2
votes
1answer
226 views

How to make sure that mysqldump command is working in command prompt

I'm getting this error: C:\Users\IT-Admin>mysqldump 'mysqldump' is not recognized as an internal or external command, operable program or batch file.
0
votes
1answer
96 views

Number of rows in SQL dump [closed]

Is there any way to check how many rows are in an SQL dump file? I have a large 32GB dump, and wanted to check how many rows are in the file. I am using a windows machine with a MySQL server, Thanks!...
0
votes
1answer
34 views

Why is mysqldump not saving newly added databases?

I am using the following command to backup all mysql databases in script that is run as cronjob. mysqldump -u root -p'***' --all-databases > backupdb.sql I have added new databases recently and ...
1
vote
1answer
401 views

How to dump whole database from remote server on sequel pro

I have a database connection to a remote host on SequelPro Mac OSX. How can i dump whole database. I need a dump like what mysqldump command does: $ mysqldump --opt -u [uname] -p[pass] [dbname] > [...
1
vote
0answers
56 views

Mariadb improve performance while import dumpfile

I have a dumpfile with millions data inside. I want to decrease time because I waited too long. I imported 10 million data (3 GB) and I waited 1:30 hours. I will import nearly 100 million data (30 GB) ...
0
votes
0answers
31 views

MySQL import a sql file but login for which is different than localhost db

I have a SQL file which I want to import into MySQL using the command line but get an error saying that the username does not have access to it. The file is too big, like 35GB, so I can't open it and ...
0
votes
1answer
49 views

Use --databases on mysqldump or not?

I have seen this two ways: mysqldump -u username -ppassword --databases mydb >with.sql mysqldump -u username -ppassword mydb >without.sql I did a linux 'diff' on the two and the with.sql file ...
3
votes
1answer
75 views

mysqldump: How to include index type?

When I dump the schema of my database mysqldump --no-data my_db > schema.sql I get lines like this KEY `index_tbl_on_col` (`col`) but I want lines like this KEY `index_tbl_on_col` (`col`) ...
1
vote
1answer
417 views

Myloader command: Table doesn't exist

I took the backup of mysql database using mydumper command: mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u root -p password@123 -v 3 Now, I am restoring mysql database ...
0
votes
0answers
228 views

Restore .dmp file into csv

We have asked our clients for certain data in a csv file format and we just got a .dmp file. I have been trying to open it using MySQL as if it were .dump but it doesn't work. I have seen other ...
2
votes
3answers
439 views

Mysqldump treats some table as View

When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables. So for some tables it looks like -- Retrieving table ...
0
votes
3answers
567 views

Is any tool for taking MySQL Dump Automatically with a particular time? [closed]

I am using VS2010 and MySQL for my project. My client wantw to take mysqldump every day like a daily backup. I want to give my client a tool for taking mysqldumps on a particular time schedule. Do ...
1
vote
2answers
2k views

Can't restore mysqldump database backup in Ubuntu 12.04

I'm trying to restore a database backup created using mysqldump. If I do: $ mysqldump -u ROOT -p DATABASE_NAME < DATABASE.SQL the database remains empty, even though I get some screen output ...
0
votes
1answer
59 views

Importing MySQL tables from other database in live site with mysqldump can cause trouble?

Scenario: I want to replicate MySQL tables from one database to other database. Possible best solution: May be to use MySQL Replication feature. Current solution on what I'm working as workaround (...
0
votes
1answer
86 views

MySQL backup practices

I have a MySQL master-master replication with second master in read-only mode. My questions reg backup are: I have 10 databases so should I take daily backup as one dump containing all the databases,...