本例环境为 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里面没有的参数，你本地可以直接注释掉。
3289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
