The delimited dump of data:
SELECT * INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mydb1.table1;
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE mydb1.table1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES STARTING BY 'xxx',
LINES TERMINATED BY '\n';
Note:(1)Backing up your data using the delimited dump is more efficient than mysqldump, and the dump file is smaller on disk.(2)To dump or restore multiple tables at once, you can use mk-parallel-dump.(3)Here you should make sure the in/out file is readable/writeable by mysql (for example, the file is owned by mysql) . Otherwise, you will have a permission problem.ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE UPDATE t SET c1_newtype = c1 ALTER TABLE t DROP COLUMN c1 RENAME COLUMN t.c1_newtype TO c1
----
example: change a column name
ALTER TABLE genes CHANGE oldName newName VARCHAR(9);
----
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS:
SHOW TABLE STATUS;
CHECK TABLE mytable;
OPTIMIZE TABLE mytable;
ANALYZE TABLE mytable;
REPAIR TABLE mytable;
-------------------------
Example: to create a table
CREATE TABLE test.genes(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
gene VARCHAR(15),
chrom VARCHAR(6),
direction CHAR(1),
start1 INT,
stop1 INT,
start2 INT,
stop2 INT,
exons INT,
exonStarts VARCHAR(60),
exonStops VARCHAR(60),
unknown varchar(8),
remark VARCHAR(15)
) ENGINE=InnoDB;
---------------------------
Here is the official table from UCSC
CREATE TABLE `knownGene` ( `name` varchar(255) NOT NULL DEFAULT '', `chrom` varchar(255) NOT NULL DEFAULT '', `strand` char(1) NOT NULL DEFAULT '', `txStart` int(10) unsigned NOT NULL DEFAULT '0', `txEnd` int(10) unsigned NOT NULL DEFAULT '0', `cdsStart` int(10) unsigned NOT NULL DEFAULT '0', `cdsEnd` int(10) unsigned NOT NULL DEFAULT '0', `exonCount` int(10) unsigned NOT NULL DEFAULT '0', `exonStarts` longblob NOT NULL, `exonEnds` longblob NOT NULL, `proteinID` varchar(40) NOT NULL DEFAULT '', `alignID` varchar(255) NOT NULL DEFAULT '', KEY `name` (`name`), KEY `chrom` (`chrom`(16),`txStart`), KEY `chrom_2` (`chrom`(16),`txEnd`), KEY `protein` (`proteinID`(16)), KEY `align` (`alignID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;(to make this post easier to find, I leave a few unique/weird labels here: mysql import infile wzyalex138671)
