View source for Krissy's MySQL Command Reference with Examples

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

&nbsp;

== Using the mysqladmin administrative utility ==

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

&nbsp;

== Connecting to a MySQL database from PHP (simple example) ==

 <nowiki>mysql_connect($db_hostname, $db_username, $db_password) or die("Could not connect");</nowiki>
 <nowiki></nowiki>
 <nowiki>mysql_select_db($db_database);</nowiki>
 <nowiki></nowiki>
 <nowiki>$result = mysql_query($Query);</nowiki>
 <nowiki></nowiki>
 <nowiki>for ($i = 0; $i < mysql_num_rows($result); $i++) {</nowiki>
 <nowiki>        $row_array = mysql_fetch_row($result);</nowiki>
 <nowiki>        echo ("<P>" . $row_array[1] . "</P>");</nowiki>
 <nowiki>}</nowiki>

&nbsp;

== 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

&nbsp;

== 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;

&nbsp;

== 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


&nbsp;


[[Category:Computer Technology]]
[[Category:Programming]]
[[Category:MySQL]]