MySQL/MySQL Practical Guide

安裝 MySQL

免安裝使用

下載：

下載64位系統的community server版的最新版本號的zip包。例如：mysql-8.0.28-winx64.zip。下載地址：https://dev.mysql.com/downloads/mysql/

下載後解壓：D:\mysql-8.0.28-winx64

配置環境變量

可以不設置，略過此步。

變量名：MYSQL_HOME

變量值：D:\mysql-8.0.13-winx64

環境變量path里添加：%MYSQL_HOME%\bin;

初始化並生成data文件夾

以管理員身份運行cmd.exe

進入D:\mysql-8.0.28-winx64\bin 下執行命令：

mysqld --initialize-insecure --console

在D:\mysql-8.0.28-winx64目錄下生成data目錄

如果用 mysqld --initialize --console 初始化數據庫，則會為root創建一個臨時password。後續操作需要用這個password登錄。

輸入mysqld --initialize命令，初始化mysql的data數據目錄，初始化完畢後，會在解壓mysql目錄下生成一個data文件夾，有個計算機名字.err結尾文件（LAPTOP-37S66HI6.err），打開後@localhost:後面就是隨機生成的密碼。

啟動服務

執行命令：net start mysql 啟動mysql服務，若提示：服務名無效...解決方法見下一步

解決啟動服務失敗（報錯）

提示：服務名無效

解決方法：

執行命令：mysqld --install 即可（不需要my.ini配置文件 ）

可在資源管理器中把mysql進程全結束了，重新啟動即可。

卸載mysql

#停止服务
net stop mysql

#卸载服务 
mysqld --remove

客戶端登錄/退出mysql

登錄本機mysql:

d:\mysql-8.0.13-winx64\bin>mysql -u root -p
Enter password: ******

注意用戶名前可以有空格也可以沒有空格，但是密碼前必須沒有空格，否則讓你重新輸入密碼. root密碼為空，不用輸入密碼，直接回車即可

連接到遠程主機上的MYSQL，假設遠程主機的IP為：110.110.110.110，用戶名為root,密碼為abcd123。則鍵入以下命 令：

　　mysql -h110.110.110.110 -P3307 -u root -pabcd123

退出mysql:

  mysql> quit 

MySQL用戶賬號管理

修改默認的加密外掛程式

MySQL8.0.4之前，MySQL的密碼認證外掛程式是「mysql_native_password」，之後使用的是「caching_sha2_password」。

Mysql 安裝完客戶端連接報錯：「Authentication plugin 'caching_sha2_password' cannot be loaded: 」 意思是客戶端不支持caching_sha2_password的加密方式。

可以配置default_authentication_plugin項。在mysql安裝目下的配置文件my.ini中：

[mysqld]
default_authentication_plugin=mysql_native_password

root更改密碼與加密方式

在MySQL 8.04前，執行：

SET PASSWORD=PASSWORD('[修改的密码]');

在MySQL 8.04之後， 執行語句：

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; 

其中WITH mysql_native_password為可選

另一種方法：給root加個密碼ab12。首先在DOS下進入目錄mysql\bin，然後鍵入以下命令

mysqladmin -u root -password ab12

再將root的密碼改為djg345。

mysqladmin -u root -p ab12 password ******

忘記root密碼

1. net stop mysql
2. mysqld --skip-grant-tables
3. mysql -u root
4. update mysql.user set password='這裏填寫你要設置的密碼' where user='root';
5. 或者update user set password=password("new_pass") where user="root";
6. net start mysql;//啟動mysql服務

查詢所有用戶/密碼

查詢用戶、密碼、加密方式：

mysql> select host,user,authentication_string,plugin from mysql.user;

創建用戶

CREATE USER 'TestUser'@'%' IDENTIFIED BY 'MyPassword123!'; 

設置或修改用戶密碼、權限

修改root賬戶遠程訪問

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '密码' WITH GRANT OPTION;   

或者

 update mysql.user set host='%' where user='root';

連結數據庫修改Root賬戶密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass1234!';  
ALTER USER 'TestUser'@'%' IDENTIFIED WITH mysql_native_password BY 'MyPassword123!';

注意:用以上命令授權的用戶不能給其它用戶授權,如果想讓該用戶可以授權,用以下命令:

   grant all on *.* to 'UserName'@'%';
   GRANT all ON databasename.tablename TO 'username'@'hostname' ;

注意，數據庫名、表名不能用單引號圍起來。

創建數據庫

首先登錄作為root用戶。

查看所有的數據庫列表：

show databases;

創建一個叫做people的數據庫

create database people; 

在數據庫中創建表與數據:

查看當前所在的數據庫：

 select database(); 

設置當前數據庫：

USE people

創建一張表：

CREATE TABLE peopleInfo

(

peopleID int unsigned not null auto_increment primary key,

firstName varchar(30),

lastName varchar(30),

age int,

gender varchar(13)

);

列出當前數據庫中所有表：

show tables;

查看一張表的詳細定義：

describe peopleInfo;

刪除一張表：

drop table peopleInfo;

備份與恢復數據庫

導出數據庫的所有表結構定義：

mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql

導入時，在mysql裏面執行

 mysql> source d:\dumpfile.sql

把MySQL目錄下的data子目錄完整複製覆蓋到另一台電腦的同版本MySQL目錄下，就可以實現數據庫中所有信息的複製遷移。

升級數據庫

對於Windows，把zip壓縮的免安裝的mysql程序包在原來的老版本上覆蓋即可。

需要停止與開始服務：

 net stop ServiceName

與

net start ServiceName

必要時，刪除與增加服務：

mysqld --remove  SQL-x.y
mysqld --install  SQL-x.y

數據庫設置

在Linux上，配置文件為mysql.conf 在Windows上，配置文件是mysql.ini 例如：

 #########################################################
    [client]
    port=3306
    default-character-set=utf8
 #regin
    [mysqld]
    port=3306 
    character_set_server=utf8
    basedir="E:\wamp\Mysql"
    #解压目录
    datadir="E:\wamp\Mysql\data"
    #解压目录下data目录
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    [WinMySQLAdmin]
    E:\wamp\Mysql\bin\mysqld.exe
 #########################################################

查看MySQL變量：

  Mysql> show variables like '%timeout%';

配置參數：

• interactive_timeout: 伺服器關閉交互式連接前等待活動的秒數。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。參數默認值：28800秒（8小時）
• wait_timeout:伺服器關閉非交互連接之前等待活動的秒數。參數默認值：28800秒（8小時）
• max_connections: 允許的同時客戶的數量。增加該值增加 mysqld 要求的文件描述符的數量。不足會導致 too many connections 錯誤。 默認數值是100
• record_buffer: 每個做順序掃描的線程為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。默認數值是131072(128k)
• skip-locking: 避免MySQL的外部鎖定。
• skip-name-resolve : 禁止MySQL對外部連接進行DNS解析，可以消除MySQL進行DNS解析的時間。如果開啟該選項，則所有遠程主機連接授權都要使用IP位址方式
• back_log: 指出在MySQL暫時停止響應新請求之前的短時間內多少個請求可以被存在堆疊中。 如果系統在一個短時間內有很多連接，則需要增大該參數的值，該參數值指定到來的TCP/IP連接的偵聽隊列的大小。試圖設定back_log高於作業系統的限制將是無效的。默認值為50。
• key_buffer_size: 指定用於索引的緩衝區大小，增加可得到更好的索引處理性能。該參數值設置的過大反而會是伺服器整體效率降低
• max_allowed_packet: Server接受的數據包大小
• thread_stack: 每個連接被創建的時候,mysql分配給它的內存.
• table_cache: 表高速緩存的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被打開並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現 open_tables等於table_cache，並且opened_tables在不斷增長，那麼需要增加table_cache的值了(上述狀態值可以使用SHOW STATUS LIKE 『Open%tables'獲得)。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩定或者連接失敗。
• sort_buffer_size: 排序時所能使用的緩衝區大小。該參數對應的分配內存是每連接獨佔，如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。
• read_buffer_size: 讀查詢操作所能使用的緩衝區大小。該參數對應的分配內存也是每連接獨享。
• join_buffer_size:聯合查詢操作所能使用的緩衝區大小，該參數對應的分配內存也是每連接獨享。
• myisam_sort_buffer_size: MyISAM表發生變化時重新排序所需的緩衝
• thread_cache_size: 可以復用的保存在中的線程的數量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。
• query_cache_size: 指定MySQL查詢緩衝區的大小
• tmp_table_size: 通過設置tmp_table_size選項來增加一張臨時表的大小
• max_connect_errors: 對於同一主機，如果有超出該參數值個數的中斷錯誤連接，則該主機將被禁止連接。如需對該主機進行解禁，執行：FLUSH HOST;。
• wait_timeout : 指定一個請求的最大連接時間，對於4GB左右內存的伺服器可以設置為5-10。
• thread_concurrency : 該參數取值為伺服器邏輯CPU數量*2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支持H.T超線程，所以實際取值為4*2=8
• skip-networking : 開啟該選項可以徹底關閉MySQL的TCP/IP連接方式
• innodb_additional_mem_pool_size: InnoDB 用來保存數據字典信息和其他內部數據結構的內存池的大小，單位是 byte，參數默認值為8M。數據庫中的表數量越多，參數值應該越大，如果 InnoDB 用完了內存池中的內存，就會從作業系統中分配內存，同時在 error log 中打入報警信息。
• innodb_flush_log_at_trx_commit: 設置為0就是等到innodb_log_buffer_size列隊滿後再統一儲存,默認為1
• innodb_log_buffer_size
• innodb_thread_concurrency: 伺服器CPU有幾個就設置為多少
• key_buffer_size
• read_rnd_buffer_size