我已经写了一个快速的shell脚本来取得MySql数据库的每个备份＆这很好。 我为所有的mysql服务器工具使用相同的脚本，我已经安装在Ubuntu 10.04服务器上。
现在问题是运行shell脚本时，我得到一个错误，如下所示
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES 我已经授予了information_schema数据库的权限，但上述错误仍然存在。
任何人都可以帮我解决这个问题。 让我知道是否需要在shell脚本中进行任何更改。
简单的shell脚本给我所有的朋友
#!/bin/bash MyUser='root' # Username d=`date +%b_%d_%Y_%H_%M` m="/var/backups/bugzilla_backup/Bugzilla_Dump_$d" mkdir $m #Get List of Databases /usr/bin/mysql -u $MyUser -Bse 'show databases' > /usr/local/bin/dbname # Backup listed Databases while read l; do /usr/bin/mysqldump $l -u $MyUser > $m/$l-$d; /bin/bzip2 $m/$l-$d; sleep 15; done < /usr/local/bin/dbname
问题是，也是试图转储架构，直到最近这个请求被自动忽略。 您需要在脚本中添加一个variables来忽略某些表。
这里是我的脚本忽略information_scheme，并且还将date和24小时的时间添加到文件名称。
#!/bin/bash # Shell script to backup MySql database # To backup Nysql databases file to /backup dir and later pick up by your # script. You can skip few databases from backup too. # Last updated: Aug - 2005- and march 2013 # -------------------------------------------------------------------- # This is a free shell script under GNU GPL version 2.0 or above # Copyright (C) 2004, 2005 nixCraft project # ------------------------------------------------------------------------- # This script is part of nixCraft shell script collection (NSSC) # Visit http://bash.cyberciti.biz/ for more information. # ------------------------------------------------------------------------- MyUSER="YOURUSERNAME" # USERNAME MyPASS="YOURPASSSWORD FOR THE ABOVE USERNAME" # PASSWORD MyHOST="localhost" # Hostname # Linux bin paths, change this if it can not be autodetected via which command MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" CHOWN="$(which chown)" CHMOD="$(which chmod)" GZIP="$(which gzip)" # Backup Dest directory, change this if you have someother location DEST="/root/dbbackup" # Main directory where backup will be stored MBD="$DEST/mysql" # Get hostname HOST="$(hostname)" # Get date and time in mm-dd-yyyy--H:M:S format NOW="$(date +"%m-%d-%Y--%H%M")" # File to store current backup file FILE="" # Store list of databases DBS="" # DO NOT BACKUP these databases IGGY="information_schema performance_schema" [ ! -d $MBD ] && mkdir -p $MBD || : # Only root can access it! $CHOWN 0.0 -R $DEST $CHMOD 0600 $DEST # Get all database list first DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" for db in $DBS do skipdb=-1 for i in $IGGY do [ "$db" == "$i" ] && skipdb=1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$MBD/$db.$HOST.$NOW.gz" # do all inone job in pipe, # connect to mysql using mysqldump for select mysql database # and pipe it out to gz file in backup dir :) $MYSQLDUMP -u $MyUSER -p$MyPASS $db | $GZIP -9 > $FILE fi done
本页build议您可以通过将--single-transaction添加到您的mysqldump命令或通过授予您正在用于mysqldump的用户的LOCK TABLES特权来修复它。
mysql> GRANT SELECT,LOCK TABLES ON mysql.* TO 'root'@'localhost';
我不知道为什么在MySQL。*上授予LOCK TABLES会解决在information_schema。*中locking表的问题，但这就是他们所说的工作。
你说“我已经给了information_schema数据库的许可”。 MySQL的权限比这更复杂一些。
你究竟给了root用户什么权限？
你给了哪个root用户？ MySQL用户帐户实际上是“用户@主机”。 root @ localhost可以拥有不同的密码和一组权限给[email protected]。
这个查询可能有助于：
mysql> SELECT user, host , Lock_tables_priv FROM mysql.user WHERE user = 'root'; +------+--------------------------+------------------+ | user | host | Lock_tables_priv | +------+--------------------------+------------------+ | root | localhost | Y | | root | % | N | | root | 127.0.0.1 | Y | +------+--------------------------+------------------+
我还应该提到，没有密码的root用户不是一个好主意。