Note: This web page was automatically created from a PalmOS "pedit32" memo.

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)


Back to Dan's palm memos