If you want to convert MYSQL tables to INNODB use the following script:
mysql -u root -p -e "SHOW TABLES IN DATABASE-NAME;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
and then run:
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
Replace “DATABASE-NAME” with your database name. You also need root access (preferably).
How to configure MySQL to use UTF8 (UTF8_general_ci) as default collation in MySQL for the new databases?
Step 1:
Add in in /etc/my.cnf the following lines:
[mysql]
default-character-set=utf8
[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8
default-character-set=utf8
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
Step 2: Restart MySQL
/etc/init.d/mysql restart
To save diacritics in a MySQL database you have 5 steps to follow:
1. Set(create) your database with collation utf8_general_ci;
2. Set(create) your table with utf8_general_ci collation also;
3. Make sure the text fields where you’ll insert diacritics have also the utf8_general_ci collation
4. Convert your string into ‘utf-8′ using any method you like. I recommend using PHP’s multibyte functions
e.g. $my_string = mb_convert_encoding($txt, $charset, mb_detect_encoding($my_string));
where: $my_string is the string that will be inserted
$charset is thecharset where your diacritics are included.
Check also here the Complete list of Popular character encodings
5. Before the insert query execute this query SET NAMES latin2; (if your diacritics are from one of the latin charsets)
To backup all the databases in MySQL use:
mysqldump --all-databases -pPASSWORD | bzip2 -c > databasebackup.sql.bz2
The password is for the user Root.
To restore MySQL use:
mysql -pPASSWORD < databasebackup.sql