MySQL Notes

These notes are for my own convenience - to have the information I want most of the time on a single page. If you don't already have the O'Reilly MySQL & mSQL book... it's a very nice reference.

mysql_install_db

To check that MySQL is running

mysqladmin version

If MySQL is not running, start with

safe_mysqld &

or with logging...

safe_mysqld --log &

Changing 'root' password (default administrative account - not related to root on your machine)

mysqladmin -u root password 'mynewpassword'

Note: the -p in all commands below tell MySQL to prompt for a password

Also... you might want to use the -u switch to send a command as a specifi user (might be important for access)

Creating a database

mysqladmin -p create DATABASE

Deleting a database

mysqladmin -p drop DATABASE

Shutting down mysqld

mysqladmin -p shutdown

Displaying table structure for a particular database (-d keeps actual table data from showing)

mysqldump -d -u USER -p DATABASE

To recreate tables, etc... from mysqldump output

mysql table_name < dump.txt

Reloading access tables for server

mysqladmin -u root -p reload

MySQL administration - global access privileges

Every user that connects to MySQL must be added to the user table in the mysql database. This table defines global privileges for the server (not specific database access). Access privileges to specific databases are define in the db table.

The structure of the user table is as follows:

Field	Type	Default value
Host	Char(60)
User	Char(16)
Password	Char(16)
Select_priv	enum('N', 'Y')	N
Insert_priv	enum('N', 'Y')	N
Update_priv	enum('N', 'Y')	N
Delete_priv	enum('N', 'Y')	N
Create_priv	enum('N', 'Y')	N
Drop_priv	enum('N', 'Y')	N
Reload_priv	enum('N', 'Y')	N
Shutdown_priv	enum('N', 'Y')	N
Process_priv	enum('N', 'Y')	N
File_priv	enum('N', 'Y')	N
Grant_priv	enum('N', 'Y')	N
References_priv	enum('N', 'Y')	N
Index_priv	enum('N', 'Y')	N
Alter_priv	enum('N', 'Y')	N

Typical SQL statement to add a user to table:

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)


VALUES ('localhost', 'nobody', password('mypassword'), 'Y', 'Y', 'Y', 'Y');

MySQL Administration - access privileges for specific databases

Access rights for specific databases are defined in the db table in the mysql database.

The structure of the db table is as follows:

Field	Type	Default value
Host	Char(60)
Db	Char(32)
User	Char(16)
Select_priv	enum('N', 'Y')	N
Insert_priv	enum('N', 'Y')	N
Update_priv	enum('N', 'Y')	N
Delete_priv	enum('N', 'Y')	N
Create_priv	enum('N', 'Y')	N
Drop_priv	enum('N', 'Y')	N
References_priv	enum('N', 'Y')	N
Index_priv	enum('N', 'Y')	N
Alter_priv	enum('N', 'Y')	N

Typical SQL statement to add a user to table:

INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv)


VALUES ('localhost', 'mydb', 'nobody', 'Y', 'Y', 'Y', 'Y');