[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Mysqldump 1146 Error

Posted on 2009-02-13
Medium Priority
Last Modified: 2012-08-14
I'm trying to backup a MySQL database using mysqldump, and am running into problems.

MySQL version is 5.0.37 (we have to use this version due to vendor specifications) and all database tables are MyISAM.

This is a fairly large database (~100GB) and backups run overnight.  Due to system load during the day, I can't run backups during business hours, hence I have to try making a change and don't get feedback on it until the next day.  The backup fails after about 90 minutes, and it always fails on one table.  What's special about this table is that every 30 minutes there is a process that drops and re-creates it.  The backup always fails on this table with error 1146, saying the table does not exist.

My first attempt was to just do a backup specifying the database name.  It failed with the 1146 error.  

After realizing that this other process was dropping and re-creating this table, I decided to run mysqldump with the --ignore-table option and ignore the table.  This also failed with the same error.  My thought process was that mysqldump probably makes a list of tables to backup (or ignore) based on some table identifier rather than the name, so when I said to ignore that table, it was really looking to ignore its id instead.  After the table was dropped, a new one was created with the same name but a different id, so it would attempt to back that new table up as well.

My latest thought was to query INFORMATION_SCHEMA.TABLES for all table names EXCEPT the one that is failing, and then using mysqldump and specifying exactly which tables to back up.  This also failed with the same error.  Apparently it feels compelled to back up this table even though I don't say to do so.

So now I don't know what to do.  I need to back up this database, and can skip this table that keeps getting dropped without consequence, I just need to figure out how to get mysqldump to let me do so.  Any ideas would be greatly appreciated!

Question by:CaptainBob007
  • 2
LVL 26

Expert Comment

ID: 23633197
Could you please post your mysqldump command? also I want to see mysql config file (my.cnf/my.ini depending upon your os environment)

Author Comment

ID: 23634406
The first thing I do is query for a list of tables that I know won't change.  Then i run the mysqldump.  code is below.
echo "`date +%H:%M:%S` Backing up database dbName">>$LogFile
TableList=mysql dbName -u $DBUSER -p$DBPASSWORD -e "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dbName' AND LEFT(table_name,4) <> 'ztmp';" | grep -v table_name
mkfifo dbPipe
gzip <dbPipe> $BackupDir/DB_dbName.sql.gz &
mysqldump -u $DBUSER -p$DBPASSWORD --log-error=$LogFile dbName $TableList > dbPipe
if test $BackupResult -eq 0
  echo "success">>$LogFile
  ErrorCount=`expr $ErrorCount + 1`
rm dbPipe

Open in new window

LVL 26

Accepted Solution

Umesh earned 2000 total points
ID: 23634805
>>>MySQL version is 5.0.37 (we have to use this version due to vendor specifications) and all database tables are MyISAM.

But from config fle I feel like you are using InnoDB tables only... if that is the case then your dump command should have --single-transaction parameter...

Anyways try this once and let me know..

Please add this line to config file...


And try this dump command...

mysqldump -uuser_name -ppassword --opt --triggers --routines --databases DB1 DB2 DB3 | gzip > /path/to/full_dump.gz

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question