mySQL

mySQL

mySQL database information.

.. contents::

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:

.. code-block:: mysql

CREATE DATABASE lostquery

Or optionally make Character set utf8 for unicode support

.. code-block:: mysql

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:

.. code-block:: mysql

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

.. code-block:: bash

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

Drop user

.. code-block:: sql

DROP USER username@localhost

Drop database

.. code-block:: sql

DROP DATABASE databasename

Show the MySQL processlist or list of currently running queries

.. code-block:: mysql

mysqladmin -u root -p processlist

Create a read only user for backing up databases

.. code-block:: mysql

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

Backup all databases into separate files

.. code-block:: bash

#!/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 <http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/>_