小白过来_个人页

同步RDS数据库到自建mysql数据库

文本只是新手级别（临时遇上，硬着头皮上的这种，从 RDS 同步到本地mysql的教程很少，而且有些情况还不太一样，来来回来折腾了一周才出了结果），很多my.cnf 里的参数都不知所以，所以基本上都是照搬 RDS 里面的参数，一句一句的查，然后写到本地的 my.cnf 里面，希望我的经验能帮到有需要的各位。 备份恢复到本地的相关说明，请参考https://help.aliyun.com/knowledge_detail/41817.html 本例环境为 Mysql5.6 阿里云 RDS / Mysql 5.6本地环境Ubuntu 14.04 / Mysql 5.6 1.安装Percona Xtrabackup wget https://repo.percona.com/apt/percona-release_0.1-5.(lsb_release -sc)_all.debdpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.debapt-get updateapt-get install percona-xtrabackup-24 2.安装Mysql服务和客户端 apt-get install mysql-server-5.6 mysql-client-5.6/etc/init.d/mysql stop 3.下载数据库备份文件 wget -c '<数据备份文件外网下载地址>' -O <自定义文件名>.tar.gz 4.解压备份文件 bash rds_backup_extract.sh -f <数据备份文件名>.tar.gz -C /home/mysql/data 5.恢复解压好的文件 innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data 6.修改backup-my.cnf参数 vim /home/mysql/data/backup-my.cnf # This MySQL options file was generated by >>innobackupex.# The MySQL server# 以下参数请勿直接复制，注释的5个参数是固定的，不然启动会报错[mysqld]innodb_checksum_algorithm=innodb#innodb_log_checksum_algorithm=innodbinnodb_data_file_path=ibdata1:200M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=1048576000#innodb_fast_checksum=falseinnodb_page_size=16384#innodb_log_block_size=512innodb_undo_directory=.innodb_undo_tablespaces=0#rds_encrypt_data=false#innodb_encrypt_algorithm=aes_128_ecb 7.修改文件属主 chown -R mysql:mysql /home/mysql/data 8.启动MySQL进程 mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data 9.登录MySQL数据库进行修改 注：删除表示因为无法设置 slave ，需要重新导入（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.sqlmysql>quit 10.修改my.cnf（把backup-my.cnf参数复制到my.cnf） mysqladmin shutdownvim /etc/mysql/my.cnf [client] port = 3306socket = /var/run/mysqld/mysqld.sock[mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = 0 [mysqld] user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /home/mysql/datatmpdir = /tmplc-messages-dir = /usr/share/mysqlskip-external-lockingmyisam-recover = BACKUPlog_error = /var/log/mysql/error.log#阿里云RDS优化配置auto_increment_increment = 1auto_increment_offset = 1back_log = 3000binlog_cache_size = 1Mbinlog_checksum = CRC32binlog_row_image = fullbinlog_stmt_cache_size = 32768character_set_server = utf8concurrent_insert = 1connect_timeout = 10default_storage_engine = InnoDBdefault_time_zone = SYSTEMdefault_week_format = 0delayed_insert_limit = 100delayed_insert_timeout = 300delayed_queue_size = 1000delay_key_write = ONdiv_precision_increment = 4eq_range_index_dive_limit = 10explicit_defaults_for_timestamp = falseft_min_word_len = 4ft_query_expansion_limit = 20group_concat_max_len = 1024innodb_adaptive_hash_index = ONinnodb_additional_mem_pool_size = 2097152innodb_autoinc_lock_mode = 1innodb_concurrency_tickets = 500innodb_ft_max_token_size = 84innodb_ft_min_token_size = 3innodb_large_prefix = 0innodb_lock_wait_timeout = 50innodb_max_dirty_pages_pct = 75innodb_old_blocks_pct = 37innodb_old_blocks_time = 0innodb_online_alter_log_max_size = 134217728innodb_open_files = 300innodb_print_all_deadlocks = OFFinnodb_purge_batch_size = 20innodb_purge_threads = 1innodb_read_ahead_threshold = 56innodb_read_io_threads = 4innodb_rollback_on_timeout = OFFinnodb_stats_method = nulls_equalinnodb_stats_on_metadata = OFFinnodb_stats_sample_pages = 8innodb_strict_mode = OFFinnodb_table_locks = ONinnodb_thread_concurrency = 0innodb_thread_sleep_delay = 10000innodb_write_io_threads = 4interactive_timeout = 7200key_cache_age_threshold = 300key_cache_block_size = 1024key_cache_division_limit = 100log_queries_not_using_indexes = OFFlong_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 = 0lower_case_table_names = 1low_priority_updates = 0max_allowed_packet = 1024Mmax_connect_errors = 20max_length_for_sort_data = 1024max_prepared_stmt_count = 16382max_write_lock_count = 102400myisam_sort_buffer_size = 262144net_read_timeout = 30net_retry_count = 10net_write_timeout = 60open_files_limit = 65535performance_schema = OFFquery_alloc_block_size = 8192query_cache_limit = 1048576query_cache_size = 0query_cache_type = 1query_cache_wlock_invalidate = OFFquery_prealloc_size = 8192#rds_reset_all_filter = 0slow_launch_time = 2sql_mode =table_definition_cache = 512table_open_cache = 2000thread_stack = 262144tmp_table_size = 262144transaction_isolation = READ-COMMITTEDwait_timeout = 86400#优化结束#GTID设置server-id = 148log-bin = mysql.binlog-bin-index = mysql-bin.indexlog-slave-updates = 1skip_slave_start = 1relay-log = relay-logrelay_log_index = relay-log.indexexpire_logs_days = 0max_binlog_size = 500Mdefault-storage-engine=INNODBmaster-info-repository=TABLErelay-log-info_repository=TABLEbinlog-format=ROWgtid-mode=onenforce-gtid-consistency=true#backup-my.cnf参数innodb_checksum_algorithm=innodb#innodb_log_checksum_algorithm=innodbinnodb_data_file_path=ibdata1:200M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=1048576000#innodb_fast_checksum=falseinnodb_page_size=16384#innodb_log_block_size=512innodb_undo_directory=.innodb_undo_tablespaces=0#backup-my.cnf结束replicate-ignore-db=mysqlreplicate-ignore-db=testreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-do-db=db1replicate-do-db=db2#GTID结束[mysqldump]quickquote-namesmax_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M!includedir /etc/mysql/conf.d/ 注：my.cnf的参数可以参考RDS的参数，我这里是照搬，请自己对照情况进行修改。 11.设置slave(请先在rds控制台创建一个用来同步的账户，建议只读) /etc/init.d/mysql/restart cat /home/data/mysql/xtrabackup_slave_info#文件里面就两段字，复制下来，待会在 mysql 里面写入。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='同步账号', MASTER_PASSWORD='同步密码', MASTER_AUTO_POSITION=1;mysql>START SLAVE;mysql>SHOW SLAVE STATUS G 问题解答 1.首次启动数据库出现如下提示 [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*什么的不管用。 2.unknown variable 'xxxx' [ERROR]/usr/sbin/mysqld: unknown variable 'xxxx' PS:到my.cnf里面注释xxxx 3.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修复即可 4.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 memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0xxxxxxxxxxxxxAttempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong... PS：...他只是卡住了而已，my.cnf里面部分参数设置不当，等一会就可以连了。。别问我为什么知道。。 5.同步时报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里面没有的参数，你本地可以直接注释掉。 对于GTID的参数，除了 server-id 不能与 master 一样，其他的都可以从 RDS 里面查询。 以上，遇到问题欢迎留言。