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…

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

postfix - mailbox size limit and message size limit

postfix is my MTA of choice. I use it for my mailserver because its simplicity , security and sendmail-compatible (the widely used smtp in the world but not as secure). It is also extensible by plugging other servers for various purposes (antispam, antivirus,database etc).

I had one problem with file attachment larger than 10MB. Users couldn't send it although I have setup squirrelmail (SM) to be able to attach files summed up more than 20MB and I had modified php settings as per here. The problem was not in SM setting. It was postfix. By default, attachment size that can be sent by postfix is 10MB ~ 10240000 byte. How did I know it? I looked in log file (for my system it is in /var/log/mail/errors. For other system, the file to look is /var/log/maillog). The line looked like this:

Feb 26 16:30:53 webmail postfix/sendmail[30775]: fatal: me@mymailserver.org(74): Message file too big


Solution
Open /etc/postfix/main.cf with a text editor of choice and find message_size_limit directive an…