Farid Ahmadian / DevOps

MySQL cheat sheet

Public domain

Table Repair


Add a column to an existing table

ALTER TABLE table_name ADD new_column INT NOT NULL;
ALTER TABLE table_name ADD new_column VARCHAR(50) AFTER existing_column;
ALTER TABLE table_name ADD new_column VARCHAR(50) FIRST;

Change a column name

ALTER TABLE table_name CHANGE old_name new_name VARCHAR(50);

Add unique to an existing column

ALTER TABLE table_name ADD UNIQUE (existing_column);

Remove a column

ALTER TABLE table_name DROP COLUMN column_name;

Export database schema

mysqldump -uroot -p database -d > databse-schema.sql

Export entire database

mysqldump -uroot -p database > databse-backup.sql

Export full databases

mysqldump -u root -p  --all-databases --routines --max_allowed_packet=512M > mysql_full_backup.sql

Sotring IP

mysql> SELECT INET_ATON('');
| INET_ATON('') |
|                 3232238130 | 
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA('3232238130');
| INET_NTOA('3232238130') |
|           | 
1 row in set (0.00 sec)

Replace a string

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://oldlink.com', 'http://newlink.com');

Datediff and TIMETOSEC

SELECT datediff( pr_endtime, pr_starttime ) AS date_diff FROM problem_reports;

SELECT TIME_TO_SEC(TIMEDIFF(pr_endtime,pr_starttime))/86400 FROM problem_reports;

SELECT TIME_TO_SEC( TIMEDIFF( pr_endtime, pr_starttime))/3600 FROM problem_reports;

BY: Pejman Moghadam, Farid Ahmadian
TAG: mysql, wordpress
DATE: 2011-06-08 10:36:58

Farid Ahmadian / DevOps [ TXT ]

With many thanks and best wishes for dear Pejman Moghadam, someone who taught me alot in linux and life :)