Centos下MySQL使用總結

標籤：

一、MySQL安裝

   

 Centos下安裝mysql 請點開:http://www.centoscn.com/CentosServer/sql/2013/0817/1285.html

 

二、MySQL的幾個重要目錄

 

    MySQL安裝完成後不象SQL Server預設安裝在一個目錄，它的資料庫檔案、設定檔和命令檔案分別在不同的目錄，瞭解這些目錄非常重要，尤其對於Linux的初學者，因為 Linux本身的目錄結構就比較複雜，如果搞不清楚MySQL的安裝目錄那就無從談起深入學習。　　

下面就介紹一下這幾個目錄。　　

2.1、資料庫目錄　　

/var/lib/mysql/ 　　

2.2、設定檔　　

/usr/share/mysql（mysql.server命令及設定檔）　　

2.3、相關命令　　

/usr/bin(mysqladmin mysqldump等命令) 　　

2.4、啟動指令碼　　

/etc/rc.d/init.d/（啟動指令檔mysql的目錄）

 

三、登入MySQL  3.1、串連本機MySQL

    例1：串連到本機上的MYSQL。

        首先在開啟DOS視窗，然後進入目錄 mysqlbin，再鍵入命令mysql -uroot -p，斷行符號後提示你輸密碼，如果剛安裝好MYSQL，超級使用者root是沒有密碼的，故直接斷行符號即可進入到MYSQL中了，MYSQL的提示符 是：mysql>。

3.2、串連遠程MySQL

    例2：串連到遠程主機上的MYSQL。假設遠程主機的IP為：110.110.110.110，使用者名稱為root,密碼為abcd123。則鍵入以下命令：

        mysql -h110.110.110.110 -uroot -pabcd123

        （注:u與root可以不用加空格，其它也一樣）

3.3、退出MYSQL

命令： exit （斷行符號）。

四、修改登入密碼　　

   

    MySQL預設沒有密碼，安裝完畢增加密碼的重要性是不言而喻的。

格式：mysqladmin -u使用者名稱 -p舊密碼 password 新密碼

4.1、首次改密碼

例1：給root加個密碼ab12。首先在DOS下進入目錄mysqlbin，然後鍵入以下命令：

        mysqladmin -uroot -password ab12

        註：因為開始時root沒有密碼，所以-p舊密碼一項就可以省略了。

4.2、再次改密碼

例2：再將root的密碼改為djg345。

        mysqladmin -uroot -pab12 password djg345

五、增加使用者

    

    （注意：和上面不同，下面的因為是MySQL環境中的命令，所以後面都帶一個分號作為命令結束符）

        格式：grant select on 資料庫.* to 使用者名稱@登入主機 identified by \"密碼\"

        例1、增加一個使用者test1密碼為abc，讓他可以在任何主機上登入，並對所有資料庫有查詢、插入、修改、刪除的許可權。首先用以root使用者連入MySQL，然後鍵入以下命令：

        grant select,insert,update,
        delete on *.* to [email protected]\"%\" Identified by \"abc\";

        但例1增加的使用者是十分危險的，你想如某個人知道test1的密碼，那麼他就可以在internet上的任何一台電腦上登入你的MySQL資料庫並對你的資料可以為所欲為了，解決辦法見例2。

        例2、增加一個使用者test2密碼為abc,讓他只可以在localhost上登入，並可以對資料庫mydb進行查詢、插入、修改、刪除的操作 （localhost指本地主機，即MySQL資料庫所在的那台主機），這樣使用者即使用知道test2的密碼，他也無法從internet上直接存取資料 庫，只能通過MySQL主機上的web頁來訪問。

        grant select,insert,update,
        delete on mydb.* to [email protected] identified by \"abc\";

        如果你不想test2有密碼，可以再打一個命令將密碼消掉。

        grant select,insert,update,delete on mydb
        .* to [email protected] identified by \"\";　

    用新增的使用者如果登入不了MySQL，

在登入時用如下命令：　　mysql -u user_1 -p　-h 192.168.113.50　（-h後跟的是要登入主機的ip地址）　

 

六、啟動與停止　　

 

6.1、啟動　　

MySQL安裝完成後開機檔案mysql在/etc/init.d目錄下，在需要啟動時運行下面命令即可。　　

[[email protected] init.d]# /etc/init.d/mysql start 　　

6.2、停止　　

/usr/bin/mysqladmin -u root -p shutdown 　　

6.3、自動啟動　　6.3.1、察看mysql是否在自動啟動列表中　　

[[email protected] local]#　/sbin/chkconfig –list 　　

6.3.2、把MySQL添加到你系統的啟動服務組裡面去　　

[[email protected] local]#　/sbin/chkconfig　– add　mysql 　　

6.3.3、把MySQL從啟動服務組裡面刪除。　　

[[email protected] local]#　/sbin/chkconfig　– del　mysql

 

七、更改MySQL目錄　　

 

    MySQL預設的資料檔案儲存目錄為/var/lib/mysql。

假如要把目錄移到/home/data下需要進行下面幾步：　　

7.1、home目錄下建立data目錄　　

cd /home 　　mkdir data 　　

7.2、把MySQL服務進程停掉： 　　

mysqladmin -u root -p shutdown 　　

7.3、把/var/lib/mysql整個目錄移到/home/data 　　

mv /var/lib/mysql　/home/data/ 　　

這樣就把MySQL的資料檔案移動到了/home/data/mysql下　　

7.4、找到my.cnf設定檔　　

如果/etc/目錄下沒有my.cnf設定檔，請到/usr/share/mysql/下找到*.cnf檔案，拷貝其中一個到/etc/並改名為my.cnf)中。

命令如下：　　

[[email protected] mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf 　　

7.5、編輯MySQL的設定檔/etc/my.cnf 　　

為保證MySQL能夠正常工作，需要指明mysql.sock檔案的產生位置。

修改socket=/var/lib/mysql/mysql.sock一行中等號右邊的值為：/home/mysql/mysql.sock 。

操作如下：　　

vi　 my.cnf　　　

(用vi工具編輯my.cnf檔案，找到下列資料修改之) 　　

# The MySQL server 　　　 [mysqld] 　　　

port　　　= 3306 　　　

#socket　 = /var/lib/mysql/mysql.sock（原內容，為了更穩妥用“#”注釋此行）　　　

socket　 = /home/data/mysql/mysql.sock　　　（加上此行）　　

7.6、修改MySQL啟動指令碼/etc/rc.d/init.d/mysql 　　

最後，需要修改MySQL啟動指令碼/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等號右邊的路徑改成你現在的實際存放路徑：home/data/mysql。　　

[[email protected] etc]# vi　/etc/rc.d/init.d/mysql 　　

#datadir=/var/lib/mysql　　　　（注釋此行）　　

datadir=/home/data/mysql　　 （加上此行）　　

7.7、重新啟動MySQL服務　　

/etc/rc.d/init.d/mysql　start 　　

或用reboot命令重啟Linux 　　

如果工作正常移動就成功了，否則對照前面的7步再檢查一下。　　

 

八、MySQL的常用操作　　

 

注意：MySQL中每個命令後都要以分號；結尾。　

8.1、MySQL常用操作命令　8.1.1、顯示資料庫列表：

        show databases;

        剛開始時才兩個資料庫：mysql和test。MySQL庫很重要它裡面有MYSQL的系統資訊，我們改密碼和新增使用者，實際上就是用這個庫進行操作。

8.1.2、顯示庫中的資料表：

        use mysql； ／／開啟庫，學過FOXBASE的一定不會陌生吧

        show tables;

8.1.3、顯示資料表的結構：

        describe 表名;

8.1.4、建庫：

        create database 庫名;

8.1.5、建表：

        use 庫名；

        create table 表名 (欄位設定列表)；

8.1.6、刪庫和刪表:

        drop database 庫名;

        drop table 表名；

8.1.7、將表中記錄清空：

        delete from 表名;

8.1.8、顯示表中的記錄：

        select * from 表名;

8.1.9、增加記錄　　

例如：增加幾條相關紀錄。　　

mysql> insert into name values(‘‘,‘張三‘,‘男‘,‘1971-10-01‘); 　　

mysql> insert into name values(‘‘,‘白雲‘,‘女‘,‘1972-05-20‘); 　　

可用select命令來驗證結果。　　

mysql> select * from name; 　　

+----+------+------+------------+ 　　

| id | xm　 | xb　 | csny　　　 | 　　

+----+------+------+------------+ 　　

|　1 | 張三 | 男　 | 1971-10-01 | 　　

|　2 | 白雲 | 女　 | 1972-05-20 | 　　

+----+------+------+------------+ 　　

8.1.10、修改紀錄　　

例如：將張三的出生年月改為1971-01-10 　　

mysql> update name set csny=‘1971-01-10‘ where xm=‘張三‘; 　　

8.1.11、刪除紀錄　　

例如：刪除張三的紀錄。　　

mysql> delete from name where xm=‘張三‘; 　　

8.2、一個建庫和建表以及插入資料的執行個體

        drop database if exists school; //如果存在SCHOOL則刪除

        create database school; //建立庫SCHOOL

        use school; //開啟庫SCHOOL

        create table teacher //建立表TEACHER

        (

        id int(3) auto_increment not null primary key,

        name char(10) not null,

        address varchar(50) default ’深圳’,

        year date

        ); //建表結束

        //以下為插入欄位

        insert into teacher values(’’,’glchengang’,’深圳一中’,’1976-10-10’);

        insert into teacher values(’’,’jack’,’深圳一中’,’1975-12-23’);

        註：在建表中（1）將ID設為長度為3的數字欄位:int(3)並讓它每個記錄自動加一:auto_increment並不可為空:not null而且讓他成為主欄位primary key（2）將NAME設為長度為10的字元欄位（3）將ADDRESS設為長度50的字元欄位，而且預設值為深圳。varchar和char有什麼區別 呢，只有等以後的文章再說了。（4）將YEAR設為日期欄位。

        如果你在MySQL提示符鍵入上面的命令也可以，但不方便調試。你可以將以上命令原樣寫入一個文字檔中假設為school.sql，然後複製到c:\\下，並在DOS狀態進入目錄\\mysql\\bin，然後鍵入以下命令：

        mysql -uroot -p密碼 < c:\\school.sql

        如果成功，空出一行無任何顯示；如有錯誤，會有提示。（以上命令已經調試，你只要將//的注釋去掉即可使用）。

 

九 、修改資料庫結構

 

9.1、欄位操作9.1.1、增加欄位

alter table dbname add column <欄位名><欄位選項>

9.1.2、修改欄位

alter table dbname change <舊欄位名> <新欄位名><選項>

9.1.3、刪除欄位

alter table dbname drop column <欄位名>

 

十、資料匯出

 

    資料匯出主要有以下幾種方法：

　　使用select into outfile "filename"語句

　　使用mysqldump公用程式

10.1、使用select into outfile "filename"語句

可以在mysql的命令列下或在php程式中執行它。我下面以在mysql命令列下為 例。在php中使用時，將其改成相應的查詢進行處理即可。不過在使用這個命令時，要求使用者擁有file的許可權。如我們有一個庫為phptest，其中有一 個表為driver。現在要把driver卸成檔案。執行命令：

　　mysql> use phptest;

　　Database Changed

　　mysql> select * from driver into outfile "a.txt";

　　Query OK, 22 rows affected (0.05 sec)

上面就可以完成將表driver從資料庫中卸到a.txt檔案中。注意檔案名稱要加單引 號。那麼這個檔案在哪呢？在mysql目錄下有一個data目錄，它即是資料庫檔案所放的地方。每個庫在單獨佔一個子目錄，所以phptest的目錄為 c:\mysql\data\phptest(注意：我的mysql安裝在c:\mysql下)。好，現在我們進去，a.txt就是它。開啟這個檔案，可 能是：

　　1 Mika Hakinnen 1

　　2 David Coulthard 1

　　3 Michael Schumacher 2

　　4 Rubens Barrichello 2

　　...

可能還有很多記錄。每個欄位之間是用定位字元分開的(\t)。那麼我們可以修改輸出檔案 名的目錄，以便放在指定的位置。如"a.txt"可以改成"./a.txt"或"/a.txt"。其中"./a.txt"放在c:\mysql\data 目錄下了，而"/a.txt"檔案則放在c:\目錄下了。所以select命令認為的目前的目錄是資料庫的存放目錄，這裡是c:\mysql\data。

使用select命令還可以指定卸出檔案時，欄位之間的分隔字元，逸出字元，包括字元，及記錄行分隔字元。列在下面：

　　FIELDS

　　TERMINATED BY "\t"

　　[OPTIONALLY] ENCLOSED BY ""

　　ESCAPED BY ""

　　LINES

　　TERMINATED BY "\n"

 

　　TERMINATED 表示欄位分隔

 

　　[OPTIONALLY] ENCLOSED 表示欄位用什麼字元包括起來，如果使用了OPTIONALLY則只有CHAR和VERCHAR被包括ESCAPED 表示當需要轉義時用什麼作為逸出字元LINES TERMINATED 表示每行記錄之間用什麼分隔上面列的是預設值，而且這些項都是可選的，不選則使用預設值。可以根據需要進行修改。給出一個例子如下：

　　mysql> select * from driver into outfile "a.txt" fields terminated by ","

　　enclosed by """;

　　Query OK, 22 rows affected (0.06 sec)

結果可能如下：

　　"1","Mika","Hakinnen","1"

　　"2","David","Coulthard","1"

　　"3","Michael","Schumacher","2"

　　"4","Rubens","Barrichello","2"

　　...

可以看到每個欄位都用","進行了分隔，且每個欄位都用"""包括了起來。注意，行記錄分隔字元可以是一個字串，請大家自行測試。不過，如果輸出檔案在指定目錄下如果存在的話就會報錯，先刪除再測試即可。

10.2、使用mysqldump公用程式

從上面的select方法可以看出，輸出的檔案只有資料，而沒有表結構。而且，一次只 能處理一個表，要處理多個表則不是很容易的。不過可以將select命令寫入一個sql 檔案（複製文本應該是很容易的吧），然後在命令列下執行即可：mysql 庫名先來個最簡單的吧：

mysqldump phptest > a.sql

可能結果如下：

　　# MySQL dump 7.1

　　#

　　# Host: localhost Database: phptest

　　#--------------------------------------------------------

　　# Server version 3.22.32-shareware-debug

　　#

　　# Table structure for table "driver"

　　#

　　CREATE TABLE driver (

　　drv_id int(11) DEFAULT "0" NOT NULL auto_increment,

　　drv_forename varchar(15) DEFAULT "" NOT NULL,

　　drv_surname varchar(25) DEFAULT "" NOT NULL,

　　drv_team int(11) DEFAULT "0" NOT NULL,

　　PRIMARY KEY (drv_id)

　　);

　　#

　　# Dumping data for table "driver"

　　#

 

　　INSERT INTO driver VALUES (1,"Mika","Hakinnen",1);

　　INSERT INTO driver VALUES (2,"David","Coulthard",1);

　　INSERT INTO driver VALUES (3,"Michael","Schumacher",2);

　　INSERT INTO driver VALUES (4,"Rubens","Barrichello",2);

　　...

 

　　如果有多表，則分別列在下面。可以看到這個檔案是一個完整的sql檔案，如果要將 其匯入到其它的資料庫中可以通過命令列方式，很方便：mysql phptest < a.sql。如果將資料從本地傳到伺服器上，則可以將這個檔案上傳，然後在伺服器通過命令列方式裝入資料。

如果只想卸出建表指令，則命令如下：

　　mysqldump -d phptest > a.sql

如果只想卸出插入資料的sql命令，而不需要建表命令，則命令如下：

　　mysqldump -t phptest > a.sql

那麼如果我只想要資料，而不想要什麼sql命令時，應該如何操作呢？

　　mysqldump -T./ phptest driver

其中，只有指定了-T參數才可以卸出純文字檔案，表示卸出資料的目錄，./表示當前目 錄，即與mysqldump同一目錄。如果不指定driver表，則將卸出整個資料庫的資料。每個表會產生兩個檔案，一個為.sql檔案，包含建表執行。 另一個為.txt檔案，只包含資料，且沒有sql指令。

對卸出的資料檔案，也可以同select方法一樣，指定欄位分隔符號，包括字元，轉義欄位，行記錄分隔字元。參數列在下面：

　　--fields-terminated-by= 欄位分隔符號

　　--fields-enclosed-by= 欄位包括符

　　--fields-optionally-enclosed-by= 欄位包括符，只用在CHAR和VERCHAR欄位上

　　--fields-escaped-by= 逸出字元

　　--lines-terminated-by= 行記錄分隔字元

我想大家應該明白這些參數的意思了吧。一個例子如下：

　　mysqldump -T./ --fields-terminated-by=, --fields-enclosed-by=" phptest driver

輸出結果為：

　　"1","Mika","Hakinnen","1"

　　"2","David","Coulthard","1"

　　"3","Michael","Schumacher","2"

　　"4","Rubens","Barrichello","2"

　　...

請注意字元的使用。

10.3、小結

以上為使用select和mysqldump公用程式來卸出文本的方法。select適合利用程式進行處理，而mysqldump則為手工操作，同時提供強大的匯出功能，並且可以處理整個庫，或庫中指定的多表。大家可以根據需求自行決定使用。

同時還有一些方法，如直接資料庫檔案拷貝也可以，但是移動後的資料庫系統與原系統應一致才行。這裡就不再提了。

 

十一、資料匯入

 

同匯出相類似，匯入也有兩種方法：

　　使用LOAD DATA INFILE "filename"命令

　　使用mysqlimport公用程式

　　使用sql檔案

由於前兩個處理與匯出處理相似，只不過是它們的逆操作，故只給出幾種命令使用的例子，不再解釋了，大家可以自行查閱手冊。

11.1、使用load命令：

　　load data infile "driver.txt" into table driver fields terminated by ","

　　enclosed by """;

11.2、使用mysqlimport公用程式：

　　mysqlimport --fields-terminated-by=, --fields-enclosed-by=" phptest driver.txt

11.3、使用SQL檔案

    則可以使用由mysqldump匯出的sql檔案，在命令列下執行mysql庫名。

首先要聲明一點，大部分情況下，修改MySQL是需要有mysql裡的root許可權的，所以一般使用者無法更改密碼，除非要求管理員。

方法一

使用phpmyadmin，這是最簡單的了，修改mysql庫的user表，不過別忘了使用PASSWORD函數。

方法二

使用mysqladmin，這是前面聲明的一個特例。

mysqladmin -u root -p password mypasswd

輸入這個命令後，需要輸入root的原密碼，然後root的密碼將改為 mypasswd。把命令裡的root改為你的使用者名稱，你就可以改你自己的密碼了。當然如果你的mysqladmin串連不上mysql server，或者你沒有辦法執行mysqladmin，那麼這種方法就是無效的。而且mysqladmin無法把密碼清空。

下面的方法都在mysql提示符下使用，且必須有mysql的root許可權：

方法三

　　mysql> INSERT INTO mysql.user (Host,User,Password)

　　VALUES(‘%‘,‘jeffrey‘,PASSWORD(‘biscuit‘));

　　mysql> FLUSH PRIVILEGES

確切地說這是在增加一個使用者，使用者名稱為jeffrey，密碼為biscuit。在《mysql中文使用手冊》裡有這個例子，所以我也就寫出來了。注意要使用PASSWORD函數，然後還要使用FLUSH PRIVILEGES。

方法四

和方法三一樣，只是使用了REPLACE語句

　　mysql> REPLACE INTO mysql.user (Host,User,Password)

　　VALUES(‘%‘,‘jeffrey‘,PASSWORD(‘biscuit‘));

　　mysql> FLUSH PRIVILEGES

方法五

使用SET PASSWORD語句，

mysql> SET PASSWORD FOR [email protected]"%" = PASSWORD(‘biscuit‘);

擬也必須使用PASSWORD()函數，但是不需要使用FLUSH PRIVILEGES。

方法六

使用GRANT ... IDENTIFIED BY語句

mysql> GRANT USAGE ON *.* TO [email protected]"%" IDENTIFIED BY ‘biscuit‘;

這裡PASSWORD()函數是不必要的，也不需要使用FLUSH PRIVILEGES。

注意： PASSWORD() [不是]以在Unix口令加密的同樣方法施行口令加密。

MySQL 忘記口令的解決辦法

如果 MySQL 正在運行，首先殺之： killall -TERM mysqld。

啟動 MySQL ：bin/safe_mysqld --skip-grant-tables &就可以不要求輸入密碼就進入 MySQL 了。然後就是

　　>use mysql

　　>update user set password=password("new_pass") where user="root";

　　>flush privileges;

重新殺 MySQL ，用正常方法啟動 MySQL 。

十二、備份與恢複　　12.1、備份　　

例如：將上例建立的aaa庫備份到檔案back_aaa中　　

[[email protected] root]# cd　/home/data/mysql　(進入到庫目錄，本例庫已由val/lib/mysql轉到/home/data/mysql，見上述第七部分內容) 　　

[[email protected] mysql]# mysqldump -u root -p --opt aaa > back_aaa 　　

12.2、恢複　　

[[email protected] mysql]# mysql -u root -p ccc < back_aaa

 

十三、mysqladmin 公用程式的使用

 

mysqladmin 公用程式可用來維護 MySQL 比較一般性的工作（新增、刪除資料庫、設定使用者密碼及停止 MySQL 等等），詳細的說明可以使用 mysqladmin --help 來查看。（以本文的安裝為例 mysqladmin 位於 /usr/local/mysql/bin/mysqladmin）。

新增資料庫 dbtest

# /usr/local/mysql/bin/mysqladmin -u root -p create dbtest

Enter password:

Database "dbtest" created.

刪除資料庫

# /usr/local/mysql/bin/mysqladmin -u root -p drop dbtest

Enter password:

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

 

Do you really want to drop the ‘dbtest‘ database [y/N]

y

Database "dbtest" dropped

設定使用者密碼（將 maa 的密碼改為 7654321，mysqladmin 會先詢問 maa 的原密碼）

# /usr/local/mysql/bin/mysqladmin -u maa -p password 7654321

Enter password:

#

停止 MySQL 服務

# ./mysqladmin -u root -p shutdown

Enter password:

注意，shutdown MySQL 後，必須由作業系統的 root 帳號執行下列指令才能啟動 MySQL：

/usr/local/mysql/share/mysql/mysql.server start

修改MYSQL的預設編碼MySQL的預設編碼是Latin1，不支援中文，那麼如何修改MySQL的預設編碼呢，下面以UTF-8為例來說明需要注意的是，要修改的地方非常多，相應的修改方法也很多。下面是一種最簡單最徹底的方法：Windows系統 
1、中止MySQL服務 
2、在MySQL的安裝目錄下找到my.ini，如果沒有就把my-medium.ini複製為一個my.ini即可 
3、開啟my.ini以後，在[client]和[mysqld]下面均加上default-character-set=utf8，儲存並關閉 
4、啟動MySQL服務

Centos下MySQL使用總結