mysqldump数据库同步遇到的问题-CSDN博客

1、同步数据是遇到没有 lock database权限，报 “ mysqldump: Got error: 1044: Access denied for user 'spider_data'@'121.201.10.2' to database 'db_rankapp' when doing LOCK TABLES” 错误

在mysqldump参数上加上 “ --single-transaction” 参数即可

参考文档：

2、同步时遇到 mysqldump 版本跟线上mysql不一致，报“mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':”错误

eagleeye@zs-dw-mon-web01:/opt/idreamsky/product/micontrol$ mysqldump -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' --single-transaction db_rankapp tb_pub_app_income
-- MySQL dump 10.13  Distrib 5.5.27, for Linux (x86_64)
--
-- Host: 115.159.196.80    Database: db_rankapp
-- ------------------------------------------------------
-- Server version    5.6.16-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

我的同步脚本如下，放在crontab里执行：

#/bin/bash
#同步appannie爬虫在腾讯云爬取的排行榜收入下载数据
set -x 

PATH=$PATH:/usr/local/bin
export PATH

db_option="--replace -c --add-drop-table=false --no-create-db --no-create-info --skip-tz-utc --single-transaction"
db_option2="--single-transaction"
character="--default-character-set=utf8"

day=`date +%Y-%m-%d`
echo $day
#mysqldump -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option2 $character db_rankapp config_app  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
#[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_app_rank --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_app_income_rank --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_app_income --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_pub_income_rank --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_pub_country_income --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

mysqldump5.6 -h115.159.196.80 -uspider_data -p'SQH1NRZNsxFUbth1' $db_option $character db_rankapp tb_pub_app_income --where "updated_at >='$day'"  |mysql -h 172.16.150.13 -ueagleeye -p'EYeapp$ea@2' db_spider
[ $? -ne 0 ] && exit 1

exit 0