Hi! I have MySQL 5.7.20 and I think I messed up terribly. I noticed my database was in latin1 and so I followed the instructions on this page (http://tlug.dnho.net/?q=node/276) to change it into utf8. Well that didn't work. And now half of my information is gone! I've tried restoring multiple times and even reversing the steps. Is the info gone for good or can I get it back!?
Topic on Project:Support desk
If you have the original SQL script, that's your backup and restoring it should work, but the --default-character-set=latin1 --skip-set-charset options may skip some importan information from the original database. If the original data was really latin1, importin it with --default-character-set=latin1 should work. However, I doubt MediaWiki would use latin1 for database even in the more older versions. Maybe it wasn't in latin1 at all, or even if the database default was latin1, tables were created as utf8 (the charset can be specified for each table).
If you made any modification to MediaWiki configuration like Manual:$wgDBTableOptions you'll need to restore them as well.
Hi sir! Here's what happened... everything was listed as latin1 using $mysql status. So I did what the page said, $mysqldump -u root -p --opt --default-character-set=latin1 --skip-set-charset DBNAME > DBNAME" Then I did $sed -e 's/latin1/utf8/g' -i ./DBNAME.sql. I could not create the database like it said because it said it already existed. So I followed the last step. $mysql -p --default-character-set=utf8 DBNAME < DBNAME.sql. I was editing the wiki like normal but noticed it seemed like I was creating pages I already had. I figured I better check the site stats. Half of my pages, users, and multimedia files were missing!
So I tried to reverse the second step, using $sed -e 's/utf8/latin1/g' -i ./DBNAME.sql, and the third, $mysql -p --default-character-set=latin1 DBNAME < DBNAME.sql. That didn't work. That's when I wrote here.
I kept trying things and nothing would work. I dumped the databases, couldn't log back on, finally would up having to purge mysql and reinstall it... twice. Then I remembered I used automysqlbackup earlier in the day based on the steps found on Manual:Backing up a wiki#Automysqlbackup. It was extremely rough but I was finally able to get the file sourced. But it yielded the same results - half of my pages are still missing! And now, even though I finally got the site working again, I'm getting backtrace errors saying that tables that exist do not.
I don't know what I can do. I'm somewhat heartbroken and distraught. I checked one of the tables (user_id) and there are a lot less rows than there were users. Does this really mean all of my information is gone... for good? Please, sir, tell me I can recover everything!
I just checked - I have 4 backups and 1 of them is several hundred megabytes larger that the other 3. I assume the larger one is the one I need to focus on? I had made a "dump.xml" file but I tried to import it and now it's empty (went from 370 MB to 0). Sigh.
The mysqldump from the page you linked, should export the entire database, and the number of rows of the user table should be the same. You should check if, when importing the dump, there's any error when inserting rows to the tables (which may happen if the character set is wrong.
As I said, every table can have a different character set than the default character set of the database (which applies only when creating new tables if you don't specify a character set for them). Hopefully the data may be OK, but the table definition/default charset not, and fixing it may give you to the previous state, if the shell hasn't done weird things with it (look at this). I suggest you posting this problem on a mysql related forum, since that's probably just mysql related and it doesn't necessarily need MediaWiki knowledge, mysql experts can give you better advice.
Thank you sir. After a plethora of problems and obstacles I was finally able to integrate the backup that automysqlbackup created. It was a blessing seeing everything come back!!