MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

87
votes
7answers
54k 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
1answer
68 views

incompatibility of max_allowed_packet in mysql

I have a client and I want to dump a row such as : $ mysqldump -uroot -p pahlevan_azad mp_posts --where="id='655'" --max-allowed-packet=1024 > dump.sql Warning: option 'max_allowed_packet': ...
1
vote
2answers
64 views

Why database size is smaller in RDS

I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer. I use this query: SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + ...
1
vote
3answers
49 views

Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

Based on best practices, I'm wondering if we should have at least one slave that's used solely as a backup server, without servicing any forward-facing requests from users. For example, if we have a ...
0
votes
0answers
21 views

MySQL replication Got fatal error 1236

I am getting this error when starting a new slave replication from master. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded ...
0
votes
1answer
77 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
0
votes
1answer
37 views

Lost connection to mysql while restoring dump. Is it safe to resume?

I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS. The dump was created with the following command: mysqldump --all-databases \ --single-transaction \ ...
0
votes
1answer
23 views

Mysql Replication backup and restoration failed multipal time

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason. I have 2 host Master & Slave with below details. Server1_Master os-centos6.7 mysql- ...
0
votes
1answer
36 views

How to import a single table in Windows environment

I am running Windows 7 with xampp. I can easily import a database with the following command: mysql -u user -p database < backup.sql The problem is that I have a database around 8.5 GB, which ...
6
votes
4answers
16k views

Error while restoring a Database from an SQL dump

I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error: $ >mysql -u root -p -h localhost -D database ...
28
votes
8answers
112k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
0
votes
0answers
42 views

mysqldump resulting in empty file

I keep getting empty files generated from running $command = 'mysqldump -uroot -pmysql pakaranak tamp2 > mydb_tables.sql'; exec($command); I'm using phpMyAdmin Version 2.10.3. So, any ideas?
3
votes
0answers
59 views

mysqldump with --skip-grant-tables

It appears the files in my /var/lib/mysql/mysql are borked, and I can only start mysqld with --skip-grant-tables. Now I want to export all tables with mysqldump --all-databases, wipe and reinstall ...
3
votes
1answer
34 views

missing frm files for restore

We backup a database daily with mysqldump using the following options: --quick --hex-blob --lock-tables --skip-extended-insert --flush-logs --max_allowed_packet=500M Each database gets its own ...
2
votes
2answers
9k views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
3
votes
2answers
166 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 ...
0
votes
1answer
91 views

error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

I'm trying to load mysqldump and I keep getting following error: ERROR 2013 (HY000) at line X: Lost connection to MySQL server during query /etc/my.cnf: [mysqld] max_allowed_packet = 16M ...
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
79 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
23 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
21 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
100 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
16k 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
17 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
42 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
69 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 ...
1
vote
2answers
361 views

Recommendation for the mysqldump?

I have approx 90 GB of database, I need to schedule a mysqldump backup , thats why I Required a recommendation how much time it takes for the full hot backup? Because I didn't taken that much size of ...
4
votes
2answers
14k 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
26 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
31 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
43 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 ...
11
votes
6answers
12k 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
51 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
98 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
26 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
34 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
26 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 > ...
0
votes
1answer
33 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 ...
105
votes
7answers
107k 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
17 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
41 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 ...
1
vote
1answer
159 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
49 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
59 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
57 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
60 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 ...