Change MySQL 5.5 default character-set to UTF8

Last night cPanel upgraded the version of MySQL on one of our servers to the latest MySQL 5.5. Unfortunately this results in over 4000 emails from that server, crying that various websites couldn't connect to the MySQL database. The error found in /var/log/mysqld.log was
InnoDBErrorlog file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB
than specified in the .cnf file 0 26214400 bytes!
131218 23:06:21 [ERROR] Plugin 'InnoDB' init function returned error.
131218 23:06:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131218 23:06:21 [ERROR] /usr/sbin/mysqldunknown variable 'default-character-set=utf8'
131218 23:06:21 [ERROR] Aborting 

MySQLdump to reset Slave server from Master

Just a quick entry here. I keep forgetting how to reset a mysql slave server from the master, without disrupting the operations on the master server. Something silly that doesn’t stick in my head, so I am putting here for future use:

If you have Server A with the master, and Server B with the slave, and you need to reset the slave if it gets lost, simply do this:

Login to the Server A and issue this command:

mysqldump --master-data --all-databases newdbs.sql 

Then scp or ftp the newdbs.sql file to Server B.

Login to Server B, stop http and turn off the slave (slave stop;), load in the new database SQL file you brought over and restart the mysql slave (slave start;) and http:

/etc/init.d/httpd stop
mysql 
newdbs.sql
/etc/init.d/httpd start 

To be sure everything is working as it should, you can go into mysql and ‘show slave status;’

 

MySQL export/import and charset problems

Today I discovered I messed up a database upgrade in MySQL. I had a rather large database which I exported from my main server and imported onto my testing server. Both machines had the same version of MySQL. I ran some update scripts on the testing server DB, verified the data, then exported the updated database, and reimported it into the main servers MySQL database.

Then I notice only after a few days that the data I re-imported had all the accented characters broken. Somewhere in my conversion the Latin1 charset did not export in UTF8 from Mysql as it was supposed to in MySQL 4.1.12 or greater!

The big issue here is that I could not simply run the upgrade again, as I would lose all changes to the database in the past week.

After some research I found the following bit of information using iconv ( iconv - Convert encoding of given files from one encoding to another)

Step 1
Re-export the data from the database normalling using
mysqldump—add-drop-table databasename > mydump.sql

Step 2
Use iconv to conver tthe format from UTF8 to ISO-8859-1
iconv -f UTF-8 -t ISO-8859-1 mydump.sql -o newdump.sql

Step 3
Re-import newdump.sql into MySQL with
mysql databasename

< newdump.sql

Now, if everything is normal you should have a great working database again. Or, if like with me, Murphy didn’t allow it to work normally, you might have gotten this error:
iconv: illegal input sequence at position 49823

It took me awhile to find the solution, but after much head banging, I finally found the following solution, whiched worked the charm, I might add!

Step 2
iconv -f UTF-8 -t ISO-8859-1//TRANSLIT mydump.sql -o newdump.sql

This will get the conversion done using transliteration, which means that when a character cannot be represented in the target character set, it can be approximated through one or several similarly looking characters (source: www.gnu.org).

Replace DB content on the fly

This appears to be a little known fact, so it has earned the right to be posted as a how-to on my site.

If you have a database with lots of content and you discover to your dismay that you have made a mistake on some of the text, in many rows, you might find yourself asking “How am I going to fix all those errors?”

For example, you have a table with 5000 rows and one column is a URI. You have entered the URI in this database as http://www.mystartupcompany.com/index.php?id=4001, http://www.mystartupcompany.com/index.php?id=4006, http://www.mystartupcompany.com/index.php?id=4009 and so on.

Now you discover that you can have the URI’s in a search engine friendly format that looks like this: http://www.mystartupcompany.com/product/4001, http://www.mystartupcompany.com/product/4006, http://www.mystartupcompany.com/product/4009 and so on.

Can you imagine having to go through the database one by one and correct all 5000 URI’s?

Relax, there is a simple and fast way to do it.

We will assume for arguments sake, that the table name is products, and the column name to modify is prod_uri.

Simply run this command:
UPDATE products SET prod_uri=REPLACE(prod_uri,‘mystartupcompany.com/index.php?id=’,‘mystartupcompany.com/product/’);

Note that the above code is all on one line.

Within a second or two, all the columns and rows will be updated.

Many thanks to Rob over at MacOSXHints for bringing this to my attention.