Tips for MySQL

MySQL is the most popular database in the world. I'm not doubt about it. Most of forums, portals and web-based database applications use it as the back-end engine.

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 size

   if ($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.