# clone an existing database
$ mysql -u root -e "create database dbname_test"
$ mysqldump -u root dbname | mysql -u root dbname_test

# clone via ssh
$ mysqldump -u username -p'password' db-name | ssh user@remote.box.com mysql -u username -p'password' db-name

# fix a date that's out by 11hrs (timezone offset)
UPDATE content_type_artbook
SET field_book_year_value = DATE_FORMAT(field_book_year_value + INTERVAL 11 HOUR, '%Y-%m-%dT%T')
WHERE field_book_year_value LIKE "%-12-31T13:00:00"

mysql> select * in turba_objects;

mysql> select * from turba_objects where owner_id='luke@zina.org';

mysql> delete from turba_objects where owner_id='luke@zina.org';

mysql> CREATE DATABASE wp_gutstring;
Query OK; 1 row affected (0.04 sec)

mysql> GRANT ALL PRIVILEGES ON wp_gutstring.* TO 'thenownow'@'localhost';
Query OK; 0 rows affected (0.06 sec)

mysql> FLUSH PRIVILEGES; 
Query OK; 0 rows affected (0.07 sec)

mysql> GRANT ALL PRIVILEGES ON [dbname].* to [dbuser]@'' identified by '[dbpassword]';

mysql> SET PASSWORD FOR 'mrsnow'@'localhost' = OLD_PASSWORD('newpassword'); 
Query OK; 0 rows affected (0.03 sec) 

SHOW DATABASES;

USE mysql;
SHOW TABLES; 
SELECT user FROM `user`;

# import from gz
gunzip < ~/Documents/jobs/laudanum/Backups/dump-20090917.sql.gz | sudo mysql5 -p wp_cms

# import to remote server
mysql5 -u [username] -p -h [hostname.com] dev < dump.sql

mysql> UPDATE rc_posts SET guid = REPLACE(guid, 'dev2.houseoflaudanum.com' 'www.ruthcullen.com');
Query OK, 116 rows affected (0.03 sec)
Rows matched: 116  Changed: 116  Warnings: 0

mysql> select from_unixtime(timestamp, '%h:00 %d/%m/%Y') as date from log group by date;

# people who didn't refuse or receive the video . ( not OK, not REFUSED )
mysql > select part_id, action from log where part_id like '1.1' and action not in ('OK','REFUSED');

mysql> select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='OK' group by date;

select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='OK' group by date union select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(*) from log where action='REFUSED' group by date

select from_unixtime(timestamp, '%Y/%m/%d %h:00') as date, COUNT(action) from (select action from log where action = 'OK' union all select action from log where action = 'refused') group by date;

mysql> SELECT FROM_UNIXTIME(timestamp, '%Y/%m/%d %H:00') AS date, SUM(CASE WHEN action='REFUSED' THEN 1 ELSE 0 END) AS refusedcount, SUM(CASE WHEN action='OK' THEN 1 ELSE 0 END) AS okcount FROM log GROUP BY date INTO OUTFILE '/tmp/result.txt';

mysql> SELECT FROM_UNIXTIME(timestamp, '%Y/%m/%d %H:00') AS date, SUM(CASE WHEN action='REFUSED' THEN 1 ELSE 0 END) AS refusedcount, SUM(CASE WHEN action='OK' THEN 1 ELSE 0 END) AS okcount, SUM(CASE WHEN action!='OK' AND action!='REFUSED' AND device_id!='EVENT' THEN 1 ELSE 0 END) AS okcount FROM log GROUP BY date INTO OUTFILE '/tmp/result.txt';

mysql> select part_id, count(*) from log where part_id like '1%' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
| 1 | 814 |
| 1.1 | 84 |
| 1.2 | 22 |
| 1.3 | 2 |
+---------+----------+

mysql> select part_id, count(*) from log where part_id like '1%' and action = 'OK' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
| 1 | 174 |
| 1.1 | 33 |
| 1.2 | 16 |
| 1.3 | 2 |
+---------+----------+

mysql> select part_id, count(*) from log where part_id like '1%' and action = 'REFUSED' group by part_id;
+---------+----------+
| part_id | count(*) |
+---------+----------+
| 1 | 595 |
| 1.1 | 41 |
| 1.2 | 6 |
+---------+----------+

mysql> select * from log where exists ( select 1 from log as earlierrefusal where earlierrefusal.device_id = log.device_id and earlierrefusal.timestamp < log.timestamp and earlierrefusal.action = 'REFUSED' );

mysql> select device_id, part_id, count(*) as messagecount from log group by device_id, part_id having count(*) > 1;