MySQL Tips and Trick

I am finding MySQL to be a very robust and productive tool.  Isn't that what it's all about these days, remaining productive and showing your worth?  My old boss used to always say sell yourself as if you were your own company.  In order to do that and come up with ways you've seen me write about a few tools we use and or have developed.  Many of those tools use MySQL as a backend database.  I have learned that you can store plenty of data in the database and use it now or in the future.

Future articles will talk about archiving as those processes are developed.

Here they are:

These tips and tricks might be helpful when working with MySQL.

Alter an existing column via command line

alter table tablename modify columnname VARCHAR(35) ;

Create calendar table (example shows one date column from 2009-01-01 thru 2031-12-31) 

 

use UnixSystems;

truncate table ALL_DATES;

drop table ints;

CREATE TABLE ints ( i tinyint );

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO ALL_DATES (AD_date)

SELECT date('2009-01-01') + interval a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i day

FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e

WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 8399

ORDER BY 1;

Copy table and it's data to a new table 

 

CREATE TABLE new_tablename LIKE database.tablename; INSERT new_tablename SELECT * FROM database.tablename;

Count rows in a table 

 

SELECT COUNT(*) FROM tablename;

Database creation recommendation 

 

Whenever possible use a standard id column with the table name, i.e. a table/schema named Help should have a column named idHelp, this should be the primary key and be auto incremented

delete data older than 1 day using current date and time 

 

delete from ProcessMapsUpdateRequests where datetime <= date_sub( now(), interval 1 day );

Determine size of tables and/or databases 

 

select table_schema, sum(data_length + index_length) / 1024 / 1024 from information_schema.tables group by table_schema; (lists by table in MB)

select sum(data_length + index_length) / 1024 / 1024 from tables; (lists all tables combined in MB)

Determine the size your InnoDB buffer should be set at in my.cnf 

 

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),

SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size

FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE

engine='InnoDB') A,(SELECT 3 pw) B;

Different types of output use the following option when starting mysql session

 

XML - use a -X (/opt/coolstack/mysql_32bit/bin/mysql -X)

Vertical - use --vertical (/opt/coolstack/mysql_32bit/bin/mysql --vertical)

HTML - use -H (/opt/coolstack/mysql_32bit/bin/mysql -H)

Tabs - use -t (/opt/coolstack/mysql_32bit/bin/mysql -t)

Remove timestamp from column in select statement 

 

Use the DATE function. I.E. select DATE(fieldname) from table

Reset that status of a slave - like after fixing an error 

 

restart the slave using svcadm restart mysql32-csk - check status with 'show slave status\G' to see if errorno is gone.

Send the output from a query to a text file

select DISTINCT hostname from dynamic where zoneinfo='global' into outfile 'textfile name';

Select data in reverse order by a specific column and limit to the last 300 entries. 

 

select datetime,DISKGROUP,DEVICE,AVGSERVTIME from DiskServiceTimes where HOST='orwell' order by datetime DESC limit 300;

Select data for the next 3 months 

 

echo "select event_title,event_start_date,event_end_date,event_description from events where

DATE( event_start_date ) +0 >= CURDATE() AND month( event_start_date ) BETWEEN month( CURDATE() ) +0 AND month( CURDATE() ) +3 and event_type='1' ORDER BY event_start_date;" | /opt/coolstack/mysql_32bit/bin/mysql -H dotproject >> ${OUTPUTFILE}

Select data from the last 3 days using just the date 

 

datetime >= DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 3 DAY)

Select dates to pull data for by prompting user to fill in variables 

 

echo "select dateTime,userName,text from ajax_chat_messages where datetime > '${STARTDATE} ${STARTTIME}' and datetime < '${ENDDATE} ${ENDTIME}' ORDER BY datetime;" | /opt/coolstack/mysql_32bit/bin/mysql -H ajaxchat > /tmp/chat_log.html

Select unique or distinct entries 

 

mysql> SELECT DISTINCT firstname, city FROM address;

start replication 

 

in the slave database run start slave;

stop replication 

 

in the slave database run stop slave;

Suppress column headers 

 

Use --skip-column-names (/opt/coolstack/mysql_32bit/bin/mysql --skip-column-names)

Use limit to restrict the number or results from a query

select apps from dynamic where hostname='${VAR1}' into outfile '${LOCALTEMP}' LIMIT 0;