mysql 4.1.22 common command 常用命令

1.   数据备份 ------  refer to    http://www.newasp.net/tech/data/18173.html

      1.1 backup everythings of database "test"

            "C:/Program Files/MySQL/MySQL Server 4.1/bin/mysqldump.exe" -uuser -p test > c:/DBtest.txt

      1.2  backup everythings of a table in database "test" named "igoogleuser"

      "C:/Program Files/MySQL/MySQL Server 4.1/bin/mysqldump.exe" -uuser -p test igoogleuser > c:/igoogleuser.txt
　　
backup database Meet_A_Geek's table Orders to a txt file：
　　bin/mysqldump –p Meet_A_Geek Orders >MeetAGeek_Orders.txt
　　
backup records which meet some conditions in database's table:
　　bin/mysqldump –p –where="Order_ID > 2000" Meet_A_Geek Orders > Special_Dump.txt

 

outport a database's structure:

 mysqldump -u david_yeung -p -d --add-drop-table takkoTest >takkoTestStructure.sql　　

 

get remote data:

mysqldump -uuser -p -h192.168.128.196 cs_order order > /home/itadgz/eric/orderData.html

 

2.    datetime column default 值的解决方案   -----------利用两个timestamp字段只更新第一个的特性

CREATE TABLE `igoogleuser` (
  `id` int(11) NOT NULL auto_increment,
  `userName` char(20) NOT NULL default '',
  `password` char(20) NOT NULL default '',
  `updatetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `createtime` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `userName` (`userName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

3.

改列名:

alter table history change machine_id remote_ip int(10) default -1;

 

加多个列:

alter table history add history_machine_id int(10),add history_program_id int(10);

 

刪多列:

alter table history drop column history_machine_id,drop column history_program_id;