mysql記事本

涓€銆佽繛鎺YSQL銆?

聽聽聽聽 鏍煎紡锛?mysql -h涓繪満鍦闆潃 -u鐢ㄦ埛鍚?锛峱鐢ㄦ埛瀵嗙爜

1銆佽繛鎺ュ埌鏈満涓婄殑MYSQL銆?

聽聽聽聽 棣栧厛鎵撳紑DOS绐楀彛锛岀劧鍚庤繘鍏ョ洰褰昺ysql/bin锛屽啀閿叆鍛戒護mysql -u root -p锛屽洖杞﹀悗鎻愮ず浣犺緭瀵嗙爜.娉ㄦ剰鐢ㄦ埛鍚嶅墠鍙互鏈夌┖鏍間篃鍙互娌℃湁绌烘牸锛屼絾鏄瘑鐮佸墠蹇呴』娌℃湁绌烘牸锛屽惁鍒欒浣犻噸鏂拌緭鍏ュ瘑鐮?

濡傛灉鍒氬畨瑁呭ソMYSQL锛岃秴绾х敤鎴穜oot鏄病鏈夊瘑鐮佺殑锛屾晠鐩存帴鍥炶濺鍗沖彲杩涘叆鍒癕YSQL涓簡锛孧YSQL鐨勬彁绀虹鏄細 mysql>

聽

2銆佽繛鎺ュ埌杩滅▼涓繪満涓婄殑MYSQL銆傚亣璁捐繙绋嬩富鏈虹殑IP涓猴細110.110.110.110锛岀敤鎴峰悕涓簉oot,瀵嗙爜涓篴bcd123銆傚垯閿叆浠ヤ笅鍛戒護锛?

聽聽聽聽 mysql -h110.110.110.110 -u root -p 123;锛堟敞:u涓巖oot涔嬮棿鍙互涓嶇敤鍔犵┖鏍鹼紝鍏跺畠涔熶竴鏍鳳級

3銆侀€€鍑篗YSQL鍛戒護锛?exit 锛堝洖杞︼級

浜屻€佷慨鏀瑰瘑鐮併€?

聽聽聽聽 鏍煎紡锛歮ysqladmin -u鐢ㄦ埛鍚?-p鏃у瘑鐮?password 鏂闆瘑鐮?

1銆佺粰root鍔犱釜瀵嗙爜ab12銆傞鍏堝湪DOS涓嬭繘鍏ョ洰褰昺ysql/bin锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠?

聽聽聽聽 mysqladmin -u root -password ab12

聽娉細鍥犱負寮€濮嬫椂root娌℃湁瀵嗙爜锛屾墍浠?p鏃у瘑鐮佷竴椤瑰氨鍙互鐪佺暐浜嗐€?

2銆佸啀灏唕oot鐨勫瘑鐮佹敼涓篸jg345銆?

聽聽聽聽 mysqladmin -u root -p ab12 password djg345

涓夈€佸鍔犳柊鐢ㄦ埛銆?

锛堟敞鎰忥細鍜屼笂闈笉鍚岋紝涓嬮潰鐨勫洜涓烘槸MYSQL鐜涓殑鍛戒護锛屾墍浠ュ悗闈㈤兘甯︿竴涓垎鍙蜂綔涓哄懡浠ょ粨鏉熺锛?

鏍煎紡锛歡rant select on 鏁版嵁搴?* to 鐢ㄦ埛鍚岪鐧誨綍涓繪満 identified by 鈥滃瘑鐮佲€?

1銆佸鍔犱竴涓敤鎴穞est1瀵嗙爜涓篴bc锛岃浠栧彲浠ュ湪浠諱綍涓繪満涓婄櫥褰曪紝骞跺鎵€鏈夋暟鎹簱鏈夋煡璇€佹彃鍏ャ€佷慨鏀廣€佸垹闄ょ殑鏉冮檺銆傞鍏堢敤root鐢ㄦ埛杩炲叆MYSQL锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠わ細

grant select,insert,update,delete on *.* to [email聽protected]鈥?鈥?Identified by 鈥渁bc鈥?

浣嗗鍔犵殑鐢ㄦ埛鏄崄鍒嗗嵄闄╃殑锛屼綘鎯沖鏌愪釜浜虹煡閬搕est1鐨勫瘑鐮侊紝閭ｄ箞浠栧氨鍙互鍦╥nternet涓婄殑浠諱綍涓€鍙扮數鑴戜笂鐧誨綍浣犵殑mysql鏁版嵁搴撳苟瀵逛綘鐨勬暟鎹彲浠ヤ負鎵€娆蹭負浜嗭紝瑙ｅ喅鍔炴硶瑙?銆?

2銆佸鍔犱竴涓敤鎴穞est2瀵嗙爜涓篴bc,璁╀粬鍙彲浠ュ湪localhost涓婄櫥褰曪紝骞跺彲浠ュ鏁版嵁搴搈ydb杩涜鏌ヨ銆佹彃鍏ャ€佷慨鏀廣€佸垹闄ょ殑鎿嶄綔锛坙ocalhost鎸囨湰鍦頒富鏈猴紝鍗矼YSQL鏁版嵁搴撴墍鍦ㄧ殑閭ｅ彴涓繪満锛夛紝

杩欐牱鐢ㄦ埛鍗充嬌鐢ㄧ煡閬搕est2鐨勫瘑鐮侊紝浠栦篃鏃犳硶浠巌nternet涓婄洿鎺ヨ闂暟鎹簱锛屽彧鑳介€氳繃MYSQL涓繪満涓婄殑web椤墊潵璁塊棶浜嗐€?

grant select,insert,update,delete on mydb.* to [email聽protected] identified by 鈥渁bc鈥?

濡傛灉浣犱笉鎯硉est2鏈夊瘑鐮侊紝鍙互鍐嶆墦涓€涓懡浠ゅ皢瀵嗙爜娑堟帀銆?

grant select,insert,update,delete on mydb.* to [email聽protected] identified by 鈥溾€?

涓嬬瘒鎴戞槸MYSQL涓湁鍏蟲暟鎹簱鏂歸潰鐨勬搷浣溿€傛敞鎰忥細浣犲繀椤婚鍏堢櫥褰曞埌MYSQL涓紝浠ヤ笅鎿嶄綔閮芥槸鍦∕YSQL鐨勬彁绀虹涓嬭繘琛岀殑锛岃€屼笖姣忎釜鍛戒護浠ュ垎鍙風粨鏉熴€?

涓€銆佹搷浣滄妧宸?

1銆佸鏋滀綘鎵撳懡浠ゆ椂锛屽洖杞﹀悗鍙戠幇蹇樿鍔犲垎鍙鳳紝浣犳棤椤婚噸鎵撲竴閬嶅懡浠わ紝鍙鎵撲釜鍒嗗彿鍥炶濺灏卞彲浠ヤ簡銆?

涔熷氨鏄浣犲彲浠ユ妸涓€涓畬鏁寸殑鍛戒護鍒嗘垚鍑犺鏉ユ墦锛屽畬鍚庣敤鍒嗗彿浣滅粨鏉熸爣蹇楀氨OK銆?

2銆佷綘鍙互浣跨敤鍏夋爣涓婁笅閿皟鍑轟互鍓嶇殑鍛戒護銆?

浜屻€佹樉绀哄懡浠?

1銆佹樉绀哄綋鍓嶆暟鎹簱鏈嶅姟鍣ㄤ腑鐨勬暟鎹簱鍒楄〃锛?

mysql> SHOW DATABASES;

娉ㄦ剰锛歮ysql搴撻噷闈㈡湁MYSQL鐨勭郴缁熶俊鎭紝鎴戜滑鏀瑰瘑鐮佸拰鏂闆鐢ㄦ埛锛屽疄闄呬笂灏辨槸鐢ㄨ繖涓簱杩涜鎿嶄綔銆?

2銆佹樉绀烘暟鎹簱涓殑鏁版嵁琛細

mysql> USE 搴撳悕锛?

mysql> SHOW TABLES;

3銆佹樉绀烘暟鎹〃鐨勭粨鏋勶細

mysql> DESCRIBE 琛ㄥ悕;

4銆佸緩绔嬫暟鎹簱锛?

mysql> CREATE DATABASE 搴撳悕;

5銆佸緩绔嬫暟鎹〃锛?

mysql> USE 搴撳悕;

mysql> CREATE TABLE 琛ㄥ悕 (瀛楁鍚?VARCHAR(20), 瀛楁鍚?CHAR(1));

6銆佸垹闄ゆ暟鎹簱锛?

mysql> DROP DATABASE 搴撳悕;

7銆佸垹闄ゆ暟鎹〃锛?

mysql> DROP TABLE 琛ㄥ悕锛?

8銆佸皢琛ㄤ腑璁闆綍娓呯┖锛?

mysql> DELETE FROM 琛ㄥ悕;

9銆佹樉绀鴻〃涓殑璁闆綍锛?

mysql> SELECT * FROM 琛ㄥ悕;

10銆佸線琛ㄤ腑鎻掑叆璁闆綍锛?

mysql> INSERT INTO 琛ㄥ悕 VALUES (鈥漢yq鈥?鈥滿鈥?;

11銆佹洿鏂拌〃涓暟鎹細

mysql-> UPDATE 琛ㄥ悕 SET 瀛楁鍚?=鈥檃',瀛楁鍚?=鈥檅鈥?WHERE 瀛楁鍚?=鈥檆';

12銆佺敤鏂囨湰鏂瑰紡灏嗘暟鎹鍏ユ暟鎹〃涓細

mysql> LOAD DATA LOCAL INFILE 鈥淒:/mysql.txt鈥?INTO TABLE 琛ㄥ悕;

13銆佸鍏?sql鏂囦歡鍛戒護锛?

mysql> USE 鏁版嵁搴撳悕;

mysql> SOURCE d:/mysql.sql;

14銆佸懡浠よ淇敼root瀵嗙爜锛?

mysql> UPDATE mysql.user SET password=PASSWORD(鈥欐柊瀵嗙爜鈥? WHERE User=鈥檙oot鈥?

mysql> FLUSH PRIVILEGES;

15銆佹樉绀簎se鐨勬暟鎹簱鍚嶏細

mysql> SELECT DATABASE();

16銆佹樉绀哄綋鍓嶇殑user锛?

mysql> SELECT USER();

涓夈€佷竴涓緩搴撳拰寤鴻〃浠ュ強鎻掑叆鏁版嵁鐨勫疄渚?

drop database if exists school; //濡傛灉瀛樺湪SCHOOL鍒欏垹闄?

create database school; //寤虹珛搴揝CHOOL

use school; //鎵撳紑搴揝CHOOL

create table teacher //寤虹珛琛═EACHER

(

id int(3) auto_increment not null primary key,

name char(10) not null,

address varchar(50) default 鈥樻繁鍦斥€?

year date

); //寤鴻〃缁撴潫

//浠ヤ笅涓烘彃鍏ュ瓧娈?

insert into teacher values(鈥?鈥檃llen鈥?'澶ц繛涓€涓€?'1976-10-10鈥?;

insert into teacher values(鈥?鈥檍ack鈥?'澶ц繛浜屼腑鈥?'1975-12-23鈥?;

濡傛灉浣犲湪mysql鎻愮ず绗﹂敭鍏ヤ笂闈㈢殑鍛戒護涔熷彲浠ワ紝浣嗕笉鏂逛究璋冭瘯銆?

锛?锛変綘鍙互灏嗕互涓婂懡浠ゅ師鏍峰啓鍏ヤ竴涓枃鏈枃浠朵腑锛屽亣璁句負school.sql锛岀劧鍚庡鍒跺埌c://涓嬶紝骞跺湪DOS鐘舵€佽繘鍏ョ洰褰?/mysql//bin锛岀劧鍚庨敭鍏ヤ互涓嬪懡浠わ細

mysql -uroot -p瀵嗙爜 < c://school.sql

濡傛灉鎴愬姛锛岀┖鍑轟竴琛屾棤浠諱綍鏄劇ず锛涘鏈夐敊璇紝浼氭湁鎻愮ず銆傦紙浠ヤ笂鍛戒護宸茬粡璋冭瘯锛屼綘鍙灏?/鐨勬敞閲婂幓鎺夊嵆鍙嬌鐢級銆?

锛?锛夋垨鑰呰繘鍏ュ懡浠よ鍚庝嬌鐢?mysql> source c://school.sql; 涔熷彲浠ュ皢school.sql鏂囦歡瀵煎叆鏁版嵁搴撲腑銆?

鍥涖€佸皢鏂囨湰鏁版嵁杞埌鏁版嵁搴撲腑

1銆佹枃鏈暟鎹簲绗﹀悎鐨勬牸寮忥細瀛楁鏁版嵁涔嬮棿鐢╰ab閿殧寮€锛宯ull鍊肩敤//n鏉ヤ唬鏇?渚嬶細

3 rose 澶ц繛浜屼腑 1976-10-10

4 mike 澶ц繛涓€涓?1975-12-23

鍋囪浣犳妸杩欎袱缁勬暟鎹瓨涓簊chool.txt鏂囦歡锛屾斁鍦╟鐩樻牴鐩綍涓嬨€?

2銆佹暟鎹紶鍏ュ懡浠?load data local infile 鈥渃://school.txt鈥?into table 琛ㄥ悕;

娉ㄦ剰锛氫綘鏈€濂藉皢鏂囦歡澶嶅埗鍒?/mysql//bin鐩綍涓嬶紝骞朵笖瑕佸厛鐢╱se鍛戒護鎵撹〃鎵€鍦ㄧ殑搴撱€?

浜斻€佸浠芥暟鎹簱锛氾紙鍛戒護鍦―OS鐨?/mysql//bin鐩綍涓嬫墽琛岋級

1.瀵煎嚭鏁翠釜鏁版嵁搴?

瀵煎嚭鏂囦歡榛樿鏄瓨鍦╩ysql/bin鐩綍涓?

mysqldump -u 鐢ㄦ埛鍚?-p 鏁版嵁搴撳悕 > 瀵煎嚭鐨勬枃浠跺悕

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2.瀵煎嚭涓€涓〃

mysqldump -u 鐢ㄦ埛鍚?-p 鏁版嵁搴撳悕 琛ㄥ悕> 瀵煎嚭鐨勬枃浠跺悕

mysqldump -u user_name -p database_name table_name > outfile_name.sql

3.瀵煎嚭涓€涓暟鎹簱缁撴瀯

mysqldump -u user_name -p -d 鈥揳dd-drop-table database_name > outfile_name.sql

-d 娌℃湁鏁版嵁 鈥揳dd-drop-table 鍦ㄦ瘡涓猚reate璇彞涔嬪墠澧炲姞涓€涓猟rop table

4.甯﹁瑷€鍙傛暟瀵煎嚭

mysqldump -uroot -p 鈥揹efault-character-set=latin1 鈥搒et-charset=gbk 鈥搒kip-opt database_name > outfile_name.sql

聽

濡備綍鏇存敼mysql鏁版嵁搴撶殑榛樿缂栫爜鏂瑰紡2009-06-05 15:22淇敼my.ini鏂囦歡

鍔犱笂

default-character-set=gb2312

璁懼畾鏁版嵁搴撳瓧绗﹂泦

alter database da_name default character set 'charset'

1)璁劇疆鏁版嵁搴撶紪鐮?/etc/my.cnf

[mysqld]

default-character-set=gbk

...

[client]

default-character-set=gbk

锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛嶏紞锛?

2)鎸夊瓧绗﹂泦瀵煎嚭

$mysqldump -u root -p dbname --default-character-set=gbk > a.sql;

3)鏌ョ湅SQL鏂囦歡鐨勭紪鐮? [[email聽protected] gethtml]# file a.sql

聽聽 a.sql: UTF-8 Unicode ...

[[email聽protected] gethtml]# iconv -f utf-8 -t gbk a.sql > a2.sql

[[email聽protected] gethtml]# file a2.sql

聽聽 a2.sql: Non-ISO extended-ASCII English text 杩欐椂宸茬粡鏄痝bk鐨勭紪鐮佷簡

3)瀵煎叆

鏌ョ湅鏁版嵁搴撴湇鍔″拰瀹㈡埛绔瓧绗﹂泦

mysql> status;

Server characterset:聽聽聽 gbk

Db聽聽聽聽 characterset:聽聽聽聽聽 gbk

Client characterset:聽聽聽聽聽 latin1

Conn. characterset:聽聽聽聽 latin1

mysql> set names 'gbk';聽聽 //杩欐牱

mysql> status;

Server characterset:聽聽 gbk

Db聽聽聽聽 characterset:聽聽聽聽 gbk

Client characterset:聽聽聽聽 gbk

Conn. characterset:聽聽聽 gbk

杩欐椂鎵嶈兘瀵兼暟鎹? mysql> source a.sql;

----------------------------------------------------------------------------------

鍗曠嫭璁劇疆鏌愪釜鏁版嵁搴擄細

alter database testdb character set utf8;

鏌ョ湅mysql鏀寔鐨勭紪鐮侊細

show character set;

鏌ョ湅绯葷粺鐨勫瓧绗﹂泦鍜屾帓搴忔柟寮忕殑璁懼畾鍙互閫氳繃涓嬮潰鐨勪袱鏉″懡浠わ細

mysql> SHOW VARIABLES LIKE ''character_set_%'';

LINUX 濡備笅:

1)瀵煎嚭鏁版嵁

[[email聽protected] ~]$ mysqldump -u root -p dbname --default-character-set=gbk > base_user.sql;

2)鏌ョ湅瀵煎嚭鐨剆ql鏂囦歡鐨勭紪鐮? [[email聽protected] ~]$ file base_user.sql

銆€ base_user.sql: UTF-8 Unicode text, with very long lines

3)杞垚瑕佺敤鐨勭紪鐮? [[email聽protected] ~]$ iconv -f utf-8 -t gbk base_user.sql >base_user_gbk.sql

4)杩炴帴鏁版嵁搴撳苟鏌ョ湅褰撳墠搴撶殑缂栫爜

[[email聽protected] ~]$ mysql -uroot

銆€mysql> use testdb;

mysql> status;

聽聽聽 Server characterset:聽聽聽 latin1

聽聽聽 Db聽聽聽聽 characterset:聽聽聽 gbk

聽聽聽 Client characterset:聽聽聽 latin1

聽聽聽 Conn. characterset:聽聽聽 latin1

5)璁劇疆鎴愰渶瑕佺殑缂栫爜

mysql>set names 'gbk';

6)鏌ョ湅鐜闆湪鐨勭紪鐮? mysql> status;

聽聽聽 Server characterset:聽聽聽 latin1

聽聽聽 Db聽聽聽聽 characterset:聽聽聽 gbk

聽聽聽 Client characterset:聽聽聽 gbk

聽聽聽 Conn. characterset:聽聽聽 gbk

7)瀵煎叆鎴戜滑杞崲鎴恎bk鍚庣殑鏂囦歡

mysql> source base_user_gbk.sql;