Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
116
questions
205
votes
7
answers
286k
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 ...
- 4,199
55
votes
3
answers
72k
views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
- 4,429
530
votes
4
answers
990k
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?
- 5,435
166
votes
13
answers
324k
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, ...
- 1,821
2
votes
1
answer
6k
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 ...
47
votes
8
answers
92k
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
...
- 3,112
123
votes
5
answers
196k
views
MySQL any way to import a huge (32 GB) sql dump faster?
I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:
mysql -uroot dbname < dbname.sql
It is taking too long. ...
- 1,333
26
votes
10
answers
78k
views
Any option for mysqldump to ignore databases for backup?
We have 40 databases in our server.
We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore ...
- 697
8
votes
3
answers
9k
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 ...
- 4,199
5
votes
1
answer
4k
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 ...
- 133
108
votes
6
answers
174k
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 ...
- 1,215
48
votes
1
answer
71k
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?
- 723
14
votes
2
answers
49k
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 it....
- 391
8
votes
2
answers
19k
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 ...
- 723
6
votes
4
answers
5k
views
Backup and restore "mysql" database
I use the following command to backup some normal databases:
mysqldump --host=host --user=user --password=passwd --result-file=result.sql -R --single-transaction database_name
My question is : do I ...
- 271
4
votes
1
answer
2k
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 ...
- 41
3
votes
2
answers
2k
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 ...
- 443
2
votes
1
answer
1k
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 ...
- 31
289
votes
18
answers
281k
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 5.0....
- 3,377
10
votes
4
answers
26k
views
Verify mysqldump backup is corruption free
We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
- 211
6
votes
2
answers
22k
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,562
5
votes
2
answers
9k
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 ...
- 51
5
votes
2
answers
2k
views
How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast.
The problem is, from research, MyISAM is okay with being ...
- 151
61
votes
8
answers
128k
views
Restore mysql database with different name
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?
- 1,506
30
votes
2
answers
21k
views
How can I determine the size of a dump file prior to dumping?
How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
- 425
6
votes
1
answer
19k
views
MySQL settings useful to speed up a mysqldump import
Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server. The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.
Someone else tested the import on a MySQL server which has, ...
- 1,294
4
votes
1
answer
8k
views
Speeding up mysqldump / reload
Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the ...
- 169
4
votes
1
answer
23k
views
MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump
while trying to restore a MySql Dump, i get the following error:
ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes
any ideas?
- 1,513
2
votes
1
answer
3k
views
importing myisam 5.0 database into a 5.5 innodb server
I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
1
vote
1
answer
727
views
Translating backup size to database size
I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere.
Is there a good rule of thumb for translating the size of a backup into the size of the database once it ...
- 133
1
vote
2
answers
530
views
mysqldump, preserve update_time and modify_time attributes as listed by show table status from
Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?
If not, is there an option that does this?
From the manual it looks like mysqldump ...
- 113
1
vote
1
answer
2k
views
MySQLdump on specific db date
I'm new in here and i don't really know where to start but here is my concern. I need to backyp a MySQL DB running under windows once a week. For e.g every friday i need to backup and transfer to ...
- 35
24
votes
6
answers
40k
views
slow load speed of data from mysqldump
I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
- 393
20
votes
1
answer
29k
views
What are the optimal mysqldump settings? [closed]
After some search I ended up with the following settings:
mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--default-character-set=...
- 323
16
votes
4
answers
104k
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
...
- 605
14
votes
2
answers
89k
views
How to log verbose output from mysqldump?
You typically save the verbose output of a Linux command like this:
# command > output.txt
But when you use a command like mysqldump, the > option outputs the dump of the database tables to ...
- 2,407
12
votes
2
answers
34k
views
MySQL replication: 'Duplicated entry for PRIMARY key'
Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync.
Basically 'mysqldump' was running almost whole night and then the ...
- 459
7
votes
2
answers
22k
views
MySQL - MySQLDump problems with large table
I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables.
One particular table (called 'responses') is 8Gb - this table is InnoDB.
The live database is replicated to a ...
- 183
6
votes
4
answers
18k
views
how to dump a single stored procedure from a database
I need to create a dump file that contains a single stored procedure from a database, not all routines.
I've tried something like this:
mysqldump -u root -pssmart --skip-triggers --no-create-info --...
- 155
6
votes
4
answers
7k
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 ...
- 959
5
votes
2
answers
7k
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
4
votes
1
answer
12k
views
Move ibdata1, set innodb_data_file_path
I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
- 203
3
votes
2
answers
2k
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 ...
- 135
3
votes
2
answers
9k
views
How do I replicate a MySql server's data without using mysqldump?
I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze.
A quick test using one of the tables shows that it ...
- 393
2
votes
3
answers
3k
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 ...
- 63
2
votes
2
answers
2k
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 ...
- 1,425
2
votes
2
answers
2k
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 ...
- 319
2
votes
1
answer
831
views
Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?
I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
- 337
2
votes
1
answer
7k
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?
- 1,506
2
votes
1
answer
1k
views
mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
- 703