Difference between revisions of "Krissy's MySQL Command Reference with Examples"

Revision as of 19:38, 20 June 2007

         ________________________________________________
        /                                                \
       (  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);

" . $row_array[1] . "

}

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