Skip to main content

index

JodGig Reports DB Migration Notes​

Before Migration Setup​

rds-jod-prod-reports-instance-1​

show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| jod-v2-prod-reports |
| mysql               |
| performance_schema  |
| sys                 |
+---------------------+
5 rows in set (0.03 sec)
SELECT
    table_name AS `Table`,
    ROUND(data_length / 1024 / 1024, 2) AS `Data Size (MB)`,
    ROUND(index_length / 1024 / 1024, 2) AS `Index Size (MB)`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Total Size (MB)`
FROM
    information_schema.tables
WHERE
    table_schema = 'jod-v2-prod-reports'
ORDER BY
    `Total Size (MB)` DESC;
+-------------------------------+----------------+-----------------+-----------------+
| Table                         | Data Size (MB) | Index Size (MB) | Total Size (MB) |
+-------------------------------+----------------+-----------------+-----------------+
| users                         |           1.52 |            0.36 |            1.88 |
| model_has_permissions         |           0.02 |            0.02 |            0.03 |
| model_has_roles               |           0.02 |            0.02 |            0.03 |
| permissions                   |           0.02 |            0.02 |            0.03 |
| role_has_permissions          |           0.02 |            0.02 |            0.03 |
| roles                         |           0.02 |            0.02 |            0.03 |
| failed_jobs                   |           0.02 |            0.00 |            0.02 |
| media                         |           0.02 |            0.00 |            0.02 |
| migrations                    |           0.02 |            0.00 |            0.02 |
| oauth_access_tokens           |           0.02 |            0.00 |            0.02 |
| oauth_auth_codes              |           0.02 |            0.00 |            0.02 |
| oauth_clients                 |           0.02 |            0.00 |            0.02 |
| oauth_personal_access_clients |           0.02 |            0.00 |            0.02 |
| oauth_refresh_tokens          |           0.02 |            0.00 |            0.02 |
| password_resets               |           0.02 |            0.00 |            0.02 |
| personal_access_tokens        |           0.02 |            0.00 |            0.02 |
+-------------------------------+----------------+-----------------+-----------------+

Summary​

Database stores only user credentials.

  • files are generated which contain reports from read-only to rds-jod-prod-instance-1
  • files are emailed to users

Current setup uses 2 r6g.large instances

  • huge overkill for such a small database.

Reports reads from rds-jod-prod-instance-1

  • writer instance of the previous setup
  • probably the cause for slow queries/high db usage.

rds-jod-prod-instance-1​

Users in rds-jod-prod-instance-1

SELECT User, Host FROM mysql.user;
+-------------------+-----------+
| User              | Host      |
+-------------------+-----------+
| Adi               | %         |
| Ali               | %         |
| JODProd2022       | %         |
| Rhesa             | %         |
| google_read_only  | %         |
| rdsproxyadmin     | %         |
| renz              | %         |
| reports_read_only | %         |
| shaund            | %         |
| sql_read_only     | %         |
| mysql.sys         | localhost |
| rdsadmin          | localhost |
+-------------------+-----------+

Grants for reports_read_only​

show grants for 'reports_read_only'@'%';
+------------------------------------------------------------+
| Grants for reports_read_only@%                             |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'reports_read_only'@'%'              |
| GRANT SELECT ON `jod-v2-prod`.* TO 'reports_read_only'@'%' |
+------------------------------------------------------------+

CREATE USER 'reports_read_only'@'10.0.0.0/16' IDENTIFIED BY ''; GRANT SELECT ON jodgig_reports.* TO 'reports_read_only'@'10.0.0.0/16'; FLUSH PRIVILEGES;