65 خطوط
2.4 KiB
Markdown
65 خطوط
2.4 KiB
Markdown
# SQLite to MySQL controlled migration
|
|
|
|
## Preconditions
|
|
|
|
- Work from a dedicated branch and record the source commit.
|
|
- Put the application in a planned write freeze.
|
|
- Keep the source SQLite file outside the web root.
|
|
- Create an immutable copy and verify its SHA-256 hash.
|
|
- Provision an empty MySQL 8 database using `utf8mb4` and `utf8mb4_unicode_ci`.
|
|
- Use a dedicated least-privilege migration account. Do not commit its password.
|
|
|
|
## Dry run
|
|
|
|
Configure only the `MIGRATION_MYSQL_*` variables. Keep `DB_CONNECTION=sqlite`.
|
|
|
|
```sh
|
|
php artisan migrate --database=migration_mysql --force
|
|
php artisan db:transfer-sqlite-to-mysql
|
|
```
|
|
|
|
The transfer command does not write unless `--execute` is supplied.
|
|
|
|
## Transfer
|
|
|
|
```sh
|
|
php artisan db:transfer-sqlite-to-mysql --execute --truncate
|
|
php artisan db:transfer-sqlite-to-mysql --execute
|
|
```
|
|
|
|
The second execution proves idempotency. Both runs must finish with identical source
|
|
and target row counts. Sessions, access tokens, cache, queues, reset tokens, and the
|
|
migration repository are intentionally excluded; users must authenticate again.
|
|
|
|
## Validation gates
|
|
|
|
- All migrations succeed on an empty MySQL database.
|
|
- Every transferred table has equal source and target row counts.
|
|
- Foreign-key validation reports no orphan rows.
|
|
- Unique business keys have no duplicates.
|
|
- JSON columns contain valid JSON.
|
|
- Dates, times, decimals, booleans, Persian text, and emoji round-trip correctly.
|
|
- Backend, frontend build, and browser tests pass with MySQL as the active test database.
|
|
- A manual smoke test covers login, project/task CRUD, sprint, meeting, files, notifications, settings, and reports.
|
|
|
|
## Cutover
|
|
|
|
1. Enable maintenance/write-freeze mode.
|
|
2. Take and hash a final SQLite backup.
|
|
3. Re-run the idempotent transfer without `--truncate`.
|
|
4. Run validation gates.
|
|
5. Change production secrets to `DB_CONNECTION=mysql`.
|
|
6. Clear configuration cache, restart application and queue workers, then run health and smoke checks.
|
|
7. Retain the SQLite source read-only for the defined rollback period.
|
|
|
|
## Rollback
|
|
|
|
If any validation or smoke check fails:
|
|
|
|
1. Stop writes immediately.
|
|
2. Restore the previous application configuration and `DB_CONNECTION=sqlite`.
|
|
3. Restore the verified final SQLite copy if the source was touched.
|
|
4. Restart application and workers.
|
|
5. Run `/up`, authentication, project, task, and file smoke checks.
|
|
6. Preserve MySQL unchanged for incident analysis; never merge partial target data back manually.
|