PDA

View Full Version : Backup Error Message from mysqldump



mmiller
January 18th, 2007, 02:53 PM
I was setting up ZRM for MySQL on one of my machines. I am getting this error message.

<code>mysqldump: mysqldump: Couldn't execute 'show create table `ACL`': Can't find file: 'ACL.MYI' (errno: 2) (1017)
ERROR: mysqldump command did not succeed.
</code>

Have any of you seen this or know what this error message means?

Thanks,

shailen
January 18th, 2007, 03:04 PM
I was setting up ZRM for MySQL on one of my machines. I am getting this error message.

<code>mysqldump: mysqldump: Couldn't execute 'show create table `ACL`': Can't find file: 'ACL.MYI' (errno: 2) (1017)
ERROR: mysqldump command did not succeed.
</code>

Have any of you seen this or know what this error message means?

Thanks,

IT seems like that a table named ACL doesn't exist.

can you verify this? :)

find /var/lib/mysql/ | grep -i acl.myi

assuming your instance of mysql is installed in /var/lib/mysql.

acl.myi is one of the files ON DISK that are created when the table with the same name is created.

Can you post the contents of your mysql-zrm.conf file (for the backup set you're running this for)

thanks!

mmiller
January 18th, 2007, 06:21 PM
I did a find, the find command did not find any file called acl.myi. I will post my conf it's really simple but I may have missed somthing.

Thanks,

<code>
#
# Template for Zmanda Recovery Manager for MySQL configuration file
#
# Global configuration file is /etc/mysql-zrm/mysql-zrm.conf
# The file should be copied to /etc/mysql-zrm/<backup set name>/mysql-zrm.conf
# if backup set specific modifications are required.
#
# MySQL ZRM configuration file describes the backup configuration for
# a backup set. This file is organized into five sections for convenience
# - Backup parameters,
# - Databases/tables that are part of backup set,
# - MySQL server parameters
# - ZRM parameters.
# - ZRM plugin parameters.
#
# For more information about Zmanda Recovery Manager for MySQL, please
# see mysql-zrm(1) and/or Administration manual at
# http://mysqlbackup.zmanda.com/
#
# Any line starting with '#' are comments and will be ignored

#
# Backup parameters
#

# Backup comment. This is a text string which can be retrieved
# using the mysql-zrm-reporter(1) tool. You can store some notes
# about the backup set.
# This parameter is optional and has no defaults.
#comment=This is a comment

# Backup level. It can be full or incremental
# Use 0 for full and 1 for incremental backups
# This parameter is optional and default value is full backup.
#
backup-level=0

# Backup method
# Values can be "raw" or "logical". Logical backup are backups using
# mysqldump(1) tool
# This parameter is optional and default value is "raw".
#
backup-mode=logical

# Size of LVM snapshot. This parameter is optional and is required only
# for "raw" backup-mode and if the MySQL database data are stored in
# LVM logical volumes.
# A size suffix of k for kilobyte, m for megabyte, g for gigabyte
# or t for terabyte
#
#lvm-snapshot=10M

# Directory to which backups are done. All backups are stored under this
# directory. This parameter is optional and the default
# value is "/var/lib/mysql-zrm"
#
destination=/www/mysql-zrm

# Specifies how long the backup should be retained. The value can be
# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or
# years (suffix Y). 30 days in a month and 365 days in a year are assumed
# This parameter is optional and the default is the backups are retained
# forever.
#
retention-policy=90D

# This parameter should be set to 1 if MySQL ZRM backups are being on done on a
# MySQL replication slave.
#replication=1

# This parameter should be set to 1 if backups should be compressed. If this
# parameter is set, gzip(1) command is used by default. If different
# compression algorithm should be used, it must be set in "compress-plugin"
# parameter. Default: There is no data compression.
compress=1

# This specifies the program to be used for compression. The "compression"
# parameter must be set for this parameter to be used. The compression
# command should also support -d option for uncompress backup images. If
# value is not specified then gzip(1) is used for compression.
#compress-plugin=/usr/bin/gzip

# This parameter should be set to 1 if backups should be encrypted.
# The "encrypt-plugin" parameter must be configured. Default: There is no
# data encryption.
#encrypt=1

# This parameter specifies that the program that should be used for
# backup data encryption. "decrypt-option" parameter should also be specified.
#encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt.pl"

# This specifies the option to be passed to the encryption
# program specified as "encrypt-plugin" parameter for decryption.
#decrypt-option="-d"

#
# Databases/Tables in the backup set
#
# One of the "all-databases" or "databases" or "tables"/"database" parameters
# should be specified. If none of the them are specified, "all-databases"
# is assumed.
#

# This parameter should be set to 1 if all databases are part of this backup set
#
all-databases=1

# List of databases that are part of this backup set. Multiple database
# names are separated by space character. This parameter is ignored if
# "all-databases" is set 1.
#
#databases=wikidb forums

# List of specific tables that are part of this backup set. This parameter
# should not be specified if all tables in the databases in "databases"
# parameter are part of the backup set. Multiple table names should be
# separated by space character. The database to which these tables belong
# to should be specified in "database" parameter.
#
#tables=text user page
#database="wikidb"

#
# MySQL server parameters
#

# MySQL database user used for backup and recovery of the backup set.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
#
user="some Luser"

# MySQL database user password.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file or no password is used.
#
password="password"

# Fully qualified domain name of the MySQL server.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
#
#host="localhost.company.com"

# Port to which the MySQL server is listening to. This parameter is optional
# and default value is 3306
#
#port=3306

#Name of Socket file that can be used for connecting to MySQL
#
socket=/var/lib/mysql/mysql.sock

# ssl-options are arguments that are passed to MySQL client commands
# for SSL connection to the MySQL server. This parameter is optional and is
# required only if MySQL server allows SSL connections.
#
#ssl-options="--ssl --ssl-ca=file1 --ssl-cert=file2 --ssl-key=file3"

# Directory where MySQL commands can be found. The parameter is optional.
#
mysql-binpath="/usr/bin"

# Directory where MySQL binary logs can be found. The parameter is optional.
#
#mysql-binlog-path="/var/log/mysql"

# Directory to use for temporary storage. This parameter is optional
#
tmpdir=/tmp

#
# ZRM parameters
#

# This parameter controls the verbosity of MySQL ZRM logging. The MySQL ZRM logs
# are available at /var/log/mysql-zrm/mysql-zrm.log. This parameter is optional
# default value is 0 (less verbose).
# The valid values are 0 and 1
#
#verbose=1

# After a backup run the backup report is emailed to the mailto address
# This parameter is optional and default behavior is not to send mail
# notifications.
#
mailto="spammaster@gmail.com"

# The list of backup reports that are generated after each backup run if
# "html-report-directory" parameter is specified.
# If this parameter is not specified, "backup-status-info" report is generated.
# Valid report names are : backup-method-info, backup-status-info,
# backup-retention-info, backup-performance-info,
# restore-full-info, restore-incr-info,
# replication-info, backup-app-performance-info
# See mysql-zrm-reporter(1) for details of backup reports.
# Multiple report names should be separated by ",".
#
html-reports=backup-status-info,backup-method-info,backup-retention-info,backup-performance-info,backup-app-performance-info

# Directory in which Text/HTML reports will be created by mysql-zrm-reporter(1)
# tool. If this parameter is specified, the mysql-zrm-reporter(1) creates the
# backup reports in this directory after each successful or unsuccessful
# backup run.
# Text reports will be created under "Text" sub-directory
# HTML reports will be created under "Html" sub-directory
#
html-report-directory=/www/mysql-zrm/reports/

# If backup reports are required as RSS feed, "webserver-url" parameter must
# be specified. The value must be set to a valid location on the web server
# in which HTML reports are located and that URL can be used by
# administrator/user to browse HTML reports and can get to the RSS feeds.
# If this parameter is not specified, backup reports are not generated as
# RSS feeds. The list of reports that are available as RSS feed is specified
# in "html-reports".
#
#webserver-url=http://www.company.com/reports/html/

# Location of RSS header file. Administrators can customize RSS channel
# properties using this file. A template for RSS header is available in
# /usr/share/mysql-zrm/plugins/RSS.header file. Location of RSS header
# must be provided if "webserver-url" is specified.
#
#rss-header-location=/etc/mysql-zrm/

#
# ZRM plugin parameters.
# ZRM provides plugin interfaces to allow MySQL administrators to customize
# the backup to their environment.
#

# COPY plugin: Only one copy-plugin must be configured for a backup set.
#
# Socket Copy plugin is to used to transfer backup files from MySQL server to
# the machine running ZRM for MySQL with sockets.
#
# Please read the Notes at /usr/share/doc/mysql-zrm/README-plugin-socket-copy
#
#copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl

# SSH Copy plugin is to used to transfer backup files from MySQL server to
# the machine running ZRM for MySQL with ssh
#
# Please read the Notes at /usr/share/doc/mysql-zrm/README-plugin-ssh-copy
#
#copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl

# PRE-BACKUP plugin: Plugin that will be called before a backup run for
# the backup set.
#pre-backup-plugin="/usr/share/mysql-zrm/plugins/pre-backup.pl"

# Set of parameters passed to the pre-backup-plugin. These parameters are
# passed to "pre-backup-plugin" before a backup run for the backup set.
# "pre-backup-plugin" parameter must be specified.
#pre-backup-plugin-options="option1 option2"

# POST-BACKUP plugin: Plugin that will be called after a backup run for
# the backup set.
#post-backup-plugin="/usr/share/mysql-zrm/plugins/post-backup.pl"

# Set of parameters passed to the post-backup-plugin. These parameters are
# passed to "post-backup-plugin" after a backup run for the backup set.
# "post-backup-plugin" parameter must be specified.
#post-backup-plugin-options="option1 option2"

# PRE-SCHEDULER plugin: Plugin that can be used to dynamically determine the
# start time for a backup run.
#pre-scheduler-plugin="/usr/share/mysql-zrm/plugins/pre-scheduler.pl"

# ZRM Plugin configuration parameters

# This parameter is used by the encrypt plugin and
# specifies the file containing the passphrase.
#passfile="/tmp/a.pass"

# This parameter is used by ssh-plugin.pl plugin to specify the user to be
# used to ssh to the remote host
#ssh-user="root"

# This parameter is used by the ssh-copy.pl and socket-copy.pl plugins
# to specify the location of mysql client binaries on the remote host.
#remote-mysql-binpath="/usr/bin"

# This parameter is used by the socket-copy.pl plugin to specify the port
# to be opened on the remote host.
#socket-remote-port="25300"
</code>

shailen
January 19th, 2007, 08:02 AM
i'm assuming this is a local backup is that correct?

Can you turn the verbose option on (set verbose=1) in the mysql-zrm.conf file and then re run the backup job and copy/paste that output.

everything in your conf file seems fine. (i'm assuming you have confirmed that the specified paths in the conf file accurate.)

mmiller
January 22nd, 2007, 12:04 PM
<quote>
I'm assuming this is a local backup is that correct?
</quote>

Correct.

Output with verbose=1 in the config file.
<code>
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
INFO: ZRM for MySQL Community Edition - version 1.1.3
INFO: Mail address: spameme@donkey.com is ok
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=90D
INFO: tmpdir=/tmp
INFO: mysql-binpath=/usr/bin
INFO: all-databases=1
INFO: destination=/www/mysql-zrm
INFO: backup-mode=logical
INFO: password=******
INFO: user=backup-user
INFO: backup-level=0
INFO: quiet=0
INFO: mailto=donkey@donkey.com
INFO: html-reports=backup-status-info,backup-method-info,backup-retention-info ,backup-performance-info,backup-app-performance-info
INFO: socket=/var/lib/mysql/mysql.sock
INFO: html-report-directory=/www/mysql-zrm/reports/
INFO: compress=
INFO: }
INFO: ZRM Temporary configuration file = /etc/mysql-zrm/dailyrun/tmp8QnKp.conf
WARNING: Could not open file /etc/mysql-zrm/dailyrun/last_backup. No such file o r directory
INFO: Getting mysql variables
INFO: "/usr/bin"/mysqladmin --user="backup-user" --password="*****" --socket="/v ar/lib/mysql/mysql.sock" variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 4.1.20
INFO: log_bin=OFF
WARNING: Binary logging is off. Incremental backups cannot be done.
INFO: backup set being used is dailyrun
INFO: backup-set=dailyrun
INFO: backup-date=20070122124345
INFO: host=localhost
INFO: backup-date-epoch=1169498625
INFO: retention-policy=90D
INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1.3
INFO: mysql-version=4.1.20
INFO: backup-directory=/www/mysql-zrm/dailyrun/20070122124345
INFO: Executing pre-backup-plugin
INFO: Flushing the logs
INFO: flush-logs
INFO: Getting master logname using command "/usr/bin"/mysql --user="backup-user" --password="*****" --socket="/var/lib/mysql/mysql.sock" -e "show master status"
INFO: backup-level=0
INFO: Getting list of Databases
INFO: "/usr/bin"/mysql --user="backup-user" --password="*****" --socket="/var/li b/mysql/mysql.sock" -e "show databases;"
INFO: Command used for logical backup is "/usr/bin"/mysqldump --opt --extended-i nsert --single-transaction --default-character-set=utf8 --create-options --user= "backup-user" --password="*****" --socket="/var/lib/mysql/mysql.sock" --all-data bases > "/www/mysql-zrm/dailyrun/20070122124345/backup.sql"
mysqldump: mysqldump: Couldn't execute 'show create table `ACL`': Can't find fil e: 'ACL.MYI' (errno: 2) (1017)
ERROR: mysqldump command did not succeed.
Command used is "/usr/bin"/mysqldump --opt --extended-insert --single-transacti on --default-character-set=utf8 --create-options --user="backup-user" --password ="*****" --socket="/var/lib/mysql/mysql.sock" --all-databases > "/www/mysql-zrm /dailyrun/20070122124345/backup.sql"INFO: backup-status=Backup failed
INFO: Backup failed
INFO: mailing file /tmp/FkurVRiu0E
INFO: mail command is cat "/tmp/FkurVRiu0E"|mail -s "Errors while running MySQL ZRM" spamme@donkey.com
ERROR: /usr/bin/mysql-zrm did not finish successfully
ERROR : Missing file /var/lib/mysql-zrm/reports/html/index, can not parse this i ndex file
</code>

I have verifyed the paths and the backup user. I don't see anything that looks out of order.

Thanks.

shailen
January 22nd, 2007, 01:40 PM
Can you try the same thing BUT instead of specifying all databases

specify only one of the databases....

(exclude the database named mysql)

Also, are you using any special characters in your password?

mmiller
February 12th, 2007, 11:01 AM
Can you try the same thing BUT instead of specifying all databases

specify only one of the databases....

(exclude the database named mysql)

Also, are you using any special characters in your password?

I did this and everything worked. I dropped two databases that where older and not used anymore. I think the missing table was in there. Everything works fine now.

Thanks,

shailen
February 12th, 2007, 01:43 PM
I did this and everything worked. I dropped two databases that where older and not used anymore. I think the missing table was in there. Everything works fine now.

Thanks,


Thanks for the update!

-Shailen