________________________________________________
         /                                                \
        (  Krissy's MySQL Command Reference with Examples  )
         \________________________________________________/

Using the mysql text based client
----------------------------------------

00. CREATE A DATABASE
    mysql> create database databasename;

    GRANT / REVOKE ACCESS / CREATE USER
    mysql> grant select,insert,update,delete,create,drop,alter,index on databasename.* to person@localhost identified by 'password'
    mysql> revoke ALL on *.* from person@localhost;
    
01. DISPLAY AVAILABLE DATABASES
    mysql> show databases;

02. OPEN AND USE A DATABASE
    mysql> use databasename;

03. CREATE A TABLE
    mysql> create table tablename (field1 integer,field2 char(50));
    mysql> create table tablename (field1 integer not null auto_increment,field2 integer,primary key(field1));

04. ADD DATA TO TABLE
    mysql> insert into tablename (field1, field2) values (num, 'string');

05. DELETE A TABLE CONTENTS AND STRUCTURE
    mysql> drop table tablename;

06. LIST FIELDS IN A TABLE
    mysql> show columns from tablename;

07. MODIFY EXISTING RECORD
    mysql> update tablename set fieldname='stringvalue';
    mysql> update tablename set fieldname=numval;
    mysql> update tablename set field1='value', field2=num, field3='value';

08. ADD A COLUMN TO TABLE
    mysql> alter table tablename add column fieldname char(20);
    mysql> alter table tablename add column field1 date, add column field2 time;

Using the mysqladmin administrative utility
----------------------------------------

00. DELETE A DATABASE
    (accomplished using mysqladmin from linux console)
    bash$ mysqladmin -p drop databasename;

Connecting to a MySQL database from PHP (basic example)
----------------------------------------

mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect");

mysql_select_db($db_database);

$result = mysql_query($Query);

for ($i = 0; $i < mysql_num_rows($result); $i++) {
        $row_array = mysql_fetch_row($result);
        echo ("<P>" . $row_array[1] . "</P>");
}


Backing up a LIVE MySQL database:
----------------------------------------
hotcopy:
mysqlhotcopy databasename /home/username/placetoputbackupfile -p mysqlpassword

tarball:
tar -sp -zcvf mysqlhotcopy.tar.gz  /home/username/placetoputbackupfile -P

tar -sp -zcvf archive.tar.gz /mounted/directory -P

-s same owner
-p same permissions

-z gzip compression
-c create
-v verbose
-f filename

-P absolute path

Change your MySQL root password
----------------------------------------

Issue the following statements in the mysql client:

    mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
    mysql> FLUSH PRIVILEGES;

Troubleshooting:
----------------------------------------
Fatal Error call to undefined function mysql_connect()

Trustix 2.1 used PHP 4 and 2.2 uses PHP 5 and in that
version the following line needs unremarked in php.ini

extension=mysql.so

Make sure you have all the packages installed (Trustix)

swup --search-package php | grep mysql

On Trustix the php.ini files for PHP4 are at:
/etc/httpd/php4/php.ini
On Trustix the php.ini files for PHP5 are at:
/etc/httpd/php.ini