Database Management

Notes for ubuntu hardy
-----------------------

Installation procedure
----------------------
- Install the server and client packages
- Set the mysql root user password


Per database procedure
----------------------
- Create a database
- Grant access to the database for user



==============================================================


Packages to install
-------------------
- mysql-server
- mysql-client


Files and Directories
---------------------
1- /var/lib/mysql/   : data directory
2- /var/log/mysql/   : log files
   /var/log/mysql.log
   /var/log/mysql.err


Commands of Interest
--------------------
1- /etc/init.d/mysql stop|start|restart   : rarely used to restart server
2a- mysql -u user -p databasename         : to connect to the server for
                                          : SQL session
2b- mysql -h hostname -u user -p databasename : to connect to the server for
                                              : SQL session, from remote machine


To leave MySQL client
-----------------------
1- exit


To view MySQL accounts
-----------------------
1- $ mysql -u root -p
2- use mysql
3- SELECT host, user, password FROM user;


To set the MySQL root account password
--------------------------------------
1- mysql -u root
2- use mysql
3- UPDATE user SET password = password('/b{2}|[^b]{2}/') WHERE user = 'root';
4- FLUSH PRIVILEGES;


To create a database
--------------------
1- $ mysql -u root -p
2- CREATE DATABASE my_db_name;


To create a user, with access to all tables of a database
---------------------------------------------------------
1- $ mysql -u root -p
2- GRANT ALL PRIVILEGES ON my_db_name.* 
   TO my_uname@localhost IDENTIFIED BY 'my_password';

To create a user, with access to all tables of a database,
from another system.
---------------------------------------------------------
1- $ mysql -u root -p
2- GRANT ALL PRIVILEGES ON my_db_name.* 
   TO my_uname@remote.system IDENTIFIED BY 'my_password';
   
To grant access to a new database for an existing user
---------------------------------------------------------
1- $ mysql -u root -p
2- GRANT ALL PRIVILEGES ON my_db_name_2.* 
   TO my_uname@localhost;
   
To remove access to a database for an existing user
---------------------------------------------------------
1- $ mysql -u root -p
2- REVOKE ALL PRIVILEGES ON my_db_name_2.* 
   FROM my_uname@localhost;
   
To delete an existing user
---------------------------------------------------------
1- $ mysql -u root -p
2- DROP user my_uname@localhost;
   Note that all database privileges for the user must be
   revoked prior to this statement.

To allow network access
---------------------------------------------------------
1- $ edit /etc/mysql/my.cnf
2- comment out the bind-address field.