Convert table character set to UTF8

From http://dev.mysql.com/doc/refman/5.0/en/charset-column.html
----
To change the character set (and collation) for all columns in an existing table, use...
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
----

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

How to configure MySQL for UTF8 when running MovableType as FastCGI

Use the modified dispatcher.

#!/usr/bin/perl -w

use strict;
use MT::Bootstrap;
use CGI::Fast;

# preload app packages
use MT::App::CMS;
use MT::App::Comments;
use MT::App::Trackback;
use MT::App::Search;
## uncomment if necessary, but this adds a lot of
## overhead since it loads up LibXML.
##use MT::AtomServer;


my $handlers = {
    'mt.fcgi' => { class => 'MT::App::CMS', name => 'AdminScript' },
    'mt-comments.fcgi' => { class => 'MT::App::Comments', name => 'CommentScript' },
    'mt-tb.fcgi' => { class => 'MT::App::Trackback', name => 'TrackbackScript' },
    'mt-search.fcgi' => { class => 'MT::App::Search', name => 'SearchScript' },
## See note above about this...
##    'mt-atom.fcgi' => { class => 'MT::AtomServer', name => 'AtomScript' },
};

eval {
    while (my $q = new CGI::Fast) {
        my $cgi = $q->script_name;
        $cgi =~ s!.*/!!;
        my $pkg = $handlers->{$cgi}{class};
        die "Invalid handler for $cgi" unless $pkg;
        my $app = $pkg->new(CGIObject => $q) or die $pkg->errstr;
        local $SIG{__WARN__} = sub { $app->trace($_[0]) };
        $app->init_request(CGIObject => $q) unless $app->{init_request};
        fixup_script_names($app);
        
        # do a little nasty over-the-back jump and setup MySQL on every connection        
        my $driver = MT::Object->driver;
        my $dbh = $driver->{dbh};
              my $names_cmd = $dbh->prepare("SET NAMES UTF8");
        $names_cmd->execute();
    
        $app->run;
        my $mode = $app->mode || '';
        if ("$pkg->$mode" eq 'MT::App::CMS->plugin_control') {
            exit; # allows server to recycle after changing plugin switches
        }
    }
};
if ($@) {
    print "Content-Type: text/htmlnn";
    print "Got an error: $@";
}

sub fixup_script_names {
    my ($app) = @_;
    $app->config($handlers->{$_}{name}, $_) foreach keys %$handlers;
}

Search & Replace

update tablename set field = replace(field,'search_for_this','replace_with_this');

Workaround for 'consider upgrading MySQL client' errors

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');

CRON and MySQL database backup, gzipped

/usr/local/bin/mysqldump --default-character-set=utf8 --user=username --password=password --quote-names --complete-insert --extended-insert --quick --compact --lock-tables=false --skip-add-locks --all-databases | gzip > /home/path/backup/`date "+%Y.%m.%d"`.gz

Make a backup of all tables startin with...

This will backup all tables starting with "prefix_" to a gzipped file backup.sql.gz.

echo "SHOW TABLES" | mysql -uUSERNAME -pPASSWORD -D DBNAME | grep ^prefix_ | xargs mysqldump -uUSERNAME -pPASSWORD DBNAME | gzip -c > backup.sql.gz

You have to replace USERNAME, PASSWORD and DBNAME twice.

Note: On textdrive you want to add --skip-opt to mysqldump, otherwise the command will abort with an error because privs for lockings tables are missing (for me at least).

To check which tables were backed up, you can use:

zcat test.sql.gz | grep CREATE

Read http://dev.mysql.com/doc/mysql/en/mysqldump.html to find out which other options to add to mysqldump

How To Fix MySQL Error 28

MySQL: 1030: got error 28 from server handler

cd /tmp
df -i /tmp
df -h /tmp

Delete anything that’s not supposed to be there.
Stop all databases:

/etc/rc.d/init.d/chkservd stop
/etc/rc.d/init.d/mysql stop

Then fix tables:

cd /var/lib/mysql

Check each letter for errors:

myisamchk -cs a*/*.MYI

Repair where necessary:

myisamchk -r a*/*.MYI
myisamchk -r b*/*.MYI
myisamchk -r c*/*.MYI
myisamchk -r d*/*.MYI
myisamchk -r e*/*.MYI
myisamchk -r f*/*.MYI
myisamchk -r g*/*.MYI
myisamchk -r h*/*.MYI
myisamchk -r i*/*.MYI
myisamchk -r j*/*.MYI
myisamchk -r k*/*.MYI
myisamchk -r l*/*.MYI
myisamchk -r m*/*.MYI
myisamchk -r n*/*.MYI
myisamchk -r o*/*.MYI
myisamchk -r p*/*.MYI
myisamchk -r q*/*.MYI
myisamchk -r r*/*.MYI
myisamchk -r s*/*.MYI
myisamchk -r t*/*.MYI
myisamchk -r u*/*.MYI
myisamchk -r v*/*.MYI
myisamchk -r w*/*.MYI
myisamchk -r x*/*.MYI
myisamchk -r y*/*.MYI
myisamchk -r z*/*.MYI

Turn everything back on:

/etc/rc.d/init.d/chkservd start
/etc/rc.d/init.d/mysql start

Show fields from a Mysql table

SHOW FIELDS FROM `$bd_tabla`

Show tables from a MySQL database

SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']

Show databases from a MySql Server

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']