ºìÁªLinuxÃÅ»§
Linux°ïÖú

Linux¾«Æ·Ö®²¶×½mysqlÖв»¿ÉºöÊÓµÄ֪ʶµã£¨Ò»£©

·¢²¼Ê±¼ä:2014-08-30 20:28:38À´Ô´:ºìÁª×÷Õß:СС295813080
Ò»¡¢mysqlÃüÁîµÄ·ÖÀà
mysqlÃüÁî¿É·ÖΪ¿Í»§¶ËÃüÁîºÍ·þÎñÆ÷¶ËÃüÁîÁ½Àࣺ
1£©¿Í»§¶ËÃüÁî
\c:ÌáÇ°ÖÕÖ¹½áÊøÓï¾ä
\g:ÎÞÂÛÓï¾ä½áÊø·ûÊÇʲô£¬Ö±½Ó½«´ËÓï¾äËÍÖÁ·þÎñÆ÷¶ËÖ´ÐУ»
\G:ÎÞÂÛÓï¾ä½áÊø·ûÊÇʲô£¬Ö±½Ó½«´Ë¾äË͵½·þÎñÆ÷¶ËÖ´ÐУ¬¶øÇÒ½á¹ûÒÔÊúÅÅ·½Ê½ÏÔʾ£»
\! COMMAND:Ö´ÐÐshellÃüÁî
\W:Óï¾äÖ´ÐнáÊøºóÏÔʾ¾¯¸æÐÅÏ¢£»
\#:¶Ôн¨µÄ¶ÔÏó£¬Ö§³Ö²¹È«¹¦ÄÜ£»
\r£ºÖØÐÂÁ¬½Óµ½·þÎñÆ÷ÉÏÀ´
²¹³ä£ºmysql¿Í»§¶ËÃüÁîÀïÌáµ½µÄ»¹ÓÐ mysqldump(±¸·Ý¹¤¾ß), mysqlimport(µ¼È빤¾ß),mysqlcheck
2£©·þÎñÆ÷¶ËÃüÁ
mysqld
mysql_multi£º Ö§³Ö¶àʵÀýµÄ
mysql_safe£º Ö§³Ö°²È«Ï̵߳Ä
mysqlbinlog£º ²é¿´mysqlµÄ¶þ½øÖƹ¤¾ß
mysqlhotcopy£ºmysqlµÄ±¸·Ý¹¤¾ß
·þÎñÆ÷Óï¾äÓÐÓï¾ä½áÊø·û£¬Ä¬ÈÏΪ·ÖºÅ
\d: ¶¨ÒåÓï¾ä½áÊø·û£¨delimiter¶¨ÒåÓï¾ä½áÊø·û£©
//£ºÓï¾ä½áÊø·û
eg£ºshow database // ÈôÏë»»»ØÀ´Ê¹ÓÃ\d
ËùÓзþÎñÆ÷Óï¾äÖ»ÓÐÖ´Ðпͻ§¶Ë²ÅÄܽøÐУ¬¶ø¿Í»§¶Ë²»ÐèÒªÓï¾ä½áÊø·û£»
·þÎñÆ÷¶ËÃüÁîÈçºÎ»ñÈ¡°ïÖú£¿
help COMMAND :»ñÈ¡°ïÖúÐÅÏ¢
help command index£º»ñÈ¡Ë÷Òý°ïÖúÐÅÏ¢ £¨ÒªÏë»ñµÃ½øÒ»²½µÄ°ïÖúÐÅÏ¢£¬¿ÉÒÔ°ÑÖ´ÐÐhelp command indexºóÏÔʾµÄURLºóµÄÁ´½Ó¸´ÖÆÏÂÀ´Ìùµ½ä¯ÀÀÆ÷ÄÚ»¹ÄÜ»ñÈ¡µ½¹Ù·½Îĵµ£©
¶þ¡¢mysqladminÃüÁîµÄʹÓãº
1£©¼ò½é£ºmysqladminÓÃÀ´Ö´ÐйÜÀíÃüÁîµÄרÓÃÃüÁҲÊÇÒ»¸ö¿Í»§¶ËÃüÁͨ¹ý¿Í»§¶ËÁ¬½Óµ½·þÎñÆ÷¶ËÈ¥¹ÜÀíÃüÁmysqladminºÜ¶ÀÌØ£¬ËüÊÇÒ»¸ö¾ßÓÐÐí¶à×ÓÃüÁîµÄÃüÁî
2£©Ó÷¨: mysqladmin [option] command [arg] [command [arg]]...
mysqladmin -uroot -p password 'NEW_PASS' ΪÓû§É趨ÃÜÂëµÄ
3£©³£ÓÃÃüÁ
processlist ½ø³ÌÁÐ±í£¬¿ÉÒÔÁгö·þÎñÆ÷ÉÏÕýÔÚÖ´ÐÐËùÓнø³ÌÁбí
status ²é¿´mysqlµÄ״̬
--sleep N: ÏÔʾƵÂÊ
--count N£ºÏÔʾ¶à¸ö״̬
eg:mysqladmin status --sleep 2 mysqlÁ½ÃëÖÓÏÔʾһ´Î
eg:mysqladmin status --sleep 2 --count 2 mysqlÁ½ÃëÖÓÏÔʾ1´ÎÏÔʾ2´Î
extended-status £ºÏÔʾ״̬±äÁ¿
varitables: ÏÔʾ·þÎñÆ÷±äÁ¿
flush-privileges: ÈÃmysqldÖضÁÊÚȨ±í£¬µÈͬÓÚreload£»
flush-status£ºÖØÖôó¶àÊýµÄ·þÎñÆ÷µÄ״̬±äÁ¿
flush-tables£»¹Ø±Õ´ò¿ªµÄËùÓеıí
flush-threads: ÖØÖÃÏ̳߳صģ¬»òÏ̻߳º´æ
flush-logs: ¶þ½øÖƺÍÖмÌÈÕÖ¾¹ö¶¯
flush-hosts: Ë¢ÐÂÖ÷»ú£¬Çå³ýÖ÷»úµÄÄÚ²¿ÐÅÏ¢
kill £ºÉ±ËÀÒ»¸öÏ̵߳Ä
refresh: Ï൱ÓÚͬʱִÐÐflush-hostsºÍflush-logsͬʱִÐÐ
shutdown: ¹Ø±Õmysql·þÎñÆ÷½ø³Ì£¬¿ÉÍ£Ö¹mysql·þÎñÆ÷
version: ·þÎñÆ÷°æ±¾¼°µ±Ç°×´Ì¬ÐÅÏ¢
start-slave:Æô¶¯¸´ÖÆ£¬Æô¶¯´Ó·þÎñÆ÷¸´ÖÆÏß³Ì(¸´ÖƵÄḬ̈߳üÀ¨Á½¸öSQL threadºÍIO thread)
stop-slave:¹Ø±Õ¸´ÖÆ£¬Í£Ö¹¸´ÖÆỊ̈߳»
Èý¡¢´æ´¢ÒýÇæ
1£©´æ´¢ÒýÇæµÄ¸ÅÄ´æ´¢ÒýÇæ´ÓijÖÖÒâÒåÉϽ²½Ð±íÀàÐÍ£¬Ã¿Ò»¸ö±í¶¼¿ÉÒÔ×ÔÓɵĶÀÁ¢µÄÑ¡ÔñÒ»ÖÖ´æ´¢ÒýÇ棬´æ´¢ÒýÇæ²»ÊÇÊý¾Ý¿âÖ®¼äµÄ¸ÅÄî¶øÊDZíÖ®¼äµÄµÄ¸ÅÄͬһ¸öÊý¾Ý¿âÖеĶàÕűíÍêÈ«¿ÉÒÔʹÓò»Í¬µÄ´æ´¢ÒýÇ棬Ҳ¾ÍÒâζ×ŵײãµÄ´æ´¢»úÖÆÊǸ÷²»ÏàͬµÄ¡£
2£©´æ´¢ÒýÇæÖ÷Òª°üÀ¨MyISAMºÍInnoDB
MyISAM:(´æ´¢ÒýÇ棬Ҳ±»³ÆΪ±íÀàÐÍ)ÎÞÊÂÎñ£¬±íËø£»Ã¿¸ö±íÈý¸öÎļþ·Ö±ðÈçÏ£º
.form:±í½á¹¹¶¨ÒåÎļþ
.MYD:±íÊý¾Ý¶¨ÒåÎļþ
.MYI:±íË÷Òý¶¨ÒåÎļþ
InnoDB£ºÊÂÎñ£¬ÐÐËø£»ËùÓÐ±í¹²ÏíÒ»¸ö±í¿Õ¼äÎļþ(½¨Ò飺ÿ±íÒ»¸ö¶ÀÁ¢µÄ±í¿Õ¼äÎļþ£»)
.form: ±í½á¹¹
.ibd: ±í¿Õ¼ä£¨ÀïÃæ´æ´¢Á˱íÊý¾ÝºÍ±íË÷Òý£©
3£©show engines:¿ÉÒÔÏÔʾµ±Ç°·þÎñÆ÷ËùÖ§³ÖµÄËùÓÐÒýÇ棬
show table status [like ...] ÏÔʾһÕűíµÄ״̬ÐÅÏ¢
eg£ºshow table status like 'use'; ÏÔʾuse±íµÄ״̬ÐÅÏ¢
eg£ºshow table status like 'use'\G ²é¿´use±íµÄÊôÐÔÐÅÏ¢£¬²¢ÊúÅÅÏÔʾ
ËÄ¡¢mysqlµÄÊý¾ÝÀàÐÍ(MySqL data types )Ïà¹ØµÄ֪ʶµã
1¡¢mysqlµÄÊý¾ÝÀàÐÍ£º
1£©ÊýÖµÐÍ
¾«È·ÊýÖµ
int
decimal Ê®½øÖÆ
½üËÆÊýÖµ
float
double
real ʵÊý
2£©×Ö·ûÐÍ
¶¨³¤£ºchar(#)¡¢binary
±ä³¤£ºvarchar(#)¡¢varbinary
text,blob£¨´ó×Ö·û£¬text²»Çø·Ö´óСд£¬blobÇø·Ö´óСд£©
enum£¬set £¨ÄÚÖÃÀàÐÍ£©
3£©ÈÕÆÚʱ¼äÐÍ
data£¬time£¬datatime£¬timestamp
2¡¢Êý¾ÝÀàÐ͵ÄÒâÒ壺
1£©´æÈëµÄÖµÀàÐÍ
2£©Õ¼¾ÝµÄ´æ´¢¿Õ¼ä
3£©¶¨³¤ºÍ±ä³¤
4£©ÈçºÎ±È½Ï¼°ÅÅÐò
5£©ÊÇ·ñÄܹ»´´½¨Ë÷Òý
2¡¢ÊýÖµÀàÐÍ£º
int ÕûÐÍ
tinyint £¨Î¢ÕûÐÍ£© ÓзûºÅʱÊÇ0-255£¬ÎÞ·ûºÅʱÊÇ-128-127
smallint £¨Ð¡ÕûÐÍ£©
mediumint£¨ÖÐÕûÐÍ£©
int £¨ÕûÐÍ£©
bigint £¨´óÕûÐÍ£©
decimal£º¶¨µãÊýÖµ
float£ºµ¥¾«¶È¸¡µãÖµ 4bytes
double£ºË«¾«¶È¸¡µãÖµ 8bytes
bit£º°´Î»´æ´¢
3¡¢×Ö·û´®Êý¾ÝÀàÐÍ£º
char ×Ö·ûÐÍ×î¶àÖ»ÄÜ´æ´¢255¸ö×Ö·û ¶¨³¤
varchar ×î´óÖ»ÄÜ´æ´¢65535¸ö×Ö·û ±ä³¤£¨²»Çø·Ö×Ö·û´óСд£©
binary ¶¨³¤
varbinary ±ä³¤ (Çø·Ö×Ö·û´óСд)
tinyblob ΢ÐÍblob 255×Ö½Ú£¨¶þ½øÖƵĴó¶ÔÏó£¬ËüÃÇÊÇ×Ö½Ú×Ö·û´®Çø·Ö×Ö·û´óСд£©
blob ±ê×¼blob 64kb
mediumblob Öм¶blob 16Mb
longblob ³¤blob 4Gb
tinytext ΢ÐÍtext£¨²»Çø·Ö×Ö·û´óСд£©×î¶àÖ»ÄÜ´æ´¢255¸ö×Ö·û
text ±ê×¼text ×î¶àÖ»ÄÜ´æ´¢65535¸ö×Ö·û
mediumtext Öм¶text ×î¶àÖ»ÄÜ´æ´¢16777215¸ö×Ö·û
longtext ³¤text ×î¶àÖ»ÄÜ´æ´¢4294967235¸ö×Ö·û
enum ö¾ÙÐÍ ×î¶àÖ»ÄÜ´æ´¢65535
set ¼¯ºÏ 1-64
4¡¢ÈÕÆÚʱ¼äÐÍ£º
data     3 bytes
time     3 bytes
datatime     8 bytes
timestamp    4 bytes
year       1 bytes
5¡¢×Ö·û´®ÀàÐ͵ÄÐÞÊÎÊֶΣº
not null ²»ÔÊÐíΪ¿Õ
null ¿ÉÒÔΪ¿Õ
default Ö¸¶¨Ä¬ÈÏÖµ
binary ²»Çø·Ö×Ö·û´óС
show characher sets; ÏÔʾµ±Ç°×Ö·ûÐÍÊý¾Ý¿â×Ö·û¼¯
show collation; ÏÔʾµ±Ç°×Ö·ûÐÍÊý¾Ý¿âËùÖ§³ÖµÄÅÅÐò¹æÔò
show global varables like '%char%';
select database(); ±íʾִÐÐÒ»¸öº¯Êý(Ò»¸öÄÚÖõĺ¯Êý)
select last_insert_id();
auto_increment ×Ô¶¯Ôö³¤(ÕûÐÍ ·Ç¿Õ ÎÞ·ûºÅ)
eg: create table test(id int unsigned auto_increment not null primary key,Name char(20))
unsigned ÎÞ·ûºÅ
cerate table test(id int unsigned
²¹³ä£º
1¡¢mysql»¹ÓÐÒ»¸öÌصã¾ÍÊÇÖ§³ÖÃû³Æ²¹È«
Ãû³Æ²¹È«£º´ò¿ªÊý¾Ý¿âʱ±ØÐ뽫ÿһ¸öÊý¾Ý¿âµÄÃû³ÆºÍÿһ¸ö±íµÄÃû³Æÿһ¸ö×ֶεÄÃû³Æ¶¼ÒªÔØÈëÄÚ´æµ±ÖÐÀ´£¬Õâ¿ÉÄܻᵼÖÂÎÒÃÇÔÚÁ¬½Óµ½mysqlÉÏʱÓдóÁ¿µÄÑÓ³Ù£¬ÒòΪËü±ØÐèÒª±éÀúÿһ¸öÊý¾Ý¿âµÄÿһ¸ö±íµÄ¶¨Ò壬ÕâÊǷdz£ÂýµÄ¡£
--html £º¿ÉÒÔÈÃÊý¾Ý¿âÏÔʾΪhtml¸ñʽ
2¡¢¼üºÍË÷Òý
1£©¼üÒ²±»³ÆΪÊÇÔ¼Êø£¬¿ÉÓÃ×÷Ë÷Òý£¬ÊôÓÚÌØÊâË÷Òý£¨ÓÐÌØÊâÏÞ¶¨£¬±ÈÈçÊýÖµ²»Ïàͬ£©£ºËüÃÇ´æ´¢ÏÂÀ´¶¼ÊÇB+TreeµÄ½á¹¹¡£
2£©Ë÷ÒýÊǹØϵÊý¾Ý¿âµÄÄÚ²¿ÊµÏÖ¼¼Êõ£¬ÊôÓÚÄÚģʽµÄ·¶³ë £¬create indexÓï¾ä¶¨ÒåË÷Òýʱ£¬¿ÉÒÔ¶¨ÒåË÷ÒýÊÇΨһË÷Òý¡¢·ÇΨһË÷Òý»ò¾Û´ØË÷Òý
3£©Ë÷ÒýÓÐÁ½ÖÖÀàÐÍ£º
b+tree£º¾ßÓж¯Ì¬Æ½ºâµÄÓŵã
hash£º¾ßÓвéÕÒËٶȿìµÄÌصã
¼¸ºõËùÓеÄ×ֶζ¼Ö§³Öb+treeË÷Òý£¬µ«²¢·ÇËùÓеÄ×ֶζ¼Ö§³ÖhashË÷Òý¡£
4£©´´½¨Ë÷ÒýµÄÔ­Ôò£º
£¨1£©¶Ô½Ï´óµÄ±í²Å½¨Á¢Ë÷Òý£¨Ô¼5000Ìõ¼Ç¼ÒÔÉÏ£©£¬²¢¼ìË÷µÄÊý¾ÝÉÙÓÚ×ÜÐÐÊýµÄ2%µ½4%
£¨2£©Ò»¸ö±í¿É½¨Á¢ÈÎÒâ¶à¸öË÷Òý£¬µ«²»ÄÜÌ«¶à£¬»áÔö¼Óϵͳά»¤µÄ¿ªÏú£¬Ë÷Òý½¨Á¢ºóÓÉϵͳά»¤¡£
£¨3£©¾­³£³öÏÖÔÚwhere×Ó¾ä»òÁª½ÓÌõ¼þÖеÄÁÐÉÏ×÷ΪË÷Òý¹Ø¼ü×Ö
£¨4£©Ë÷Òý¹Ø¼ü×Ö¿ÉÒÔÊÇÒ»¸öÁУ¬Ò²¿ÉÒÔÊǶà¸öÁÐ×éºÏ³ÉµÄ¸´ºÏË÷Òý¡£Èç¹ûÊǸ´ºÏË÷Òý£¬Ôò²éѯÌõ¼þÖк¬ÓÐÖ÷¹Ø¼ü×Öʱ£¬ÏµÍ³Ê¹ÓÃË÷Òý£¬¼Ó¿ì²éѯËٶȡ£Èç¹û²éѯÌõ¼þÖÐÖ»º¬Óдιؼü×Ö£¬Ôòϵͳ²»Ê¹ÓÃË÷Òý¡£
£¨5£©Ë÷ÒýÐÅÏ¢»á´æ´¢µ½Êý¾Ý×ÖµäÖС£
http://www.magedu.com/
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 6 ÌõÆÀÂÛ

  1. ×·ÃÎÈË456 ÓÚ 2016-06-01 09:31:10·¢±í:

    ×Ô¼ºµÄ֪ʶÃæÀ©Õ¹ÁË£¬Ñ§µ½Á˲»ÉٵĶ«Î÷

  2. brettwang ÓÚ 2016-04-14 06:59:23·¢±í:

    ºÃÈ«Ã棬ѧϰÁË 1# СС295813080

  3. brettwang ÓÚ 2016-04-04 11:18:53·¢±í:

    1# СС295813080



    ºÃÈ«Ã棬ѧϰÁË

  4. brettwang ÓÚ 2016-03-12 09:29:32·¢±í:

    ֪ʶÍØÕ¹ÁË

  5. it´óÏÉ ÓÚ 2015-07-09 13:38:19·¢±í:

    ֪ʶÍØÕ¹ÁË

  6. canxiang ÓÚ 2015-04-30 15:28:18·¢±í:

    ºÃÈ«Ã棬ѧϰÁË{:3_117:}