同步阿里云RDS数据库到自建mysql数据库

共7824个字

备份恢复到本地的相关说明,请参考阿里云官方教程

本例环境为 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.deb
dpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.deb
apt-get update
apt-get install percona-xtrabackup-24

2.安装mysql服务和客户端
apt-get install mysql-server-5.6 mysql-client-5.6
 /etc/init.d/mysql stop

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

4.解压备份文件(本例以/home/mysql/data目录为例,请自行修改

 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
[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

7.
修改文件属主
chown -R mysql:mysql /home/mysql/data8.启动MySQL进程
mysqld_safe –defaults-file=/home/mysql/data/backup-my.cnf –user=mysql –datadir=/home/mysql/data

9.登录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

10.修改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的参数,我这里是照搬,请自己对照情况进行修改。

11.设置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

 

问题解答

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 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里面部分参数设置不当,等一会就可以连了。。别问我为什么知道。。

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的参数,官方有很详细的解释,这里就不多做解释了。

以上,遇到问题欢迎留言。

 

❤ 喜欢 828

3条回应:“同步阿里云RDS数据库到自建mysql数据库”

  1. Quality articles is the crucial to interest the viewers to visit
    the web page, that’s what this web site is providing.

  2. Wonderful site you have here but I was curious if you
    knew of any message boards that cover the same topics discussed here?
    I’d really like to be a part of online community where I can get feedback from other knowledgeable people
    that share the same interest. If you have any recommendations, please let me know.

    Many thanks!

  3. Quality content is the important to attract the viewers to pay a visit the website, that’s what this site is
    providing.

发表评论

必填项已用*标注

归档于 Ubuntu