Skip to main content

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.


Comments

Suria Mohd Noor said…
salam... boleh tumpang tanya, mcm manakalau nk connect databae ke proxy server guna perl scripting?

tqvm!
zamri said…
Salam. Maaf ya. Saya tak pandai sgt perl scripting.

Popular posts from this blog

mplayer-gui error : Error in skin config file

After installing mplayer-gui package, I can't start it.

$ gmplayer MPlayer 1.1-4.8 (C) 2000-2012 MPlayer Team mplayer: could not connect to socket mplayer: No such file or directory Failed to open LIRC support. You will not be able to use your remote control. Error in skin config file on line 6: PNG read error in /usr/share/mplayer/skins/default/main Config file processing error with skin 'default'
After googling a bit, I found out that it was due to the png files in dir /usr/share/mplayer/skins/default. This is the default skin directory. To fix this error, I have to install ImageMagick package because I want to use the convert program to convert all of the png files to format png24. Thus, cd /usr/share/mplayer/skins/default; for FILE in *.png ; do sudo convert $FILE -define png:format=png24 $FILE ; done
Rerun gmplayer and all should be fine.
Have fun!
UPDATE (02-10-2017)

It doesn't work on Ubuntu 16.04 (xenial) but there's a workaround here.

You can update your syst…

Moving your mysql database to another hard disk

Recently, my server's only hard disk was almost full. I bought a new hard disk with bigger size and I decided to just add it as a second hard disk. Since I need to move it to the 2nd hard disk, I need to find a proper way to move the db with minimum downtime. So I googled around and found a solution.
First, I needed to format the 2nd hard disk and I chose xfs as the filesystem. I created 2 partitions using Linux's fdisk for this task. First partition is 10 GB and 2nd one is around 900 GB. That's approximately added up to 1 TB. Then I mounted the 2nd partition in current partition eg /media/hd2 as follows:
mount -t xfs /dev/sdb5 /media/hd2
where /dev/sdb5 is the partition and /media/hd2 is the mounting dir.
Stop mysql db before doing anything:
service mysql stop
Afterthat, I copied the entire db to newly mounted hard disk:
cp -rv /var/lib/mysql /media/hd2
It will take a while if you have huge databases.
Then, change the ownership of the dir to user and group of mysql:
chown -R mysql:…

Transparent proxy with squid 2.6

I have upgraded my squid from 2.5 STABLE13 to 2.6 STABLE18. Transparent proxy is setup differently in this version. You need this directives in squid.conf (usually in /etc or /usr/local/etc or /usr/local/squid/etc, check with your distro).

acl our_networks src 192.168.2.0/24 127.0.0.1
http_access allow our_networks
http_port 192.168.2.1:3128 transparent
always_direct allow all

where 192.168.2.1 is your proxy server IP address.


If you have flushed your iptables, create new rule:

iptables -t nat -A PREROUTING -i eth0 -p tcp –dport 80 -j REDIRECT –to-port 3128

where 3128 is the port where squid is running.
References:
http://www.deckle.co.za/squid-users-guide/Transparent_Caching/Proxy