0
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

非エンジニアでもDBダンプできるようにしてあげる手順

1. SSHでログインする環境整える

bash
% grep -A4 "hoge " ~/.ssh/bundle/core/config
Host hoge hoge.xxx-yyy.com
    HostName    11.222.333.444
    User        ec2-user
    Port        50381
    IdentityFile    ~/.ssh/bundle/core/hoge_core_prd.pem

2. SSHポートフォワーディングで穴開ける

bash
% ssh -f -N -L 3307:hoge-prd-cluster.cluster-xxxxxxxx.ap-northeast-1.rds.amazonaws.com:3306 ec2-user@11.222.333.444 -i ~/.ssh/bundle/core/hoge_core_prd.pem -p 59039

3. MySQLに接続する

bash
[N16LT052.local]% mysql -h 127.0.0.1 -D fuga -u readonly -P 3307 -p'readonly' -A
mysql: [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 21520288
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

4. SQLを実行する

bash
SELECT
    DATE_FORMAT(dl.created_at, "%Y/%m/%d") AS '利用日',
    u.name AS '利用者氏名',
    '勉強' AS '利用用途',
    d.serial AS 'シリアルナンバー',
    ROUND(TIME_TO_SEC(TIMEDIFF(MAX(dl.created_at), mp.created_at)) / 60, 1) AS '時間(分)',
    ROUND(TIME_TO_SEC(TIMEDIFF(MAX(dl.created_at), mp.created_at)) / 60 * AVG(pt.distance), 1) AS '距離(m)',
    ROUND(MAX(pt.height), 1) AS '高度(m)'
FROM
    datelists dl
INNER JOIN
    mission_projects mp
ON
    dl.mission_project_id = mp.id
INNER JOIN
    users u
ON
    mp.user_id = u.id
INNER JOIN
    fugas d
ON
    dl.fuga_id = d.id
INNER JOIN
    pointables pt
ON
    mp.id = pt.pointable_id
WHERE pt.deleted_at IS NULL
AND pt.pointable_type = 'App\\MissionProject'
AND u.deleted_at IS NULL
AND u.company_id IN (SELECT id FROM companies WHERE deleted_at IS NULL AND name NOT LIKE '%test%' AND name NOT LIKE '%テスト%' AND name NOT LIKE '%アカウント%')
AND YEAR(CONVERT_TZ(dl.created_at, '+00:00', '+09:00')) = 2019
AND MONTH(CONVERT_TZ(dl.created_at, '+00:00', '+09:00')) = 2
GROUP BY dl.id;

5. 実行結果をCSVに整形

そのままCSVファイルに出してもいい
https://qiita.com/catatsuy/items/9fdf4423d5f4885b9bf9

6. そのファイルをsendmailで〇〇さんに送る

7. 上記の手順をスケジューラ(crontab)に登録する

以上

こちらのイベントの一環で投稿しました!
https://qiita.com/official-events/d523df99d6479293ffa7

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
0
Help us understand the problem. What are the problem?