求军哥来看看

jalena 发表于 2013-9-1 19:55:53

服务器是centos6.4 x64的版本。安装的是lnmp1.0 mysql版本5.5.28-log
现在导出数据库报错，求军哥看看。。。

这是备份命令

for db in `mysql -u111 -p1111 -B -N -e 'SHOW DATABASES' | xargs`; do
(mysqldump -u1111 -p1111 ${db} --skip-lock-tables | gzip -9 - > ${db}.sql.gz)
done

报错是这个
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `INNODB_BUFFER_PAGE`': Lost connection to MySQL server during query (2013)
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) when trying to connect
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) when trying to connect
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

[ 本帖最后由 jalena 于 2013-9-1 20:00 编辑 ]

licess 发表于 2013-9-2 10:09:24

mysql可能挂掉或没启动

jalena 发表于 2013-9-2 12:34:50

回复 2# 的帖子

启动了的，现在主要是要写个备份的脚本。总是在导出数据的地方报错，不过现在好了，我把几个默认库给排除了就ok。
#!/bin/bash

# Author: Jalena
# Website: http://jalena.bcsytv.com/archives/1099

# 常规定义
MYSQL_USER="user"
MYSQL_PASS="pass"
DROPBOX_DIR="/backup/$(date +%Y-%m-%d)"
BACK_DIR="backup"

# 备份网站数据目录
NGINX_DATA="/usr/local/nginx/conf/vhost"
BACKUP_BLOG="/home/wwwroot/blog"
BACKUP_DEFAULT="/home/wwwroot/default"
BACKUP_JOOMLA="/home/wwwroot/joomla"

# 定义备份文件名
mysql_DATA=mysql_$(date +"%Y%m%d").tar.gz
www_BLOG=blog_$(date +%Y%m%d).tar.gz
www_DEFAULT=deafult_$(date +%Y%m%d).tar.gz
www_joomla=joomla_$(date +%Y%m%d).tar.gz
nginx_config=nginx_$(date +%Y%m%d).tar.gz

# 定义Dropbox远程文件夹（3天删除一次）
Old_DROPBOX_DIR=/backup/$(date -d -3day +%Y-%m-%d)

# 判断本地备份目录，不存在则创建
if [ ! -d $BACK_DIR ] ;
then
mkdir -p "$BACK_DIR"
fi

# 进入备份目录
cd $BACK_DIR

# 备份所有数据库
# 导出需要备份的数据库，清除不需要备份的库
mysql -uroot -pprivacy -B -N -e 'SHOW DATABASES' > databases.db
sed -i '/performance_schema/d' databases.db
sed -i '/information_schema/d' databases.db
sed -i '/mysql/d' databases.db

for db in $(cat databases.db)
do
mysqldump -u$MYSQL_USER -p$MYSQL_PASS ${db} | gzip -9 - > ${db}.sql.gz
done

# 打包数据库
tar -zcvf $mysql_DATA *.sql.gz

# 打包本地网站数据
tar -zcvf $www_BLOG $BACKUP_BLOG
tar -zcvf $www_DEFAULT $BACKUP_DEFAULT

# 打包Nginx配置文件
tar -zcvf $nginx_config $NGINX_DATA/*.conf

# upload
cd ~
./dropbox_uploader.sh upload /$BACK_DIR/$mysql_DATA $DROPBOX_DIR/$mysql_DATA
./dropbox_uploader.sh upload /$BACK_DIR/$www_BLOG $DROPBOX_DIR/$www_BLOG
./dropbox_uploader.sh upload /$BACK_DIR/$www_DEFAULT $DROPBOX_DIR/$www_DEFAULT
./dropbox_uploader.sh upload /$BACK_DIR/$nginx_DATA $DROPBOX_DIR/$nginx_DATA

# Delete all local backup
rm -rf $BACK_DIR

# Delete the file 3days ago
./dropbox_uploader.sh delete $Old_DROPBOX_DIR/

# DropBox info
./dropbox_uploader.sh info

exit 0

页: [1]

VPS侦探论坛's Archiver

求军哥来看看

回复 2# 的帖子