I've been moving my servers to Amazon EC2 and have been quite happy with it so far. Today I started setting up a backup script to backup the databases from RDS as well as the web files from the EC2 Instance. I got this error when attempting to execute mysqldump:
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)
Problem is that the RDS server is running MySQL 5.6 and MySQLDump is only v 5.1 on CentOS 6.4 as of this article. After a bit of googling I found the answer on a Chinese site, translated it via Google Translate and it works great. Hopefully this will help you too!
cd /root
wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz
tar -xzvf mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz
cp mysql-5.6.13-linux-glibc2.5-x86_64/bin/mysqldump /root
rm -rf mysql-5.6.13-linux-glibc2.5-x86_64
rm -rf mysql-5.6.13-linux-glibc2.5-x86_64.tar.gz
/root/mysqldump [options]
Explanation:
- Change directory where you want to permanently store new mysqldump version
- Download MySQL server version, you might need to find newer version or an x86 package file for your distro
- Untar downloaded file
- Copy msyqldump executable to /root folder (or your destination folder)
- Remove other package contents not needed
- Remove tarball not needed
- Always execute mysqldump with complete path instead of just #mysqldump