h1

KOOL tips

July 2, 2006

Some frequently used MySQL commands for reference…

 
# Create User



CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'];

# Create Database

$ mysqladmin -u <username> -p create <nameOfDatabase>

 

# Drop/Delete Database

$ mysqladmin -u <username> -p drop <nameOfDatabase>

 

# Check Process List

$ mysqladmin -u root -p proc

 

# Check Status at 5 seconds interval

$ mysqladmin -u root -p -i 5 status

 

# Dump Database

$ mysqldump –opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file   

 

$ mysqldump –opt -u <username> -h <hostname> –all-databases -p > /path/to/file

 

# Import Database

$ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file

 

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY ‘<password>’];

 

REVOKE ALL ON <dbname> FROM <dbuser@localhost>;

 

CREATE DATABASE <dbname>;

 

DROP DATABASE <dbname>;

 

DROP TABLE <tablename1[, table2, table3…]>;

 

# To activate new permissions

FLUSH PRIVILEGES;

 

USE <nameOfDatabase>;

 

SHOW DATABASES;

 

# show tables begining with the prefix

SHOW TABLES LIKE ‘prefix%’;

 

SELECT * FROM <nameOfTable>;

 

DESCRIBE <nameOfTable>;

 

INSERT INTO <table> <username, password, name1, name2, …> VALUES (‘user’, password(‘pass’), ‘value1’, ‘value2’ …);

 

CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>;

 

INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>;

 

ALTER TABLE <tableOldName> RENAME <tableNewName>;

 

UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];

 

Optimize MySQL Tables

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

OPTIMIZE TABLE tbl_name[,tbl_name]...

Remember to do this periodically and after every upgrade.

mysqlcheck -o -u root -p --all-databases

OPTIMIZE TABLE for MyISAM tables is equivalent of running:

$ myisamchk --quick --check-only-changed --sort-index --analyze *.MYI

 

MySQL Database Repair

  CHECK TABLE <tableName>

  REPAIR TABLE <tableName>

  myisamchk -e *.MYI

  myisamchk -r -q <tableName> (-r -q means `quick recovery mode’)

 

 

Tuning/Optimizing my.cnf file for MySQL

Had to do some fine tuning of MySQL 4.1.9 and here is what my.cnf file looks like for a 2GHz machine with 1GB of memory.

[mysqld]



socket=/path/to/mysql.sock



datadir=/var/lib/mysql



skip-locking



skip-innodb



# MySQL 4.x has query caching available.



# Enable it for vast improvement and it may be all you need to tweak.



query_cache_type=1



query_cache_limit=1M



query_cache_size=32M



# max_connections=500



# Reduced to 200 as memory will not be enough for 500 connections.



# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections



# which is now: 64 + (1 + 1) * 200 = 464 MB

 

 

Sorting VARCHAR data in mysql

Here’s a quick tip at sorting VARCHAR type data in mysql database with values in a column.

With the default sort, it would look something like below:

mysql> SELECT column FROM table_name ORDER BY column; 

column

======

100

1000

10000

200

2000

20000

Now with “… ORDER BY column+0”, I get it sorted right:

mysql> SELECT column FROM table_name ORDER BY column+0; 

column

======

100

200

1000

2000

10000

20000

This is a quick fix instead of sorting to CAST operator.

 

root login email alert on linux systems…

echo <<EOF >>~/.bash_profile



# Send email notification of root login



echo 'ALERT - Root Shell Access:' `who` | mail -s "Alert: Root Access from `who | awk '{print $6}'`" offsite@email.tld



EOF

You may also benefit from jailing your system if you have shell users.

 

Fried your Master Boot Record?

No worries! You are using GRUB (GRand Unified Bootloader) aren’t you?

You should atleast know where your “/boot” partition is installed.

  1. Here is my drive setup and “/boot” is setup in “hda3”.
2.           # df -h



3.           Filesystem            Size  Used Avail Use% Mounted on



4.           /dev/mapper/VolGroup00-LogVol00



5.                                  18G  4.3G   13G  26% /



6.           /dev/hda3              99M   19M   76M  20% /boot



7.           none                  506M     0  506M   0% /dev/shm
8.           # fdisk -l

9.           Disk /dev/hda: 60.0 GB, 60011642880 bytes

10.       255 heads, 63 sectors/track, 7296 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

 

   Device Boot      Start         End      Blocks   Id  System

/dev/hda1               1        1044     8385898+   7  HPFS/NTFS

/dev/hda2            1045        4699    29358787+  83  Linux

/dev/hda3            4700        4712      104422+  83  Linux

/dev/hda4            4713        7296    20755980    5  Extended

/dev/hda5            4713        7296    20755948+  8e  Linux LVM

  1. Boot up your handy knoppix and run the below commands [ notes ].
12.       # su -                    [ root shell ]



13.       # grub                    [ grub shell ]



14.       grub> root (hd0,2)        [ specify where /boot partition resides ]



15.                                 [ 3rd primary partition of the 1st HDD ]



16.                                 [ hint: start count from 0 ]



17.       grub> setup (hd0)         [ install grub in MBR ]



18.       grub> quit                [ exit the grub shell ]



19.       # shutdown -r now
  1. Here’s a one line that will do the same automatically.
21.       # grub-install hd0

You should now have a shiny new MBR.

 

One comment

  1. Thanks for MySQL optimization tip.

    You may also try servermonkeys software



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: