32

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

 5 years ago
source link: https://www.linuxprobe.com/rds-xtrabackup-mysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

本例环境为 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里面没有的参数，你本地可以直接注释掉。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK