(2)はオプションです。
同一VPC内のMySQL on EC2からRDS Auroraへリストアする場合は不要です。
stunnelの設定は過去ネタを参照してください。
ソースデータベース環境の準備
ソースデータベースのmy.cnf
パラメータはまあ適当に
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
server-id=1
character_set_server=utf8mb4
log_bin
innodb_buffer_pool_size=2G
#innodb_file_per_table
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
レプリケーションユーザの作成
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY ' ';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
移行用データベースとテーブルの準備
データベース
mysql> show databases;
+---------------------------------+
| Database |
+---------------------------------+
| information_schema |
| mysql |
| orenodb |
| performance_schema |
+---------------------------------+
6 rows in set (0.00 sec)
$ sudo mkdir /s3-restore
$ innobackupex --user=root --no-timestamp --stream=tar /s3-restore/backup | \
split -d --bytes=512000 - /s3-restore/backup_archives/backup.tar
160910 19:27:35 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
..省略..
Using server version 5.6.22-log
innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
YYMMDD HH24:MI:SS >> log scanned up to (86118369257)
..省略..
YYMMDD HH24:MI:SS [00] Writing xtrabackup_info
YYMMDD HH24:MI:SS [00] ...done
xtrabackup: Transaction log of lsn (85899943219) to (109465256896) was copied.
YYMMDD HH24:MI:SS completed OK!
mysql -u user -p -h
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| migrationtest |
| mysql |
| orenodb |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)
mysql> use orenodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+-------------------+
| Tables_in_orenodb |
+-------------------+
| myisam |
| test |
+-------------------+
2 rows in set (0.00 sec)
mysql> show table status;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+--------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+--------------------+---------+
| myisam | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8mb4_general_ci | NULL | row_format=DYNAMIC | |
| test | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8mb4_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+----------+--------------------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM myisam, test;
+------+------+
| a | a |
+------+------+
| 100 | 10 |
+------+------+
1 row in set (0.00 sec)
mysql> use orenodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_orenodb |
+-------------------+
| item |
| myisam |
| test |
+-------------------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM item;
+----------+
| COUNT(*) |
+----------+
| 16384 |
+----------+
1 row in set (0.02 sec)