第三節 配置MYSQL數據庫
配置mysql數據庫通常通過命令行選項、配置文件、和環境變量來進行，并且優先順序也是命令行最高，環境變量優先級最低。
1、配置文件定位
mysql的配置文件可以在以下四個位置：(按照查找順序)
1、/etc/my.cnf
2、DATADIR/my.cnf ? ? ? ? ? ? ? //DATADIR是存儲數據庫數據的目錄。
3、通過 --default-extra-file=fielname設置指定的位置。
4、~/.my.cnf ? ? ? ? ? ? ? ? ? ? ? ?//用戶的家目錄
所以可以在用戶的家目錄下創建.my.cnf文件，并添加一下內容：
[client]
user=root
host=localhost
password=password
并設置相應權限，防止被其他用戶查看
2、基本參數：
配置文件：/etc/my.cnf，集中式的多段配置文件
[mysqld]
datadir=/var/lib/mysql/ ? ? ? ? ? ? ?//用來定義數據庫存儲位置,要求存儲目錄必須是mysql用戶以及msyql組所有。
back_log ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //要求mysql具有的鏈接數量，最大為50
max_connections ? ? ? ? ? ? ? ? ? //允許同時訪問的數量。
[mysql]
[client]
實現開關機自動開啟和關閉MySQL服務
通常情況下，如果使用rpm方式安裝會自動安裝此功能，可以通過查看是否存在/etc/rc.d/init.d/mysql來確定，如果通過二進制安裝或者編譯安裝的，需要進入安裝目錄的mysql/support-files/目錄查找mysql.server文件。
手動安裝：
1、cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/
2、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc3.d/s99mysql
3、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc0.d/s01mysql
配置文件詳解：http://www.cnblogs.com/toby/articles/2198697.html
常用命令
查看版本信息
select version()；
顯示服務器參數變量、當前數據庫的詳細配置信息
（有些變量可以修改，這些參數可以改變數據庫的工作特性。有些立即生效，有些需要重啟數據庫生效。）
show [gloab|session] variables;
顯示服務器狀態變量，記錄了當前包括過去時間內mysql的運行統計數據
show [gloab|session]status
3、MySQL安全
用戶管理
1、創建用戶：create user
? ? ? ? ? ?實例： ?create user gongbing@192.168.1.0/24 IDENTIFIED BY ‘password’
create user gongbing@192.168.1.0.% IDENTIFIED BY ‘password’
? ? ? ? ? 可以使用通配符
? ? ? ? ? ? ? ? ? ? %表示任意字符
? ? ? ? ? ? ? ? ? ? _表示任意單個字符
2、用戶授權創建和刪除用戶權限：GRANT REVOKE
用戶授權： grant all privileges on dbname.tablename to username@host [identified by ‘password’]
授予用戶username可以通過host對dbname.tablename數據庫的表擁有所有權限
注意: 假如你在給用戶'pig'@'%'授權的時候是這樣的(或類似的):GRANT SELECT ON test.user TO 'pig'@'%', 則在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤銷該用戶對test數據庫中user表的SELECT 操作.相反,如果授權使用的是GRANT SELECT ON *.* TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤銷該用戶對test數據庫中user表的Select 權限.
3、修改用戶密碼
a）、修改root密碼，這種方式適用于知道root密碼（mysql數據庫的密碼不要和linux的密碼混淆了哦。）mysqladmin -u username -p ?password ‘newpassword’
--注意：當使用mysqladmin修改普通用戶時
[root@gc ~]# [root@gc ~]# mysqladmin -uhive -phive1 password hive
mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'
b）、直接更改user表的password字段
? ? ? ? 適用于忘記root、普通用戶密碼。
編輯my.cnf（windows的是mysql.ini）文件，添加skip-grant-tables
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables
c）、登陸mysql數據庫并修改用戶名密碼
mysql>?use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>??update user set password=password('123123') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4? Changed: 4? Warnings: 0
mysql>?flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意：mysql5.6在通過上面修改密碼后要求使用set password再設置一次密碼。
d）、通過set命令設置
語法:SET PASSWORD FOR '用戶名'@'主機' = PASSWORD('密碼')
mysql> create database gongbing;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password for 'root'@'localhost'=password('123123');
Query OK, 0 rows affected (0.00 sec)
mysql> create database gongbing;
Query OK, 1 row affected (0.00 sec)
--允許root通過遠程登錄
mysql> set password for 'root'@'%'=password('123123');
注意：要使用password函數，但不用flush privileges命令。
修改密碼摘自：http://blog.csdn.net/lichangzai/article/details/8626591
?
4、刪除用戶
? ? ? ? drop user ‘username’@‘localhost‘
系統內部安全
1、建議將數據目錄的權限設置為700
2、不要使用root啟動MySQL
3、注意~/.bash.history和~/.mysql.history目錄，防止記錄被竊取。
4、登錄時密碼不要直接輸入在-p后面。
mysql5.5數據庫root賬戶被刪除的處理方法：
1、關閉mysql，并使用mysqld_safe --skip-grant-tables &重啟
2、使用grant all privileges on *.* to root@localhost identified by ‘root’ with grant option,創建用戶并附權限。
問題：
MySQL報錯：The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
處理方法：
mysql>set global read_only=0;
mysql>flush privileges
外部網絡安全
1、修改用戶密碼
? ? ? ? ? ? 方法一、mysqladmin -u root -p ?password ‘newpassword’
? ? ? ? ? ? 方法二、set?password?for?'root'@'localhost'=password('root');
? ? ? ? ? ? 方法三、use mysql;
? ? ? ? ? ? ? ? ? ? ? ? ??update user set password=password('new_password') where user='root';
? ? ? ? ? ? ? ? ? ? ? ? ? flush privileges;
2、刪除多余賬戶
? ? ? ? delete from user where='';
小技巧：
1）、忘記MySQL的root密碼（本地登錄拒絕訪問）
1、safe_mysqld --skip-grant-tables &
2、使用root賬戶登錄指定mysql數據庫 ? ?mysql -u root mysql
3、更改密碼：update user set password=password(’newpassword’) where user=‘root’
4、更新數據庫授權表：flush privileges;
5、重啟數據庫并登錄。
2）、啟動MYSQL服務器加上--skip-show-database，可以使數據庫用戶不能瀏覽其他數據庫。
3）、啟動mysql服務器加上--log-slow-queriesl=“”，這樣mysql會把SQL執行時間超過long_query_time的寫入file中。
4）、啟動msyql服務器不監聽任何TCP/IP協議--skip-network。
優化查詢語句
./safe_mysqld --log-slow-queries=file ? ? ? ? ? ? ? ? ? ?//將sql語句查詢超時的記錄下來。
MYSQL用戶管理視頻來源：http://edu.51cto.com/lession/id-75676.html
第四節 MYSQL管理
一、使用命令工具
MYSQL客戶端命令
幫助：help
quit，\q：退出
delimiter，\d：更換結束符；，默認服務器端命令是；作為結束符
go，\g：強制將命令發送到mysql上去，可以沒有；
use，\u：用于設定默認數據庫
mysql>use mysql
ego，\G：將命令送到mysql上面，并將結果豎向現實，對亂碼有用。
mysql>?select?*?from?user\G
***************************?1.?row?***************************
??????????????????Host:?%
??????????????????User:?root
??????????????Password:?*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
???????????Select_priv:?Y
???????????Insert_priv:?Y
???????????Update_priv:?Y
???????????Delete_priv:?Y
???????????Create_priv:?Y
?????????????Drop_priv:?Y
???????????Reload_priv:?Y
?????????Shutdown_priv:?Y
??????????Process_priv:?Y
?????????????File_priv:?Y
????????????Grant_priv:?Y
???????References_priv:?Y
????????????Index_priv:?Y
????????????Alter_priv:?Y
??????????Show_db_priv:?Y
????????????Super_priv:?Y
?Create_tmp_table_priv:?Y
??????Lock_tables_priv:?Y
??????????Execute_priv:?Y
???????Repl_slave_priv:?Y
??????Repl_client_priv:?Y
??????Create_view_priv:?Y
????????Show_view_priv:?Y
???Create_routine_priv:?Y
????Alter_routine_priv:?Y
??????Create_user_priv:?Y
????????????Event_priv:?Y
??????????Trigger_priv:?Y
Create_tablespace_priv:?Y
??????????????ssl_type:?
????????????ssl_cipher:?
???????????x509_issuer:?
??????????x509_subject:?
?????????max_questions:?0
???????????max_updates:?0
???????max_connections:?0
??max_user_connections:?0
????????????????plugin:?
?authentication_string:?NULL
***************************?2.?row?***************************
system，\!：不退出當前程序的情況下，執行shell命令。
mysql>?system?ls?-l
total?151680
-rwxr-xr-x.?1?root?root????25470?Mar??3?05:38?innochecksum
-rwxr-xr-x.?1?root?root?????1458?Mar??3?05:28?msql2mysql
-rwxr-xr-x.?1?root?root??6162461?Mar??3?05:38?myisamchk
-rwxr-xr-x.?1?root?root??5768008?Mar??3?05:38?myisam_ftdump
-rwxr-xr-x.?1?root?root??5749127?Mar??3?05:38?myisamlog
-rwxr-xr-x.?1?root?root??5833390?Mar??3?05:38?myisampack
status：獲取當前服務器狀態信息
mysql>?status;
--------------
mysql??Ver?14.14?Distrib?5.1.73,?for?redhat-linux-gnu?(x86_64)?using?readline?5.1
Connection?id:??????????69
Current?database:
Current?user:???????????root@localhost
SSL:????????????????????Not?in?use
Current?pager:??????????stdout
Using?outfile:??????????''
Using?delimiter:????????;
Server?version:?????????5.5.48?Source?distribution
Protocol?version:???????10
Connection:?????????????Localhost?via?UNIX?socket
Server?characterset:????latin1
Db?????characterset:????latin1
Client?characterset:????latin1
Conn.??characterset:????latin1
UNIX?socket:????????????/var/lib/mysql/mysql.sock
Uptime:?????????????????4?hours?20?min?52?sec
Threads:?3??Questions:?787??Slow?queries:?0??Opens:?48??Flush?tables:?1??Open?tables:?41??Queries?per?second?avg:?0.050
--------------
1、mysql命令支持交互式，批處理命令式。
? ? ? ? 交互式
? ? ? ? ? ? -h --host=：指定主機名
? ? ? ? ? ? -u --user=：用戶名
? ? ? ? ? ? -p --password=：密碼
? ? ? ? ? ? -D db_name，--database=： 直接設置某個數據庫為默認數據庫
? ? ? ? ? ? -e ‘sql_statement；’：將sql語句外置。
實例：鏈接遠程192.168.1.2主機，默認庫為mysql。
? ? ? ? ? ? mysql -h 192.168.1.2 -D mysql -uroot ?-p
2、批處理方式：
方法一、mysql options <scripts.sql
方法二、mysql> source /path/scripts.sql
服務器端命令
查看幫助，使用help后面跟隨需要了解的命令，比如help create 來了解create后面可以接那些參數，再help create user來詳細了解具體使用方法。
小技巧：
如果公司為了安全考慮，對mysql進行了身份驗證，并且登陸時能夠直接指定默認數據庫可以編寫一個my.cnf文件，并放在用戶的家目錄下。
[client]
user=mysql
passowrd=mysqlpassword
database=dtedu
3、mysqladmin管理性操作：直接通過mysqladmin [option] 來進行。
create databasename； ? ? ? ? ? ? ? ? //創建一個數據庫
drop databasename； ? ? ? ? ? ? ? ? ? //刪除一個數據庫及所有包含的表
extended-status； ? ? ? ? ? ? ? ? ? ? ? ? ?//給出服務器的擴展狀態
flush-hosts; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //清空所有緩存主機
flush-logs； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?//清空所有日志
flush-tables; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //清空所有表
flush-privileges； ? ? ? ? ? ? ? ? ? ? ? ? ?//重新加載授權表
password； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //修改密碼
ping； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?//檢測mysql是否活動
processlist； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //顯示服務器中活動線程列表
refresh； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?//清空所有表并打開和關閉日志文件
shutdown； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //關閉服務器
status； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //顯示狀態信息
version； ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? //顯示mysql版本
4、mysqlbinlog
? ? ? ? 將二進制日志中的項目轉換為可讀的表單或者SQL語句。
5、mysqlcheck
? ? ? ? 用于修復、檢查數據庫及其中的表。
? ? ? ? ? ??[root@localhost?mysql]#?mysqlcheck?-o?mysql ? ? ? ??
選項：
-a 分析指定的表
-c 檢查庫或者表
-r 修復庫、表
-o 優化指定的表
-h 指定需要操作的主機
-u 指定用戶名
-p 指定密碼
-auto-repair 當檢查表有錯誤的時候自動修復
-e 完全檢查（修復方式）
-m 只檢查程序
-q 快速檢查
-F 檢查沒有正確關閉的表
-f 遇到錯誤sql，強制執行。
6、mysqlhotcopy
需要提前安裝perl-DBI和perl-DBD-mysql
參見：http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql/INSTALL.pod
? ? ? ? ? ?http://linux.fcbu.com/mysqlhotcopy.htm
perl-DBD-mysql源碼安裝包 ?http://search.cpan.org/dist/DBD-mysql/
用途：用于為活動的數據執行一次安全備份
選項：
--checkpoint ?用于指定存放操作記錄的數據庫或表
--addtodest 增量備份、新的備份自動覆蓋原來的備份。
示例：備份一個數據庫到一個目錄中
[root@localhost?data]#?/usr/local/mysql/bin/mysqlhotcopy?mysql?/root/
恢復數據庫文件，只需將備份的文件復制到數據庫指定的目錄下即可
[root@localhost?data]#?cp?-rf?/root/mysql?/usr/local/mysql/data/
mysql備份參考：http://www.linuxidc.com/Linux/2015-01/111340.htm
7、mysqldump備份數據庫
參數：
-h：指定遠程主機ip地址
-u：指定遠程需要備份主機的用戶名
-p：指定遠程備份主機的密碼，可以為空，然后手動輸入，這樣比較安全。
-a：完全備份所有數據庫
常用命令格式：
mysqldump -u root -p databasename >/var/backupmysql/backupname.sql
恢復( 導入數據庫)
前提：如果需要導入的數據庫之前沒有創建過，那么需要先建立一個同名數據庫。create database ec_school，然后才能導入。
導入方法：
方法一、進入mysql后，如果是對數據庫恢復直接使用 source /var/backupmysql/backupname.sql即可。
方法二、mysqldump -u root -p ec_school</root/ec_school.sql
二、建立和連接數據庫
1、連接數據庫
? ? mysql sqlname -u username -p
2、顯示數據庫列表
? ? show databases;
3、顯示庫中的數據表
? ? use mysql; ? ? ? ?指定使用的數據庫
? ? show tables;
mysql>?show?tables->?;+---------------------------+|?Tables_in_mysql???????????|+---------------------------+|?columns_priv??????????????||?db????????????????????????||?event?????????????????????||?func??????????????????????||?general_log???????????????||?help_category?????????????||?help_keyword??????????????||?help_relation?????????????||?help_topic????????????????||?host??????????????????????||?ndb_binlog_index??????????||?plugin????????????????????||?proc??????????????????????||?procs_priv????????????????||?proxies_priv??????????????||?servers???????????????????||?slow_log??????????????????||?tables_priv???????????????||?time_zone?????????????????||?time_zone_leap_second?????||?time_zone_name????????????||?time_zone_transition??????||?time_zone_transition_type?||?user??????????????????????|+---------------------------+24?rows?in?set?(0.00?sec)
4、顯示數據庫中表的結構
mysql>?describe?user
????->?;
+------------------------+-----------------------------------+------+-----+---------+-------+
|?Field??????????????????|?Type??????????????????????????????|?Null?|?Key?|?Default?|?Extra?|
+------------------------+-----------------------------------+------+-----+---------+-------+
|?Host???????????????????|?char(60)??????????????????????????|?NO???|?PRI?|?????????|???????|
|?User???????????????????|?char(16)??????????????????????????|?NO???|?PRI?|?????????|???????|
|?Password???????????????|?char(41)??????????????????????????|?NO???|?????|?????????|???????|
5、創建數據庫
create database databasename；
6、創建表
use databasename；
create table tablename；?
7、刪除數據庫、表
drop database databasename；
drop table tablename；
8、清空表中的記錄
delete from tablename；
9、顯示表中的記錄
select * from tablename；
三、用戶權限管理
GRANT和REVOKE
1、revoke（撤銷權限）
撤權和授權的格式基本一樣，只是撤權使用的是from指向用戶名，而不是to。
2、GRANT(授權)
語法格式：GRANT privileges （columns） ON what?TO?user IDENTIFIED BY "password" WITH GRANT OPTION
實例：
1、授予用戶test對數據庫mysql有讀取并修改數據庫內容，但不能創建新表或者刪除表
grant select,insert,delete,update on mysql.* to test@localhost identified by ‘6776’;
2、授予用戶只對表的某些列可以查看的權限。
grant select (treet,city,zip) on mysql.address to mysql@localhost identified by ‘123’;
對應privileges所指定的用戶權限主要有一下幾種表示方法：
| 權限指定符 | 含義 |
alter | 修改表和索引 |
create? | 創建數據庫和表 |
delete | 刪除表中的記錄 |
drop | 刪除數據庫和表 |
index | 創建和刪除索引 |
insert | 向表中插入新行 |
select | 查找表中的數據 |
update | 更新表的記錄，修改，編輯 |
file | 讀寫服務器上的文件 |
| process | 查看服務器中執行的進程信息或者殺死進程 |
reload? | 重載授權表或者清空日志，緩存 |
? ??
shutdown | 關閉服務器 |
all | 所有服務 |
usage | 特殊的“無權限”權限 |
? ??? ??
columns：用于定義用戶可以設置的表的“列”，多個“列”之間用“逗號”分割。
what：用于確定數據庫及表的范圍。
user：權限授予的用戶，他由用戶名、主機名組成，可以指定那個用戶可以通過那個主機連接到數據庫中來。
通過username@hostname的方式可以限制用戶只能在指定的主機上登陸訪問數據庫，主機名可以使用%來表示任意主機，192.168.%表示一個網絡地址范圍，mysql3.23版本之后可以通過直接跟子網掩碼號來確定ip的網絡地址。
with grant option ：被授權用戶有再給其他用戶授權的能力。
小技巧：通過測試發現，手動授權會造成各種問題，建議初始化mysql數據庫后，通過phpmyadmin來完成其他用戶的權限賦值過程，會比較穩定。
配置phpmyadmin可以參考后面的下面的文檔，主要是copy config_sample_inc.php文件到htdocs/config_inc.php，并修改參數secret。
轉載于:https://blog.51cto.com/137783/1968772
)
)