mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
28 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. ...
0
votes
0answers
5 views

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

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 ...
1
vote
1answer
12 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. ...
2
votes
2answers
1k 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 ...
0
votes
1answer
2k 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
25 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
183 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
12 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?
1
vote
4answers
264 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 ...
0
votes
0answers
25 views

Importing backup(.sql) file into a database. local to remote machine.

I have a database backup(.sql file) in my local machine. It is around 36Gb. And I need to import this backup to a database which is in a remote server, which has a free space of 39Gb. If I try to ...
0
votes
1answer
19 views

Are certain environment variables required for mysql login-path?

I created a bash script that backs up my database, db_backup.sh It basically just runs these commands: DB=/home/linuxuser/backups/backupfilename.sql.gz mysqldump --login-path=local db_name | gzip ...
0
votes
1answer
8 views

mysqldump is not dumping data by default

mysqldump is dumping only the table structures, without data, by default. mysqldump -u user -d database -p Which is the option to dump is with data? How can I configure it, to include the data? (I ...
0
votes
1answer
36 views

mysqldump Error 2013

I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the 'maia_mail' table # mysqldump -u root -p maia > maia.sql mysqldump: Error ...
2
votes
3answers
71 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
2answers
231 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 ...
1
vote
1answer
2k views

Extremely slow mySql restores

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE. In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
0
votes
0answers
11 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 ...
1
vote
2answers
126 views

Using mysqldump on RDS to export a large file in TSV format

I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica. However, RDS doesn't provide a way to use ...
0
votes
1answer
249 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 ...
0
votes
0answers
23 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 ...
16
votes
3answers
35k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
0
votes
1answer
661 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
577 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 ...
0
votes
1answer
155 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
1
vote
3answers
4k 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 ...
0
votes
1answer
648 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
20 views

Setting MySQLDump to keep running in the background after session times out

I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database. However, as our ...
0
votes
1answer
6k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') ...
2
votes
2answers
59 views

An easy way to select one db dump from multiple db MySQL dump

In an urgent situation another person made a backup of all the databases from my webserver to one *.sql file (MySQL dump). Now I have a ~700 Mb file with about 20 different databases. As I only need ...
0
votes
1answer
14 views

Get list of excluded databases from command line by asking user

Based on this I'm working on this bash script for export all databases from MySQL but exlude some ones and also system ones. This is how it looks like: read -e -p "Enter MySQL root user: " -i "root" ...
4
votes
4answers
10k 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 ...
0
votes
0answers
14 views

Getting warning ignoring option '--databases' due to invalid value 'testdatabase' in mysql while importing data

i am getting warning ignoring option '--databases' due to invalid value 'testdatabase' in mysql while importing sql data into a database.Its working fine when I do it on my local machine but same is ...
2
votes
1answer
52 views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
0
votes
2answers
86 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 ...
0
votes
1answer
21 views

How do you dump specific table(s)?

Hi i have a shell script for doing a selected databases backup. Now my requirement is i wanted to take a particular tables in a database to be backed up. Each database backup need to be created in ...
0
votes
0answers
143 views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
4
votes
3answers
4k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
1
vote
1answer
384 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 ...
0
votes
1answer
34 views

Shell script for partial table backup

I have multiple database in my MYSQL server and i want to take some particular tables backup from each database. can anyone help me in writting a shell script for this scenario.
-1
votes
1answer
1k views

mysql dump a table to remote host [closed]

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
0
votes
1answer
21 views

Mysql events table

I am trying to create a script to backup all mysql databases. This is the command that I am trying : sudo mysqldump -u user -ppassword --all-databases > /tmp/all-database.sql And this is the ...
3
votes
2answers
190 views

When does creating a .sql backup with mysqldump become too large?

Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup ...
1
vote
2answers
333 views

Is there a Successor or an Alternative to mk-parallel-dump?

Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in ...
0
votes
0answers
65 views

Daily backup with mysqldump

I want a periodic MySQL database backup, but I don't have permission to access ssh. When I try to run mysqldump via command line in Plesk Scheduled Tasks, it only creates a blank file 0 kb sized. I'm ...
2
votes
1answer
36 views

Is it possible to gain the precise position of a live mysql database when performing full backup

I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups. The production server is on the west coast while business hours are ...
3
votes
1answer
3k views

How can I mysqldump all databases except the mysql schema?

I want to backup all databases on mysql server. I want to exclude the mysql schema. How can I do that ?
0
votes
1answer
59 views

Recovering Mysql - how to get a dumpfile from wampserver and none running db

I had the following: - Wampserver/mysql/drupal + batch file with auto backup by use of mysqldump. - Backup was distributed off and online. then : my laptop was stolen :-( and I need to recover ...
0
votes
2answers
151 views

Access denied to user ODBC, but trying to login as root

I'm trying to do a backup of a MySQL database on Windows, but for some reason it's trying to log me in as ODBC instead of root - does anyone know why? As you can see below the red line, I can login ...
0
votes
1answer
64 views

Dump with special characters in table column name

I'm taking over an old database, and while doing recovery tests from my dump I found out a table is wrongly backed up, the table definition is truncated. Looking at the live db I've found a column ...
80
votes
7answers
71k 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 ...