Why This Migration Is Worth Doing

This Flask blog ships with SQLite by default, which is exactly the right choice for local development and small personal sites. It removes setup friction, keeps deployment simple, and works well when one process writes occasionally. The tradeoff appears later, not on day one. Once your blog becomes a long-running service with admin publishing, comments, traffic logging, image uploads, and regular backups, SQLite starts to show its limits more clearly: write concurrency is narrow, operational tooling is less standardized, and production recovery often revolves around a single database file.

That does not mean every blog should move to MySQL immediately. It means a migration becomes reasonable when the application is no longer just a lightweight content file with routes around it. In this project, the migration path is already hinted at in the codebase. config.py reads DATABASE_URL first, and the repository README includes a MySQL URL example using SQLAlchemy and utf8mb4. In other words, the application layer is already prepared. The work is mostly operational: backup, schema creation, data transfer, cutover, verification, and rollback.

When It Makes Sense to Leave SQLite

For a personal Flask blog, I would treat these as the real migration signals:

• The blog now runs continuously on a Linux server instead of being started only when needed.
• Admin publishing, comment writes, analytics updates, or automation tasks are beginning to overlap.
• You want a more standard database workflow for backup, credentials, monitoring, or future scaling.
• You are planning to treat the blog as a durable production service rather than a side project that can tolerate occasional manual repair.

If none of that is true yet, keep SQLite. It is still one of the best defaults in small Flask systems. Migrate when the operational benefits become concrete, not because MySQL sounds more "serious."

Preparation: Do These Three Things First

1. Back Up SQLite and Uploaded Files

This repository already includes a backup script:

cd D:\project\webokelog
python scripts/backup_sqlite.py

That script copies database.db and archives static/uploads/ into backups/. For a content site, that is the right baseline because the database and uploaded assets form one logical recovery unit. If the migration goes wrong, the fastest recovery path is usually "restore the old state exactly," not "reconstruct everything manually."

2. Create a Dedicated MySQL Database and User

Do not point the app at a root account. Create a dedicated schema and an application user with scoped privileges:

CREATE DATABASE blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'blog_user'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-strong-password';
GRANT ALL PRIVILEGES ON blog.* TO 'blog_user'@'127.0.0.1';
FLUSH PRIVILEGES;

The critical detail here is utf8mb4. Blog content is not just ASCII. Titles, summaries, tags, multilingual text, and emoji all become easier to handle when the database and connection both use full Unicode support from the start.

3. Install a MySQL Driver Without Cutting Over Yet

The project’s default requirements.txt does not include a MySQL driver, so install one explicitly:

pip install PyMySQL

Then prepare a temporary environment value for testing:

APP_ENV=production
DATABASE_URL=mysql+pymysql://blog_user:replace-with-a-strong-password@127.0.0.1:3306/blog?charset=utf8mb4

Keep this separate from your live production .env until the target database has been tested. The safest pattern is to prove connectivity, schema creation, and import behavior first, and only then replace the production connection string.

A Migration Sequence That Works in Practice

Step 1: Let the Application Create an Empty Schema

This Flask blog uses Flask-SQLAlchemy. That means the cleanest way to prepare MySQL is usually to let the current models create tables in the target database:

$env:DATABASE_URL="mysql+pymysql://blog_user:replace-with-a-strong-password@127.0.0.1:3306/blog?charset=utf8mb4"
python -c "from app import app, db; ctx = app.app_context(); ctx.push(); db.create_all(); print('schema ready')"

Why start this way instead of translating a SQLite dump directly? Because SQLite and MySQL have different dialect behavior, defaults, and edge cases. Rebuilding tables from the current model definitions is usually more predictable for a Flask app like this one, especially around the core blog tables such as article, category, tag, and article_tag.

Step 2: Run a One-Off Import Script

The safest data move is a one-way import from SQLite into the pre-created MySQL schema. Keep the import isolated from the final production cutover. A simplified script for this repository can look like this:

import os
import sqlite3
from app import app, db, Article, Category, Tag

SQLITE_PATH = "database.db"
os.environ["DATABASE_URL"] = (
    "mysql+pymysql://blog_user:replace-with-a-strong-password@127.0.0.1:3306/blog?charset=utf8mb4"
)

con = sqlite3.connect(SQLITE_PATH)
con.row_factory = sqlite3.Row

with app.app_context():
    db.create_all()

    categories = {}
    for row in con.execute("select * from category"):
        item = Category(name=row["name"], slug=row["slug"], description=row["description"] or "")
        db.session.add(item)
        db.session.flush()
        categories[row["id"]] = item

    tags = {}
    for row in con.execute("select * from tag"):
        item = Tag(name=row["name"], slug=row["slug"])
        db.session.add(item)
        db.session.flush()
        tags[row["id"]] = item

    for row in con.execute("select * from article order by id"):
        article = Article(
            title=row["title"],
            slug=row["slug"],
            language=row["language"] or "zh",
            translation_key=row["translation_key"] or row["slug"],
            summary=row["summary"] or "",
            content_md=row["content_md"] or "",
            content_html=row["content_html"] or "",
            cover_image=row["cover_image"] or "",
            status=row["status"] or "draft",
            is_top=bool(row["is_top"]),
            view_count=row["view_count"] or 0,
            category=categories[row["category_id"]],
            author_user_id=row["author_user_id"],
            created_at=row["created_at"],
            updated_at=row["updated_at"],
            published_at=row["published_at"],
        )
        db.session.add(article)
        db.session.flush()

        for rel in con.execute("select tag_id from article_tag where article_id = ?", (row["id"],)):
            article.tags.append(tags[rel["tag_id"]])

    db.session.commit()

For this specific codebase, that covers the content path most readers care about first: categories, tags, articles, and many-to-many tag relations. If your production site also uses user submissions, comments, ad settings, or editable site configuration, add site_user, comment, ad_config, and site_config in a dependency-aware order.

The key engineering rule is simple: import parent tables first, then the tables that reference them by foreign key.

Step 3: Verify Before You Touch Production

Do not switch the live DATABASE_URL yet. First, verify the target database:

SELECT COUNT(*) FROM article;
SELECT COUNT(*) FROM category;
SELECT COUNT(*) FROM tag;
SELECT language, COUNT(*) FROM article GROUP BY language;
SHOW VARIABLES LIKE 'character_set%';

Then launch the app against MySQL and do a real smoke test:

$env:DATABASE_URL="mysql+pymysql://blog_user:replace-with-a-strong-password@127.0.0.1:3306/blog?charset=utf8mb4"
python app.py

Check the homepage, article detail pages, category pages, tag pages, admin article list, and login flow. The most common migration failures are not dramatic crashes. More often, the app starts fine while subtle relationships are broken: tag associations disappear, a bilingual article loses its translation_key, or publication timestamps no longer render the way you expect.

How to Perform the Production Cutover Safely

Use a low-traffic maintenance window and keep the steps disciplined:

1. Stop writes or temporarily pause admin publishing so SQLite does not keep changing during the final move.
2. Create one more backup.
3. Run the import script against the final SQLite state.
4. Replace the production DATABASE_URL with the MySQL connection string.
5. Restart Gunicorn or the app process.
6. Watch logs and test the admin and public pages immediately.

If your deployment uses Gunicorn + Nginx + systemd, keep an eye out for connection errors, charset issues, or ORM-level OperationalError traces after restart. Also make sure the SQLAlchemy engine option already present in this project stays intact:

SQLALCHEMY_ENGINE_OPTIONS = {"pool_pre_ping": True}

That setting is useful for long-running MySQL connections because it helps the app detect dead connections before trying to use them.

Common Pitfalls

Treating a File Copy as a Database Upgrade

Copying database.db to another machine is a backup step, not a migration strategy. If production still runs on SQLite afterward, you have not changed the operational model at all.

Forgetting Character Sets

If the database is not utf8mb4, or the SQLAlchemy connection string omits charset=utf8mb4, multilingual content becomes a long-term risk. This is one of those problems that is cheap to prevent and annoying to fix later.

Switching the App Before Data Is Imported

This is one of the easiest mistakes to make during a rushed migration. If you change the production connection string first, live traffic hits an empty schema or partially imported data. The order should always be schema, import, verify, then cut over.

Verifying Counts but Not Relationships

A blog is more than rows in article. You need to confirm article_tag, category ownership, translation_key, and published_at behavior. Those fields affect rendering, routing, and SEO in ways that are easy to miss during a superficial check.

No Rollback Plan

Rollback should be boring. Keep the old SQLite file, keep the previous environment configuration, and be ready to restart the service with the old connection if the cutover shows issues. Do not delete database.db on the same day you migrate.

When SQLite Is Still the Better Choice

If the site is still maintained by one person, posting frequency is low, comments are rare, and you do not need stronger operational tooling, SQLite is still a perfectly good answer. A migration is justified when it reduces real operational pain, not when it satisfies aesthetics.

Final Takeaway

Moving a Flask blog from SQLite to MySQL is not primarily about changing a URL string. It is about turning an informal local-default database setup into a more durable production workflow. In this project, the lowest-risk path is straightforward: use the existing backup script, create the MySQL schema from the current models, import data with a one-off script, verify both counts and rendered pages, and only then switch DATABASE_URL during a quiet window.

That sequence keeps risk contained, preserves rollback options, and gives the blog a cleaner foundation for future backups, monitoring, and operational growth.