MySQL notes

mysqldump -u root -p --all-databases --opt

the --opt really helps

"show databases", "show tables" and "describe $table_name"

If I want a quick-and-dirty, plain-text dump of the schema, I prefer
using mysqldump with the -d option (no data).

mysqldump -q -Q -A -d --user=user_name --password -r dbschema.sql 
(it should prompt you for the password) 

see "mysqldump --help" for more options/details.

Please try MYdbPAL for MySQL - www.it-map.com. It is FREE, and you can
reverse-engineer the schema; view, model, forward engineer etc.

Only available for windows, 2005-02-02.  Should be released for linux
early 2005.

Other options include phpmyadmin (free?) and navicat (commercial with
a free, 30 day trial period)

The easiest route is to use mysqldump with the --no-data option.  it
will dump the full schema info including feign keys, etc.

Getting into a mysql command shell:
./mysql -u root -h localhost -p

Changing the mysql password:
set password = password("P3QObscenelyqNL8lLonga273CPassword8Xr");

To access the user, host databases, etc... type this;

mysql> use mysql;
Database changed
mysql>

To give localhost permission to access all databases, enter this:
mysql> insert into host(host,db,Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv)
values('localhost','%','Y','Y','Y','Y','Y','Y');

Create a user:
mysql> insert into user (host, user, password)
values('localhost','strombrg',password('mypassword'));

Give user access from another host:
mysql> insert into user (host, user, password)
values('seki.nac.uci.edu','strombrg',password('mypassword'));

Give user privileges on localhost:
mysql> insert into db (host,db,user,Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv) values
('localhost','mysql','strombrg','Y','Y','Y','Y','Y','Y');

Give user privileges on some other host:
mysql> insert into db (host,db,user,Select_priv, Insert_priv,
Update_priv, Delete_priv, Create_priv, Drop_priv) values
('seki.nac.uci.edu','mysql','strombrg','Y','Y','Y','Y','Y','Y');

To get out of mysql:
mysql> quit

To create a database:
mysqladmin -u root -p create mydatabase

To make mysql see changes (database creation?) :
mysqladmin -u root -p reload

Two IBM developerworks articles on MySQL - on powerpc - whatever:

http://www-128.ibm.com/developerworks/linux/library/l-pow-mysqlp1/?ca=dgr-lnxw07MySQL4LinP1

http://www-128.ibm.com/developerworks/linux/library/l-pow-mysqlp2/?ca=dgr-lnxw07MySQL4LinP2

What users and hostnames are authorized to get into mysql?

use mysql;
show tables;
describe user;
select Host,user,password from user;

From a discussion on uci-mac:

As for backing up MySQL, you can write a short script which will dump
out the contents of the DB as a text file which can then be backed up
via Networker or whatever else you are using.

ex.
mysqldump --all-databases --all --complete-insert --add-drop-table
--lock-tables -u BACKUPUSERNAME -pPASSWORD > /path/to/a/file

Really nice table summarizing a bunch of mysql commands:
http://www.pantz.org/database/mysql/mysqlcommands.shtml

Cleaning up an authentication mess:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,user,password from user;
+---------------------------------+------------------+-------------------------------------------+
| Host                            | user             | password
|
+---------------------------------+------------------+-------------------------------------------+
| localhost                       | root             |
*123456789012345678901234567890 |
| ark.oas.uci.edu                 | root             |
|
| ark.oas.uci.edu                 |                  |
|
| localhost                       |                  |
|
| localhost                       | strombrg         |
*123456789012345678901234567890 |
| localhost                       | quicksilver.oas. |
*123456789012345678901234567890 |
| quicksilver.oas.uci.edu         | bhsu             |
*123456789012345678901234567890 |
| wsip-68-15-78-106.oc.oc.cox.net | bhsu             |
*123456789012345678901234567890 |
| ktsb.oir.uci.edu                | ktaft            |
*123456789012345678901234567890 |
| ip68-4-213-185.oc.oc.cox.net    | ktaft            |
*123456789012345678901234567890 |
+---------------------------------+------------------+-------------------------------------------+
10 rows in set (0.00 sec)

mysql> delete from user where user = '';
Query OK, 2 rows affected (0.05 sec)

mysql> delete from user where user = 'strombrg';
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where user = 'quicksilver.oas.';
Query OK, 1 row affected (0.01 sec)

mysql> delete from user where user = 'bhsu';
Query OK, 2 rows affected (0.00 sec)

mysql> delete from user where user = 'ktaft';
Query OK, 2 rows affected (0.01 sec)

mysql> quit
Bye
ark-root DCS)