Mysql數(shù)據(jù)庫應(yīng)用部署及Sql操作

一、數(shù)據(jù)庫的概述；二、數(shù)據(jù)庫的分類；三、Mysql的概述：

四、Mysql存儲原理：五、案例：centos7搭建Mysql5.7；六、Sql語句操作之增刪改查；七、Sql語句操作之授權(quán)；八、Mysql基本優(yōu)化操作；九、Mysql存儲引擎的應(yīng)用；十、Mysql配置文件詳解：一、數(shù)據(jù)庫的概述；概述：數(shù)據(jù)庫(Database)是按照數(shù)據(jù)結(jié)構(gòu)組織、存儲和管理數(shù)據(jù)的倉庫，它產(chǎn)生于距今六十多年前，數(shù)據(jù)庫有很多種類型，從最簡單的存儲有各種數(shù)據(jù)的表格到能夠進行海量數(shù)據(jù)存儲的大型數(shù)據(jù)庫系統(tǒng)，在各個方面得到了廣泛的應(yīng)用。在信息化社會，充分有效地管理和利用各類信息資源，是進行科學(xué)研究和決策管理的前提條件。數(shù)據(jù)庫技術(shù)是管理信息系統(tǒng)、辦公自動化系統(tǒng)、決策支持系統(tǒng)等各類信息系統(tǒng)的核心部分，是進行科學(xué)研究和決策管理的重要技術(shù)手段。二、數(shù)據(jù)庫的分類；關(guān)系型數(shù)據(jù)庫：數(shù)據(jù)擁有固定的存儲結(jié)構(gòu)，通過庫--表--行--列的方式存儲，存儲時會有表的結(jié)構(gòu)化關(guān)系，過程如下：解析sql語句--連接層--磁盤存取--結(jié)構(gòu)化成表，；優(yōu)勢：1.容易理解，二維表的結(jié)構(gòu)非常貼近現(xiàn)實世界，二維表格，容易理解；

2.使用方便，通用的sql語句使得操作關(guān)系型數(shù)據(jù)庫非常方便；

3.易于維護，數(shù)據(jù)庫的ACID屬性，大大降低了數(shù)據(jù)冗余和數(shù)據(jù)不一致的概率；瓶頸：1.海量數(shù)據(jù)的讀寫效率，對于網(wǎng)站的并發(fā)量高，往往達到每秒上萬次的請求，對于傳統(tǒng)關(guān)系型數(shù)據(jù)庫來說，硬盤I/O是一個很大的挑戰(zhàn)。

2.高擴展性和可用性，在基于web的結(jié)構(gòu)中，數(shù)據(jù)庫是最難以橫向拓展的，當一個應(yīng)用系統(tǒng)的用戶量和訪問量與日俱增的時候，數(shù)據(jù)庫沒有辦法像webServer那樣簡單的通過添加更多的硬件和服務(wù)節(jié)點來拓展性能和負載能力。關(guān)系型數(shù)據(jù)庫到非關(guān)系型數(shù)據(jù)庫的演變：關(guān)系型數(shù)據(jù)庫的最大優(yōu)點就是事務(wù)的一致性，這個特性，使得關(guān)系型數(shù)據(jù)庫中可以適用于一切要求一致性比較高的系統(tǒng)中。比如：銀行系統(tǒng)。但是在大部分網(wǎng)頁應(yīng)用中，對這種一致性的要求不是那么的嚴格，允許有一定的時間間隔，所以關(guān)系型數(shù)據(jù)庫這個特點不是那么的重要了。相反，關(guān)系型數(shù)據(jù)庫為了維護一致性所付出的巨大代價就是讀寫性能比較差。而像微博、facebook這類應(yīng)用，對于并發(fā)讀寫能力要求極高，關(guān)系型數(shù)據(jù)庫已經(jīng)無法應(yīng)付。所以必須用一種新的數(shù)據(jù)結(jié)構(gòu)存儲來替代關(guān)系型數(shù)據(jù)庫。所以非關(guān)系型數(shù)據(jù)庫應(yīng)用而生。非關(guān)系型數(shù)據(jù)庫：NoSQL主要指那些非關(guān)系型的、分布式的，主要代表MongoDB，Redis、CouchDB。NoSQL提出了另一種理念，以鍵值來存儲，且結(jié)構(gòu)不穩(wěn)定，每一個元組都可以有不一樣的字段，這種就不會局限于固定的結(jié)構(gòu)，可以減少一些時間和空間的開銷。使用這種方式，為了獲取用戶的不同信息，不需要像關(guān)系型數(shù)據(jù)庫中，需要進行多表查詢。僅僅需要根據(jù)key來取出對應(yīng)的value值即可，所以避免了關(guān)系型數(shù)據(jù)庫復(fù)雜的查詢關(guān)系，可以大大增加查詢的效率；三、Mysql的概述：

概述：MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQLAB公司開發(fā)，目前屬于Oracle旗下公司。是一個真正的多用戶、多線程SQL數(shù)據(jù)庫服務(wù)器。SQL（結(jié)構(gòu)化查詢語言）是世界上最流行的和標準化的數(shù)據(jù)庫語言。MySQL是以一個客戶機/服務(wù)器結(jié)構(gòu)的實現(xiàn),它由一個服務(wù)器守護程序mysqld和很多不同的客戶程序和庫組成。

SQL是一種標準化的語言,它使得存儲、更新和存取信息更容易；四、Mysql存儲原理：Mysql整體架構(gòu)：1.連接池：最上層負責和客戶端進行連接，比如jdbc，odbj這樣的數(shù)據(jù)庫連接的API，在這一層有連接池的概念，類似于線程池，連接池可以同時處理很多個數(shù)據(jù)庫請求。同時這一層有SSL的安全概念，可以確保連接是安全的；2.SQL接口：當SQL語句進入MySQL后，會先到SQL接口中，這一層是封裝層，將傳過來的SQL語句拆散，將底層的結(jié)果封裝成SQL的數(shù)據(jù)格式；3.解析器：這一層負責將SQL語句進行拆分，驗證，如果語句有問題那么就返回錯誤，如果沒問題就繼續(xù)向下執(zhí)行；4.優(yōu)化器：對SQL查詢的結(jié)果優(yōu)化處理，產(chǎn)生多種執(zhí)行計劃，最終數(shù)據(jù)庫會選擇最優(yōu)化的方案去執(zhí)行，盡快返會結(jié)果。比如selecta,bfromcwhered。在這里會先查詢c表中符合d的數(shù)據(jù)并將他們的ab項進行投影，返回結(jié)果，并不會直接把整張表查出來；5.緩存：對要查詢的SQL語句進行hash后緩存，如果下一次是相同的查詢語句，則在SQL接口之后直接返回結(jié)果；6.存儲引擎：MySQL有很多種存儲引擎，每一種存儲引擎有不同的特性，他們負責組織文件的存放形式，位置，訪問文件的方法等等。比較常用的有innoDB，MyISAM，MAMORY等；7.文件系統(tǒng)：真正存放物理文件的單位；存儲引擎：1.innoDB：支持事務(wù)，具有事務(wù)提交、回滾、崩潰恢復(fù)等機制，它的設(shè)計是為處理巨大數(shù)據(jù)量時的最大性能設(shè)置的，它的CPU效率是任何磁盤的關(guān)系數(shù)據(jù)庫引擎所不能匹敵的，InnoDB存儲引擎完全與Mysql服務(wù)器整合，為在內(nèi)存中緩存數(shù)據(jù)和索引而維持它自己的緩沖池，InnoDB表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統(tǒng)上也無影響；InnoDB不創(chuàng)建目錄，使用InnoDB時，MySQL將在MySQL數(shù)據(jù)目錄下創(chuàng)建一個名為ibdata1的10MB大小的自動擴展數(shù)據(jù)文件，以及兩個名為ib_logfile0和ib_logfile1的5MB大小的日志文件；優(yōu)點：支持事務(wù)，事務(wù)隔離級別，事務(wù)采用MVCC做多版本控制；

支持外鍵，具有安全的約束性；

主鍵是唯一的聚集索引，和數(shù)據(jù)存放在一起，效率高；

可以有非聚集索引，非聚集索引單獨存放，可以通過其查到聚集索引；

對于死鎖情況，innoDB會將持有最少排它鎖的事務(wù)回滾；

支持分區(qū)、表空間、類似于Oracle；缺點：占用磁盤較多，占用內(nèi)存較多（緩存）；

讀效率慢于MyISAM；

不存儲總行數(shù)；生產(chǎn)環(huán)境場景：

業(yè)務(wù)需要事務(wù)的支持；

行級鎖定對高并發(fā)有很好的適應(yīng)能力；

業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場景，如微博，論壇等；

業(yè)務(wù)數(shù)據(jù)一致性要求較高，比如銀行業(yè)務(wù)；

硬件設(shè)備內(nèi)存較大，利用InnoDB較好的緩存能力來提高內(nèi)存利用率；2.MyISAM：是mysql5.5版本之前的默認的存儲引擎，它的前身是ISAM，MyISAM管理非事務(wù)表，提供告訴存儲和檢索，以及全文搜索能力，從而受到web開發(fā)者的喜愛；MyISAM引擎創(chuàng)建數(shù)據(jù)庫，將產(chǎn)生3個文件。文件的名字以表名字開始，擴展名之處文件類型：frm文件存儲表定義、數(shù)據(jù)文件的擴展名為.MYD（MYData）、索引文件的擴展名時.MYI（MYIndex）；優(yōu)點：查詢訪問較快，因為讀的時候不加鎖；

支持多種存儲方式：靜態(tài)表，動態(tài)表，壓縮表等；

存儲總行數(shù)；

通過key_buffer_size設(shè)置緩存索引，調(diào)高訪問的性能，減少磁盤I/O的壓力；缺點：寫入效率慢，因為寫的時候會給整張表加鎖；

沒有事務(wù)的概念；

數(shù)據(jù)恢復(fù)起來比較困難；

數(shù)據(jù)寫入過程中阻塞用戶對數(shù)據(jù)的讀取；

數(shù)據(jù)讀取過程中阻塞用戶對數(shù)據(jù)的寫入；生產(chǎn)環(huán)境場景：

公司業(yè)務(wù)不需要事務(wù)的支持；

一般單方面的讀取數(shù)據(jù)的需求較多，或者單方面寫入數(shù)據(jù)的需求較多，不適合雙方面；

服務(wù)器硬件資源相對比較差；總結(jié)：?如果要提供提交、回滾、崩潰恢復(fù)能力的事物安全（ACID兼容）能力，并要求實現(xiàn)并發(fā)控制，InnoDB是一個好的選擇；?如果數(shù)據(jù)表主要用來插入和查詢記錄，則MyISAM引擎能提供較高的處理效率；?如果只是臨時存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存中的Memory引擎，MySQL中使用該引擎作為臨時表，存放查詢的中間結(jié)果；?如果只有INSERT和SELECT操作，可以選擇Archive，Archive支持高并發(fā)的插入操作，但是本身不是事務(wù)安全的。Archive非常適合存儲歸檔數(shù)據(jù)，如記錄日志信息可以使用Archive；使用哪一種引擎需要靈活選擇，一個數(shù)據(jù)庫中多個表可以使用不同引擎以滿足各種性能和實際需求，使用合適的存儲引擎，將會提高整個數(shù)據(jù)庫的性能，在數(shù)據(jù)庫中可以使用命令showengines查看存儲引擎；五、案例：centos7搭建Mysql5.7；案例環(huán)境：系統(tǒng)類型IP地址主機名所需軟件硬件Centos7.4170864bit01my.linuxfanmysql-5.7.12.tar.gzboost_1_59_0.tar.gz內(nèi)存：4GCPU核心：2案例步驟：?下載安裝Mysql軟件程序；?優(yōu)化調(diào)整Mysql程序；?初始化Mysql數(shù)據(jù)庫服務(wù)；?測試連接訪問數(shù)據(jù)庫；?下載安裝Mysql軟件程序；[root@my~]#wget/Downloads/MySQL-5.7/mysql-5.7.12.tar.gz[root@my~]#wget/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download

##需要使用windows下載，不能使用linux下載[root@my~]#yum-yremovemysql-*boost-*[root@my~]#rpm-qamysql[root@my~]#rpm-qaboost[root@my~]#yum-yinstallgccgcc-c++ncursesbisonlibgcryptperlcmake[root@my~]#

mount/dev/cdrom/mnt[root@my~]#

rpm-ivh/mnt/Packages/ncurses-devel-5.9-13.20130511.el7.x86_64.rpm--nodeps[root@my~]#tar-zxvfboost_1_59_0.tar.gz[root@my~]#mvboost_1_59_0/usr/local/boost/[root@my~]#groupaddmysql[root@my~]#useradd-r-gmysqlmysql[root@my~]#tarzxvfmysql-5.7.12.tar.gz-C/usr/src/[root@my~]#cd/usr/src/mysql-5.7.12/

##編譯多次時，需要刪除CMAKE的緩存，rm-rf/usr/src/mysql-5.7.12/CMakeCache.txt[root@mymysql-5.7.12]#cmake.-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_DATADIR=/usr/local/mysql/data\-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci\-DMYSQL_TCP_PORT=3306-DMYSQL_USER=mysql-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_MEMORY_STORAGE_ENGINE=1-DENABLE_DOWNLOADS=1-DDOWNLOAD_BOOST=1-DWITH_BOOST=/usr/local/boost

-DSYSCONFDIR=/etc注釋：-DCMAKE_INSTALL_PREFIX=/usr/local/mysql

[MySQL安裝的根目錄]-DMYSQL_DATADIR=/mydata/mysql/data

[MySQL數(shù)據(jù)庫文件存放目錄]-DDEFAULT_CHARSET=utf8

[設(shè)置默認字符集為utf8]-DDEFAULT_COLLATION=utf8_general_ci

[設(shè)置默認字符校對]-DMYSQL_TCP_PORT=3306

[MySQL的監(jiān)聽端口]-DMYSQL_USER=mysql

[MySQL用戶名]

-DWITH_MYISAM_STORAGE_ENGINE=1

[安裝MySQL的myisam數(shù)據(jù)庫引擎]-DWITH_INNOBASE_STORAGE_ENGINE=1

[安裝MySQL的innodb數(shù)據(jù)庫引擎]-DWITH_ARCHIVE_STORAGE_ENGINE=1

[安裝MySQL的archive數(shù)據(jù)庫引擎]-DWITH_BLACKHOLE_STORAGE_ENGINE=1

[安裝MySQL的blackhole數(shù)據(jù)庫引擎]-DWITH_MEMORY_STORAGE_ENGINE=1

[安裝MySQL的memory數(shù)據(jù)庫引擎]-DENABLE_DOWNLOADS=1

[編譯時允許自主下載相關(guān)文件]-DDOWNLOAD_BOOST=1

[允許下載BOOST]-DWITH_BOOST=/usr/local/boost

[指定系統(tǒng)中存在的BOOST]-DSYSCONFDIR=/etc

[MySQL配置文件所在目錄]-DWITH_READLINE=1

[MySQL的readlinelibrary]-DMYSQL_UNIX_ADDR=/var/run/mysql/mysql.sock

[MySQL的通訊目錄]-DENABLED_LOCAL_INFILE=1

[啟用加載本地數(shù)據(jù)]-DWITH_PARTITION_STORAGE_ENGINE=1

[啟動mysql的分區(qū)存儲結(jié)構(gòu)]-DEXTRA_CHARSETS=all

[使MySQL支持所有的擴展字符]-DWITH_DEBUG=0

[禁用調(diào)試模式]-DMYSQL_MAINTAINER_MODE=0-DWITH_SSL:STRING=bundled

[通訊時支持ssl協(xié)議]-DWITH_ZLIB:STRING=bundled

[允許使用zliblibrary][root@my~]#make-j`cat/proc/cpuinfo|grepprocessor|wc-l`[root@my~]#makeinstall[root@my~]#ls/usr/local/mysqlbin

COPYING

data

docs

include

lib

logs

man

mysql-test

README

share

support-files?優(yōu)化調(diào)整Mysql程序；[root@my~]#chown-Rmysql:mysql/usr/local/mysql[root@my~]#cp/usr/src/mysql-5.7.12/support-files/my-defaultf/etc/myf[root@my~]#cp/usr/src/mysql-5.7.12/support-files/mysql.server/etc/init.d/[root@my~]#chmod+x/etc/init.d/mysql.server[root@my~]#cat<>/usr/lib/systemd/system/mysqld.service

##編寫服務(wù)控制腳本[Unit]Description=mysqldapiAfter=network.target[Service]Type=forkingPIDFile=/usr/local/mysql/logs/mysqld.pidExecStart=/etc/init.d/mysql.serverstartExecReload=/etc/init.d/mysql.serverrestartExecStop=/etc/init.d/mysql.serverstopPrivateTmp=Flase[Install]WantedBy=multi-user.targetEND注解：PrivateTmp=Flase

##此配置必須關(guān)閉，不然mysql連接文件mysql.sock文件會默認生成在以下位置/tmp/systemd-private-83bba738e8ff4837b5ae657eff983821-mysqld.service-BPxWpJ/tmp/mysql.sock，導(dǎo)致數(shù)據(jù)庫無法連接，將此配置項關(guān)閉后，則文件正常生成在/tmp/mysql.sock[root@my~]#echo"exportPATH=$PATH:/usr/local/mysql/bin/">>/etc/profile[root@my~]#source/etc/profile?初始化Mysql數(shù)據(jù)庫服務(wù)；[root@my~]#

mysqld--initialize-insecure--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data注解：--initialize-insecure

##禁用mysql的密碼策略（密碼復(fù)雜性等），--initializeaize是開啟密碼策略，自動生成密碼在mysqld.log文件中--user=mysql

##運行的賬戶--basedir=/usr/local/mysql

##mysql的安裝位置--datadir=/usr/local/mysql/data

##mysql數(shù)據(jù)庫服務(wù)數(shù)據(jù)的物理存放路徑[root@my~]#cat</etc/myf[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEScharacter_set_server=utf8init_connect='SETNAMESutf8'log-error=/usr/local/mysql/logs/mysqld.logpid-file=/usr/local/mysql/logs/mysqld.pidEND注解：[mysqld]

##聲明區(qū)域basedir=/usr/local/mysql

##mysql的安裝位置datadir=/usr/local/mysql/data

##mysql的物理文件存放位置port=3306

##mysql服務(wù)監(jiān)聽的端口sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

##mysql的模式character_set_server=utf8

##字符集init_connect='SETNAMESutf8'

log-error=/usr/local/mysql/logs/mysqld.log

##指定日志文件位置pid-file=/usr/local/mysql/logs/mysqld.pid

##指定運行服務(wù)所產(chǎn)生的pid文件位置[root@my~]#mkdir/usr/local/mysql/logs[root@my~]#chownmysql:mysql/usr/local/mysql/logs/[root@my~]#systemctlstartmysqld[root@my

~]#systemctlenablemysqldCreatedsymlinkfrom/etc/systemd/system/multi-user.target.wants/mysqld.serviceto/usr/lib/systemd/system/mysqld.service.[root@my~]#netstat-utpln|grepmysqldtcp

0

0:3306

:*

LISTEN

2885/mysqld?測試連接訪問數(shù)據(jù)庫；[root@my~]#mysql

##登錄mysql設(shè)置密碼mysql>alteruser'root'@'localhost'identifiedby'123123';mysql>exit[root@my~]#mysql-uroot-p123123mysql>

exit六、Sql語句操作之增刪改查；Sql語句分類：?DDL：數(shù)據(jù)定義語言，用來建立數(shù)據(jù)庫，數(shù)據(jù)對象和定義其列，如create、alter、drop；?DML：數(shù)據(jù)操縱語言，用來查詢、插入、刪除、修改數(shù)據(jù)庫中的數(shù)據(jù)，如select、insert、update、delete；?DCL：數(shù)據(jù)控制語言，用來控制數(shù)據(jù)庫組件的存取許可，存取權(quán)限等，如commit、rollback、grant、revoke；數(shù)據(jù)記錄類型：日期：小數(shù)類型：字符串：查看數(shù)據(jù)庫：mysql>

show

databases;

##查看數(shù)據(jù)庫++|Database

|++|information_schema||haha

||mysql

||performance_schema||sys

|++mysql>

usemysql;

##進入數(shù)據(jù)庫mysqlDatabasechangedmysql>

showtables;

##查看表...mysql>

descuser;

##查看表的結(jié)構(gòu)，表頭查看數(shù)據(jù)記錄：mysql>

select*from

user\G;

##查詢user表中的所有數(shù)據(jù)記錄，按列顯示mysql>

selecthost,userfromuser;

##指定user表的字段進行查詢+++|host

|user

|+++|localhost|mysql.sys||localhost|root

|+++mysql>

deletefrommysql.userwhereuser="";

##刪除用戶為空的數(shù)據(jù)記錄創(chuàng)建數(shù)據(jù)庫：mysql>

createdatabaseauth;

##創(chuàng)建數(shù)據(jù)庫authQueryOK,1rowaffected(0.00sec)mysql>showdatabases;++|Database

|++|information_schema||

auth

||haha

||mysql

||performance_schema||sys

|++6rowsinset(0.00sec)創(chuàng)建表：mysql>

createtableauth.users(user_namechar(16)notnull,user_passwdchar(48)default'',primarykey(user_name));

##創(chuàng)建表auth.usersQueryOK,0rowsaffected(0.00sec)mysql>

descusers;

##查看表的結(jié)構(gòu)+++++++|Field

|Type

|Null|Key|Default|Extra|+++++++|user_name

|char(16)|NO

|PRI|NULL

|

||user_passwd|char(48)|YES

|

|

|

|+++++++創(chuàng)建數(shù)據(jù)記錄：mysql>

insertintoauth.usersvalues('hehe','pwd@123');

##新增數(shù)據(jù)記錄QueryOK,1rowaffected(0.00sec)mysql>select*fromauth.users;

##查看表的數(shù)據(jù)記錄+++|user_name|user_passwd|+++|hehe

|pwd@123

|+++1rowinset(0.00sec)更新數(shù)據(jù)記錄：mysql>updateauth.userssetuser_name="haha"whereuser_name="hehe";

##更新數(shù)據(jù)記錄

QueryOK,1rowaffected(0.00sec)mysql>select*fromauth.users;+++|user_name|user_passwd|+++|haha

|pwd@123

|+++1rowinset(0.00sec)新增數(shù)據(jù)結(jié)構(gòu)（列）：mysql>altertableauth.usersadd(user_classchar(16));

##新增數(shù)據(jù)結(jié)構(gòu)（列）QueryOK,1rowaffected(0.01sec)mysql>select*fromauth.users;++++|user_name|user_passwd|user_class|++++|haha

|pwd@123

|NULL

|++++1rowinset(0.00sec)更新數(shù)據(jù)結(jié)構(gòu)的名稱（更新列名，注：當此列為空值）：mysql>altertableauth.userschangeuser_classuser_dbinteger;

##更新數(shù)據(jù)結(jié)構(gòu)的名稱（更新列名，注：當此列為空值）QueryOK,1rowaffected(0.01sec)mysql>select*fromauth.users;++++|user_name|user_passwd|user_db|++++|haha

|pwd@123

|

NULL|++++1rowinset(0.00sec)更新數(shù)據(jù)記錄：mysql>updateauth.userssetuser_db="1"whereuser_name="haha";

##更新數(shù)據(jù)記錄QueryOK,1rowaffected(0.00sec)mysql>select*fromauth.users;++++|user_name|user_passwd|user_db

|++++|haha

|pwd@123

|1

|++++1rowinset(0.00sec)刪除數(shù)據(jù)結(jié)構(gòu)（列）：mysql>altertableauth.usersdropuser_db;

##刪除數(shù)據(jù)結(jié)構(gòu)（列）QueryOK,1rowaffected(0.00sec)mysql>select*fromauth.users;+++|user_name|user_passwd|+++|haha

|pwd@123

|+++1rowinset(0.00sec)更新數(shù)據(jù)結(jié)構(gòu)的名稱（更新列名，注：當此列內(nèi)已經(jīng)存在現(xiàn)有值）：mysql>altertableuserschangeuser_nameuser_dbchar(16);

####更新數(shù)據(jù)結(jié)構(gòu)的名稱（更新列名，注：當此列內(nèi)已經(jīng)存在現(xiàn)有值）QueryOK,0rowsaffected(0.00sec)mysql>

select*fromusers;+++|user_db

|user_passwd|+++|haha

|pwd@123

|+++1rowinset(0.00sec)更新數(shù)據(jù)結(jié)構(gòu)（列）的數(shù)據(jù)類型：mysql>descauth.users;

##查看更改前的數(shù)據(jù)結(jié)構(gòu)+++++++|Field

|Type

|Null|Key|Default|Extra|+++++++|user_name

|char(16)|NO

|PRI|NULL

|

||user_passwd|char(48)|YES

|

|

|

||user_db

|int(11)

|YES

|

|NULL

|

|+++++++mysql>altertableauth.usersmodifyuser_namechar(48);

##更改第一列的數(shù)據(jù)庫結(jié)構(gòu)為char（48）QueryOK,1rowaffected(0.00sec)mysql>descauth.users;

##查看更改后的數(shù)據(jù)結(jié)構(gòu)+++++++|Field

|Type

|Null|Key|Default|Extra|+++++++|user_name

|char(48)|NO

|PRI|NULL

|

||user_passwd|char(48)|YES

|

|

|

||user_db

|int(11)

|YES

|

|NULL

|

|+++++++刪除表和庫：mysql>

droptableauth.users;

##刪除表usersQueryOK,0rowsaffected(0.00sec)

mysql>

dropdatabaseauth;

##刪除庫authQueryOK,0rowsaffected(0.00sec)七、Sql語句操作之授權(quán)；權(quán)限列表：語法：grantallon庫.*to用戶@客戶機地址identifiedby'密碼’;

##授權(quán)語法showgrantsfor用戶@客戶機地址；

##查看權(quán)限r(nóng)evoke權(quán)限列表on

庫.*from用戶@客戶機地址;

##取消授權(quán)flushprivileges;

##刷新權(quán)限特殊表示：%表示所有地址；192.168.100.%表示整個網(wǎng)段；八、Mysql基本優(yōu)化操作；1.忘記密碼：[root@my~]#vi/etc/myf[mysqld]skip-grant-tables

##添加該行，跳過密碼驗證:wq[root@my~]#

systemctlrestartmysqld[root@my~]#

mysql

##登錄后操作updatemysql.usersetpassword=password("123123")whereuser="root";

##修改root密碼exit[root@my~]#vi/etc/myf[mysqld]#skip-grant-tables

##注釋該行:wq[root@my~]#

systemctlrestartmysqld2.解決mysql亂碼問題：[root@my~]#

vi/et/myf[client]default-character-set=utf8[root@my~]#

systemctlrestartmysqld九、Mysql存儲引擎的應(yīng)用；1.查看數(shù)據(jù)庫可以配置的存儲引擎類型：2.查看表正在使用的存儲引擎：3.

創(chuàng)建表并且指定存儲引擎；4.修改已經(jīng)存在表的存儲引擎：5.修改Mysql服務(wù)的默認存儲引擎：[root@my~]#vi/etc/myf

##新添配置項

default-storage-engine=MyISAM[root@my~]#systemctlrestartmysqld[root@my~]#mysql-uroot-p123123十、Mysql配置文件詳解：[root@my~]#

vi/etc/myf[mysqld]########basicsettings########basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEScharacter_set_server=utf8init_connect='SETNAMESutf8'log-error=/usr/local/mysql/logs/mysqld.logpid-file=/usr/local/mysql/logs/mysqld.piduser=mysqlsymbolic-links=0server-id=11#bind_address=01autocommit=1skip_name_resolve=1max_connections=800max_connect_errors=100transaction_isolation=READ-COMMITTEDexplicit_defaults_for_timestamp=1join_buffer_size=128Mtmp_table_size=128Mtmpdir=/dev/shmmax_allowed_packet=16Minteractive_timeout=60wait_timeout=60read_buffer_size=16Mread_rnd_buffer_size=32Msort_buffer_size=32M########logsettings########slow_query_log=1slow_query_log_file=/usr/local/mysql/logs/mysql-slow.loglog_queries_not_using_indexes=1log_slow_admin_statements=1log_slow_slave_statements=1log_throttle_queries_not_using_indexes=10expire_logs_days=90long_query_time=1min_examined_row_limit=100########replicationsettings#########master_info_repository=TABLE#relay_log_info_repository=TABLElog_bin=/usr/local/mysql/logs/mysql-bin#sync_binlog=4gtid_mode=onenforce_gtid_consistency=1#log_slave_updatesbinlog_format=row#relay_log=/usr/local/mysql/logs/mysql-relay.log#relay_log_recovery=1#binlog_gtid_simple_recovery=1#slave_skip_errors=ddl_exist_errors########innodbsettings########innodb_page_size=16Kinnodb_buffer_pool_size=4G#innodb_buffer_pool_instances=8#innodb_buffer_pool_load_at_startup=1#innodb_buffer_pool_dump_at_shutdown=1#innodb_lru_scan_depth=2000innodb_lock_wait_timeout=5#innodb_io_capacity=4000#innodb_io_capacity_max=8000#innodb_flush_method=O_DIRECT#innodb_log_group_home_dir=/usr/local/mysql/logs/redolog/#innodb_undo_directory=/usr/local/mysql/logs/undolog/#innodb_undo_logs=128#innodb_undo_tablespaces=0#innodb_flush_neighbors=1#innodb_log_file_size=4G#innodb_log_buffer_size=16M#innodb_purge_threads=4innodb_large_prefix=1innodb_thread_concurrency=64#innodb_print_all_deadlocks=1#innodb_strict_mode=1innodb_sort_buffer_size=64M########semisyncreplicationsettings#########plugin_dir=/usr/local/mysql/lib/plugin#plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#loose_rpl_semi_sync_master_enabled=1#loose_rpl_semi_sync_slave_enabled=1#loose_rpl_semi_sync_master_timeout=5000[mysqld-5.7]#innodb_buffer_pool_dump_pct=40innodb_page_cleaners=4#innodb_undo_log_truncate=1#innodb_max_undo_log_size=2G#innodb_purge_rseg_truncate_frequency=128#binlog_gtid_simple_recovery=1log_timestamps=system#transaction_write_set_extraction=MURMUR32#show_compatibility_56=on注解：[mysqld]user=mysqlmysql以什么用戶運行port=31306mysql運行在哪個端口datadir=/data/var/mysql/mysql的數(shù)據(jù)目錄socket=/data/var/mysql/mysql.sockmysql以socket方式運行的sock文件位置symbolic-links=0是否支持符號鏈接，即數(shù)據(jù)庫或表可以存儲在myf中指定datadir之外的分區(qū)或目錄，為0不開啟########basicsettings########server-id=11mysql的服務(wù)器分配id，在啟用主從和集群的時候必須指定，每個節(jié)點必須不同#bind_address=2mysql監(jiān)聽的ip地址，如果是，表示僅本機訪問autocommit=1數(shù)據(jù)修改是否自動提交，為0不自動提交character_set_server=utf8mb4服務(wù)器使用的字符集skip_name_resolve=1禁用DNS主機名查找，啟用以后用內(nèi)網(wǎng)地址向mysqlslap請求響應(yīng)快了一半max_connections=800mysql最大連接數(shù)max_connect_errors=1000某臺host連接錯誤次數(shù)等于max_connect_errors（默認10），主機'host_name'再次嘗試時被屏蔽?？捎行Х吹姆乐筪os攻擊transaction_isolation=READ-COMMITTED數(shù)據(jù)庫事務(wù)隔離級別1.READ-UNCOMMITTED(讀取未提交內(nèi)容)級別

2.READ-COMMITTED（讀取提交內(nèi)容）

3.REPEATABLE-READ(可重讀)

4.SERIERLIZED(可串行化)

默認級別REPEATABLE-READexplicit_defaults_for_timestamp=1mysql中TIMESTAMP類型和其他的類型有點不一樣(在沒有設(shè)置explicit_defaults_for_timestamp=1的情況下）join_buffer_size=128M當我們的join是ALL,index,rang或者Index_merge的時候使用的buffer。實際上這種join被稱為FULLJOINtmp_table_size=128M規(guī)定了內(nèi)部內(nèi)存臨時表的最大值，每個線程都要分配。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果內(nèi)存臨時表超出了限制，MySQL就會自動地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下tmpdir=/dev/shm/mysql-tmp/保存臨時文件的目錄max_allowed_packet=16Mmysql最大接受的數(shù)據(jù)包大小sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"sql_mode模式，定義了你MySQL應(yīng)該支持的sql語法，對數(shù)據(jù)的校驗等等，限制一些所謂的'不合法’的操作interactive_timeout=60服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端wait_timeout=60服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)，在線程啟動時，根據(jù)全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，取決于客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義)read_buffer_size=16M讀入緩沖區(qū)的大小，將對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySQL會為它分配一段內(nèi)存緩沖區(qū)read_rnd_buffer_size=32M隨機讀緩沖區(qū)大小，當按任意順序讀取行時（列如按照排序順序）將分配一個隨機讀取緩沖區(qū)，進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度sort_buffer_size=32M是一個connection級參數(shù)，在每個connection第一次需要使用這個buffer的時候，一次性分配設(shè)置的內(nèi)存########logsettings#########log_error=/data/local/mysql-5.7.19/log/mysql-error.log錯誤日志位置slow_query_log=1是否開啟慢查詢?nèi)罩臼占痵low_query_log_file=/data/local/mysql-5.7.19/log/mysql-slow.log慢查詢?nèi)罩疚恢胠og_queries_not_using_indexes=1是否記錄未使用索引的語句log_slow_admin_statements=1慢查詢也記錄那些慢的optimizetable，analyzetable和altertable語句log_slow_slave_statements=1記錄由Slave所產(chǎn)生的慢查詢log_throttle_queries_not_using_indexes=10設(shè)定每分鐘記錄到日志的未使用索引的語句數(shù)目，超過這個數(shù)目后只記錄語句數(shù)量和花費的總時間expire_logs_days=90日志自動過期清理天數(shù)long_query_time=1設(shè)置記錄慢查詢超時時間min_examined_row_limit=100查詢檢查返回少于該參數(shù)指定行的SQL不被記錄到慢查詢?nèi)罩?#######replicationsettings#########master_info_repository=TABLE從機保存主節(jié)點信息方式，設(shè)成file時會生成和2個文件，設(shè)成table，信息就會存在mysql.master_slave_info表中。不管是設(shè)置的哪種值，都不要移動或者編輯相關(guān)的文件和表#relay_log_info_repository=TABLE用于保存slave讀取relaylog的位置信息，可選值為“FILE”、“TABLE”，以便crash重啟后繼續(xù)恢復(fù)log_bin=/data/local/mysql-5.7.19/log/mysql-binbinlog的保存位置，不能指定確定的文件名如mysql-bin.log，只能指定位置和前綴，會生成以前綴為開頭的一系列文件#sync_binlog=4這個參數(shù)是對于MySQL系統(tǒng)來說是至關(guān)重要的，他不僅影響到Binlog對MySQL所帶來的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：sync_binlog=0，當事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時候來做同步，或者cache滿了之后才同步到磁盤。sync_binlog=n，當每進行n次事務(wù)提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強制寫入磁盤。在MySQL中系統(tǒng)默認的設(shè)置是sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最好的，但是風險也是最大的。因為一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失。而當設(shè)置為“1”的時候，是最安全但是性能損耗最大的設(shè)置。因為當設(shè)置為1的時候，即使系統(tǒng)Crash，也最多丟失binlog_cache中未完成的一個事務(wù)，對實際數(shù)據(jù)沒有任何實質(zhì)性影響。從以往經(jīng)驗和相關(guān)測試來看，對于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性能差距可能高達5倍甚至更多。gtid_mode=on啟用gtid類型，否則就是普通的復(fù)制架構(gòu)enforce_gtid_consistency=1強制GTID的一致性#log_slave_updatesslave更新是否記入日志，在做雙主架構(gòu)時異常重要，影響到雙主架構(gòu)是否能互相同步binlog_format=rowbinlog日志格式，可選值“MIXED”、“ROW”、“STATEMENT”，在5.6版本之前默認為“STATEMENT”，5.6之后默認為“MIXED”；因為“STATEMENT”方式在處理一些“不確定”性的方法時會造成數(shù)據(jù)不一致問題，我們建議使用“MIXED”或者“ROW”#relay_log=/data/local/mysql-5.7.19/log/mysql-relay.log從機保存同步中繼日志的位置#relay_log_recovery=1當slave從庫宕機后，假如relay-log損壞了，導(dǎo)致一部分中繼日志沒有處理，則自動放棄所有未執(zhí)行的relay-log，并且重新從master上獲取日志，這樣就保證了relay-log的完整性#binlog_gtid_simple_recovery=1這個參數(shù)控制了當mysql啟動或重啟時，mysql在搜尋GTIDs時是如何迭代使用binlog文件的。這個選項設(shè)置為真，會提升mysql執(zhí)行恢復(fù)的性能。因為這樣mysql-server啟動和binlog日志清理更快#slave_skip_errors=ddl_exist_errors跳過指定errorno類型的錯誤，設(shè)成all跳過所有錯誤########innodbsettings########innodb_page_size=16Kinnodb每個數(shù)據(jù)頁大小，這個參數(shù)在一開始初始化時就要加入myf里，如果已經(jīng)創(chuàng)建了表，再修改，啟動MySQL會報錯innodb_buffer_pool_size=4G緩存innodb表的索引，數(shù)據(jù)，插入數(shù)據(jù)時的緩沖，專用mysql服務(wù)器設(shè)置的大小：操作系統(tǒng)內(nèi)存的70%-80%最佳#innodb_buffer_pool_instances=8可以開啟多個內(nèi)存緩沖池，把需要緩沖的數(shù)據(jù)hash到不同的緩沖池中，這樣可以并行的內(nèi)存讀寫#innodb_buffer_pool_load_at_startup=1默認為關(guān)閉OFF。如果開啟該參數(shù)，啟動MySQL服務(wù)時，MySQL將本地熱數(shù)據(jù)加載到InnoDB緩沖池中#innodb_buffer_pool_dump_at_shutdown=1默認為關(guān)閉OFF。如果開啟該參數(shù)，停止MySQL服務(wù)時，InnoDB將InnoDB緩沖池中的熱數(shù)據(jù)保存到本地硬盤#innodb_lru_scan_depth=2000根據(jù)官方文檔描述，它會影響pagecleaner線程每次刷臟頁的數(shù)量，這是一個每1秒loop一次的線程innodb_lock_wait_timeout=5事務(wù)等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會返回應(yīng)用失??；參數(shù)的時間單位是秒#innodb_io_capacity=4000

#innodb_io_capacity_max=8000這兩個設(shè)置會影響InnoDB每秒在后臺執(zhí)行多少操作.大多數(shù)寫IO(除了寫InnoDB日志)是后臺操作的.如果你深度了解硬件性能(如每秒可以執(zhí)行多少次IO操作),則使用這些功能是很可取的,而不是讓它閑著#innodb_flush_method=O_DIRECT默認值為fdatasync.如果使用硬件RAID磁盤控制器,可能需要設(shè)置為O_DIRECT.這在讀取InnoDB緩沖池時可防止“雙緩沖(doublebuffering)”效應(yīng),否則會在文件系統(tǒng)緩存與InnoDB緩存間形成2個副本(copy).如果不使用硬件RAID控制器,或者使用SAN存儲時,O_DIRECT可能會導(dǎo)致性能下降#innodb_log_group_home_dir=/data/local/mysql-5.7.19/log/redolog/innodb重做日志保存目錄#innodb_undo_directory=/data/local/mysql-5.7.19/log/undolog/innodb回滾日志保存目錄#innodb_undo_logs=128undo回滾段的數(shù)量，至少大于等于35，默認128#innodb_undo_tablespaces=0用于設(shè)定創(chuàng)建的undo表空間的個數(shù)，在mysql_install_db時初始化后，就再也不能被改動了；默認值為0，表示不獨立設(shè)置undo的tablespace，默認記錄到ibdata中；否則，則在undo目錄下創(chuàng)建這么多個undo文件，例如假定設(shè)置該值為4，那么就會創(chuàng)建命名為undo001~undo004的undotablespace文件，每個文件的默認大小為10M。修改該值會導(dǎo)致Innodb無法完成初始化，數(shù)據(jù)庫無法啟動，但是另兩個參數(shù)可以修改#innodb_flush_neighbors=1InnoDB存儲引擎在刷新一個臟頁時，會檢測該頁所在區(qū)(extent)的所有頁，如果是臟頁，那么一起刷新。這樣做的好處是通過AIO可以將多個IO寫操作合并為一個IO操作。對于傳統(tǒng)機械硬盤建議使用，而對于固態(tài)硬盤可以關(guān)閉。#innodb_log_file_size=4G這個值定義了日志文件的大小，innodb日志文件的作用是用來保存redo日志。一個事務(wù)對于數(shù)據(jù)或索引的修改往往對應(yīng)到表空間中的隨機的位置，