I'm rather new to database management and I would like some advice on better practices by using the following experience as an example.

Every time I update php software I do a quick export from phpMyAdmin (ended up with a ~250mb gzipped single sql file this time), then I store it somewhere and hope I don't ever need it.

But one day I needed to restore the backup, and this is my sad story of issues...

  1. phpMyAdmin has the import size capped as 50mb so I had to use mysql from ssh
  2. MySQL server timed out at ~2minutes so I had to split the sql file
  3. Indexes were not exported so the CMS started issuing CREATE INDEX on big tables, causing timeouts in the sql server
  4. I executed the queries to make the index manually but it was timing out anyways so I had to drop the data, execute the CREATE INDEXquery and re-import that table (in chunks, because of the timeouts)
  5. The CMS finally loaded fine, rebuilding other smaller indexes

Now I naively thought I was done, but I checked some other pages...

(╯°□°)╯︵ ┻━┻ ®ƒÂ¼

  1. All the UTF8 was interpreted as extended ascii
  2. So I dropped the database and had to set client encoding to utf8 but only in SESSION. The server timed out and mysql reconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again.
  3. I edited the sql files to set the encoding every time a new source command was used and repeated the split import.

Now the question: How could I do this correctly?

I hope I am not off-topic...

  • What parameters were used on the dump? – Rick James Jan 20 '19 at 19:55
  • 1
    Are you after the proper way to do this restore? Or the proper way to do backups (usually involves testing rather than hope for the best)? – danblack Jan 20 '19 at 22:28
  • @RickJames I used the Quick Export after selecting the database, so it used these defaults: i.imgur.com/k4Fx6k9.png – beppe9000 Jan 21 '19 at 18:59
  • @danblack While I started this question as a kind of crowd-source post-mortem analysis I think the issues I had in this specific case are probably a subset of the various things that must be accounted for during a proper backup and restore anyway. – beppe9000 Jan 21 '19 at 19:00
  • Please provide SHOW CREATE TABLE for the table with the gibberish. – Rick James Jan 21 '19 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.