mysql commonly use statement

Create table

mysql> CREATE  table if not exists tblname like old_tblname;

INSERT ... SELECT

mysql> INSERT INTO tblname1 SELECT *  from tblname2 where ...;

Empty table

mysql> TRUNCATE table story_longcon_40;

Delete/Drop table

mysql> DROP TABLE tblname; 

Delete/Drop database

mysql> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Optimize Table

mysql> OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_nam

Delete binary logs 
This will delete the binary log till master.000080

mysql> purge binary logs to 'master-bin.000081';

Start slave

mysql> START SLAVE [thread_type [, thread_type] ... ]

or

mysql> START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

[MySQL] Backing up data

There are several ways to backing up data
1) Logical backup

* SQL dumps

#mysqldump dbname tblname

Not suitable for huge backup. Both table structure and the data are stored together.(option available)

* Delimited file backups
backing up :

mysql> SELECT * INTO OUTFILE '/tmp/t1.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM test.t1

restore :

mysql> LOAD DATA INFILE '/tmp/t1.txt'
    -> INTO TABLE test1.t1
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

*parallel dump: maatkit(mk-parallel-dump)

2) File system snapshot (LVM)
not covered here.

mysql backup/dump/export table schema, definition

1) use mysqldump

server# mysqldump -uroot -p dbname tblname -d 

with this method you will get sql statement. You can use this for backing up table definition as well.

2) create table from another table

mysql> create table if not exists tblname like old_tblname;

with this method you get a copy of table. It is useful if you want to have a another table with the same structure for backing up, testing.