MySql – MindGeek

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;

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);

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]

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]

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

Linux, MySql