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...
- phpMyAdmin has the import size capped as 50mb so I had to use
- MySQL server timed out at ~2minutes so I had to split the sql file
- Indexes were not exported so the CMS started issuing
CREATE INDEXon big tables, causing timeouts in the sql server
- 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)
- The CMS finally loaded fine, rebuilding other smaller indexes
Now I naively thought I was done, but I checked some other pages...
(╯°□°）╯︵ ┻━┻ ®ƒÂ¼
- All the UTF8 was interpreted as extended ascii
- So I dropped the database and had to set client encoding to
utf8but only in SESSION. The server timed out and
mysqlreconnected, losing the encoding settings and ƒÂ¼*ing up the import yet again.
- I edited the sql files to set the encoding every time a new
sourcecommand was used and repeated the split import.
Now the question: How could I do this correctly?
I hope I am not off-topic...