Some Helpful Notes For Using MySQL

Database Operations

To show current active databases:

mysql> show databases;

To find out which database is currently selected (blank if none are selected):

mysql> select database();

To set up all the right files (case sensitive) to spawn a brand-new database:

mysql> create database DATABASENAME;

To activate a particular database:

mysql> use DATABASENAME;

Need to get rid of one?

mysql> drop database DATABASENAME;

Warning

This predictably gets rid of anything in that database. Don’t pull this trigger unless you mean it.

User Management

MySQL keeps it’s own database (literally stored in a native MySQL database) of users, passwords, and permissions.

To have a look at the users currently established, change to the system database:

mysql> use mysql
mysql> select user,host,password from user where user="xed";
+------+------+-------------------------------------------+
| user | host | password                                  |
+------+------+-------------------------------------------+
| xed  | %    | *42693AD34DACEBADA55BFAA2EAF4FCEBADA55BB8 |
+------+------+-------------------------------------------+

Change Password

Note

Looks like there are two kinds of password hashes MySQL could be using, the old kind (16 chars) and the newer kind (many more than 16 starting with a *). This apparently happens because of a parameter called old_passwords which lives in /etc/my.cnf. If you change this, then you have to make sure all your passwords are "long". You can make this transition safer by using this session variable: SET SESSION old_passwords=0; This will help create new style passwords before making major system wide commitments that could lock you out upon a restart.

Here’s one way:

mysql> UPDATE user SET password=PASSWORD('secret') WHERE user='xed';
mysql> FLUSH PRIVILEGES;

Or this might be a smoother way:

mysql> SET PASSWORD FOR xed@localhost=PASSWORD('newpass');

To create a new user:

mysql> CREATE USER 'xed'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'xed'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

These look pretty much the same, but they are both needed or else user xed will invoke the anonymous user on local host.

mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

To open permissions to the database:

mysql> GRANT ALL ON database.* TO your_mysql_name;

To delete a user from the MySQL access list:

mysql> DROP USER 'retiredguy'@'localhost';

Neater USER Table

Here’s a nice way to show the USER table so there isn’t a bazillion fields making it unreadable and useless:

SELECT password, host, user,
CONCAT(Select_priv, Create_tmp_table_priv, Create_view_priv, Show_view_priv, Lock_tables_priv) AS view,
CONCAT(Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) AS edit,
CONCAT(Grant_priv, References_priv, Index_priv, Alter_priv) AS meta,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv) AS exec,
CONCAT(Repl_slave_priv, Repl_client_priv) AS rep,
CONCAT(Super_priv, Shutdown_priv, Process_priv, File_priv, Show_db_priv, Reload_priv) AS admin
FROM user ORDER BY user, host;

You can also set privileges by database (see mysql.db table), by host (mysql.host) and even finer (see mysql.{tables_priv, columns_priv, procs_priv}).

SELECT Host, Db, User,
CONCAT(Select_priv, Create_view_priv, Show_view_priv) AS sel,
CONCAT(Insert_priv, Create_priv, Update_priv, Alter_priv, Lock_tables_priv) AS edit,
CONCAT(Delete_priv, Drop_priv) AS del,
CONCAT(Grant_priv, References_priv, Index_priv, Create_tmp_table_priv) AS misc,
CONCAT(Create_routine_priv, Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv) AS exec
FROM mysql.db ORDER by User,Host;

Table Operations

To find out the names of the tables of the active database:

mysql> SHOW TABLES;

Prepares a new structure for a database table (also case sensitive):

mysql> CREATE TABLE tablename (field1 VARCHAR(20), field2 INT(5),
-> field3 VARCHAR(20));

mysql> CREATE TABLE point (pid INT auto_increment not null primary key , x

float(15,8), Y float(15,8), Z float(15,8));

To find out the structure of a specific table (field structure etc):

mysql> DESCRIBE tablename;

Loads a prepared database table with data from a tab/CR delimited text file:

mysql> LOAD DATA LOCAL INFILE "data.txt" INTO TABLE tablename;

Insert a new single record into a database table:

mysql> INSERT INTO table
-> VALUES ('FIELD1REC','FIELD2REC',...);

Deleting tables:

mysql> DROP TABLE testtable;

Select Records

Displays all of the records in a particular table for the specified fields (* can be used as a synonym for all fields):

mysql> SELECT fieldname1, fieldname2 FROM tablename;

Complex query using multiple tables and field aliases. This selects the "name" field from the "pet" table. It calculates the age in years of the event’s "date" by subtracting the "birth" date. This age is to be used as a pseudo field which will be handled with the alias "age". The WHERE clause matches up the two tables invloved and screens for a particular type of event.

mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = "litter";

Complex query using a joined table that is joined to itself. The purpose here is to match up compatible pairs of records from the same table. Notice the table aliasing in the from clause. The where clause matches up the SPECIES and ensures compitible SEX types.

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

Modify Records

Fix screwed up data. To change a particular record that is afoul:

mysql> UPDATE point SET x="1.902" WHERE pid="9";

Here, point is the table and the value of X needs to be modified on record #9 (PID="9").

Delete Records

Pretty much like SELECT.

mysql> DELETE FROM Actions WHERE code="bogus";