[Ruby on rails + Mysql] Data migration procedure memo when switching from heroku to AWS

Write down as a memorandum about data migration when migrating the production environment of the application created by ruby on rails from heroku to AWS.

Actually, I think that moving the server environment after the actual operation is a bad move because it takes a lot of risk and effort, but forgive me because it is a personal development.

environment

・ Ruby 2.7.1 ・ Rails 6.0.3.2 · Mysql 5.7

It is assumed that the AWS infrastructure is built in the following form

スクリーンショット 2021-01-11 10.27.31.png

heroku side: Export data from clearDB (Mysql)

In the case of a production environment, is it necessary to take measures such as temporarily stopping the server to prevent data loss due to a time lag? Please follow the team policy for details. As the title suggests, only the data migration procedure is described here.

Get database URL

$heroku config --app app name| grep DATABASE_URL
CLEARDB_DATABASE_URL:     mysql://<USER_NAME>:<PASSWORD>@< HOST >/<DB_NAME>?reconnect=true

Export data

Export the data. It will probably take a while, so wait patiently.

If dump.sql is output to the current directory, it is successful.

mysqldump -uUSER_NAME -pPASSWORD -h HOST -r dump.sql --single-transaction DB_NAME --skip-column-statistics

To briefly explain the contents of the options,

--single-transaction ・・・ Enclose the dump process in a transaction. Effective for maintaining data integrity. It seems that another option is required for DBs that include MyISAM tables.

--skip-column-statistics ・・・ Avoid errors that occur when dumping to a MySQL server earlier (such as 5.7) with mysqldump 8 or later. I'm not sure about the details. ..

Refer to the following article https://blog.pinkumohikan.com/entry/mysqldump-disable-column-statistics

There was also an article that wrote about frequently used options for mysqldump, so I'll post a link here.

** Frequently used mysqldump options and usage examples ** https://qiita.com/ryounagaoka/items/7be0479a36c97618907f

AWS side: Transfer sql to EC2

Premise ・ Ssh connection is established -If the client ssh/config is as follows

Host myapp
        HostName        IPAddress
        Port            22
        IdentityFile    ~/.ssh/myapp_rsa
        User            hoge

Hit the following command

scp source file myapp:/Forwarding directory

If you cannot transfer, refer to the following site etc. http://gallardolp570.hatenablog.com/entry/2014/11/17/205325

AWS side: Import sql to RDS

Confirm the endpoint, user name, and password to connect to in advance.

mysql -h endpoint-u username-p DB name> dump.sql

If you want to check the progress, connect with a pipe and attach a pv command. https://qiita.com/hiroq/items/efd8c3580c9c9457c869

If it is reflected, the import is successful. Thank you for your hard work. There are many parts that imitate what you see, so I would appreciate it if you could point out any mistakes.

Reference site

http://kayakuguri.github.io/blog/2015/09/10/mysql-postgres-import-export/

https://photo-tea.com/p/aws-ec2-to-rds-csv/

http://gallardolp570.hatenablog.com/entry/2014/11/17/205325

https://www.it-swarm-ja.tech/ja/mysql/mysqldump%E3%82%A8%E3%83%A9%E3%83%BC1045%E6%AD%A3%E3%81%97%E3%81%84%E3%83%91%E3%82%B9%E3%83%AF%E3%83%BC%E3%83%89%E3%81%AA%E3%81%A9%E3%81%AB%E3%82%82%E3%81%8B%E3%81%8B%E3%82%8F%E3%82%89%E3%81%9A%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%81%8C%E6%8B%92%E5%90%A6%E3%81%95%E3%82%8C%E3%81%BE%E3%81%97%E3%81%9F/1043190719/

https://qiita.com/hiroq/items/efd8c3580c9c9457c869

https://blog.pinkumohikan.com/entry/mysqldump-disable-column-statistics