MySql

Mysql get size of tables in a database

To find the detail of size of tables in a database use this query in mysql console:

SELECT table_name AS “Tables”,

round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”

FROM information_schema.TABLES

WHERE table_schema = “$DB_NAME”

ORDER BY (data_length + index_length) DESC;

E.g. for database name mydatabase :

SELECT table_name AS “Tables”,

round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”

FROM information_schema.TABLES

WHERE table_schema = “mydatabase”

ORDER BY (data_length + index_length) DESC;

Read More...

MySql

Set timezone of Mysql Server

Many times timezone issues with your database server causes wrong information to be entered in reports, transaction tables etc.

To fix timezone issues with your mysql server, just login to your mysql and execute following query:

SET GLOBAL time_zone = ‘<REQUIRED TIMZONE>;

E.g. SET GLOBAL time_zone = ‘+5:30’;

If you have to get the  current timezone, use this- SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

Read More...

MySql

Import a CSV File to Mysql Database

To Import a CSV File in MySQL Database execute this query with path to the csv file you need to import:

LOAD DATA LOCAL INFILE ‘filetoimport.csv’

INTO TABLE <tablename>

FIELDS TERMINATED BY ‘,’

LINES TERMINATED BY ‘\n’

(field1, field2, field3….,field-n);

[box type=”info”] Remember to match the columns in your csv and MySQL table.[/box]

Read More...

MySql

Check Database size in Mysql

Use this Query to check the Database size in Mysql:

[box] SELECT table_schema “database_name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema;[/box]

Read More...

Linux, MySql, PHP

Php Mysql installation in a linux server

Use this command on a Red Hat based linux installation to install Php Mysql completely without any issues:

yum install php php-devel php-gd php-imap php-ldap php-mysql php-odbc php-pear php-xml php-xmlrpc curl curl-devel perl-libwww-perl ImageMagick libxml2 libxml2-devel

Read More...

Linux, MySql

Mysql Socket issue

Sometimes mysql gives an error while starting/opening mysql service for the socket, It happens when your mysql socket get corrupted.
To create a new socket you just need to:
Comment the socket settings in /etc/my.cf
Restart mysqld and httpd 🙂

Read More...