Everyday SQL statements

Tools

Status

SHOW status where Variable_name like ‘Th%’ or Variable_name like ‘%Connec%’ ;
SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]

Check/Repair tables

mysqlcheck -u root -p***** –auto-repair –check –optimize –all-databases

Profiling

watch -n 0.5 ‘mysql -u root -ppass -e “SHOW FULL PROCESSLIST” | grep Query’

http://opendba.blogspot.com/2008/03/mysql-finally-ability-to-traceprofile.html

Dump

pg_dump -U test arachnid_archiv_test –inserts -h chaos.spider.bg –encoding=utf8 -f pgsql.sql

Dump for full backup with flushing of the log files

mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS
–single-transaction –all-databases –delete-master-logs –flush-logs –master-data=2
> backup_sunday_1_PM.sql

Encoding problems

http://www.hostbulgaria.com/tutorials/mysql-charset-encoding.aspx

SHOW VARIABLES LIKE ‘character_set_%’;
curl -i http://system3.spider.bg

Creating a database

create database re_production DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Creating a user

GRANT ALL PRIVILEGES ON arachnid_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON system3_production.* TO ‘payak’@’%’ IDENTIFIED BY ‘payakpassword’ WITH GRANT OPTION;

mysqladmin -u [user] -h localhost -p password ‘[new_password]’

SQL for a table

SHOW CREATE TABLE tblname;

mysql tunel to another machine

ssh -N -f -l root -L 0.0.0.0:3307:91.196.240.132:3306 s1
open port 3307 on the local machine to 91.196.240.132:3306 and login into s1 with root

Replace text

UPDATE script_histories SET cod_script = replace(cod_script,”observer.ArchiveObserver(siteId)”,”observer.ArchiveObserver(siteId, script_id, owned_source_id)”);

Copy from one table to another

DELETE FROM system3_production.articles;
INSERT INTO system3_production.articles SELECT * FROM arachnid_from_screen.articles;

Sessions for Rails

select count(*) from sessions where updated_at < DATE_SUB(now(), INTERVAL 3 DAY);

Binnary loging

http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

Check this attachment here: mysql-presentation on replication etc.

  • See the status of the log files
    SHOW BINARY LOGS;
    SHOW MASTER STATUS;
  • Clean the binary logs instantly
    RESET MASTER;
  • Clean binary logs to date/name
    PURGE BINARY LOGS TO ”mysqld-bin.00XXXX’;
  • Configurations in my.cnf
    log-bin
    server-id = 1
    expire_logs_days = 1
    max_binlog_size = 100M

Configuration

max_allowed_packet = 50M
wait_timeout=720
max_connections=1000
connect_timeout=20

query_cache_limit=8M #~~~ removed, 1M def. max pozwl. razmer za cache-hirane na edna zajawka
query_cache_size=128M #~~~ 32M, 0 def.
query_cache_type=1

Restoring the maintian Debian User

2 Replies to “Everyday SQL statements”

Comments are closed.