How do I edit the charset of a MySQL table?

6 posts / 0 new
Topic locked
Last post
#1 Thu, 08/29/2013 - 07:30
kevindp78

How do I edit the charset of a MySQL table?

I am new to Virtualmin and am working on someone's existing database. I created a new table, and when I looked at the code for it, I saw that the default charset line reads

) ENGINE=InnoDB AUTO_INCREMENT=159 DEFAULT CHARSET=utf8;

Other tables read

) ENGINE=MyISAM AUTO_INCREMENT=8418 DEFAULT CHARSET=latin1;

How can I change the new table's default charset from within VirtualMin to be latin1? Also, what does the ENGINE mean?

Thanks!

Thu, 08/29/2013 - 07:48
jimdunn

I use the script (below). But also change the defaults in /etc/my.cnf so it won't happen again. I think you can also set it in Virtualmin->System-Settings->Server-Templates->Default-Settings->MySQL-Database.

[code][mysqld]
character-set-server = utf8
collation-server = utf8_general_ci[/code]

[code]#!/bin/bash
MPASS=`cat /private/.mysql.shadow`
DBS=(`/usr/bin/mysql --user=root --password=$MPASS -Bse 'show databases' | egrep -v 'information_schema|mysql|test'`)

for DB in "${DBS[@]}"; do
TBLS=(`/usr/bin/mysql --user=root --password=$MPASS $DB -Bse 'show tables'`)

for TBL in "${TBLS[@]}"; do
echo $DB.$TBL
/usr/bin/mysql --user=root --password=$MPASS $DB -Bse "ALTER TABLE $TBL DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
done
done[/code]

Thu, 08/29/2013 - 08:13 (Reply to #2)
kevindp78

Jim,

Thanks a ton for your response! However, I'm very inexperienced in MySQL databases, and my only exposure has been through Virtualmin. I don't know how to translate the code you provided.

Is there a way to make only a single table a different charset through Virtualmin?

What if I change the default to latin1, create a new table within an existing database, and then change the default back? Would that affect anything but the single table, or would it endanger the rest of my database?

Another idea: I've got a backed-up version of the MySQL table using the Backup Databases button in VirtualMin. Could I edit that single line for my table in Notepad++ and then upload the database to overwrite the existing one? If so, how do I do this?

Thu, 08/29/2013 - 22:21 (Reply to #3)
jimdunn

I found this on google:

[code]# DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"[/code]

(one long line at the shell script prompt)

But I would make sure I had a backup of my data!!!

Fri, 08/30/2013 - 07:34 (Reply to #4)
kevindp78

Jim,

I'm not sure I understand. In my last post, I was asking about a way to make only a single table a different charset through Virtualmin (or, a way to edit the table file manually and save it over the existing table.) I'm not sure what to do with this code, but I appreciate any help you have to offer!

Fri, 08/30/2013 - 08:38 (Reply to #5)
jimdunn

Unless you are "script savvy" I would think using PHPMYADMIN might be a good approach. It can be installed onto any domain using Virtualmin -> [select domain] -> Install-Scripts.

Topic locked