// you’re reading...

MySQL

Database noob making changes and something goes wrong…

Recently, I answered a question on a forum site from a user who stated they were “database noobs”. They made a change and had a problem. Here’s the advice I gave:

Performing an ALTER to change storage engines won’t make rows disappear. However, let me offer some advice since you said you’re ‘database noobs’ in your question.

When modifying existing schema or doing anything that could affect data, here’s some basic advice:

  • Make a backup first.
  • Have a change plan.
  • Test your plan on an offline host.
  • Have a test plan to compare before and after data.
  • Schedule and take a downtime.
  • Take a backup and snapshot immediately after your downtime goes into effect and you verify traffic has stopped.
  • If you’re running MYISAM, use ‘CHECK TABLE’ to evaluate what you’re dealing with before you ALTER.
  • Copy the table locally in addition to your backup, just in case.
  • Proceed with caution, enable “–show warnings” and other output so you have the full picture as you make your changes.
  • If the data is important to you, hire a DBA, even if just to consult during the migration so you have a seasoned veteran by your side.

There’s probably a lot more I could get into, but the above will provide you with options when something goes wrong.

As far as your missing data/rows, there’s no way to know w/o a “before/after” snapshot to compare. You can compare against your latest backup to at least verify that much.

Discussion

No comments for “Database noob making changes and something goes wrong…”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/