Inhaltsverzeichnis

Mysqldump-Fehler automatisch beheben

Anzeichen

Aus unerfindlichen Gruenden ist mysqldump der Meinung, dass in meiner Datenbank viel kaputt ist, und will keinen Dump mehr machen.

Man versucht

mysqldump -uroot -p --all-databases -ralldb.sql

und bekommt 3 unterschiedliche Fehlermeldungen:

Output: mysqldump: Got error: 1017: Can't find file: '<filename>' (errno: 2) when using LOCK TABLES
Output: mysqldump: Got error: 1102: Incorrect database name '<databasename>' when selecting the database
Output: mysqldump: Got error: 1146: Table '<databasename>.<tablename>' doesn't exist when using LOCK TABLES

Loesung

Folgendes Pythonscript behandelt die Fehler wie folgt:

import os
import re
import subprocess
import fnmatch
import shutil
 
def invalidDatabase(output):
  matches = re.findall(r"Incorrect database name '([a-zA-Z0-9_\.]*)'", output)
  if len(matches)==0:
    return
  for root,dirnames,filenames in os.walk('/var/lib/mysql/'):
    for dirname in fnmatch.filter(dirnames, matches[0]):
      print "deleting ", root+dirname
      shutil.rmtree(root+dirname)
 
def invalidTable(output):
  matches = re.findall(r"Table '([a-zA-Z0-9_-]*)[\.]*([a-zA-Z0-9_]*)'", output)
  dirname = ''
  if len(matches) == 0:
    return
  if len(matches[0]) == 1:
    tablename = matches[0]
  if len(matches[0]) == 2:
    dirname = matches[0][0]
    tablename = matches[0][1]
  print "Found table", matches, 'dirname', dirname, 'tablename', tablename
 
  for root,dirnames,filenames in os.walk('/var/lib/mysql/'):
    for filename in fnmatch.filter(filenames, tablename + '.frm'):
      if dirname != '':
        if dirname == root.split('/')[-1]:
          targetFile = root + '/' + filename
          print "Will delete", targetFile
          os.remove(targetFile)
      else:
        targetFile = root + '/' + filename
        print "Will delete", targetFile
        os.remove(targetFile)
 
def cantFindFile(output):
  matches = re.findall(r"Can't find file: '([a-zA-Z0-9_]*)'", output)
  if len(matches) == 0:
    return
  tablename = matches[0]
  for root,dirnames,filenames in os.walk('/var/lib/mysql/'):
    for filename in fnmatch.filter(filenames, tablename + '.frm'):
      dirname = root.split('/')[-1]
      print "Dropping table", tablename, "in", dirname
      os.system('mysql -uadmin -p`cat /etc/psa/.psa.shadow` '+dirname+' -e "DROP TABLE ' + tablename + ';"')
 
while True:
  cmd = ['mysqldump', '-uadmin', '-p<password>', '-ralldb.sql', '--all-databases']
  p = subprocess.Popen(cmd, stderr=subprocess.PIPE)
  p.wait()
  output = p.stderr.read()
  print "Output:", output
  invalidTable(output)
  invalidDatabase(output)
  cantFindFile(output)