多开发者使用SVN时如何同步数据库Schema并解决Maria重启故障？

Hey there, let's fix that annoying MariaDB restart issue you're facing with your SVN-based web app collaboration. First, let's get to the root of the problem: you're probably accidentally committing database runtime files (like InnoDB logs, data files) to SVN, which clash with the pre-release server's existing database state when you update. Here's how to overhaul your workflow for good:

1. 彻底清理SVN，排除数据库运行无关文件 #

First and foremost, make sure SVN never tracks database runtime files again. These files are specific to each environment (local vs pre-release) and should never be shared:

• Use TortoiseSVN to add these items to your global or project-specific ignore list:
  • InnoDB core files: ib_logfile0, ib_logfile1, ibdata1
  • Logs and error files: *.log, *.err
  • Temporary files and sockets: tmp/ directory, mysql.sock
  • If you keep your local database data folder inside the project repo, add the entire folder to ignore (e.g., local_db/)
• Pro tip: Double-check your SVN commit dialog every time to ensure you're not accidentally including these files—TortoiseSVN will gray out ignored items, making it easier to spot.

2. 用版本化迁移脚本管理数据库变更 #

Forget copying local database files to the server. Instead, standardize how you handle database schema and data changes:

• Every time you need to modify the database (add a table, alter a column, insert initial data), write a timestamped migration script (e.g., 20240520_add_user_preferences_table.sql).
• Make scripts idempotent: Add checks to avoid errors if the script runs multiple times. For example:

  -- Only add the column if it doesn't exist
  ALTER TABLE users ADD COLUMN preferences JSON NOT NULL DEFAULT '{}'
  WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='users' AND COLUMN_NAME='preferences');
  

• When you commit code changes that rely on a database update, always commit the corresponding migration script alongside it.

3. 预发布服务器的标准化更新流程 #

Establish a strict process for updating the pre-release server to avoid conflicts:

1. Backup first: Before pulling SVN updates, take a full database backup with:

   mysqldump -u your_db_user -p your_db_name > pre_release_backup_$(date +%Y%m%d).sql
   

2. Pull SVN updates: Get the latest code and migration scripts.
3. Run migrations: Execute all new migration scripts in order (by timestamp) on the pre-release database.
4. Restart MariaDB only if needed: Most schema changes don't require a restart—only do it if the migration explicitly calls for it (e.g., changing innodb settings).

4. 保障本地开发环境的一致性 #

To avoid "it works on my machine" issues, align everyone's local setup:

• Share a base database backup (e.g., a snapshot of the pre-release server's stable state) with the team. Everyone starts by restoring this backup locally.
• Create a simple script (shell/bat) that automatically runs all migration scripts in order. This way, everyone can sync their local database to the latest schema with one command, no manual work needed.

Quick Fix for Immediate Restart Issues #

If MariaDB is already stuck on the pre-release server, don't just blindly delete logs—first check the error log (usually at /var/log/mariadb/mariadb.log or your custom path) to confirm the issue. If it's an InnoDB log mismatch, follow these steps:

1. Stop MariaDB completely: sudo systemctl stop mariadb
2. Delete the InnoDB log files: sudo rm /var/lib/mysql/ib_logfile*
3. Restart MariaDB: sudo systemctl start mariadb
   This should get it running, but remember—this is a band-aid, not a solution. The real fix is the workflow changes above.

内容的提问来源于stack exchange，提问作者HPWD