MySQL Tips

Posted: June 2, 2007 in LINUX

Some tips of using MySQL on Linux

Login to MySQL using mysql client in console/terminal:

mysql -u username -p dbname

or

mysql -u username -ppassword dbname

or (using current username to log in)

mysql -ppassword dbname

security tip: username root is the default administrator. Do not use it in a live environment. Create a new one and set the appropriate permission for it.

Create a new database:
mysqladmin -u username -ppassword create databasename

(username is the administrator username that able to create a new database ie root)

or you can log in to mysql using mysql client in console. Example:

//create table with myisam engine.

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=MYISAM


//create table with HEAP engine.

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=HEAP

Delete a database:
Login to mysql and issue command drop database databasename.

(Make sure you use usernames with correct priviledge to drop a database)

What is the size of my database?
database size = the sum of all table sizes + all index sizes

  1. Open a text editor (eg. Notepad)
  2. Copy and paste the code below into your text editor ( replace username, password and dbid accordingly):

    mysql database sizeif ($filesize < filesize =””>

    # in at least kilobytes.

    for ($i = 0; $filesize > 1024; $i++) $filesize /= 1024;

    $file_size_info[‘size’] = ceil($filesize);

    $file_size_info[‘type’] = $bytes[$i];

    return $file_size_info; } $db_server = ‘mysqlhost’; $db_user = ‘username’; $db_pwd = ‘password’; $db_name = ‘dbid’;

    $db_link = @mysql_connect($db_server, $db_user, $db_pwd)

    or exit(‘Could not connect: ‘ . mysql_error()); $db = @mysql_select_db($db_name, $db_link) or exit(‘Could not select database: ‘ . mysql_error());

    // Calculate DB size by adding table size + index size:

    $rows = mysql_query(“SHOW table STATUS”); $dbsize = 0;

    while ($row = mysql_fetch_array($rows)) {$dbsize += $row[‘Data_length’] + $row[‘Index_length’]; } print “database size is: $dbsize bytes “; print ‘or’;

    $dbsize = file_size_info($dbsize); print “database size is: {$dbsize[‘size’]} {$dbsize[‘type’]}”; ?>

put this php script into your accessible directory. (taken from here).

Nice reading : Overcoming MySQL’s 4GB limit by Jeremy Zawodny.

To know what engine your database is using:

SHOW TABLE STATUS FROM yourdbname

MySQL has support for ISAM,MyISAM , HEAP, BerkeleyDB and InnoDB database engine. Depending on how your MySQL packages are compiled, it may or may not support for all these engines.


Bear in mind that ISAM and MyISAM engines lack foreign key and transactional support. BerkeleyDb and InnoDB overcome that limitation. However, BerkeleyDB and InnoDB are much slower compared to ISAM and MyISAM. If your database is of type ISAM/MyISAM, fortunately you can convert it to InnoDB using this command:


ALTER TABLE isamtable CHANGE TYPE=InnoDB

or you can use utility mysql_convert_table_format :

mysql_convert_table_format –user=username –pasword=password –type=innodb databasename tables

(if tables is omitted, all tables will be converted. That means you can convert certain tables to InnoDB and leave the rest with ISAM. One database uses more than one engine. That’s the flexibility MySQL provides. Flexibility is the key here.)


MySQL makes this happen with three steps. First, an exact copy of the table is created. Next, any incoming data changes are queued, while the copy is moved to the other engine. Finally, any queued data changes are committed to the new table, and the original one is deleted.

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