=====
mySQL
=====


mySQL
=====

.. _mysql-1:

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
  :math:`MYSQLDUMP --force --opt --user=`\ USER –password=$PASSWORD
| –databases :math:`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/>``\ \_