Login or register    

mysql

This is the administrators guide to maintaining MySQL databases

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'@'%' 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

Create a read only user for backing up databases

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

How to create a new user

Below we document two methods of creating a user with minimal access.

Create the user 'testuser' using a raw password, and grant the ability to show databases:

GRANT SHOW DATABASES ON *.* TO 'testuser'@'%' IDENTIFIED BY 'password-to-be-hashed';

Create the user 'testuser' using a hashed password, and grant the ability to show databases:

GRANT SHOW DATABASES ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '\*REMOVED000000000000000000000000000000000';

How to grant privs to a user

Grant SELECT priv on dbname database for 'testuser':

GRANT SELECT ON dbname.* TO 'testuser';

How to show grants for user

SHOW GRANTS FOR testuser;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*REMOVED000000000000000000000000000000000' |
| GRANT SELECT ON `dbname`.* TO 'testuser'@'%'                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

How to revoke access to a user

REVOKE SELECT ON dbname.* FROM 'testuser';

How to remove a user

This example shows how to remove or drop testuser:

DROP USER 'testuser';

How to create a password hash

Run this query from any MySQL client:

SET old_passwords = 0;
SELECT PASSWORD('password-to-be-hashed');
+-------------------------------------------+
| PASSWORD('password-to-be-hashed')         |
+-------------------------------------------+
| *7A5DCDE2BDA1ECAA1E54DB73AEF56B86156E2B70* |
+-------------------------------------------+
1 row in set (0.00 sec)

How to change a password with hash

external reference: http://dev.mysql.com/doc/refman/5.1/en/set-password.html

If you already have the user's password hash do this:

SET PASSWORD FOR 'testuser' = 'REMOVED000000000000000000000000000000000';

How to change a password without hash

If you don't have a password hash but you have the password, do this:

SET old_passwords = 0;
SET PASSWORD FOR 'testuser' = PASSWORD('password-to-be-hashed');

Comments

Leave a comment

Please login or register to leave a comment!