mysqldump : MySQL's Standard Dump/Backup Utility
2
votes
1answer
411 views
How do I replicate Mysql DB server to new server?
Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration).
I want to replicate ...
6
votes
1answer
534 views
recover a single mysql database on a busy master-slave replicated system
Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.
I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
1
vote
1answer
628 views
MySQL - MySqlDump uses \n instead of null
Using Windows with MySQL 5.1.
I am running a script that uses mysqldump to backup a database.
The mysqldump syntax used is:
mysqldump --user=root --port=3306 --password=topsecret some_database ...
2
votes
3answers
532 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 ...
1
vote
1answer
869 views
Write lock during mysqldump even using --lock-tables=false
When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?
As I am using MyISAM, does --single-transaction for InnoDB help?
...
3
votes
2answers
2k views
No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00
I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
20
votes
5answers
31k views
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
2
votes
2answers
115 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 ...
5
votes
3answers
12k views
How do I change the DEFINER of a VIEW in Mysql?
When I run mysqldump, I get an error:
mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES
This makes sense because foobar is a legacy ...
1
vote
1answer
79 views
mysql server password change while restoring
I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to ...
3
votes
3answers
4k views
Restore mysql database with different name
Hello Experts,
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.?
regards,
Praveen
1
vote
2answers
2k 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 ...
1
vote
1answer
778 views
mysql db backup excluding specific tables
Are there any mysql scripts for taking a database backup, while excluding specified tables?
Also, what is the best way to take backup of a running mysql system?
Regards,
Praveen
2
votes
2answers
114 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 ...
1
vote
1answer
1k views
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
2
votes
1answer
376 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 ...
5
votes
1answer
738 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 ...
2
votes
1answer
1k 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:
...
4
votes
4answers
851 views
Incomplete mysqldump
I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB.
...
2
votes
2answers
404 views
MySQL corruption even after full dump and re-import
We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over.
The error message seem to relate to indexes so I assume the data itself is ok and when I ...
3
votes
1answer
534 views
MySQL dump restore - how to retain comments
So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.
Some of our evil triggers had a comment lines starting with the # character.
When I ...
3
votes
2answers
1k views
mysqldump vs LOAD DATA INFILE
Why would you ever use mysqldump to repopulate a table over load data infile?
Here's the back story to my question. We had a table that was incorrectly partitioned. It was supposed to be ...
1
vote
1answer
2k views
Can mysqldump dump triggers and procedures?
Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?
Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
3
votes
2answers
525 views
Quick MySQL Backup
I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack.
Or mysqldump will work ...
3
votes
1answer
1k views
Consistent logical backup of databases that use MyISAM and InnoDB engines
I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.
The mysqldump utility supports these two options:
--single-transaction - Creates a consistent ...
3
votes
1answer
1k views
Restore dropped mysql database
I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years.
This year I did not do a backup and accidentally dropped the ...
1
vote
2answers
2k 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
4
votes
1answer
2k views
mysqldump with INSERT … ON DUPLICATE
I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
6
votes
3answers
1k views
Users complain that system runs slow when mysqldump is in progress.
The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump ...
2
votes
2answers
1k views
Why is mysqldump file so large?
We have a 2GB MySQL DB. We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here?
Questions
Why is this file so ...
3
votes
1answer
542 views
How does max_allowed_packet affect the backup and restore of a database?
I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
1
vote
2answers
414 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 ...
3
votes
1answer
3k views
DROP PROCEDURE IF EXISTS not included in mysqldump
I'm dumping my stored procedures only using the following command:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql
but the ...
6
votes
1answer
2k views
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
2
votes
2answers
1k views
Different MySQL Datafile Sizes After Restoration
I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86.
Some weeks ago, we upgraded the company's ...
2
votes
1answer
1k views
MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL
I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this:
mysql -e ...
3
votes
1answer
3k views
How do I open a MySQL Database Dump?
I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
7
votes
1answer
3k views
Does mysqldump export indices, by default?
I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:
--disable-keys, -K
which suggests, ...
2
votes
3answers
3k 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 ?
3
votes
3answers
2k views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
34
votes
14answers
47k views
How can I move a database from one server to another?
How can I move MySQL tables from one physical server to another?
Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.
I want to move it to a new server, ...
43
votes
7answers
23k 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
4answers
497 views
How to do a mysqldump with a use database in the dump
So the command I normally use to get a database sent to another server is:
mysqldump -u user -p --add-drop-tables database *file*
But I always have to go into the file and add a line at the top:
use ...
16
votes
4answers
21k 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...
