Some mysql howto

Posted by KsI on June 19, 2012 Leave a comment (0) Go to comments

This topic was done to skip googling in some cases.

How to get mysql database size:

mysql -uroot -pPASSWORD -D DATABASE_NAME -e "show table status\G"| egrep "(Index|Data)_length" | awk 'BEGIN { rsum = 0 } { rsum += $2 } END { print rsum }'

Source: http://eddnet.org/?p=1765

How to set mysql to read only mode:
Attention! root can do r/w regardless of read_only!

mysql> set GLOBAL read_only = true;

edit config

:/etc/mysql/conf.d# cat slave.cnf
[mysqld]

server-id = 18
innodb_buffer_pool_size = 200MB
read_only = true

How to drop user

DROP USER 'USERNAME'@'%';

How to find duplicate rows.

SELECT *,count(id) AS Num FROM clicks_log GROUP BY id HAVING (COUNT(id) > 1 );

How to delete duplicated Rows
I am not SQL guru, maybe there is another perfect way how to do it, but it`s works:

mysql> create table id_tmp AS (SELECT id FROM block_news_views_log GROUP BY id HAVING (COUNT(id) > 1) ) ;
mysql> DELETE FROM block_news_views_log WHERE ( id IN (SELECT * from id_tmp ) );
mysql> DROP table id_tmp;
mysql> SELECT id FROM block_news_views_log GROUP BY id HAVING (COUNT(id) > 1) ;