实例：同步阿里云RDS库到自建mysql库

本例环境为 Mysql5.6

阿里云 RDS / Mysql 5.6

本地环境Ubuntu 14.04 / Mysql 5.6

安装Percona Xtrabackup

wget https://repo.percona.com/apt/percona-release_0.1-5.$(lsb_release -sc)_all.deb

dpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.deb

apt-get update

apt-get install percona-xtrabackup-24

安装mysql服务和客户端

apt-get install mysql-server-5.6 mysql-client-5.6

/etc/init.d/mysql stop

下载RDS数据库备份文件

wget -c '< 数据备份文件外网下载地址>' -O < 自定义文件名>.tar.gz

解压备份文件（本例以/home/mysql/data目录为例，请自行修改）

bash rds_backup_extract.sh -f < 数据备份文件名>.tar.gz -C /home/mysql/data

恢复解压好的文件

innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data

修改backup-my.cnf参数

vim /home/mysql/data/backup-my.cnf

# This MySQL options file was generated by >>innobackupex.

# The MySQL server

[mysqld]

innodb_checksum_algorithm=innodb

#innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1048576000

#innodb_fast_checksum=false

innodb_page_size=16384

#innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

#rds_encrypt_data=false

#innodb_encrypt_algorithm=aes_128_ecb

修改文件属主

chown -R mysql:mysql /home/mysql/data

启动MySQL进程

mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data

登录MySQL数据库进行修改

mysql -uroot

mysql>delete from mysql.db where user<>'root' and char_length(user)>0;delete from mysql.tables_priv where user<>'root' and char_length(user)>0;flush privileges;

mysql>use mysql;

mysql>drop table slave_master_info;

mysql>drop table slave_relay_log_info;

mysql>drop table slave_worker_info;

mysql>drop table innodb_index_stats;

mysql>drop table innodb_table_stats;

mysql>source /usr/share/mysql/mysql_system_tables.sql

mysql>quit

mysqladmin shutdown

修改my.cnf（把backup-my.cnf参数复制到my.cnf）

vim /etc/mysql/my.cnf

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /home/mysql/data

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

skip-external-locking

myisam-recover = BACKUP

log_error = /var/log/mysql/error.log

#阿里云RDS优化配置

auto_increment_increment = 1

auto_increment_offset = 1

back_log = 3000

binlog_cache_size = 1M

binlog_checksum = CRC32

binlog_row_image = full

binlog_stmt_cache_size = 32768

character_set_server = utf8

concurrent_insert = 1

connect_timeout = 10

default_storage_engine = InnoDB

default_time_zone = SYSTEM

default_week_format = 0

delayed_insert_limit = 100

delayed_insert_timeout = 300

delayed_queue_size = 1000

delay_key_write = ON

div_precision_increment = 4

eq_range_index_dive_limit = 10

explicit_defaults_for_timestamp = false

ft_min_word_len = 4

ft_query_expansion_limit = 20

group_concat_max_len = 1024

innodb_adaptive_hash_index = ON

innodb_additional_mem_pool_size = 2097152

innodb_autoinc_lock_mode = 1

innodb_concurrency_tickets = 500

innodb_ft_max_token_size = 84

innodb_ft_min_token_size = 3

innodb_large_prefix = 0

innodb_lock_wait_timeout = 50

innodb_max_dirty_pages_pct = 75

innodb_old_blocks_pct = 37

innodb_old_blocks_time = 0

innodb_online_alter_log_max_size = 134217728

innodb_open_files = 300

innodb_print_all_deadlocks = OFF

innodb_purge_batch_size = 20

innodb_purge_threads = 1

innodb_read_ahead_threshold = 56

innodb_read_io_threads = 4

innodb_rollback_on_timeout = OFF

innodb_stats_method = nulls_equal

innodb_stats_on_metadata = OFF

innodb_stats_sample_pages = 8

innodb_strict_mode = OFF

innodb_table_locks = ON

innodb_thread_concurrency = 0

innodb_thread_sleep_delay = 10000

innodb_write_io_threads = 4

interactive_timeout = 7200

key_cache_age_threshold = 300

key_cache_block_size = 1024

key_cache_division_limit = 100

log_queries_not_using_indexes = OFF

long_query_time = 1

#loose_max_statement_time = 0

#loose_rds_indexstat = OFF

#loose_rds_max_tmp_disk_space = 10737418240

#loose_rds_tablestat = ON

#loose_rds_threads_running_high_watermark = 50000

#loose_tokudb_buffer_pool_ratio = 0

lower_case_table_names = 1

low_priority_updates = 0

max_allowed_packet = 1024M

max_connect_errors = 20

max_length_for_sort_data = 1024

max_prepared_stmt_count = 16382

max_write_lock_count = 102400

myisam_sort_buffer_size = 262144

net_read_timeout = 30

net_retry_count = 10

net_write_timeout = 60

open_files_limit = 65535

performance_schema = OFF

query_alloc_block_size = 8192

query_cache_limit = 1048576

query_cache_size = 0

query_cache_type = 1

query_cache_wlock_invalidate = OFF

query_prealloc_size = 8192

#rds_reset_all_filter = 0

slow_launch_time = 2

sql_mode =

table_definition_cache = 512

table_open_cache = 2000

thread_stack = 262144

tmp_table_size = 262144

transaction_isolation = READ-COMMITTED

wait_timeout = 86400

#优化结束

#GTID设置

server-id = 148

log-bin = mysql.bin

log-bin-index = mysql-bin.index

log-slave-updates = 1

skip_slave_start = 1

relay-log = relay-log

relay_log_index = relay-log.index

expire_logs_days = 0

max_binlog_size = 500M

default-storage-engine=INNODB

master-info-repository=TABLE

relay-log-info_repository=TABLE

binlog-format=ROW

gtid-mode=on

enforce-gtid-consistency=true

#backup-my.cnf参数

innodb_checksum_algorithm=innodb

#innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1048576000

#innodb_fast_checksum=false

innodb_page_size=16384

#innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

#backup-my.cnf结束

replicate-ignore-db=mysql

replicate-ignore-db=test

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-do-db=db1

replicate-do-db=db2

#GTID结束

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

!includedir /etc/mysql/conf.d/

注：my.cnf的参数可以参考RDS的参数，我这里是照搬，请自己对照情况进行修改。

设置slave(请先在rds控制台创建一个用来同步的账户，建议只读)

/etc/init.d/mysql/restart

cat /home/data/mysql/xtrabackup_slave_info

#文件里面就两段字，复制下来，待会用到。

mysql -uroot

mysql>SET GLOBAL gtid_purged='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1-123456';

mysql>CHANGE MASTER TO MASTER_HOST='RDS外网地址', MASTER_PORT=3306, MASTER_USER='RDS同步账号', MASTER_PASSWORD='RDS同步密码', MASTER_AUTO_POSITION=1;

mysql>START SLAVE;

mysql>SHOW SLAVE STATUS G

问题解答

首次启动数据库出现如下提示

[ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode

[ERROR] InnoDB: The system tablespace must be writable!

[ERROR] Plugin 'InnoDB' init function returned error.

[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

[ERROR] Unknown/unsupported storage engine: InnoDB

[ERROR] Aborting

PS：重启服务器即可，删除ib*什么的不管用。

unknown variable 'xxxx'

[ERROR]/usr/sbin/mysqld: unknown variable 'xxxx'

PS:到my.cnf里面注释xxxx

Table './mysql/xxx' 报错

[ERROR] /usr/sbin/mysqld: Table './mysql/db' is marked as crashed and should be repaired

[Warning] Checking table: './mysql/db'

[ERROR] 1 client is using or hasn't closed the table properly

[ERROR] /usr/sbin/mysqld: Table './mysql/event' is marked as crashed and should be repaired

[Warning] Checking table: './mysql/event'

[ERROR] 1 client is using or hasn't closed the table properly

PS：使用myisamchk -c -r /home/mysql/data/db/tablesname.MYI修复即可

information that should help you find out what is causing the crash.

It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68104 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xxxxxxxxxxxxx

Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went

terribly wrong...

PS：...他只是卡住了而已，my.cnf里面部分参数设置不当，等一会就可以连了。。别问我为什么知道。。

同步时报1236错误

[ERROR] Error reading packet from server: The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)

ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236

[ERROR] Error reading packet from server: The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)

PS:重新从RDS获取新的备份（当前的新备份）

最后，本地my.cnf里面的配置，如果你不知道有些参数数值应该设置多少，可以登陆RDS服务器使用show命令进行查询，查询到的数值单位是字节，不会换算自己百度用工具换算一下就行，上文中关于my.cnf阿里云优化的部分，全部使用了RDS控制台里面的参数（导出复制进去就行，记得注释掉有rds的参数），RDS里面没有的参数，你本地可以直接注释掉。