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
参考文档：
1、 解决mysqldump备份报错：SET OPTION SQL_QUOTE_SHOW_CREATE=1 http://yanue.net/post-124.html