mysqldump : MySQL's Client Backup Utility

learn more… | top users | synonyms

19
votes
5answers
5k views

Minimum permissions for a user to perform a mysqldump?

I want to start scheduling remote mysqldump crons, and I'd prefer to use a special account for that purposes. I want to grant that user the minimum permissions for getting a full dump, but I'm not ...
16
votes
2answers
11k views

Out of resources for mysqldump

I'm trying to do a mysqldump on a Windows server and I get the following error message : mysqldump: Got error: 23: Out of resources when opening file '.\db\sometable.MYD' (Errcode: 24) when using ...
13
votes
5answers
8k views

How can I speed up a MySQL restore from a dump file?

I am restoring a 30GB database from a mysqldump file to an empty database on a new server. When running the SQL from the dump file, the restore starts very quickly and then starts to get slower and ...
10
votes
3answers
12k views

Can't backup the mysql table with mysqldump. SELECT,LOCK TABL command denied for 'cond_instances'

I am having trouble running mysqldump as the mysql root user. When I try to backup the mysql table I get this error: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ...
8
votes
4answers
2k views

How to efficiently dump a huge MySQL innodb database?

I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is ...
8
votes
3answers
3k views

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This ...
8
votes
2answers
8k views

How do I create a read only MySQL user for backup purposes with mysqldump?

I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file. I've ...
6
votes
4answers
9k views

How to dump remote database without mysqldump?

I want to dump the database on my remotely hosted site in regular intervals using a shell script. Unfortunately the server is locked down pretty tight, has no mysqldump installed, binary files can't ...
6
votes
2answers
4k views

mysqldump error LOCK TABLES

When I execute a mysqldump I get following error: mysqldump -u root -ppassword --databases information_schema > test.sql mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to ...
6
votes
1answer
831 views

System Upgrade introduced an unusual mysql privilege error

After a box running mysql was upgraded a backup script is returning an error for databases with Innodb tables. A system running Debian 5 (Lenny) has been upgraded to Debian 6 (Squeeze), and the ...
5
votes
3answers
3k views

Redirect output of mysqldump to scp

I worked with a linux pro years ago who did this nifty trick. He could do a mysqldump but redirect the output to an scp/ssh connection rather than writing to disk. We utilised this quiet a bit where i ...
5
votes
3answers
14k views

What causes the MySQL error 1062 - duplicate entry when starting slave?

MySQL Master version: 5.5.16-1 MySQL Slave version: 5.5.18-1 The master's snapshot is created by: mysql> FLUSH TABLES WITH READ LOCK; shell> mysqldump --all-databases --master-data > ...
5
votes
10answers
2k views

trouble backing up large mysql database

I have a wordpress MU database with something like 10,000+ tables for various user's blogs. I need to upgrade wordpress MU to newest version, but want to backup the DB before hand. PHPMyAdmin fails ...
4
votes
2answers
179 views

Can I mysqldump an entire DB connection?

I am hosted on a Media Temple DV 3.5 server and looking to upgrade from mysql 5.0.90 to the latest mysql so I can use triggers and foreign keys. I am a bit nervous to do so because I don't have a ...
4
votes
10answers
3k views

how to complete mysql daily backup dump without timeouts?

what is best way to complete daily Mysql database backup, we are having critical mysql database connection timeouts during backup dump we use dump and gzip cron has a line: 1 1 * * * root nice ...
4
votes
1answer
1k views

How to avoid mysqldump warning “Skipping the data of table mysql.event”

After a recent Debian upgrade on my LAMP webserver to Wheezy, mysqldump is throwing a warning: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. Events ...
4
votes
2answers
3k views

MySQL slave replication reset with no Master Downtime (using MyISAM)

Root of the issue: In all instructions for creating a slave on a running master requires flush tables with read lock. We use MyISAM, so we can't just use --single transaction to get consistent table ...
4
votes
3answers
4k views

Does mysqldump return a status?

I am creating a script that backups a mysql db using the mysqldump utility. I am writing this script in the shell "sh". I would like to capture the output status of mysqldump in the script (i.e. if ...
4
votes
1answer
5k views

mysqldump: Got error: 1066: Not unique table/alias: 'foo' when using LOCK TABLES when lower_case_table_names=1

I'm trying to dump a MYSQL DB on a linux server (Centos 5.2 32bit, MySQL 5.0.45) In /etc/my.cnf the following value is defined: [mysqld] lower_case_table_names=1 When I try and do a dump with ...
4
votes
5answers
1k views

MySQL, is there a way to tell when was database last changed/modified?

I'm creating MySQL dump of 5 databases every hour. 3 of those databases very rarely change, so creating a MySQL dump for those 3 dbs is pretty much a waste of time and resources. Is there a way I can ...
4
votes
2answers
2k views

MySQL export tables with prefix from Unix command line

I want to export certain tables from one database and import them to another. The tables in question are used for my MediaWiki installation and are prefixed with "wiki_". I do not have access to ...
4
votes
2answers
1k views

mysql error 1062 'duplicate entry' when restoring backup

Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this. Requirement: To create an exact replica 'db4' of an existing DB 'db3'. Procedure ...
4
votes
2answers
422 views

How to get a valuation about the size of mysql table dump

Hi to everyone someone know how to get an estimate about the size of one mysql table? I mean I am planning to make a backup of all the mysql's table on all my server but I'd like to know how big ...
4
votes
4answers
3k views

How to create a SQL Server database from a mysqldump file automatically on a daily basis?

Recently, a vendor announced that they were upgrading their processes, and an automated, daily data dump we are getting from them will now come in a MySQL datadump format. Unfortunately, we're a ...
3
votes
3answers
271 views

Multiple SSH sessions in a single command

I'm trying to copy a database from one server to another. I tried using the following command and it's getting tied up on entering the ssh passwords. If I put in one server's password, it complains ...
3
votes
2answers
12k views

How do I ignore errors with mysqldump?

When dumping a database, I'm getting mysqldump: Couldn't execute 'show create table `some_table`': execute command denied to user 'some_user'@'%' for routine 'some_routime' (1370) and then the ...
3
votes
5answers
3k views

Incremental MySQL Backup using Subversion

Our MySQL database servers host several dozens of databases, with dump file size ranging from 1 to ~100 MB. Currently, our backup approach is mysqldump, wrapped in a shell script, and run from a ...
3
votes
1answer
840 views

mysqldump of latest 1000 records

how to take mysqldump of latest 1000 records from a database
3
votes
2answers
2k views

Couldn't execute 'show events' on mysqldump

While trying to backup all MySQL databases with mysqldump I get the following error: Couldn't execute 'show events': Access denied for user 'user'@'localhost' to database 'performance_schema'
3
votes
3answers
2k views

How to properly backup mediawiki database (mysql) without messing up the data?

I want to backup a mediawiki database stored in a MySQL server 5.1.36 using mysqldump. Most of the wiki articles are written in spanish and a don't want to mess up with it by creating the dump with ...
3
votes
1answer
3k views

mysqldump > unknown table engine 'PERFORMANCE_SCHEMA'

I can not backup my databases by using myslqdump, here's the message: # Error: Unknown table engine 'PERFORMANCE_SCHEMA', inpossible to LOCK TABLE This error-message doesn't disappear, also after ...
3
votes
3answers
4k views

Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

I'm getting the above mentioned error when backing up with ZRM, which is using mysqldump for backup. mysqldump --opt --extended-insert --single-transaction --create-options ...
3
votes
1answer
69 views

Is it safe to lock tables on a mysql replica slave?

I have a master mysql database where the application writes and read production data, and then I setup a slave database just in case something goes wrong with the master database server (and I can do ...
3
votes
4answers
2k views

mysqldump --where with = operator doesn't get all rows

I have a situation with a particular table that now thinks it contains 4 Petabytes of data. I know that sounds cool, but I assure you, it is only on a 60GB partition. This table has 9 fields in it. ...
3
votes
1answer
129 views

Restore database by files from broken Ubuntu server

I'm pretty lost right now. The RAID controller auf my server broke and all attached hard drives were corrupted. Luckily I copied the plain files off the server right before the crash (phew). I set ...
3
votes
3answers
187 views

Question: MySQL db Migration from Server(A) to Server(B), which tables?

When migrating a MySQL database from Server(A) to Server(B) which tables should be dumped and migrated to Server(B), including user grants? I don't want to have to manually re-enter user accounts and ...
3
votes
2answers
1k views

Using mysqldump from 5.1 package to backup data from 5.0 server?

I've ran into problems trying to backup data from table City in world database from mysql 5.0 server on local network: mysqldump --tab=/home/developer/Desktop -u developer -h 192.168.10.196 -p world ...
3
votes
1answer
238 views

File (FRM) not found? Cant view the tables?

I have 2 version of database backup. Mysqldump > db_backup.sql & FRM files (tables files) The db_backup.sql file is corrupted; it is 0 KB. I think I can't help with it anymore. Now I ...
3
votes
2answers
6k views

Recover mysql database - mysql/mysqldump gives “table <database>.<tablename> doesn't exist (1146)”

Backstory Ubuntu died (wouldn't boot) and I couldn't fix it. I booted a live cd to recover the important stuff and saved it to my NAS. One of the things I backed up was /var/lib/mysql. Reinstalled ...
2
votes
5answers
1k views

Backup MySQL database and gzip

I'm trying to get a little more usefulness out of my MySQL backups. I'm wanting to gzip my backup when it's completed from a cron job. Here's the script I have so far to do the backup. #!/bin/sh ...
2
votes
3answers
734 views

Mysql dump of slave w/o missing Master data

I am fairly new to the whole replication process of mysql so this may be an easy question to answer. I have a master and and slave. I need to set up another slave so obviously I will need to make the ...
2
votes
2answers
1k views

how to execute bash script with crontab in centos? [duplicate]

My bash file is not executed by cronjob created as root. The cronjob setup looks as it follows 0 0 * * * mysql_backup.sh does need to be decalared with the bash command 0 0 * * * bash ...
2
votes
2answers
194 views

mysqldump has a “quick” option. Why isn't this enabled by default?

In the mysqldump docs I see a "--quick" option, however there is no indication as to why somebody would want this disabled. What are the disadvantages to enabling it or why is it even an option? ...
2
votes
5answers
1k views

Slow Mysql import

I import a database, and when i run mytop i see only one thread running an insert. On a big database of 80G that will take really big time, is there any tools or may be an option to increase number of ...
2
votes
2answers
1k views

How to dump a mysql database and transfer to remote ftp in one line -for crontab-?

I need to do a weekly backup of a mySQL table and then transfer it to a remote machine using ftp. This is the command I have now in crontab: 0 18 * * 1 /usr/bin/mysqldump --add-drop-table ...
2
votes
1answer
342 views

mysqldump doesn't export all records from table

I'm using mysqldump to move the database to another server. Everything works fine, except that mysqldump doesn't export all records from one table. The table contains about 500 records, and ...
2
votes
1answer
373 views

Store the MySQL client password, or setup password less authentication

I am using the mysql command line client and I do not want to need to provide the password every time I start the client. What are my options?
2
votes
2answers
737 views

mysqldump is not dumping my data

I am running mysqldump on Ubuntu Linux (10.0.4 LTS) my mySQL version info is: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 I used the following command: mysql -u ...
2
votes
3answers
691 views

What settings to use for fastest reloading of a MySQL backup?

I have a MySQL database which dumps to a 3.5 GB backup (mysqldump) in about 10 minutes. But reloading this backup on a standby / test server takes nearly 2 hours [originally 12 before some tuning]. ...
2
votes
2answers
586 views

Mysql dump from old server

I have to clean up as mess for a client. The DNS record of the domain has been moved to a different server (IP address). But I need to retrieve MySQL data from the old server. I still have the ip ...