1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
mysql
########

This is the administrators guide to maintaining MySQL databases

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

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:

.. code-block:: mysql

 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:

.. code-block:: mysql

 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':

.. code-block:: mysql

 GRANT SELECT ON dbname.* TO 'testuser';


How to show grants for user
=======================================

.. code-block:: mysql

 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
=======================================

.. code-block:: mysql

 REVOKE SELECT ON dbname.* FROM 'testuser';


How to remove a user
=======================================

This example shows how to remove or drop testuser:

.. code-block:: mysql

 DROP USER 'testuser';


How to create a password hash
=======================================

Run this query from any MySQL client:

.. code-block:: mysql

 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:

.. code-block:: mysql

 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:

.. code-block:: mysql

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