MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

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
1answer
824 views

MySQLDump wrong dump

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for ...
3
votes
0answers
41 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
301 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 ...
3
votes
3answers
6k 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
2k 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 ...
2
votes
3answers
125 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 ...
2
votes
2answers
1k views

mysqldump: flush-privileges option

I'd like to get some clarification about the --flush-privileges option for mysqldump. Here's the description of the option from the MySQL docs: --flush-privileges Send a FLUSH PRIVILEGES ...
2
votes
1answer
4k views

mysqldump not escaping single quotes properly

I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly. Here is an example of the mysqldump generated sql script : ...
2
votes
2answers
693 views

MySQL, import SQL dump with another database

I have an SQL dump of db1 that contains this line: "USE db1" If I import that file into db2 with: mysql -D db2 -uroot -p < /var/backups/db1.sql.gz Is there the risk that db1 will be overriden? ...
2
votes
2answers
8k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
2
votes
3answers
218 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 ...
2
votes
1answer
4k 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 ...
2
votes
3answers
1k views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
2
votes
1answer
238 views

How to change the create and use statement in a large sql file without opening it

I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another ...
2
votes
2answers
458 views

Mysqldump throwing error 'Couldn't execute 'ROLLBACK TO SAVEPOINT sp':

Can any one please explain what is happening with our DB dumps? We are executing dump as below mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX ...
2
votes
1answer
6k views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
2
votes
1answer
339 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'" ...
2
votes
2answers
454 views

How does mysqldump take place when there are more than one database?

I'm writing a shell script wherein I'm doing a mysqldump of all databases. My questions are: Are the tables of all databases in the server locked during the mysqldump? If I have to do an ...
2
votes
1answer
653 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 ...
2
votes
1answer
2k views

Is there any risk associated with stopping mysqldump incomplete?

I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours. I used following command and options: mysqldump -u db -p user table --hex-blob | gzip> ...
2
votes
3answers
3k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
2
votes
1answer
3k views

mysql db backup excluding specific tables

Are there any mysql scripts for taking a database backup by excluding specified tables? Also, what is the best way to take backup of a running mysql system?
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 ...
2
votes
1answer
111 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
3k views

Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

I ran the following command to create a mysqldump mysqldump -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz I got following error after 60% (estimating checking ...
2
votes
2answers
523 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
2
votes
1answer
51 views

mysqldump confusion

I must be missing something. The line I keep seeing everywhere for a consistent backup is: mysqldump --single-transaction --all-databases > alldbs.sql Yet the MySQL docs indicate --opt is on by ...
2
votes
3answers
1k 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 ...
2
votes
1answer
5k 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 ...
2
votes
1answer
4k 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
662 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
2
votes
1answer
726 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 ...
2
votes
1answer
2k views

Can I mysqldump an entire database while using where='condition' on individual tables?

I have used mysqldump to dump an entire database, However, when I want to use --where=condition", on individual tables, I have dumped individual tables mysqldump --where='condition' mydb table ...
2
votes
2answers
526 views

mysqldump reimport isnt working correct

I am trying to restore this database with my sql file I have...yet just get this output with it not importing anything into my db. Thoughts? [root@domU-12-31-39-0B-06-AA data]# mysqldump ...
2
votes
2answers
180 views

mysqldump seems to copy excessively

I have an old_server with 50 databases. I want to create new_server with just 3 databases. When I try using mysqldump, it seems like all the database directories (although they are empty on ...
2
votes
2answers
1k views

How to enable all the possible backup options of MySQL

I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
2
votes
1answer
33 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 ...
2
votes
1answer
36 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.
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 ...
2
votes
2answers
185 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 ...
2
votes
1answer
215 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 ...
2
votes
1answer
380 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 ...
2
votes
4answers
443 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 ...
2
votes
1answer
856 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 ...
2
votes
1answer
368 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
2
votes
1answer
512 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 ...
2
votes
2answers
127 views

Database Files-Related Issue

I am using MySQL database and using Ubuntu linux machine. I noticed that my database, db_test, is under the path /var/lib/mysql/db_test There are files with the suffix .frm, .MYD, .MYI like the ...
2
votes
1answer
4k views

When are we supposed to set default-character-set for the client?

I'm trying to understand when I'm supposed to set/force default-character-set for the client. The documentation states: You can force client programs to use specific character set as follows: ...
2
votes
2answers
28 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 ...