{"revision": {"id": "f23d4c90-2f95-11f1-8555-e86a64d24d78", "node_id": "f23c51d7-2f95-11f1-a39a-e86a64d24d78", "user_id": "edc3f576-2f95-11f1-900f-e86a64d24d78", "author": "foxhop", "data": "mySQL\r\n========\r\n\r\nmySQL database information.\r\n\r\n.. contents::\r\n\r\nFull-Text Minimum word length\r\n--------------------------------\r\n\r\n\r\nFull-text minimum length default is set to 4.  To adjust this setting, edit /etc/mysql/my.cnf and add the following line under [mysqld]::\r\n\r\n ft_min_word_len = 3\r\n\r\nNext we need to restart mysql::\r\n\r\n $ sudo service mysql restart\r\n  \r\nLast repair the full-text index on the table::\r\n\r\n mysql> REPAIR TABLE table_name QUICK\r\n\r\n\r\nCreate database\r\n------------------\r\nlog into mysql as root:\r\n\r\n\r\n.. code-block:: mysql\r\n \r\n CREATE DATABASE lostquery\r\n\r\nOr optionally make Character set utf8 for unicode support\r\n\r\n.. code-block:: mysql\r\n \r\n CREATE DATABASE lostquery DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;\r\n\r\n\r\n\r\n\r\n\r\nCreate a user\r\n---------------------\r\n\r\nThe following command will create a user with a password and grant all privileges to the user on a particular database:\r\n\r\n.. code-block:: mysql\r\n\r\n GRANT ALL PRIVILEGES ON lostquery.* TO username@localhost IDENTIFIED BY 'passw0rd' WITH GRANT OPTION\r\n\r\n\r\nRestore database\r\n-----------------------\r\n\r\nThe following command with restore a database from a backup .sql file\r\n\r\n.. code-block:: bash\r\n\r\n mysql -u username -p lostquery < lostquery-backup-2012-12-10.sql\r\n\r\n\r\n \r\nDrop user\r\n-------------\r\n\r\n.. code-block:: sql\r\n\r\n DROP USER username@localhost\r\n\r\n\r\n\r\nDrop database\r\n-------------------------\r\n\r\n\r\n.. code-block:: sql\r\n\r\n DROP DATABASE databasename\r\n\r\n\r\n\r\nShow the MySQL processlist or list of currently running queries\r\n--------------------------------------------------------------------------\r\n\r\n.. code-block:: mysql\r\n\r\n mysqladmin -u root -p processlist\r\n\r\n\r\nCreate a read only user for backing up databases\r\n------------------------------------------------------------\r\n\r\n.. code-block:: mysql\r\n\r\n GRANT SELECT, LOCK TABLES ON *.* TO backup_user@localhost;\r\n\r\n\r\nBackup all databases into separate files\r\n--------------------------------------------------\r\n\r\n.. code-block:: bash\r\n\r\n #!/bin/bash\r\n \r\n USER=\"backup_user\"\r\n PASSWORD=\"\"\r\n TODAY=`date +%Y-%m-%d`\r\n OUTPUTDIR=\"/backup/db/$TODAY\"\r\n MYSQLDUMP=\"/usr/bin/mysqldump\"\r\n MYSQL=\"/usr/bin/mysql\"\r\n \r\n mkdir $OUTPUTDIR\r\n \r\n # get a list of databases\r\n databases=`$MYSQL --user=$USER --password=$PASSWORD \\\r\n  -e \"SHOW DATABASES;\" | tr -d \"| \" | grep -v Database`\r\n \r\n # dump each database in turn\r\n for db in $databases; do\r\n     $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \\\r\n     --databases $db > \"$OUTPUTDIR/$db.sql\"\r\n done\r\n\r\nReference: `Backup multiple mysql databases into separate files <http://www.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/>`_", "source_format": "rst", "revision_number": 23, "created": 1356022339000}}