mySQL

mySQL database information.

Full-Text Minimum word length

Full-text minimum length default is set to 4. To adjust this setting, edit /etc/mysql/my.cnf and add the following line under [mysqld]:

ft_min_word_len = 3

Next we need to restart mysql:

$ sudo service mysql restart

Last repair the full-text index on the table:

mysql> REPAIR TABLE table_name QUICK

Create database

log into mysql as root:

CREATE DATABASE lostquery

Or optionally make Character set utf8 for unicode support

CREATE DATABASE lostquery DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

Create a user

The following command will create a user with a password and grant all privileges to the user on a particular database:

GRANT ALL PRIVILEGES ON lostquery.* TO username@localhost IDENTIFIED BY 'passw0rd' WITH GRANT OPTION

Restore database

The following command with restore a database from a backup .sql file

mysql -u username -p lostquery < lostquery-backup-2012-12-10.sql

Drop user

DROP USER username@localhost

Drop database

DROP DATABASE databasename

Show the MySQL processlist or list of currently running queries

mysqladmin -u root -p processlist

Create a read only user for backing up databases

GRANT SELECT, LOCK TABLES ON *.* TO backup_user@localhost;

Backup all databases into separate files

#!/bin/bash

USER="backup_user"
PASSWORD=""
TODAY=`date +%Y-%m-%d`
OUTPUTDIR="/backup/db/$TODAY"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
GZIP="bin/gzip" 

mkdir $OUTPUTDIR

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.sql"
done

# compress all of the files
$GZIP $OUTPUTDIR/*

Reference: Backup multiple mysql databases into separate files