標(biāo)簽（空格分隔）： Linux 運(yùn)維 MySQL

sql文件hellodb.sql

架構(gòu)

MySQL被設(shè)計(jì)為一個(gè)單進(jìn)程多線程架構(gòu)的數(shù)據(jù)庫.其數(shù)據(jù)庫實(shí)例在系統(tǒng)上的表現(xiàn)就是一個(gè)進(jìn)程.

introduction-to-mysql-14-728.jpg

這張圖大致將MySQL分為三層(模型)從上到下依次為:
? 視圖層: 即Connectors
? 邏輯層: Pluggable Storage Engines以上部分
? 物理層: Pluggable Storage Engines及以下部分即都是文件形式(插件式存儲(chǔ)引擎)
具體說明如下

? Connectors指的是不同語言中與SQL的交互
? Management Serveices & Utilities： 系統(tǒng)管理和控制工具
? Connection Pool: 連接池。
  管理緩沖用戶連接，線程處理等需要緩存的需求
? SQL Interface: SQL接口。
  接受用戶的SQL命令，并且返回用戶需要查詢的結(jié)果。比如select     from就是調(diào)用SQL Interface
? Parser: 解析器。
  SQL命令傳遞到解析器的時(shí)候會(huì)被解析器驗(yàn)證和解析。解析器是由Lex和YACC實(shí)現(xiàn)的，是一個(gè)很長的腳本。
 主要功能：
     A. 將SQL語句分解成數(shù)據(jù)結(jié)構(gòu)，并將這個(gè)結(jié)構(gòu)傳遞到后續(xù)步驟，以后SQL語句的傳遞和處理就是基于這個(gè)結(jié)構(gòu)的 
     B. 如果在分解構(gòu)成中遇到錯(cuò)誤，那么就說明這個(gè)sql語句是不合理的
? Optimizer: 查詢優(yōu)化器。
  SQL語句在查詢之前會(huì)使用查詢優(yōu)化器對查詢進(jìn)行優(yōu)化。他使用的  是“選取-投影-聯(lián)接”策略進(jìn)行查詢。
  用一個(gè)例子就可以理解： select uid,name from user where gender = 1;
  這個(gè)select 查詢先根據(jù)where 語句進(jìn)行選取，而不是先將表全部查詢  出來以后再進(jìn)行g(shù)ender過濾
  這個(gè)select查詢先根據(jù)uid和name進(jìn)行屬性投影，而不是將屬性全部取  出以后再進(jìn)行過濾
  將這兩個(gè)查詢條件聯(lián)接起來生成最終查詢結(jié)果
? Cache和Buffer： 查詢緩存。
  如果查詢緩存有命中的查詢結(jié)果，查詢語句就可以直接去查詢緩存中取數(shù)據(jù)。
  這個(gè)緩存機(jī)制是由一系列小緩存組成的。比如表緩存，記錄緩存，key  緩存，權(quán)限緩存等
? Engine ：存儲(chǔ)引擎。
  存儲(chǔ)引擎是MySql中具體的與文件打交道的子系統(tǒng)。也是Mysql最具有特色的一個(gè)地方。
  Mysql的存儲(chǔ)引擎是插件式的。它根據(jù)MySql AB公司提供的文件訪問層的一個(gè)抽象接口來定制一種文件訪問機(jī)制（這種訪問機(jī)制就叫存儲(chǔ)引擎）
  現(xiàn)在有很多種存儲(chǔ)引擎，各個(gè)存儲(chǔ)引擎的優(yōu)勢各不一樣，最常用的  MyISAM,InnoDB,BDB
  默認(rèn)下MySql是使用MyISAM引擎，它查詢速度快，有較好的索引優(yōu)化和數(shù)據(jù)壓縮技術(shù)。但是它不支持事務(wù)。
  InnoDB支持事務(wù)，并且提供行級(jí)的鎖定，應(yīng)用也相當(dāng)廣泛。 Mysql也支持自己定制存儲(chǔ)引擎，甚至一個(gè)庫中不同的表使用不同的存儲(chǔ)引擎，這些都是允許的。

執(zhí)行流程

Mysql執(zhí)行流程

配置文件

安裝MySQL或MariaDB后執(zhí)行# my_print_defaults命令可以查看其配置文件加載順序,CentOS7中如下:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
越靠后的文件的優(yōu)先級(jí)越高

數(shù)據(jù)類型

整數(shù)

整數(shù)

浮點(diǎn)數(shù)

浮點(diǎn)數(shù)

日期時(shí)間

日期與時(shí)間

字符串

字符串

文本

Text

二進(jìn)制

二進(jìn)制類型1

二進(jìn)制類型2

ENUM類型

即枚舉類型.使用格式: FIELD_NAME enum('VALUE1', 'VALUE2', ...)
枚舉類型的取值范圍以列表的形式指定了,而且其值只能取列表中的一個(gè)元素,列表最多有65535個(gè).同set一樣其數(shù)據(jù)都不是直接將數(shù)據(jù)存入數(shù)據(jù)庫,而是將其列表中的編號(hào)存入數(shù)據(jù)庫.

SET類型

在創(chuàng)建表時(shí),set類型的取值范圍就以列表的形式指定了.使用格式:FIELD_NAME set('VALUE1','VALUE2',...);set類型最多只能有64個(gè)元素

字符集

mysql> show character set;

事務(wù)

事務(wù)就是一組原子性的查詢語句,也即將多個(gè)查詢當(dāng)作一個(gè)獨(dú)立的工作單元.

• A: 原子性
• C: 一致性
• I: 隔離性
• D: 持久性

事務(wù)常用的命令,需要關(guān)閉Innodb的autocommit功能
mysql> start transaction;                 # 啟動(dòng)事務(wù)
mysql> savepoint IDENTIFIER;              # 設(shè)置存檔
mysql> rollback | rollback to IDENTIFIER; # 回滾[到指定存檔]
mysql> commint                            # 提交事務(wù)

隔離級(jí)別

mysql> show global variables like 'tx_isolation';查看事務(wù)級(jí)別

① 讀未提交: read uncommitted # 2個(gè)此事務(wù),其中一個(gè)對同一數(shù)據(jù)的修改,對方即時(shí)能看到變化
② 讀提交:   read committed   # 2個(gè)此事務(wù)對同一數(shù)據(jù)的操作,只有一方提交后對方才能看到變化
③ 可重讀:   repeatable read  # mysql默認(rèn)為此隔離級(jí)別,2個(gè)此事務(wù)對同一數(shù)據(jù)的操作,每個(gè)事務(wù)相當(dāng)于有各自一份獨(dú)立的此數(shù)據(jù)的復(fù)本(快照),只有當(dāng)2個(gè)此事務(wù)都commit時(shí),2個(gè)事務(wù)對數(shù)據(jù)的操作會(huì)進(jìn)行匯總
④ 串行化:   serializable,一個(gè)事務(wù)對數(shù)據(jù)的操作時(shí),任何別的事務(wù)都無法對些數(shù)據(jù)同時(shí)進(jìn)行操作

存儲(chǔ)引擎

MySQL為插件式存儲(chǔ)引擎,執(zhí)行
mysql> show engines;可查看其支持的引擎類型
mysql> show variables like 'storage_engine' # 查看默認(rèn)的存儲(chǔ)引擎
mysql> show table status like 'TABNAME';查看某張表的屬性,包括存儲(chǔ)引擎
MySQL存儲(chǔ)引擎為表類型的,即每個(gè)表都可選擇不同的存儲(chǔ)引擎.

InnoDB

InnoDB存儲(chǔ)引擎支持事務(wù),其設(shè)計(jì)目標(biāo)主要面向在線事務(wù)處理(OLTP)的應(yīng)用.其特點(diǎn)是行鎖設(shè)計(jì) 支持外鍵,并支持類似于Oracle的非鎖定讀,即默認(rèn)讀取操作不會(huì)產(chǎn)生鎖.MySQL5.5.8版本開始,InnoDB是默認(rèn)的存儲(chǔ)引擎.
.frm 表結(jié)構(gòu)
.ibd 表空間(數(shù)據(jù)與索引)

InnoDB特點(diǎn)
1.默認(rèn)InnoDB表的數(shù)據(jù)和索引存儲(chǔ)于同一個(gè)表空間中.即datadir目錄中的ibdata1, ibdata2, ... 表結(jié)構(gòu)文件也在此目錄中,名為tbl_name.frm
2.事務(wù)型,適合對事務(wù)要求較高的場景中,適用于處理大量短期事務(wù)
3.支持高并發(fā),4個(gè)隔離級(jí)別(默認(rèn)為可重讀),使用間隙鎖以防止幻讀

MyISAM

1.支持全文索引,占用空間小,處理速度快
2.不支持事務(wù),不支持外鍵
3.鎖粒度為表級(jí)鎖

4.崩潰無法保證表安全恢復(fù)
5.其緩沖池只緩存索引文件,而不緩沖數(shù)據(jù)文件.

適用場景: 只讀或讀多寫少 較小的表
每個(gè)表有3個(gè)文件,存儲(chǔ)于數(shù)據(jù)庫目錄中
tbl_name.frm: 定義表結(jié)構(gòu)
tbl_name.MYD: 數(shù)據(jù)文件
tbl_name.MYI: 索引文件

? MEMORY: 使用存儲(chǔ)在內(nèi)在中的內(nèi)容來創(chuàng)建表,所有數(shù)據(jù)放在內(nèi)存中.一個(gè)表對應(yīng)一個(gè)與表名相同類型為frm的文件.該文件只存儲(chǔ)表的結(jié)構(gòu)而數(shù)據(jù)文件是存儲(chǔ)在內(nèi)存中,這樣有利于對數(shù)據(jù)的快速處理.

設(shè)置MySQL默認(rèn)存儲(chǔ)引擎:

set @@session.storage_engine='InnoDB'    # 臨時(shí)修改

vim /etc/my.cnf                          # 配置文件修改重啟后永久生效
    default-storage-engine= MyISAM|InnoDB|...
重啟mysql服務(wù)

范式

① 第一范式: 數(shù)據(jù)庫表中不能有重復(fù)字段
② 第二范式: 有主鍵,非主鍵字段依賴主鍵(唯一性,一個(gè)表只說明一個(gè)事物)
③ 第三范式: 非主鍵字段不能相互依賴
如果一個(gè)數(shù)據(jù)庫能夠滿足第三范式的要求,數(shù)據(jù)庫中基本上沒有冗余的內(nèi)容了.但要記住滿足范式不是我們的目標(biāo),優(yōu)化設(shè)計(jì)提高整個(gè)系統(tǒng)的性能才是我們的目標(biāo).

約束

? 主鍵約束: 主鍵是唯一且非空的,一張表只能有一個(gè)
? 惟一鍵約束: 可以為空,一張表可以存在多個(gè)
? 外鍵約束: 字段數(shù)據(jù)受另一張表主鍵約束
? 檢查性約束: 數(shù)據(jù)類型對數(shù)據(jù)進(jìn)行的約束

源碼CMAKE安裝MySQL5.5.33

如何編譯

1、使用cmake編譯mysql-5.5
cmake指定編譯選項(xiàng)的方式不同于make，其實(shí)現(xiàn)方式對比如下：
./configure           cmake .
./configure --help    cmake . -LH or ccmake .

指定安裝文件的安裝路徑時(shí)常用的選項(xiàng)：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFDIR=/etc


默認(rèn)編譯的存儲(chǔ)引擎包括：csv、myisam、myisammrg和heap。若要安裝其它存儲(chǔ)引擎，可以使用類似如下編譯選項(xiàng)：
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1

若要明確指定不編譯某存儲(chǔ)引擎，可以使用類似如下的選項(xiàng)：
-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
比如：
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

如若要編譯進(jìn)其它功能，如SSL等，則可使用類似如下選項(xiàng)來實(shí)現(xiàn)編譯時(shí)使用某庫或不使用某庫：
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0

其它常用的選項(xiàng)：
-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0
-DENABLE_PROFILING=1



如果想清理此前的編譯所生成的文件，則需要使用如下命令：
make clean
rm CMakeCache.txt

編譯安裝

yum -y install cmake28        # 需要epel源base包中只提供2.6版本
yum -y install readline-devel zlib-devel openssl-devel   
useradd -r -d /mydata/data -s /sbin/nologin mysql
tar xf mysql-5.5.33.tar.gz 
cd mysql-5.5.33
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.33 \
    -DMYSQL_DATADIR=/mydata/data \
    -DSYSCONFDIR=/etc \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DWITH_SSL=system \
    -DWITH_ZLIB=system \
    -DWITH_LIBWRAP=0 \
    -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
    -DMYSQL_TCP_PORT=3306 \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci
make && make install

cd /usr/local
ln -s mysql-5.5.33 mysql
chown -R mysql.mysql mysql;chown -R mysql.mysql mysql-5.5.33
mkdir -pv /mydata/data
chown -R mysql.mysql /mydata/data
cd mysql
scripts/mysql_install_db --user=mysql --datadir=/mydata/data 查看數(shù)據(jù)目錄下是否生成初始化數(shù)據(jù) ls /mydata/data
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
cp support-files/my-large.cnf /etc/my.cnf; echo "datadir = /mydata/data" >> /etc/my.cnf
echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh; 
. /etc/profile.d/mysql.sh

安裝后初始化

mysql> select User,Host,Password from mysql.user;  # 查看用戶
mysql> drop user ""@localhost;  # 刪除匿名用戶,host部分可用% _通配符
mysql> update user set password = PASSWORD('magedu') where user='root';                    # 給用戶添加密碼
或者
mysqladmin -u root password "xxxx" # 給用戶設(shè)置密碼的另一種方法
mysql> flush privileges;

優(yōu)化設(shè)置

安裝并啟動(dòng)后的初始化:mysql_secure_installation
運(yùn)行前常修改的參數(shù):

innodb_file_per_table=1
skip_name_resolve=1

InnoDB的所有表數(shù)據(jù)默認(rèn)是放在一個(gè)文件里的/var/lib/mysql/ibdata1

工作模式

① mysql < /path/to/scripts.sql 腳本模式
② mysql 交互式shell

mysql命令

mysql
    -u, --user
    -h, --host
    -p, --password
    --port
    --protocol
    --database DATABASE, -D
    

MySQL服務(wù)器參數(shù)/變量(設(shè)定MySQL/MariaDB的運(yùn)行參數(shù))

mysql> show global[session] variables [like clause]; # 查看服務(wù)器參數(shù)變量
mysql> show global[session] status [like clause];    # 查看狀態(tài)參數(shù)變量

顯示單個(gè)服務(wù)器參數(shù)變量設(shè)定值的方法:
mysql> select @@[global.|session.]SYS_VAR_NAME;
如 select @@session.tx_isolation;

運(yùn)行時(shí)修改參數(shù)/變量,其中g(shù)lobal設(shè)定僅對修改后新建立的會(huì)話有效,而session僅對當(dāng)前會(huì)話有效,且立即生效.
set [global|session] SYS_VAR_NAME = EXPR
或者
set @@[global.|session.]SYS_VAR_NAME = EXPR

設(shè)定系統(tǒng)參數(shù)sql_mode以提高數(shù)據(jù)的嚴(yán)格性,此參數(shù)默認(rèn)為空(即不符合表定義類型的數(shù)據(jù)也會(huì)被插入到表中).常用的mode如下:
TRADITIONAL             # 傳統(tǒng)
STRICT_TRANS_TABLES     # 僅對事務(wù)型表嚴(yán)格限制,如InnoDB,不允許向其插入非法數(shù)據(jù)
STRICT_ALL_TABLES       # 所有的非法數(shù)據(jù)都不允許
修改方式
set global sql_mode=MODE
set @@global.sql_mode=MODE

創(chuàng)建表

先規(guī)劃表結(jié)構(gòu)

create table tb_name1(stu_id int primary key, stu_name varchar(20), stu_sex boolean) ;
create table tb_name2(s_id int, c_id int, grade float, primary key(stu_id,course_id));  # 多字段組合主鍵
create schema if not exists students character set 'gbk' collate
create table tab1 like tab2;       # 復(fù)制表結(jié)構(gòu)

創(chuàng)建tb_name2的子表(tb_name2是tb_name3的父表)

create table tb_name3(id int primary key, 
                      stu_id int, course_id int, 
                      constraint c_fk foreign key(stu_id,course_id) 
                      references tb_name2(s_id, c_id));    
                      # stu_id course_id為表tb_name3的外鍵,c_fk是外鍵的別名.子表的外鍵關(guān)聯(lián)的必須是父表的主鍵或組合主鍵中的一個(gè)且類型必須為一致

表數(shù)據(jù)的完整性約束

unsigned       # 無符號(hào)的.對數(shù)字類型
primary key    # 主鍵
foreigh key    # 外鍵
not null       # 非空
unique         # 惟一
auto_increment # 自增
default        # 默認(rèn)

查看表結(jié)構(gòu)、定義

mysql> describe tbl_name或者desc tbl_name        # 查看表的基本定義
mysql> show create table tbl_name                # 查看表的詳細(xì)定義
mysql> show table status [{from|in} db_name] [like 'pattern' | where expr]

修改表(結(jié)構(gòu)屬性層面 alter&drop)

alter table OLDTABNAME rename [to] NEWTABNAME;   # 修改表名
alter table TABNAME modify FIELDNAME TYPE;       # 修改字段數(shù)據(jù)類型
alter table TABNAME change OLDFIELDNAME NEWFIELDNAME TYPE; # 修改字段名
alter table TABNAME add FIELD1 TYPE [約束條件] [first|after FIELD2]; # 添加字段
alter table TABNAME drop FIELDNAME;              # 刪除字段
alter table TABNAME modify FIELD1 TYPE first|after FIELD2; # 修改字段的排列位置
alter table TABNAME engine=xxxx;                 # 修改表的存儲(chǔ)引擎
alter table TABNAME charset=utf8;                # 修改表的數(shù)據(jù)字符集
alter table TABNAME drop foreign key 外鍵別名;   # 刪除表的外鍵約束
drop table TABNAME;                              # 刪除表(沒有被其它表關(guān)聯(lián))

如果與其它表有關(guān)聯(lián)的表,正確的方法應(yīng)該是刪除子表的外鍵約束然后再刪除父表.這樣不會(huì)影響子表的其他數(shù)據(jù),可以保證數(shù)據(jù)庫的安全.
假如example4表(外鍵為stu_id,別名為d_fk)是example1表的子表
alter table example4 drop foreign key d_fk;
drop table example1;

索引

索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu),可以有來快速查詢數(shù)據(jù)庫表中的特定記錄.索引同數(shù)據(jù)庫表中一列或多列組合而成,其作用提高對表中數(shù)據(jù)的查詢速度,是提高數(shù)據(jù)庫性能的重要方式.MySQL中所有的數(shù)據(jù)類型都可以被索引,其索引分類如下:
? 普通索引

在創(chuàng)建普通索引時(shí),不附加任何限制條件.此類索引可以創(chuàng)建在任何數(shù)據(jù)類型中,其值是否唯一和非空由字段本身的完整性約束條件決定.

? 唯一性索引

使用unique參數(shù)可以設(shè)置索引為唯一性索引.限制該索引的值必須是唯一的.主鍵就是> 一種唯一性索引.其字段的每一個(gè)值都是唯一的.

? 全文索引

使用fulltext參數(shù)可以設(shè)置索引為全文索引.全文索引只能創(chuàng)建在char varchar或text類型的字段上.查詢數(shù)據(jù)量圈套的客串類型的字段時(shí),使用全文索引可以提高查詢速度.(目前只有MyISAM支持)

? 單列索引

在表中的單個(gè)字段上創(chuàng)建索引.其可以是以上3種中的任意一種索引.

? 多列索引

多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引.該索引指向創(chuàng)建時(shí)對應(yīng)的多個(gè)字段,可以通過這幾個(gè)字段進(jìn)行查詢.但是只有查詢條件中使用了這些字段中第一個(gè)字段時(shí),索引才會(huì)被使用.

? 空間索引

使用spatial參數(shù)可以設(shè)置索引為空間索引.空間索引只能建立在空間數(shù)據(jù)類型上,樣可以提高系統(tǒng)獲取空間數(shù)據(jù)的效率.MySQL中的空間數(shù)據(jù)類型包括geometry和point linestring polygon,目前只有MyISAM存儲(chǔ)引擎支持空間檢索,而且索引的字段不能為空值.

索引的缺點(diǎn):
創(chuàng)建和維護(hù)索引需要耗費(fèi)時(shí)間,耗費(fèi)時(shí)間的數(shù)量隨著數(shù)據(jù)量的增加而增加.索引需要占用物理空間.索引可以提高查詢的速度,但是會(huì)影響插入記錄的速度.插入大量記錄的速度影響更加明顯.

索引的設(shè)計(jì)原則

1. 選擇唯一性索引: 其值是唯一的,可以更快地通過該索引來確定某條記錄.
2. 為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引
3. 為常作為查詢條件的字段建立索引
4. 限制索引的數(shù)目
5. 盡量使用數(shù)據(jù)量少的索引
6. 盡量使用前綴來索引
7. 刪除不再使用或者很少使用的索引

創(chuàng)建索引

? 創(chuàng)建表的時(shí)候直接創(chuàng)建索引
create table TABNAME(屬性名 數(shù)據(jù)類型 [完整性約束條件],..,[unique|fulltext|spatial] index|key [別名] (屬性名 1 [(長度)] [asc|desc]));

屬性名 1: 前面定義好的某個(gè)字段的名稱.
長度:     可選參數(shù),其指索引的長度,必須是客串類型才可以使用
asc|desc: 表示升序或降序排列

? 在已經(jīng)存在的表上創(chuàng)建索引
create [unique|fulltext|spatial] index 索引名 on 表名 (屬性名 [(長度) [asc|desc]]);

? 在已經(jīng)存在的表上通過alter table語句來創(chuàng)建索引
alter table 表名 add [unique|fulltext|spatial] index 索引名  (屬性名 [(長度) [asc|desc]]);


創(chuàng)建普通索引
create table index1(id int,
                    name varchar(20),
                    sex boolean,
                    index(id)
);
使用explain語句可以查看索引是否被使用
explain select * from index1 where id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index1
         type: ref
possible_keys: id             # 此行及以下一行說明索引起作用了
          key: id
      key_len: 5
          ref: const
         rows: 1
        Extra: 
1 row in set (0.01 sec)


創(chuàng)建唯一性索引
create table index2(
    id int unique,
    name varchar(20),
    unique index index2_id(id asc)
);

創(chuàng)建全文索引
create table index3(
    id int,
    info varchar(20),
    fulltext index index3_info(info)
)engine=MyISAM;

創(chuàng)建單列索引
create table index4(
    id int,
    subject varchar(30),
    index index4_st(subject(10))
);

創(chuàng)建多列索引
create table index5(
    id int,
    name varchar(20),
    sex char(4),
    index index5_ns(name,sex)
);

創(chuàng)建空間索引
create table index6(
    id int,
    space geometry ont null,
    spatial index index6_sp(space)
)engine=MyISAM;

創(chuàng)建唯一鍵索引 全文索引 多列索引示例:
create table user(
userid int(10) primary key not null unique auto_increment, username varchar(20) not null, 
passwd varchar(20) not null, 
info text, 
unique index index_uid(userid desc), 
index index_user(username,passwd), 
fulltext index index_info(info)
)engine=MyISAM default charset=utf8;

索引何時(shí)不生效

① 像like "%xxx"這樣%號(hào)為第一個(gè)字符時(shí)索引不會(huì)被使用.
② 如果是多列索引,但查詢條件并不是多列索引的第一個(gè)字段時(shí),索引也不會(huì)起作用.示例如下:

mysql> create index index_birth_department on student(birth,department);
mysql> explain select * from student where birth=1991; # 多列索引生效
mysql> explain select * from student where deartment='CS'; # 多列索引不生效

③ 查詢語句只有or關(guān)鍵字時(shí),如果or前后的兩個(gè)條件的列都是索引時(shí),索引生效,否則索引不生效.

刪除索引

mysql> drop index 索引名 on 表名;

臨時(shí)禁用/開啟索引

mysql> alter table TABNAME disable|enable keys;

視圖(view)

視圖它是一個(gè)虛表,存儲(chǔ)下來的select語句.常見的操作如下

create view VIEW_NAME [(column_list)] as select_statement;   # 創(chuàng)建
alter view VIEW_NAME [(column_list)] as select_statement;    # 修改
drop view VIEW_NAME [if exists] VIEW_NAME [, VIEW_NAME] ...; # 刪除

觸發(fā)器

查詢語句

基本語法

select [distinct] FIELDS from TABNAME
    where 條件  
    group by FIELDS having 條件
    order by FIELDS [asc|desc]
    limit 初始位置,記錄數(shù)

集合函數(shù)

count()
sum()
avg()
max()
min()

連接查詢

連接查詢是將兩個(gè)或多個(gè)表按某個(gè)條件連接起來,從中選取需要的數(shù)據(jù).它包括?內(nèi)連接查詢與?外連接查詢
內(nèi)連接查詢: 只查詢出指定字段取值相同的記錄.主鍵與外鍵交集
select num,name,employee.d_id,age,sex,d_name,function \ from employee, department \ where employee.d_id=department.d_id;

外連接查詢: 主外鍵字段值不相等的記錄也可以查詢出來.分為左外連接與右外連接(分別表示以哪此表為主)

select FIELD1,FIELD2,... 
from TAB1 left|right join TAB2
on TAB1.FIELD=TAB2.FIELD;

子查詢(嵌套select,但效率不高)

子查詢的效率不高源自需要為內(nèi)層的查詢結(jié)果建立臨時(shí)表,查詢完畢后還要撤銷這些臨時(shí)表.但連接查詢不需要,所以后者查詢效率更高.

in (select子語句): 在select子語句中出現(xiàn)過的值
比較運(yùn)算符 (select子語句):
exists (select子語句): 判斷子語句為真時(shí)執(zhí)行,否則不執(zhí)行主select
比較符 any (select子語句): 與子語句查詢出的任意一個(gè)值比較
比較符 all (select子語句): 只有滿足內(nèi)層子select子語句返回的所有結(jié)果才執(zhí)行外層查詢語句.

合并查詢結(jié)果

取所有select語句的結(jié)果的交集部分.

select 語句1 union|union all
select 語句2 union|union all
select 語句3 union|union all
...
select 語句1 union select 語句2;

使用正則表達(dá)式查詢

select FIELD1,FIELD2,... from TAB where FIELD regexp|rlike "XX";

插入、更新、刪除數(shù)據(jù)(數(shù)據(jù)內(nèi)容層面 insert update delete)

insert into TABNAME values(v1,v2,..);
insert into TABNAME(FIELD1,FIELD2,...) values(v1,v2,...),(x1,x2,...);
update TABNAME set FIELD1= ,FIELD2= where 條件;
delete from TABNAME where 條件; # 不跟where子名會(huì)刪除整表內(nèi)容

MySQL運(yùn)算符

?算術(shù)運(yùn)算符

算術(shù)運(yùn)算符

?比較運(yùn)算符

比較運(yùn)算符

?邏輯運(yùn)算符

邏輯運(yùn)算符

?位運(yùn)算符

位運(yùn)算符

MySQL函數(shù)

1. 數(shù)學(xué)函數(shù)

數(shù)學(xué)函數(shù)

2. 字符串函數(shù)

   
   
   字符串函數(shù)

忘記管理員密碼的解決辦法

1. 啟動(dòng)mysqld進(jìn)程時(shí)，使用--skip-grant-tables和--skip-networking選項(xiàng)(systemctl管理的話要編輯mariadb.service,并執(zhí)行systemctl daemon-reload;CentOS6的話需要更改/etc/init.d/mysqld文件)
2. 通過update命令修改管理員密碼,flush privileges加載授權(quán)表.
3. 改回1步驟以正常方式啟動(dòng)mysqld進(jìn)程

MySQL用戶帳號(hào)與權(quán)限管理

mysql -u USERNAME -h HOSTNAME -P PORT -p PASSWD -e "SQL語句" 登錄語句
創(chuàng)建用戶:
mysql> create user 'user'@'host' identified by [PASSWORD] 'password';
mysql> insert into mysql.user(User,Password) values('xx','xxx',PASSWORD('pwd'));
grant priv_type on DB.TAB to USER identified by [PASSWORD]"PWD";

刪除用戶:
mysql> drop user 'user'@'host' ...;
mysql> delete from mysql.user where Host='xxx' User='xx';
重命名用戶:
rename user OLD to NEW [OLD to NEW]...;
修改用戶密碼:
? set password [for 'user'@'host']=PASSWORD('xxxxx'); flush privileges;
? update mysql.user set Password=PASSWORD('xxxx') where User='USERNAME' and Host='HOST'
? mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS'

用戶權(quán)限

安裝MySQL時(shí)會(huì)自動(dòng)安裝一個(gè)名為mysql的數(shù)據(jù)庫.mysql數(shù)據(jù)庫下面存儲(chǔ)的都是權(quán)限表(如user db host tables_priv columns_priv proc_priv).用戶登錄以后,MySQL數(shù)據(jù)庫系統(tǒng)會(huì)根據(jù)這些權(quán)限表的內(nèi)容為每個(gè)用戶賦予相應(yīng)的權(quán)限.

MySQL中權(quán)限分配是按照user表、db表、table_priv表和columns_priv表的順序依次判斷各表中的值是否為Y,是的話就不需要檢查后面的表了.

user表
Host: 主機(jī)名
User: 用戶名
Password: 密碼
xxx_priv: 以priv結(jié)尾的字段決定了用戶的權(quán)限.

ssl_type: #以下4個(gè)都與安全加密相關(guān)
ssl_cipher:
x509_issuer:
x509_subject

max_questions # 每小時(shí)可以允許多少次查詢,0表示不限制
max_updates # 更新
max_connections # 連接
max_user_connections# 單個(gè)用戶可以同時(shí)具有的連接數(shù).

db表

db表存儲(chǔ)了某個(gè)用戶對一個(gè)數(shù)據(jù)庫的權(quán)限.

host表

較少使用

tables_priv表

可以對單個(gè)表進(jìn)行權(quán)限設(shè)置

columns_priv表

此表可以對單個(gè)數(shù)據(jù)列進(jìn)行權(quán)限設(shè)置

procs_priv表

此表可以對存儲(chǔ)過程和存儲(chǔ)函數(shù)進(jìn)行權(quán)限設(shè)置.

用戶權(quán)限

MySQL權(quán)限說明(user表)

數(shù)據(jù)庫層面的權(quán)限分析

授權(quán)語句

語法:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO USER [, USER] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

GRANT PROXY ON user_specification
    TO user_specification [, user_specification] ...
    [with GRANT OPTION]

with后跟的選項(xiàng):
    GRANT OPTION: 被授權(quán)的用戶可以將這些權(quán)限賦予給別的用戶
    MAX_QUERIES_PER_HOUR count: 設(shè)置每小時(shí)可以允許執(zhí)行count次查詢.
    MAX_UPDATES_PER_HOUR count: 
    MAX_CONNECTIONS_PER_HOUR count:
    MAX_USER_CONNECTIONS count
object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user
    [
        IDENTIFIED BY [PASSWORD] 'password'
      | IDENTIFIED WITH auth_plugin [AS 'auth_string']
    ]

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count             # 0表示不限次數(shù)
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

mysql> grant all on db_name.tbl_name to 'user'@'host' ;     # 授權(quán)

取消授權(quán)

mysql> revoke update on db_name.tbl_name from 'user'@'host'; # 收回授權(quán)

顯示用戶權(quán)限

mysql> show grants for 'user'@'host';

數(shù)據(jù)備份與還原

備份

? 備份:mysqldump命令(屬于邏輯備份,性能較差)

將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個(gè)文本文件.其原理是查出需要備份的表的結(jié)構(gòu),分析出需要什么create語句與insert語句可以生成這張表,將這些個(gè)create與這個(gè)龐大的insert語句保存在文本文件中.應(yīng)該在MySQL服務(wù)器壓力最小的時(shí)間段執(zhí)行,并且需要考慮到備份需要影響到的時(shí)間有.當(dāng)數(shù)據(jù)庫相當(dāng)大時(shí)，不建議此種備份方式。
缺點(diǎn): 會(huì)丟失數(shù)據(jù)精度，無法備份索引

備份類型

備份什么:
數(shù)據(jù) 配置文件 二進(jìn)制日志 事務(wù)日志
備份策略:
完全+增量 完全+差異

按備份數(shù)據(jù)庫的內(nèi)容
? 完全備份: 對數(shù)據(jù)庫進(jìn)行一個(gè)完整的備份.
? 增量備份: 在上次完全備份的基礎(chǔ)上,對于更改的數(shù)據(jù)進(jìn)行備份.
? 差異備份: 僅備份自上一次完全備份以來變量的那部分?jǐn)?shù)據(jù)；
? 日志備份: 對MySQL數(shù)據(jù)庫二進(jìn)制日志的備份.

MySQL完全增量差異的區(qū)別示意圖.png

物理備份、邏輯備份
? 物理備份：復(fù)制數(shù)據(jù)文件進(jìn)行備份,也稱祼文件備份.恢復(fù)時(shí)間較短.不需要
? 邏輯備份：從數(shù)據(jù)庫導(dǎo)出數(shù)據(jù)另存在一個(gè)或多個(gè)文件中；恢復(fù)所需要的時(shí)間往往較長.但適用于任意存儲(chǔ)引擎.移植性較好,且方便使用文本處理工具直接對其處理

根據(jù)數(shù)據(jù)服務(wù)是否在線
? 熱備：讀寫操作均可進(jìn)行的狀態(tài)下所做的備份.MySQL官方稱之為Online Backup.
? 溫備：可讀但不可寫狀態(tài)下進(jìn)行的備份；
? 冷備：讀寫操作均不可進(jìn)行的狀態(tài)下所做的備份；一般只需要復(fù)制相關(guān)的數(shù)據(jù)庫物理文件即可.MySQL官方稱之為Offline Backup

幾種備份工具

? mysqldump(邏輯備份|)

mysqldump命令用法:
# mysqldump -u USERNAME -h HOSTNAME -p DBNAME [tab1 tab2 ...] > backup.sql,如果只備份一個(gè)數(shù)據(jù)庫時(shí),mysqldump不會(huì)自動(dòng)生成數(shù)據(jù)庫,還原時(shí)需要手動(dòng)創(chuàng)建數(shù)據(jù)庫,即下面2步操作
mysql> create database db;
# mysql db < backup.sql

# mysqldump -u USERNAME -h HOSTNAME -p --databases db1 db2 ... > backup.sql   備份多個(gè)指定數(shù)據(jù)庫
# mysqldump -u USERNAME -p -h HOSTNAME --all-databases > all.sql  備份所有數(shù)據(jù)庫
    -p: 要求輸入密碼
    USERNAME: 用戶名
    DBNAME: 數(shù)據(jù)庫名
    MyISAM存儲(chǔ)引擎：支持溫備 備份時(shí)要鎖定表
        -x,--lock-all-tables:鎖定所有庫的所有表,讀鎖
        -l,--lock-tables: 鎖定指定庫所有表
    InnoDB存儲(chǔ)引擎: 支持熱備與溫備
        --single-transaction: 創(chuàng)建一個(gè)事務(wù),基于此快照執(zhí)行備份.InnoDB務(wù)必加上,不要與--lock-all-tables一同使用
    -R,--routines: 存儲(chǔ)過程和存儲(chǔ)函數(shù)
    --triggers: 備份觸發(fā)器
    -E,--events: 備份事件
    --routines:  
    --triggers:
    --master-data[=NUM]: 記錄備份文件時(shí),binlog所處的時(shí)間 偏移量也會(huì)被記錄
        0: 不記錄二進(jìn)制日志及其位置
        1: 記錄為chage master to語句,此語句不被注釋
        2.: 記錄為chage master to語句,此語句被注釋
        記錄的內(nèi)容示例:
        -- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000001', MASTER_LOG_POS=245;
    --flush-logs: 鎖定表完成后,即進(jìn)行二進(jìn)制日志滾動(dòng)操作.
    tab: 指定數(shù)據(jù)庫中的各表,沒有的話將備份整個(gè)數(shù)據(jù)庫.
    backup.sql: 備份的文本文件(并非必須得以.sql結(jié)尾)
    --lock-all-tables: 備份前鎖定所有庫所有表

mysqldump真實(shí)場景備份操作

備份所有數(shù)據(jù)庫: 每周完全備份+每日增量
# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/all.sql
查看all.sql中(--master-data)記錄的二進(jìn)制日志文件,在其之前的二進(jìn)制日志文件可備份后都刪除.
mysql> prege binary logs to "mysql-bin.00000x";   # 備份后刪除舊的二進(jìn)制日志文件
增量備份
mysql> flush logs;
# cd /mydata/data
# mysqlbinlog mysql-bin.0000011 > /root/mon-incremental.sql     # 可以改名以便知道具體是哪個(gè)完全備份后的哪天的增量備份

# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql --data-dir=/mydata/data
# /etc/init.d/mysqld start
# mysql -uroot -p < /root/all.sql                           # 還原完全備份的數(shù)據(jù)
# mysql -uroot -p < /root/mon-incremental.sql               # 還原增量備份的數(shù)據(jù)
# mysql -uroot -p < /root/tues-incremental.sql
# ...

? xtrabackup開源備份工具
支持對InnoDB做熱備,是一款開源的物理備份工具.下載percona-xtrabackup(CentOS7)

yum -y install ./percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
xtrabackup --backup | --prepare [options]
    --print-defaults: 打印程序參數(shù)列表
    --no-defaults:  不從任何文件是讀取默認(rèn)選項(xiàng)
    --defaults-file= : 
    --target-dir= : 備份的目標(biāo)目錄
    --backup:  創(chuàng)建備份實(shí)例
    --stats: 

xtrabackup

? 備份:直接復(fù)制整個(gè)數(shù)據(jù)庫中目錄(InnoDB不適用)

最簡單的備份方法,就是將MySQL中的數(shù)據(jù)庫文件直接復(fù)制出來.使用這種方法前最好將數(shù)據(jù)庫服務(wù)停止以便數(shù)據(jù)不會(huì)發(fā)生變化.

? 備份:使用mysqlhotcopy工具快速備份(InnoDB不適用)

mysqlhotcopy(一個(gè)Perl腳本,需要安裝)可以在不停止MySQL服務(wù)器下進(jìn)行備份,其速度快于mysqldump.其主要使用lock tables,flush tables,cp來進(jìn)行快速備份.工作原理是,先將需要備份的數(shù)據(jù)庫加上一個(gè)讀操作鎖,然后用flush tables將內(nèi)在中的數(shù)據(jù)寫回到硬盤上的數(shù)據(jù)庫中,最后用cp將需要備份的數(shù)據(jù)庫文件復(fù)制到目標(biāo)目錄.

# mysqlhotcop [option] dbname1 dbname2 .. backDir/
    --help: mysqlhotcopy幫助
    --allowold: 如果備份目錄下存在相同的備份文件,則將舊的備份文件名加上_old
    --keepold: 如果備份目錄下存在相同的備份文件,則將舊的備份更名而不是刪除.
    --flushlog: 本次備份之后,將對數(shù)據(jù)庫的更新記錄到日志中.
    --noindices: 只備份數(shù)據(jù)文件,不備份索引文件.
    --user=用戶名: 用來指定用戶名,可以用-u替代
    --password=密碼: 指定密碼,可以用-p替代但須與-p緊挨著
    --port=端口號(hào): 指定端口號(hào),可以用-P替代
    --socket=socket文件: 用來指定socket文件,可以用-S替代

備份策略:
1.完全+差異+binlog 或 完全+增量+binlog

2. 多久一次完全備份

MySQL備份腳本

db_backup.sh

#!/bin/bash

. /etc/init.d/functions

# 要備份的數(shù)據(jù)庫名，多個(gè)數(shù)據(jù)庫用空格分開
databases=(db1 db2 db3) 

# 備份文件要保存的目錄
basepath='/root/backup/b3log.org/mysql/'

if [ ! -d "$basepath" ]; then
  mkdir -p "$basepath"
fi

# 循環(huán)databases數(shù)組
for db in ${databases[*]};do

    # 備份數(shù)據(jù)庫生成SQL文件
    /bin/nice -n 19 /usr/bin/mysqldump -uUSER -pPASSWORD --master-data=2 --database $db > $basepath$db-$(date +%Y%m%d).sql
    
    # 將生成的SQL文件壓縮
    /bin/nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz $basepath$db-$(date +%Y%m%d).sql
    
   
done
# 刪除7天之前的備份數(shù)據(jù)
find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;

# 刪除生成的SQL文件
rm -rf $basepath/*.sql

設(shè)置計(jì)劃任務(wù)來執(zhí)行MySQL備份

crontab -e
    # mysql backup 
    00 03 * * * /bin/bash /root/db_backup.sh

還原

? mysql命令還原
mysql -u root -p[dbname] < backup.sql
? 直接復(fù)制數(shù)據(jù)庫文件到數(shù)據(jù)庫目錄

必須保證兩個(gè)MySQL數(shù)據(jù)庫的主版本號(hào)是相同的.

數(shù)據(jù)庫遷移

? 相同版本的MySQL數(shù)據(jù)庫之間
mysqldump -h host1 -u root --password=password1 --all-databases | mysql -h host2 -u root --password=password2

? 不同版本的MySQL數(shù)據(jù)庫之間
高版本的MySQL數(shù)據(jù)庫通常都會(huì)兼容低版本.

日志

MySQL日志是記錄數(shù)據(jù)庫日常操作和錯(cuò)誤信息的文件.當(dāng)數(shù)據(jù)庫遭到意外的損害時(shí),可以通過日志文件來查詢出錯(cuò)原因,并且可以通過日志文件進(jìn)行數(shù)據(jù)恢復(fù).MySQL中日志可分為:
? 二進(jìn)制日志: 以二進(jìn)制文件的形式記錄數(shù)據(jù)庫中的操作,但不記錄查詢語句
? 錯(cuò)誤日志: 記錄MySQL服務(wù)器的啟動(dòng)、關(guān)閉、 運(yùn)行錯(cuò)誤信息(文本)
? 通用查詢?nèi)罩? 記錄用戶登錄和記錄查詢的信息(文本)
? 慢查詢?nèi)罩? 記錄執(zhí)行時(shí)間超過指定時(shí)間的操作(文本)
? 中繼日志: 在當(dāng)前從服務(wù)器上要求執(zhí)行一遍的日志(格式同二進(jìn)制日志一樣)
? 事務(wù)日志: 將隨機(jī)IO轉(zhuǎn)化

二進(jìn)制日志

也叫做變更日志,主要用于記錄數(shù)據(jù)庫的變化情況以及還原數(shù)據(jù).任何可能引起數(shù)據(jù)變化的操作都會(huì)記錄.通過其可以查詢MySQL數(shù)據(jù)庫中進(jìn)行了哪些改變.MySQL的每一次重啟都會(huì)生成一個(gè)相應(yīng)的二進(jìn)制日志文件.
二進(jìn)制日志的格式的兩種:1基于語句 2基于行 3前兩種的混合模式(binlog_format=mixed)
二進(jìn)制日志文件最好不要與數(shù)據(jù)文件放在同一塊硬盤上,應(yīng)該放置在有冗余能力的磁盤上

mysql-bin.index文本文件記錄了所有的二進(jìn)制日志文件數(shù)量
啟動(dòng)與設(shè)置

vim /etc/my.cnf
    log-bin=mysql-bin    # 名稱示例:mysql-bin.000001
/etc/init.d/mysqld restart 或修改MySQL服務(wù)器參數(shù)"log_bin":
set session log_bin = 1;

查看并輸出指定二進(jìn)制日志內(nèi)容
mysqlbinlog 二進(jìn)制日志文件
選項(xiàng)
--start-datetime
--stop-datetime
--start-position
--stop-position
示例
msyqlbinlog --start-datetime "2015-03-26 15:14:39" mysql-bin.000003 > /root/a.sql

查看所有二進(jìn)制日志文件名與大小
mysq> show binary logs;

查看當(dāng)前正在使用的二進(jìn)制日志文件
mysql> show master status;

查看二進(jìn)制日志事件
mysql> show binlog events in 'mysql-bin.000005';
mysql> show binlog events in 'mysql-bin.000005' from 107; # 從指定位置查看

刪除二進(jìn)制日志

mysql> reset master;  # 刪除所有二進(jìn)制日志
mysql> purge master logs to "mysql-bin.000007"; # 刪除小于此編號(hào)的所有二進(jìn)制日劇
mysql> purge master logs to "yyyy-mm-dd hh:MM:ss;" # 刪除指定時(shí)間之前創(chuàng)建的二進(jìn)制日志

或設(shè)置expire_logs_days

用二進(jìn)制日志還原數(shù)據(jù)庫

如果數(shù)據(jù)庫遭到意外損壞,首先應(yīng)該使用最近的備份文件加上二進(jìn)制日志來還原.備份文件時(shí)間之前的二進(jìn)制日志可以刪除.備份文件的恢復(fù)上面提到.

二進(jìn)制日志還原

mysqlbinlog filename.number | mysql -u root -p
使用mysqlbinlog命令進(jìn)行還原操作時(shí),必須是編號(hào)小的先還原

暫時(shí)停止二進(jìn)制日志功能

在配置文件中設(shè)置log-bin選項(xiàng)后MySQL服務(wù)器將會(huì)一直開啟二進(jìn)制日志功能.但有命令可以臨時(shí)暫停二進(jìn)制日志功能(不希望某些sql語句記錄在二進(jìn)制日志中).

mysql> set sql_log_bin=0; # 暫停二進(jìn)制日志功能
mysql> set sql_log_bin=1; # 開啟二進(jìn)制日志功能

截取掉部分二進(jìn)制日志內(nèi)容
mysqlbinlog --stop-position=695 /var/lib/mysql/master-log.000001 > new.sql

手動(dòng)滾動(dòng)二進(jìn)制日志
mysql> flush logs; # 會(huì)滾動(dòng)生成一個(gè)新的二進(jìn)制日志文件,在從服務(wù)器上會(huì)滾動(dòng)中繼日志

二進(jìn)制日志的同步參數(shù)
sync_binlog=#

設(shè)定多久同步一次二進(jìn)制近幾年至磁盤文件中,0表示不同步,任何正數(shù)值都表示對二進(jìn)制日志每多少次寫操作之后同步一次.當(dāng)autocommit的值為1時(shí),每條語句的執(zhí)行都會(huì)日志同步,否則,每個(gè)事務(wù)的提交會(huì)引起二進(jìn)制近幾年同步.

錯(cuò)誤日志

錯(cuò)誤日志記錄著開啟與關(guān)閉MySQL服務(wù)的時(shí)間,以及服務(wù)運(yùn)行過程中出現(xiàn)哪些異常等信息.配置文件里若沒有配置的話,一般名為hostname.err

設(shè)置錯(cuò)誤日志

vim /etc/my.cnf
    log-error=/PATH/TO/FILE    #
    log-warning={1|0}
/etc/init.d/mysqld restart

刪除錯(cuò)誤日志
# mysqladmin -u root -p flush-logs # 會(huì)將舊日志文件更名為filename.err-old

開啟的錯(cuò)誤日志
mysql> flush logs;

通用查詢?nèi)罩?/strong>

vim /etc/my.cnf
    [mysqld]
    log = [/PATH/TO/FILE]
/etc/init.d/mysqld restart

vim /etc/my.cnf
    [mysqld]
    log-slow-queries[=/PATH/TO/FILE]
    long_query_time=SECOND            # 如果查詢超過這個(gè)時(shí)間,這個(gè)查詢語句將被記錄到慢查詢?nèi)罩?

explain select 語句
    id:             select語句的編號(hào)
    select_type:    select語句的類型
        simple:簡單查詢
        primary:主查詢或最外層的查詢語句
        union: 連接查詢的第二個(gè)或后面的查詢語句
    table: 查詢的表
    type: 表示連接類型
        system:表中只有一條記錄
        const: 表中有多條記錄
        all: 表示對表進(jìn)行了完整的掃描
        eq_ref: 表示多表連接時(shí),后面的表使用了unique或者primary key;
        ref:表示多表查詢時(shí),后面的表使用了普通索引
        unique_subquery: 表示子查詢中使用了unique或者primary key
        index_subquery:表示子查詢中使用了普通索引
        range: 表示查詢語句中給出了查詢范圍
        index: 表示對表中的索引進(jìn)行了完整的掃描
    possible_keys: 表示查詢中可能使用的索引
    key: 表示查詢使用到的索引
    key_len: 表示索引字段的長度
    ref: 表示使用哪個(gè)列或常數(shù)與索引一起來查詢記錄
    rows: 表示查詢的行數(shù)
    Extra: 表示查詢過程的附件信息
    
describe select 語句也可簡寫成desc

1.臨時(shí)禁用\開啟索引
mysql> alter table TABNAME disable|enable keys;
2.禁用唯一性檢查
mysql> set unique_checks=[0|1]  # 0為關(guān)閉,1為開啟
3.優(yōu)化insert語句: 將多條對同一表進(jìn)行操作的insert語句匯合成單條,可以減少與數(shù)據(jù)庫之間的連接等操作.

1.分析表
mysql> analyze table TAB1 [,TAB2 ...];
2.檢查表: 執(zhí)行過程會(huì)給表加上只讀鎖
mysql> check table TAB1 [,TAB2 ..] [OPTION];
    OPTION: 按執(zhí)行效率從高到低(只對MyISAM類型表有效)
        QUICK
        FAST 
        CHANGED 
        MEDIUM 
        EXTENDED
3.優(yōu)化表: 可以消除刪除和更新造成的磁盤碎片,執(zhí)行過程中也會(huì)給表加上只讀鎖
mysql> optimize table TAB1 [,TAB2...];

vim /etc/my.cnf
    [mysqld]
    key_buffer_size:
    table_cache:
    query_cache_size=0:
    query_cahce_type:
    max_connections=100: 最大連接數(shù)
    sort_buffer_size:
    read_buffer_size:
    read_rnd_buffer_size:
    innodb_buffer_pool_size:
    innodb_flush_log_at_trx_commit: