MYSQL部署配置管理5.6版本

一、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、數據庫的物理結構（怎么存儲的）

對象存儲：

庫----> 目錄

表：

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 app@'10.0.0.%' identified by '123456';

#創建單個用戶

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

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

#給用戶授權

mysql> grant all on mysql.* to zhangsan1@'10.0.10.%';

#刪除用戶

drop user 'user'@'主機域' 

特殊的刪除方法:

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

mysql> flush privileges;

#創建用戶同時授權

grant all on *.* to oldgirl@'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 lisi@'10.0.10.%'\G;

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

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

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

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

2 rows in set (0.00 sec)

注：當單個用戶權限出現沖突時，以權限最大的設置為準

#單獨收回數據庫某個權限

mysql> revoke drop on wordpress.* from wordpress@'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 oldboy@'localhost';

#收回某個數據庫的所有權限

revoke all on wordpress.* from wordpress@'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