MYSQL部署組態管理5.6版本

標籤：緩衝   hive   row   replicate   tor   ddr   char   檔案描述符   install   

一、MYSQL伺服器端程式階層

1.1 串連層

連線協定：tcpip  socket

驗證串連合法性

分配連接線程為用戶端服務

1.2 SQL層

解析

最佳化

授權表

查詢

提供緩衝

1.3 儲存引擎層

外掛程式式的

儲存資料到磁碟

資料提取

2、資料庫的邏輯結構

庫     

show databases

use mysql

表

show tables;

記錄（行、列）

select user,host,password from user;

desc user

3、資料庫的物理結構（怎麼儲存的）

Object Storage Service：

庫----> 目錄

表：

MyIASM：

user.frm

user.MYD

user.MYI

InnoDB：

共用資料表空間：ibdata1:ibdata2 

獨立資料表空間：t1.frm t1.ibd

例子：建立一個資料庫和表，查看一下獨立資料表空間儲存的不同

create database oldboy;

use oldboy

create table t1 (id int);

insert into t1 values(1);

select id from t1;

desc t1;

二、MYSQL安裝前準備

1）獨立安裝磁碟

 mkfs.ext4 /dev/sdb

 mkdir /application

 mount /dev/sdb /application

 blkid   ----->查詢/dev/sdb 的uuid

 vi /etc/fstab

 xxx-xxx-xxxx-xxxx    /application   ext4  defaults  0 0

2）依賴包安裝

 yum  install  -y  ncurses-devel libaio-devel c++  gcc 

yum groupinstall "Development tools" -y

3)安裝先行編譯cmake

yum install cmake -y

rpm -qa cmake

4)建立mysql使用者

useradd -s /sbin/nologin -M mysql

id mysql

perror查看錯誤碼

5）官網下載mysql包檔案並解壓安裝

wget -q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz

ls -l mysql-5.6.36.tar.gz

tar xf mysql-5.6.36.tar.gz

cd mysql-5.6.36

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36 \

-DMYSQL_DATADIR=/application/mysql-5.6.36/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITH_ZLIB=bundled \

-DWITH_SSL=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DENABLE_DOWNLOADS=1 \

-DWITH_DEBUG=0

註：mysql安裝參數部分解釋收藏

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql     #安裝目錄

-DMYSQL_DATADIR=/data                       #資料庫存放目錄

-DSYSCONFDIR=/etc                           #MySQL配輯檔案

-DWITH_MYISAM_STORAGE_ENGINE=1              #安裝myisam儲存引擎

-DWITH_INNOBASE_STORAGE_ENGINE=1            #安裝innodb儲存引擎

-DWITH_ARCHIVE_STORAGE_ENGINE=1             #安裝archive儲存引擎

-DWITH_BLACKHOLE_STORAGE_ENGINE=1           #安裝blackhole儲存引擎

-DWITH_LOCAL_INFILE=1                       #允許從本地匯入資料

-DWITH_READLINE=1                           #快速鍵功能

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock           #Unix socket 檔案路徑

-DDEFAULT_CHARSET=utf8                      #預設字元

-DDEFAULT_COLLATION=utf8_general_ci         #校正字元

-DEXTRA_CHARSETS=all                        #安裝所有擴充字元集

-DMYSQL_TCP_PORT=3306                    

-DMYSQL_USER=mysql                          、

-DWITH_SSL=yes       

-DWITH_MEMORY_STORAGE_ENGINE=1              #安裝memory儲存引擎

-DWITH_FEDERATED_STORAGE_ENGINE=1           #安裝frderated儲存引擎

6）初始化mysql資料庫以及設定資料庫

cp support-files/my*.cnf /etc/my.cnf

註：編譯的MySQL可以暫時不需要設定設定檔。

    如果以前作業系統中安裝過rpm格式的mysql，系統中可能會遺留/etc/my.cnf檔案，我們需要將它刪除掉

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data --user=mysql

make && make install

ln -s /application/mysql-5.6.36/ /application/mysql

7）mysql開機啟動

chown -R mysql.mysql /application/mysql/

cp support-files/mysql.server /etc/init.d/mysqld

chmod 700 /etc/init.d/mysqld

chkconfig mysqld on

chkconfig --list mysqld

/etc/init.d/mysqld start

netstat -lntup|grep 330

echo 'PATH=/application/mysql/bin/:$PATH' >>/etc/profile

tail -1 /etc/profile

source /etc/profile

echo $PATH

mysql

註：

此時資料庫啟動會提示，找不到xx/tmp/mysql.sock,原因是5.6.36版本不會自動建立tmp目錄，需要我們手工 mkdir /application/mysql/tmp

8）mysql排錯

1、查日誌####tail -100 /application/mysql/data/db02.err 

2、查螢幕輸出

故障:ERROR! The server quit without updating PID file 

1.許可權.chown -R mysql.mysql

2.killall mysqld

3.重新初始化資料.

4.運行1年了,出問題(非法（斷電）關機或者非法關資料庫，例如kill -9).

9）mysql清理無用的使用者

select user,host from mysql.user;

drop user ''@'db02';

drop user ''@'localhost';

drop user 'root'@'db02';

drop user 'root'@'::1';

select user,host from mysql.user;

drop database test;

show databases;

10）用戶端串連mysql

通過socket方式：(mysql本地串連預設使用socket方式)

mysql -uroot -p123456 -S /usr/local/mysql/data/mysql.sock

通過TCP/IP方式

mysql -uroot -p123456 -h 遠程IP

11)mysql使用者管理

#查詢使用者名稱、密碼和許可權

mysql> select user,password,host from mysql.user;

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

| user | password                                  | host      |

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

| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |

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

#查看資料庫

show databases;

#建立資料庫

create database app;

#查看指定資料庫表

use mysql;

show tables;

#建立使用者並對指定資料庫授權

mysql> grant all on app.* to [email protected]'10.0.0.%' identified by '123456';

#建立單個使用者

create user '使用者'@'主機' IDENTIFIED BY '密碼';

create user 'oldboy'@'locahost' identified by 'oldboy123'; 

#給使用者授權

mysql> grant all on mysql.* to [email protected]'10.0.10.%';

#刪除使用者

drop user 'user'@'主機域' 

特殊的刪除方法:

mysql> delete from mysql.user where user='app' and host='localhost';

mysql> flush privileges;

#建立使用者同時授權

grant all on *.* to [email protected]'172.16.1.%' identified by 'oldgirl123';

flush privileges;

#查看某個使用者資料庫許可權

mysql> select user,host,password from mysql.user;

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

| user     | host      | password                                  |

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

| root     | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| zhangsan | 10.0.10.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| lisi     | 10.0.10.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| super    | localhost | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |

| wanger   | 10.0.10.% | *E8D868B7DA46FC9F996DC761C1AE01754A4447D5 |

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

mysql> show grants for [email protected]'10.0.10.%'\G;

*************************** 1. row ***************************

Grants for [email protected]%: GRANT USAGE ON *.* TO 'lisi'@'10.0.10.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

*************************** 2. row ***************************

Grants for [email protected]%: GRANT SELECT ON `app`.* TO 'lisi'@'10.0.10.%'

2 rows in set (0.00 sec)

註：當單個使用者權限出現衝突時，以許可權最大的設定為準

#單獨收回資料庫某個許可權

mysql> revoke drop on wordpress.* from [email protected]'10.0.0.%';

Query OK, 0 rows affected (0.00 sec)

#可以授權的使用者權限:

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

#收回許可權:

REVOKE INSERT ON wordpress.* from [email protected]'localhost';

#收回某個資料庫的所有許可權

revoke all on wordpress.* from [email protected]'10.0.0.%';

註：企業裡建立使用者一般是授權一個內網網段登入，最常見的網段寫法有兩種。

方法1：172.16.1.%（%為萬用字元，匹配所有內容）。

方法2：172.16.1.0/255.255.255.0，但是不能使用172.16.1.0/24，是個小遺憾。

例：部落格授權:

grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by 'blog123';

revoke create,drop on blog.* from 'blog'@'172.16.1.%';

11）mysql密碼忘記更改方法

適用於V5.6版本

#用以下命令啟動mysql

/usr/local/mysql/bin/mysqld_safe  --skip-grant-table --skip-networking & 

#登陸mysql

mysql

#修改密碼：

mysql> select user,password,host from mysql.user

mysql> update mysql.user set password=PASSWORD('123456') where user='wordpress' and host='192.168.56.%';

#正常啟動mysql

mysql -uroot -p123456 -h 192.168.56.11

12）mysql設定檔讀取順序

/etc/my.cnf -->  /etc/mysql/my.cnf -->  $MYSQL_HOME/my.cnf --> ~/.my.cnf  

--> --defaults-extra-file  -->  --defaults-file  最後讀取命令列上其他的配置

13）mysql錯誤碼

perror

http://oldboy.blog.51cto.com/2561410/1728380

my.cnf參數說明僅僅個人收藏

[client]

port = 3309

socket = /home/mysql/mysql/tmp/mysql.sock

[mysqld]

!include /home/mysql/mysql/etc/mysqld.cnf #包含的設定檔 ，把使用者名稱，密碼檔案單獨存放

port = 3309

socket = /home/mysql/mysql/tmp/mysql.sock

pid-file = /usr/local/mysql/var/mysql.pid

basedir = /home/mysql/mysql/

datadir = /usr/local/mysql/mysql/mysql/var/

# tmp dir settings

tmpdir = /home/mysql/mysql/tmp/

slave-load-tmpdir = /home/mysql/mysql/tmp/

#當slave 執行 load data infile 時用

#language = /home/mysql/mysql/share/mysql/english/

character-sets-dir = /home/mysql/mysql/share/mysql/charsets/

# skip options

skip-name-resolve #grant 時，必須使用ip不能使用主機名稱

skip-symbolic-links #不能使用串連檔案

skip-external-locking #不使用系統鎖定，要使用myisamchk,必須關閉伺服器

skip-slave-start #啟動mysql,不啟動複製

#sysdate-is-now

# res settings

back_log = 50 #接受隊列，對於沒建立tcp串連的請求隊列放入緩衝中，隊列大小為back_log，受限制與OS參數

max_connections = 1000 #最大並發串連數 ，增大該值需要相應增加允許開啟的檔案描述符數

max_connect_errors = 10000 #如果某個使用者發起的串連error超過該數值，則該使用者的下次串連將被阻塞，直到管理員執行flush hosts ; 命令；防止駭客

#open_files_limit = 10240

connect-timeout = 10 #連線逾時之前的最大秒數,在Linux平台上，該逾時也用作等待伺服器首次回應的時間

wait-timeout = 28800 #等待關閉串連的時間

interactive-timeout = 28800 #關閉串連之前，允許interactive_timeout（取代了wait_timeout）秒的不啟用時間。用戶端的會話wait_timeout變數被設為會話interactive_timeout變數的值。

slave-net-timeout = 600 #從伺服器也能夠處理網路連接中斷。但是，只有從伺服器超過slave_net_timeout秒沒有從主伺服器收到資料才通知網路中斷

net_read_timeout = 30 #從伺服器讀取資訊的逾時

net_write_timeout = 60 #從伺服器寫入資訊的逾時

net_retry_count = 10 #如果某個通訊連接埠的讀操作中斷了，在放棄前重試多次

net_buffer_length = 16384 #包訊息緩衝區初始化為net_buffer_length位元組，但需要時可以增長到max_allowed_packet位元組

max_allowed_packet = 64M #

#table_cache = 512 #所有線程開啟的表的數目。增大該值可以增加mysqld需要的檔案描述符的數量

thread_stack = 192K #每個線程的堆棧大小

thread_cache_size = 20 #線程緩衝

thread_concurrency = 8 #同時啟動並執行線程的資料 此處最好為CPU個數兩倍。本機配置為CPU的個數

# qcache settings

query_cache_size = 256M #查詢快取大小

query_cache_limit = 2M #不緩衝查詢大於該值的結果

query_cache_min_res_unit = 2K #查詢快取分配的最小塊大小

# default settings

# time zone

default-time-zone = system #伺服器時區

character-set-server = utf8 #server層級字元集

default-storage-engine = InnoDB #預設儲存

# tmp & heap

tmp_table_size = 512M #暫存資料表大小，如果超過該值，則結果放到磁碟中

max_heap_table_size = 512M #該變數設定MEMORY (HEAP)表可以增長到的最大空間大小

log-bin = mysql-bin #這些路徑相對於datadir

log-bin-index = mysql-bin.index

relayrelay-log = relay-log

relayrelay_log_index = relay-log.index

# warning & error log

log-warnings = 1

log-error = /home/mysql/mysql/log/mysql.err

log_output = FILE #參數log_output指定了慢查詢輸出的格式，預設為FILE，你可以將它設為TABLE，然後就可以查詢mysql架構下的slow_log表了

# slow query log

slow_query_log = 1

long-query-time = 1 #慢查詢時間 超過1秒則為慢查詢

slow_query_log_file = /home/mysql/mysql/log/slow.log

#log-queries-not-using-indexes

#log-slow-slave-statements

general_log = 1

general_log_file = /home/mysql/mysql/log/mysql.log

max_binlog_size = 1G

max_relay_log_size = 1G

# if use auto-ex, set to 0

relay-log-purge = 1 #當不用中繼日誌時，刪除他們。這個操作有SQL線程完成

# max binlog keeps days

expire_logs_days = 30 #超過30天的binlog刪除

binlog_cache_size = 1M #session層級

# replication

replicate-wild-ignore-table = mysql.% #複製時忽略資料庫及表

replicate-wild-ignore-table = test.% #複製時忽略資料庫及表

# slave_skip_errors=all

key_buffer_size = 256M #myisam索引buffer,只有key沒有data

sort_buffer_size = 2M #排序buffer大小；線程層級

read_buffer_size = 2M #以全表掃描(Sequential Scan)方式掃描資料的buffer大小 ；線程層級

join_buffer_size = 8M # join buffer 大小;線程層級

read_rnd_buffer_size = 8M #MyISAM以索引掃描(Random Scan)方式掃描資料的buffer大小 ；線程層級

bulk_insert_buffer_size = 64M #MyISAM 用在塊插入最佳化中的樹緩衝區的大小。注釋：這是一個per thread的限制

myisam_sort_buffer_size = 64M #MyISAM 設定恢複表之時使用的緩衝區的尺寸,當在REPAIR TABLE或用CREATE INDEX建立索引或ALTER TABLE過程中排序 MyISAM索引分配的緩衝區

myisam_max_sort_file_size = 10G #MyISAM 如果臨時檔案會變得超過索引，不要使用快速排序索引方法來建立一個索引。注釋：這個參數以位元組的形式給出.重建MyISAM索引(在REPAIR TABLE、ALTER TABLE或LOAD DATA INFILE過程中)時，允許MySQL使用的臨時檔案的最大空間大小。如果檔案的大小超過該值，則使用索引值緩衝建立索引，要慢得多。該值的單位為位元組

myisam_repair_threads = 1 #如果該值大於1，在Repair by sorting過程中並行建立MyISAM表索引(每個索引在自己的線程內)

myisam_recover = 64K#允許的GROUP_CONCAT()函數結果的最大長度

transaction_isolation = REPEATABLE-READ

innodb_file_per_table

#innodb_status_file = 1

#innodb_open_files = 2048

innodb_additional_mem_pool_size = 100M #幀緩衝的控制對象需要從此處申請緩衝，所以該值與innodb_buffer_pool對應

innodb_buffer_pool_size = 2G #包括資料頁、索引頁、插入緩衝、鎖資訊、自適應雜湊所以、資料字典資訊

innodb_data_home_dir = /usr/local/mysql/var/

#innodb_data_file_path = ibdata1:1G:autoextend

innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend #資料表空間

innodb_file_io_threads = 4 #io線程數

innodb_thread_concurrency = 16 #InnoDB試著在InnoDB內保持作業系統線程的數量少於或等於這個參數給出的限制

innodb_flush_log_at_trx_commit = 1 #每次commit 日誌緩衝中的資料刷到磁碟中

innodb_log_buffer_size = 8M #事物日誌緩衝

innodb_log_file_size = 500M #事物日誌大小

#innodb_log_file_size =100M

innodb_log_files_in_group = 2 #兩組事物日誌

innodb_log_group_home_dir = /usr/local/mysql/mysql/var/#日誌組

innodb_max_dirty_pages_pct = 90 #innodb主線程重新整理緩衝池中的資料，使髒資料比例小於90%

innodb_lock_wait_timeout = 50 #InnoDB事務在被復原之前可以等待一個鎖定的逾時秒數。InnoDB在它自己的 鎖定表中自動檢測事務死結並且復原事務。InnoDB用LOCK TABLES語句注意到鎖定設定。預設值是50秒

#innodb_flush_method = O_DSYNC

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

disable-auto-rehash #允許通過TAB鍵提示

default-character-set = utf8

connect-timeout = 3

MYSQL部署組態管理5.6版本