- 序章
- MySQL基础知识
- MySQL系统管理
序章
MySQL的安装
源码编译安装
# 安装相关软件
yum install -y gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel make cmake
# 获取MySQL源码，MySQL版本下载链接:https://dev.mysql.com/downloads/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.13.tar.gz
tar zxvf mysql-8.0.13.tar.gz
cd mysql-8.0.13
# 添加mysql用户
groupadd -r mysql
mkdir -p /data/mysql/data
chown -R mysql.mysql /data/mysql/data/
# 添加配置文件目录
mkdir -p /etc/mysql
chown -R mysql.mysql /etc/mysql
# 编译MySQL
mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql
# -DCMAKE_INSTALL_PREFIX：指定安装目录
# -DMYSQL_DATADIR：数据文件存放位置
# -DSYSCONFDIR：配置文件目录
# -DDOWNLOAD_BOOST：实时下载boost
# -DWITH_BOOST:下载boost保存目录
cmake . -DCMAKE_INSTALL_PREFIX=/data/mysql -DMYSQL_DATADIR=/data/mysql/data -DSYSCONFDIR=/etc/mysql -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/data/mysql/boost
make
make install
# 对安装录更改用户权限
chown root.mysql -R /data/mysql/
cd /data/mysql
# 安装完成后，进入安装目录中，对mysql进行初始化操作
bin/mysqld --initialize
# 拷贝服务脚本，赋权限并添加到启动列表中
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
# 修改配置文件
vi /etc/mysql/my.cnf
输入:
[mysqld]
basedir = /data/mysql
datadir = /data/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
user = mysql
[client]
port = 3306
socket = /tmp/mysql.sock
# 启动服务
bin/mysqld &
# 配置环境变量
echo "export PATH=/data/mysql/bin:$PATH" >/etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
# 删除系统安装完成后产生的匿名用户并给root设置密码
# 若登录mysql时，忘记了用户密码可以在启动mysqld服务时加--skip-grant-tables即可直接登录： mysqld --skip-grant-tables &
# 登录mysql
mysql -uroot -hlocalhost -p
# 删除所有匿名用户
drop user 'username'@'localhost';
# 给所有的root用户设定密码
1. UPDATE user SET password = PASSWORD('your_password') WHERE user = 'username';
FLUSH PRIVILEGES;
2. SET PASSWORD FOR root@localhost = PASSWORD('123123123');
# 创建用户并授权用户
create user 'username'@'localhost' identified by 'your_password';
create database database_name default character set = 'utf8' default collate = 'utf8_general_ci';
grant all privileges on database_name.* to 'username'@'localhost';
flush privileges;
MySQL的配置
基础配置
basedir = path 使用给定目录作为根目录(安装目录)。
character-sets-dir = path 给出存放着字符集的目录。
datadir = path 从给定目录读取数据库文件。
pid-file = filename 为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统); Init-V脚本需要使用这个文件里的进程ID结束mysqld进程。
socket = filename 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统; 默认设置一般是/var/lib/mysql/mysql.sock文件)。在Windows环境下，如果MySQL客户与服务器是通过命名管道进行通信 的，–sock选项给出的将是该命名管道的名字(默认设置是MySQL)。
lower_case_table_name = 1/0 新目录和数据表的名字是否只允许使用小写字母; 这个选项在Windows环境下的默认设置是1(只允许使用小写字母)。
mysqld程序：语言设置
character-sets-server = name 新数据库或数据表的默认字符集。为了与MySQL的早期版本保持兼容，这个字符集也可以用–default-character-set选项给出; 但这个选项已经显得有点过时了。
collation-server = name 新数据库或数据表的默认排序方式。
lanuage = name 用指定的语言显示出错信息。
mysqld程序：通信、网络、信息安全
enable-named-pipes 允许Windows 2000/XP环境下的客户和服务器使用命名管道(named pipe)进行通信。这个命名管道的默认名字是MySQL，但可以用–socket选项来改变。
local-infile [=0] 允许/禁止使用LOAD DATA LOCAL语句来处理本地文件。
myisam-recover [=opt1, opt2, ...] 在启动时自动修复所有受损的MyISAM数据表。这个选项的可取值有4种:DEFAULT、BACKUP、QUICK和FORCE; 它们与myisamchk程序的同名选项作用相同。
old-passwords 使用MySQL 3.23和4.0版本中的老算法来加密mysql数据库里的密码(默认使用MySQL 4.1版本开始引入的新加密算法)。
port = n 为MySQL程序指定一个TCP/IP通信端口(通常是3306端口)。
safe-user-create 只有在mysql.user数据库表上拥有INSERT权限的用户才能使用GRANT命令; 这是一种双保险机制(此用户还必须具备GRANT权限才能执行GRANT命令)。
shared-memory 允许使用内存(shared memory)进行通信(仅适用于Windows)。
shared-memory-base-name = name 给共享内存块起一个名字(默认的名字是MySQL)。
skip-grant-tables 不使用mysql数据库里的信息来进行访问控制(警告:这将允许用户任何用户去修改任何数据库)。
skip-host-cache 不使用高速缓存区来存放主机名和IP地址的对应关系。
skip-name-resovle 不把IP地址解析为主机名; 与访问控制(mysql.user数据表)有关的检查全部通过IP地址行进。
skip-networking 只允许通过一个套接字文件(Unix/Linux系统)或通过命名管道(Windows系统)进行本地连接，不允许ICP/IP连接; 这提高了安全性，但阻断了来自网络的外部连接和所有的Java客户程序(Java客户即使在本地连接里也使用TCP/IP)。
user = name mysqld程序在启动后将在给定UNIX/Linux账户下执行; mysqld必须从root账户启动才能在启动后切换到另一个账户下执行; mysqld_safe脚本将默认使用–user=mysql选项来启动mysqld程序。
mysqld程序：内存管理、优化、查询缓存区
bulk_insert_buffer_size = n 为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。
key_buffer_size = n 用来存放索引区块的RMA值(默认设置是8M)。
join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。
max_heap_table_size = n HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里。
max_connections = n MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。
query_cache_limit = n 允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。
query_cache_size = n 查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。
query_cache_type = 0/1/2 查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，”按需分配”模式，只响应SELECT SQL_CACHE命令。
read_buffer_size = n 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。
read_rnd_buffer_size = n 类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。
sore_buffer = n 为排序操作分配的缓存区的长度(默认设置是2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。
table_cache = n 同时打开的数据表的数量(默认设置是64)。
tmp_table_size = n 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。
mysqld程序：日志
log [= file] 把所有的连接以及所有的SQL命令记入日志(通用查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname.log文件作为这种日志文件(hostname是服务器的主机名)。
log-slow-queries [= file] 把执行用时超过long_query_time变量值的查询命令记入日志(慢查询日志); 如果没有给出file参数，MySQL将在数据库目录里创建一个hostname-slow.log文件作为这种日志文件(hostname是服务器主机 名)。
long_query_time = n 慢查询的执行用时上限(默认设置是10s)。
long_queries_not_using_indexs 把慢查询以及执行时没有使用索引的查询命令全都记入日志(其余同–log-slow-queries选项)。
log-bin [= filename] 把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。
log-bin-index = filename 二进制日志功能的索引文件名。在默认情况下，这个索引文件与二进制日志文件的名字相同，但后缀名是.index而不是.nnnnnn。
max_binlog_size = n 二进制日志文件的最大长度(默认设置是1GB)。在前一个二进制日志文件里的信息量超过这个最大长度之前，MySQL服务器会自动提供一个新的二进制日志文件接续上。
binlog-do-db = dbname 只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname 不把给定数据库里的变化情况记入二进制日志文件。
sync_binlog = n 每经过n次日志写操作就把日志文件写入硬盘一次(对日志信息进行一次同步)。n=1是最安全的做法，但效率最低。默认设置是n=0，意思是由操作系统来负责二进制日志文件的同步工作。
log-update [= file] 记载出错情况的日志文件名(出错日志)。这种日志功能无法禁用。如果没有给出file参数，MySQL会使用hostname.err作为种日志文件的名字。
mysqld程序：主控镜像服务器
server-id = n 给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。
log-bin = name 启用二进制日志功能。这种日志的文件名是filename.n或默认的hostname.n，其中的n是一个6位数字的整数(日志文件顺序编号)。
binlog-do/ignore-db = dbname 只把给定数据库里的变化情况记入二进制日志文件/不把给定的数据库里的变化记入二进制日志文件。
mysqld程序：从属镜像服务器
server-id = n 给服务器分配一个唯一的ID编号
log-slave-updates 启用从属服务器上的日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)。
master-host = hostname 主控服务器的主机名或IP地址。如果从属服务器上存在mater.info文件(镜像关系定义文件)，它将忽略此选项。
master-user = replicusername 从属服务器用来连接主控服务器的用户名。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-password = passwd 从属服务器用来连接主控服务器的密码。如果从属服务器上存在mater.info文件，它将忽略此选项。
master-port = n 从属服务器用来连接主控服务器的TCP/IP端口(默认设置是3306端口)。
master-connect-retry = n 如果与主控服务器的连接没有成功，则等待n秒(s)后再进行管理方式(默认设置是60s)。如果从属服务器存在mater.info文件，它将忽略此选项。
master-ssl-xxx = xxx 对主、从服务器之间的SSL通信进行配置。
read-only = 0/1 0: 允许从属服务器独立地执行SQL命令(默认设置); 1: 从属服务器只能执行来自主控服务器的SQL命令。
read-log-purge = 0/1 1: 把处理完的SQL命令立刻从中继日志文件里删除(默认设置); 0: 不把处理完的SQL命令立刻从中继日志文件里删除。
replicate-do-table = dbname.tablename 与–replicate-do-table选项的含义和用法相同，但数据库和数据库表名字里允许出现通配符”%” (例如: test%.%–对名字以”test”开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = name 只对这个数据库进行镜像处理。
replicate-ignore-table = dbname.tablename 不对这个数据表进行镜像处理。
replicate-wild-ignore-table = dbn.tablen 不对这些数据表进行镜像处理。
replicate-ignore-db = dbname 不对这个数据库进行镜像处理。
replicate-rewrite-db = db1name > db2name 把主控数据库上的db1name数据库镜像处理为从属服务器上的db2name数据库。
report-host = hostname 从属服务器的主机名; 这项信息只与SHOW SLAVE HOSTS命令有关–主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1 主、从服务器使用压缩格式进行通信–如果它们都支持这么做的话。
slave-skip-errors = n1, n2, …或all 即使发生出错代码为n1、n2等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。如果配置得当，从属服务器不应 该在执行 SQL命令时发生错误(在主控服务器上执行出错的SQL命令不会被发送到从属服务器上做镜像处理); 如果不使用slave-skip-errors选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, MASTER_USER=, MASTER_PASSWORD= ; 这是命令行不是MySQL配置，配置此主机作为复制的slave服务器
mysqld–InnoDB：基本设置和表空间文件
skip-innodb 不加载InnoDB数据表驱动程序–如果用不着InnoDB数据表，可以用这个选项节省一些内存。
innodb-file-per-table 为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里(后者是默认设置)。该选项始见于MySQL 4.1。
innodb-open-file = n InnoDB数据表驱动程序最多可以同时打开的文件数(默认设置是300)。如果使用了innodb-file-per-table选项并且需要同时打开很多数据表的话，这个数字很可能需要加大。
innodb_data_home_dir = p InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。
innodb_data_file_path = ts 用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend属性和一个最大长度(max:n)。例如，ibdata1:1G; ibdata2:1G:autoextend:max:2G的意思是: 表空间文件ibdata1的最大长度是1GB，ibdata2的最大长度也是1G，但允许它扩充到2GB。除文件名外，还可以用硬盘分区的设置名来定义表 空间，此时必须给表空间的最大初始长度值加上newraw关键字做后缀，给表空间的最大扩充长度值加上raw关键字做后缀(例如/dev/hdb1: 20Gnewraw或/dev/hdb1:20Graw); MySQL 4.0及更高版本的默认设置是ibdata1:10M:autoextend。
innodb_autoextend_increment = n 带有autoextend属性的表空间文件每次加大多少兆字节(默认设置是8MB)。这个属性不涉及具体的数据表文件，那些文件的增大速度相对是比较小的。
innodb_lock_wait_timeout = n 如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。
innodb_fast_shutdown 0/1 是否以最快的速度关闭InnoDB，默认设置是1，意思是不把缓存在INSERT缓存区的数据写入数据表，那些数据将在MySQL服务器下次启动 时再写入 (这么做没有什么风险，因为INSERT缓存区是表空间的一个组成部分，数据不会丢失)。把这个选项设置为0反面危险，因为在计算机关闭时，InnoDB 驱动程序很可能没有足够的时间完成它的数据同步工作，操作系统也许会在它完成数据同步工作之前强行结束InnoDB，而这会导致数据不完整。
mysqld程序：InnoDB–日志
innodb_log_group_home_dir = p 用来存放InnoDB日志文件的目录路径(如ib_logfile0、ib_logfile1等)。在默认的情况下，InnoDB驱动程序将使用 MySQL数据目录作为自己保存日志文件的位置。
innodb_log_files_in_group = n 使用多少个日志文件(默认设置是2)。InnoDB数据表驱动程序将以轮转方式依次填写这些文件; 当所有的日志文件都写满以后，之后的日志信息将写入第一个日志文件的最大长度(默认设置是5MB)。这个长度必须以MB(兆字节)或GB(千兆字节)为单 位进行设置。
innodb_flush_log_at_trx_commit = 0/1/2 这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。设置值0的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。
innodb_flush_method = x InnoDB日志文件的同步办法(仅适用于UNIX/Linux系统)。这个选项的可取值有两种: fdatasync，用fsync()函数进行同步; O_DSYNC，用O_SYNC()函数进行同步。
innodb_log_archive = 1 启用InnoDB驱动程序的archive(档案)日志功能，把日志信息写入ib_arch_log_n文件。启用这种日志功能在InnoDB与 MySQL一起使用时没有多大意义(启用MySQL服务器的二进制日志功能就足够用了)。
mysqld程序–InnoDB：缓存区的设置和优化
innodb_log_buffer_pool_size = n 为InnoDB数据表及其索引而保留的RAM内存量(默认设置是8MB)。这个参数对速度有着相当大的影响，如果计算机上只运行有 MySQL/InnoDB数据库服务器，就应该把全部内存的80%用于这个用途。
innodb_log_buffer_size = n 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。
innodb_additional_men_pool_size = n 为用于内部管理的各种数据结构分配的缓存区最大长度(默认设置是1MB)。
innodb_file_io_threads = n I/O操作(硬盘写操作)的最大线程个数(默认设置是4)。
innodb_thread_concurrency = n InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
mysqld程序：其它选项
bind-address = ipaddr MySQL服务器的IP地址。如果MySQL服务器所在的计算机有多个IP地址，这个选项将非常重要。
default-storage-engine = type 新数据表的默认数据表类型(默认设置是MyISAM)。这项设置还可以通过–default-table-type选项来设置。
default-timezone = name 为MySQL服务器设置一个地理时区(如果它与本地计算机的地理时区不一样)。
ft_min_word_len = n 全文索引的最小单词长度工。这个选项的默认设置是4，意思是在创建全文索引时不考虑那些由3个或更少的字符构建单词。
Max-allowed-packet = n 客户与服务器之间交换的数据包的最大长度，这个数字至少应该大于客户程序将要处理的最大BLOB块的长度。这个选项的默认设置是1MB。
Sql-mode = model1, mode2, … MySQL将运行在哪一种SQL模式下。这个选项的作用是让MySQL与其他的数据库系统保持最大程度的兼容。这个选项的可取值包括ansi、db2、 oracle、no_zero_date、pipes_as_concat。
mysqldump程序
quick 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项
max_allowed_packet = 32M
mysql程序
no-auto-rehash
default-character-set=utf8
safe-updates 仅仅允许使用键值的 UPDATEs 和 DELETEs .
myisamchk程序
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M
mysqlhotcopy程序
interactive-timeout
mysqld_safe
open-files-limit = 8192 增加每个进程的可打开文件数量.警告: 确认你已经将全系统限制设定的足够高!打开大量表需要将此值设大
client
default-character-set=utf8
MySQL基础知识
MYSQL和SQL入门
嵌入式服务器
嵌入式服务器库：libmysqld
嵌入式客户端库：libmysql
表现为库函数形式的服务器/客户端，可以把它嵌入到程序里以编写出独立的基于MySQL的应用程序
查看数据表的结构
- desc database_name;
- explain database_name;
- show fields from database_name [like "%keyword"];
- show columns from database_name [like "%keyword"];
mysqlshow程序
用show语句能看到的信息也都能用mysqlshow程序查看到
创建数据表时几个特殊子句
PRIMARY KEY (column_name_1,column_name_2...) 创建主键
foreign key (column_name) references target_database_name (target_column_name) 创建外键
engine = engine_name 指定存储引擎，常用存储引擎：InnoDB、MyISAM
添加数据
- 利用insert语句添加数据
insert into tbl_name(column1, column2...) values(value1, value2...),(value1, value2...);
insert into tbl_name set column1=value1, column2=value2, ...; - 通过从文件中读取来添加新行
- 运行sql文件
- mysql sampdb < insert_member.sql 未登录mysql客户端
- source insert_member.sql 已登录mysql客户端
- LOAD DATA语句
LOAD DATA LOCAL INFILE 'file_path' INTO TABLE tbl_name;
默认情况下，文件各数据列的值以制表符分隔(N表示值为null)，各数据行以换行符分隔，排序顺序与数据表的结构排序一致
LOCAL可以使客户端程序读取数据文件并发送到服务器加载
如果LOCAL功能处于禁用的状态可以--local-infile选项开启(mysql --local-infile) - mysqlimport工具程序
mysqlimport --local db_name file_path
文件名中第一个句号字符(.)之前的字符串对应数据表的名字，慎重数据文件的名字！！！
- 运行sql文件
NULL值
NULL值使用IS NOT NULL 或者 IS NULL 或者 <=> 来判断
对于包含NULL值的数据行，如果设定按升序排列，它们将出现在查询结果的开头；如果设定按降序排列，它们将出现在查询的末尾
随机抽取一条或一组数据
e.g select last_name , first_name from president order by rand() limit 2,3;
设置和使用SQL变量
命名语法：@变量名
赋值语法：@变量名 := 变量值
SET语句也能用来对变量赋值
with rollup子句
对数据行分组统计结果做进一步统计而得到一个额外的超级统计结果行
使用SQL管理数据
SQL模式
- 模式值
不区分字母的大小写
e.g：STRICT_ALL_TABLES、STRICT_TRANS_TABLES、TRANDITIONAL、ANSI_QUOTES、ANSI、... - 设置SQL模式
给出一个由单个模式值或多个以逗号分隔的模式值构成的值，或者给出一个空字符串以清除该值- 启动MySQL服务器的时候设置SQL模式
--sql-mode="STRICT_ALL_TABLES,STRICT_TRANS_TABLES" - 在配置文件中设置SQL模式选项
sql-mode="STRICT_ALL_TABLES,STRICT_TRANS_TABLES" - 运行时改变SQL模式(本次会话专用)
SET sql_mode = "STRICT_ALL_TABLES,STRICT_TRANS_TABLES" - 运行时改变SQL模式(全局性设置)
SET GLOBAL sql_mode = "STRICT_ALL_TABLES,STRICT_TRANS_TABLES"
- 启动MySQL服务器的时候设置SQL模式
- 查看SQL模式
SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;
字符集和排序方式
- 字符集和排序方式设定
- 启动服务器时
mysql --character-set-server=utf8 --collation-server=utf8_general_ci - 启动服务器后
设置系统变量character-set-server、collation-server - 设定数据库
CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; - 设定数据表
CREATE TABLE tbl_name (...) CHARACTER SET utf8 COLLATE utf8_general_ci; - 设定数据列
CREATE TABLE tbl_name (
c CHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci
) - 按照特定排序方式对字符串值排序
SELECT c FROM tbl_name ORDER BY c COLLATE latin1_spanish_ci;
- 启动服务器时
- 字符集和排序方式查看
- 查看可用的字符集
SHOW CHARACTER SET; - 查看可用的排序方式
SHOW COLLATION; - 查看当前的字符集
SHOW VARIABLES LIKE 'character_set_%'; - 查看当前的排序方式
SHOW VARIABLES LIKE 'collation_%';
- 查看可用的字符集
数据库操作
MySQL把数据库的字符集和排序方式等属性保存在数据目录下的对应数据库子目录的db.opt文件中
- 数据库的选定
- 已登录mysql客户端
use database_name; - 未登录mysql客户端
mysql -p -h host_name -u user_name database_name;
- 已登录mysql客户端
- 数据库的创建
CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation]; - 数据库的删除
DROP DATABASE db_name; - 数据库的变更
ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];
存储引擎
- 查看可供使用的存储引擎
show engines;
Transactions栏的值表明存储引擎是否支持事务
XA栏的值表明存储引擎是否支持分布式事务处理
Savepoints的值表明存储引擎是否支持部分事务回滚 - 查看支持事务处理的存储引擎
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE TRANSACTIONS='YES'; - 创建数据表时会同时在与数据表文件同一目录下创建一个同名的.frm后缀文件，用于保存数据表的格式
- 创建数据表时指定存储引擎使用 ENGINE=INNODB 语句
- 服务器启动时，可以通过使用--default-storage-engine选项来指定默认存储引擎
- 服务器运行期间，可以通过设置系统选项storage-engine改变默认存储引擎
- 查看数据表使用的存储引擎
show create table tbl_name;
show table status;
SELECT engine FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like "%tbl_name%"; - 修改数据表的存储引擎
ALTER TABLE tbl_name ENGINE = InnoDB;
数据表操作
- 数据表的创建
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [LIKE tbl_name | select语句] [ENGINE = InnoDB]; - 数据表的删除
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name[, tbl_name1, tbl_name2...]; - 数据表的变更
ALTER TABLE tbl_name action [, action...]- 改变数据列的数据类型
ALTER TABLE tbl_name MODIFY column_name column_definition;
ALTER TABLE tbl_name CHANGE column_name new_column_name column_definition; - 修改数据表的存储引擎
ALTER TABLE tbl_name ENGINE = engine_name; - 重命名数据表
ALTER TABLE tbl_name RENAME TO new_tbl_name;
RENAME TABLE old_tbl_name TO new_tbl_name [,old_tbl_name_1 TO new_tbl_name_1];
- 改变数据列的数据类型
MERGE数据表(分表处理)
MERGE存储引擎把一组具有"完全一样的结构"(数据列必须按照同样的顺序定义同样的名字和类型)"MyISAM数据表"当作一个逻辑数据表来对待，使得可以同时对它们进行查询
- 创建
CREATE TABLE tbl_name (...) ENGINE = merge UNION = (tbl_name1, tbl_name1...) [INSERT_METHOD = [NO | FIRST | LAST]] - 修改
alter TABLE tbl_name UNION = (tbl_name1, tbl_name1...) [INSERT_METHOD = [NO | FIRST | LAST]]
分区数据表(分区处理)
分区数据访问被分别存储在不同区的多个不同存储引擎数据表的内容的一个真实的数据表
- 创建
CREATE TABLE tbl_name (...) PARTITION BY 分区函数 (分区选项);
分区函数:根据取值范围进行分区RANGE、根据列表进行分区LIST、根据散列值进行分区HASH
e.g:
CREATE TABLE tbl_name (
dt DATETIME NOT NULL,
info VARCHAR(100) NOT NULL,
INDEX(dt),
) PARTITION BY RANGE(YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2005),
PARTITION p1 VALUES LESS THAN (2006),
PARTITION p2 VALUES LESS THAN (2007),
PARTITION p3 VALUES LESS THAN (2008),
PARTITION p4 VALUES LESS THAN MAXVALUE
) - 修改
e.g:
ALTER TABLE tbl_name REORGANIZE PARTITION p4
INTO (
PARTITION p4 VALUES LESS THAN (2009),
PARTITION p5 VALUES LESS THAN MAXVALUE
)
federated数据表
federated存储引擎可以访问在其他主机上由另一个MySQL服务器实际管理的数据表
federated数据表的数据列必须与远程数据表相同，并给出相应的连接字符串让本地服务器知道如何连接远程服务器
CREATE TABLE tbl_name (...) ENGINE = FEDERATED CONNECTION = '连接字符名/远程数据表名'
连接字符名:使用create server语句创建一个存储服务器定义(需要超级权限),定义保存为mysql数据库中的servers数据表的一个数据行
create server语句如下:
CREATE SERVER 连接字符名
FOREIGN DATA wrapper mysql
OPTIONS (
USER 'user_name',
PASSWORD 'secret',
HOST 'host_name',
DATABASE 'db_name',
PORT '3306'
)
索引
MyISAM支持BLOB、TEXT、FULLTEXT、SPATIAL索引，InnoDB支持BLOB、TEXT索引
primary KEY的名字总是PRIMARY,数据列必须具备NOT NULL属性
-
创建
- ALTER TABLE tbl_name
ADD (INDEX | UNIQUE | PRIMARY KEY | FULLTEXT | SPATIAL) [index_name](column_name_1[(length)][, column_name_2[(length)]...])
[,(INDEX | UNIQUE | PRIMARY KEY | FULLTEXT | SPATIAL) [index_name](column_name_1[(length)][, column_name_2[(length)]...])] - create table tbl_name (
...column definitions...
INDEX | UNIQUE | PRIMARY | FULLTEXT | SPATIAL) [index_name] (column_name_1[(length)],column_name_2[(length)]...)
) - CREATE INDEX | UNIQUE | PRIMARY | FULLTEXT | SPATIAL) index_name(column_name_1[(length)],column_name_2[(length)]...)
- ALTER TABLE tbl_name
-
删除
- drop index index_name on tbl_name;
drop index `PRIMARY` on tbl_name;(删除主键) - ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP `PRIMARY` INDEX;(删除主键)
- drop index index_name on tbl_name;
-
FULLTEXT索引
- 特点
- FULLTEXT索引只能在MyISAM数据表里创建
- 数据类型只能是CHAR、VARCHAR、TEXT
- 全文搜索忽略出现率50%以上的单词、常用单词、太短的单词(太短的单词指的是单词的长度，默认单词的最小值为4个字符，最大值为84个字符；可通过修改配置文件中mysqld块的ft_min_word_len和ft_max_word_len两个参数进行调整，然后重启服务器并重建索引(REPAIR TABLE tbl_name QUICK;))
- 用MATCH操作符列出被搜索的数据列，用AGAINST操作符给出搜索字符串
- 搜素字符串可以包含多个单词，使用空格分隔
- 自然语言模式
- 查询结果按照相关程度递减的顺序排序，查看相关程度如下：
select match(column_name) AGAINST(search_char) AS relevance FROM tbl_name;
- 查询结果按照相关程度递减的顺序排序，查看相关程度如下：
- 布尔模式
- 查询结果不再按照相关程度排序
- 在搜索一个短语时，要求所有单词必须按照短语的单词顺序出现，使用双引号把短语括起来即可
- 搜索字符串修饰符
- 在单词前面加上一个加号(+)表示该单词必须出现在匹配数据行中
- 在单词前面加上一个减号(-)表示该单词不得出现在匹配数据行中
- 在单词后面加上一个型号(*)表示匹配以该单词开头的所有单词
- 查询扩展模式
- SQL语句
SELECT * FROM tbl_name
WHERE MATCH(index_column_name[ ,index_column_name_1...])
AGAINST([+ | -]search_char[*] [IN NATURAL LANGUAGE MODE | IN BOOLEAN MODE | WITH QUERY EXPANSION]);
- 特点
获取数据库的元数据
- 用show语句获取元数据
- 列出服务器所管理的数据库
SHOW DATABASES; - 查看给定数据库的CREATE DATABASE语句
SHOW CREATE DATABASE db_name; - 列出默认数据库或给定数据库里的数据表
SHOW TABLES;
SHOW TABLES FROM db_name; - 查看数据表的CREATE TABLE语句
SHOW CREATE TABLE tbl_name; - 查看数据表里的数据列或索引信息
SHOW COLUMNS FROM tbl_name;
DESC tbl_name;
EXPLAIN tbl_name;
SHOW INDEX FROM tbl_name; - 查看默认数据库或某给定数据库里的数据表的描述性信息
SHOW TABLE STATUS;
SHOW TABLE STATUS FROM db_name; - 检测给定数据库是否存在
SELECT * FROM tbl_name WHERE FALSE;
- 列出服务器所管理的数据库
- 从INFOMATION_SCHEMA数据库获取元数据
直接使用SQL语句查询INFOMATION_SCHEMA数据库相关数据表内容 - 从命令行获取元数据(运行以下命令时记得加必要的连接参数选项)
- 服务器所管理的数据库
mysqlshow - 列出某给定数据库里的数据表
mysqlshow db_name - 查看某给定数据表里的数据列信息
mysqlshow db_name tbl_name - 查看某给定数据库里的索引信息
mysqlshow --keys db_name tbl_name - 查看某给定数据库里的数据表的描述性信息
mysqlshow --status db_name - 查看给定数据库下【所有】数据表结构
mysqldump --no-data db_name [tbl_name]...
- 服务器所管理的数据库
数据类型
查看MySQL对数据表优化类型的建议
MySQL建议可以改善相关数据表的查询性能并减少数据表的存储空间占用量
但是只有在数据足够多的情况下，MySQL建议才会真正得到优化，需要按找实际情况判断
select * from tbl_name procedure analyse()G
存储程序
复合语句和语句分隔符
复合语句由BEGIN开头，END结束,在它们之间可以写出任意数量的语句，这些语句必须以分号(;)这个分隔符彼此隔开
由于分隔符冲突，需要使用delimiter命令临时改变分隔符，在复合语句块后面再恢复过来
存储例程
创建存储例程(存储函数和存储过程)必须要拥有数据库的create routine权限
默认情况下，服务器采用自动化的权限授予/撤销机制(如果想取消该机制，把automatic_sp_privileges系统变量设置为0即可)
-
存储函数用create function语句来创建,直接使用函数名调用
如果log_bin_trust_function_creators系统变量没有被激活，那就必须具备super权限才能创建函数
delimiter $$
CREATE FUNCTION function_name(param1, param2...)
RETURNS INT
[DETERMINISTIC | NO | READS SQL DATA]
BEGIN
...SQL definition...
RETURN(0);
END$$
delimiter ; -
存储过程用create procedure语句来创建,使用CALL语句调用
delimiter $$
CREATE PROCEDURE procedure_name([IN | OUT | INOUT] param_name_1 DATA_TYPE [, [IN | OUT | INOUT] param_name_2 DATA_TYPE...])
BEGIN
...SQL definition...
END$$
delimiter ;
触发器
需要拥有数据表的trigger权限才能创建和删除触发器
- 创建语句如下：
delimiter $$
CREATE TRIGGER trigger_name
(BEFORE | AFTER)
(INSERT | UPDATE | DELETE)
ON tbl_name FOR EACH
BEGIN
...SQL definition...
END$$
delimiter ;
事件
- 创建或删除事件必须拥有数据库的event权限
- 使用事件必须启用事件调度器
在配置文件中mysqld块添加event_scheduler=ON - 查看事件调度器的状态
SHOW VARIABLES LIKE 'event_scheduler'; - 启动或停止事件调度器
SET GLOBAL event_scheduler = (ON | OFF); - 激活或禁用某个事件
ALTER EVENT event_name ( ENABLE | DISABLE ); - 创建事件
delimiter $$
CREATE EVENT event_name
ON SCHEDULE (EVERY n (HOUR | DAY | MONTH) | AT datetime)
[STARTS datetime] [ENDS datetime]
DO
BEGIN
...SQL definition...
END$$
delimiter ;
查询优化
使用索引优化
- 尽量为用来搜索、分类、分组或者相联结的数据列编制索引，不要为作为输出显示的数据列编制索引
- 综合考虑各数据列的维度势
- 索引的数据类型应尽量“小”
- 为字符串值的前缀编索引
- 充分利用最左边的前缀
- 适可而止，不要建立过多的索引
- 让索引的类型与打算进行的比较操作的类型保持匹配
- 在索引定义加上USING BTREE创建"B树"索引
- 如果匹配模式是以一个纯字符串而不是一个通配符开头的话，"B树"索引可以用于使用LIKE操作符进行的模式匹配操作
- 利用MySQL内置比较函数(比如STRCMP函数)对数据列值进行处理，任何索引都会失效
- 利用“慢查询”日志找出性能低劣的查询
- 可用mysqldumpslow工具程序对日志汇总
MySQL的查询优化程序
- 对数据表进行分析
使用ANALYSE TABLE tbl_name;让服务器对键值进行一次分析，生成关于索引值分布情况的统计数据 - 使用EXPLAIN语句检查优化器操作
- type字段：表示数据是如何从数据表读出的
- possible_key字段：表示候选索引
- key字段：表示实际使用索引
- rows字段：表示优化器对在查询过程的每个阶段需要检查多少个数据行的预估值
- ref字段：表示引用值
- 向优化器提供提示或在必要时屏蔽之
- 利用FORCE INDEX、USE INDEX、IGNORE INDEX限定词告诉服务器使用哪些索引
SELECT * FROM tbl_name (FORCE | USE | IGNORE) INDEX (column_name_1 [, column_name_2]); - 利用STRAIGHT_JOIN强制优化器按特定的顺序使用数据表
SELECT * FROM tbl_name_1 STRAIGHT_JOIN tbl_name_2 ON tbl_name_1.column_name_1=tbl_name_2.column_name_2 - from后面列出的数据表时，应该把那个将被选取的数据行个数最少的数据表放在第一个
要是对是哪个数据表没有把握，那就把数据行个数最多的那个数据表放在第一个
- 利用FORCE INDEX、USE INDEX、IGNORE INDEX限定词告诉服务器使用哪些索引
- 尽量使用数据类型相同的数据列进行比较
- 使带索引的数据列在比较表达式中单独出现，即不对数据列进行计算
- 不要在LIKE模式的开始位置使用通配符
- 利用优化器的长处，尽可能把子查询改写成联结查询
- 在观察查询运行情况时，应实验各种查询的变化格式，而且要多次运行它们，避免由于缓存造成的影响
- 避免过多使用MySQL的自动类型转换功能
为提高查询效率而挑选数据类型
- 尽量使用数值操作，少使用字符串操作
- 如果“小”类型够用，就不要选用“大”类型
- 选用适用于存储引擎的格式
- 对于MyISAM数据表，尽量使用char存储字符串数据
- 对于InnoDB数据表，尽量使用varchar存储字符串数据
- 尽量把数据列声明为NOT NULL
- 考虑使用ENUM数据列
- 利用procedure analyse()语句分析数据表
select * from tbl_name procedure analyse([数据列的维度, 数据列的长度]); - 对容易产生碎片的数据表进行管理
- OPTIMIZE TABLE tbl_name;用来清理MyISAM数据表里的碎片
- 对各种存储引擎都适用的碎片整理
mysqldump db_name tbl_name > dump.sql
mysql db_name < dump.sql
- 把数据压缩到BLOB或TEXT数据列里
- 使用人造索引
- 尽量避免对很大的BLOB或TEXT值进行检索
- 把BLOB或TEXT数据列剥离到单独一个数据库里
调度和锁定问题
- MySQL的默认调度策略
- 写入比读取有更高的优先权
- 对数据表的写操作必须按照“写”请求先来后到的顺序一个接一个地进行
- 对同一个数据表进行的读操作可以同时进行
- 可以改变MySQL调度策略的语句修饰符
- LOW_PRIORITY用在DELETE、INSERT、LOAD DATA、REPLACE、UPDATE
- HIGH_PRIORITY用在SELECT、INSERT
- DELAYED用在INSERT、REPLACE
- low_priority和high_priority限定符只对支持数据表锁定功能的存储引擎(MyISAM、MERGE、MEMORY)
- delayed限定符对MYISAM、MEMORY、ARCHIVE和BLACKHOLE存储引擎都可以使用
系统管理员所完成的优化
- 使文件打开操作的次数最小化，尽量保持文件打开状态，由table_cache系统变量控制
查看数据表缓存的工作情况：SHOW STATUS LIKE 'Opened_tables'; - MyISAM键缓冲区用来保持MyISAM数据表有关索引的操作的索引块，由key_buffer_size系统变量控制
- 使用MyISAM额外的键缓存并分配给特定的数据表使用(由于服务器重启后，这些专用键缓存就会消失，所以可以把它们放到一个文件里并用--init-file服务器选项在启动时运行)
- 创建一个新的键缓存，让它大到足以容纳来自member数据表的索引
set global member_cache.key_buffer_size = 1024 * 1024; - 把member数据表指定给这个键缓存
cache index member in member_cache; - 把member数据表的索引提前加载到它的键缓存里去
load index into cache member;
- 创建一个新的键缓存，让它大到足以容纳来自member数据表的索引
- InnoDB存储引擎有它自己用来缓冲数据和索引的缓存，由innodb_buffer_pool_size系统变量控制
- InnoDB存储引擎的日志缓冲区，由innodb_log_buffer_size系统变量控制
- 使用查询缓存
- 查看服务器是否支持查询缓存
show variables like 'have_query_cache'; - 涉及的系统变量(在配置文件mysqld块下设置)
- query_cache_type决定查询缓存的操作模式
- 0：不缓存查询结构和不检索已被缓存的结果
- 1：缓存查询，但不包括以SELECT SQL_NO_CAHCE
- 2：只缓存以SELECT SQL_CACHE开头的查询
- query_cache_size决定了为查询缓存分配的内存大小，以字节为单位
- query_cache_limit设置能够缓存的最大结果集的大小，比这个值大的查询结果不能被缓存
- query_cache_type决定查询缓存的操作模式
- 在select关键字后面添加一个修饰符，客户程序也可以控制各个查询的缓存
如果缓存模式是1或者2,那么SELECT SQL_CACHE将使可缓存查询的结果被缓存，SELECT SQL_NO_CACHE将使结果不被缓存
- 查看服务器是否支持查询缓存
硬件优化
- 在机器里安装更多的内存
- 如果有足够的RAM可以让所有的数据交换都发生在一个内存文件系统里，可以重新配置系统来删除所有的磁盘数据交换设备
- 添加更快的磁盘来改善I/O等待时间
- 在物理设备之间分散磁盘读写活动，提供并行度(分布式)
- 使用多处理器硬件
MySQL系统管理
MySQL系统管理简介
MySQL程序说明
- myisamchk
用来检查和修复MyISAM数据表、分析键分布情况、禁用或启用索引的工具程序
- myisampack
用来对数据表进行压缩并生成只读MyISAM数据表的工具程序
- mysql
具备命令行编辑功能、用来向MySQL服务器发送SQL语句的交互式程序，也可用于批量处理方式执行存放在文件里的语句
- mysql.server
用来启动和中止MySQL服务器的脚本
- mysql_config
当你准备编译一个基于MySQL的程序时，可以利用这个工具程序来确定该程序的标志
- mysql_install_db
用来对服务器的数据目录和权限表进行初始化的脚本
- mysqladmin
用来完成管理工作的工具程序
- mysqlbinlog
以文本格式显示二进制文件和中断日志内容的程序
- mysqlcheck
用来对数据表进行检查、修复、优化和分析的工具程序
- mysqld
MySQL服务器，只有先运行这个程序，客户程序才能访问在它管理之下的数据库
- mysqld_multi
用来同时启动和关闭多个服务器的脚本
- mysqld_safe
用来启动和监控MySQL服务器的脚本
- mysqldump
用来导出数据表内容的客户程序
- mysqlhotcopy
数据库备份实用工具程序
- mysqlimport
用来往数据表里批量加载数据的客户程序
- mysqlshow
用来查看关于数据库或数据表的信息的客户程序
- perror
显示出错代码含义的实用工具程序
MySQL的数据目录
数据目录的位置
- 设置数据目录
- 源码编译时，可以在运行configure工具的时候使用--localstatedir = dir_name命令行选项为数据目录另行指定一个默认的位置
- 启动服务器时，使用--datadir = dir_name选项可以指定数据库目录的位置
- 在配置文件的mysqld块下配置datadir = dir_name
- 查看数据目录路径
- 查看配置文件my.cnf(使用mysqld --verbose --help命令查看配置文件的路径)
- 进入mysql客户程序运行show variables like 'datadir';语句
- 使用mysqladmin客户程序(mysqladmin variables)
- MySQL版本升级或转储时，让服务器对全体数据库和数据表的名字进行必要的编码
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names - 转储数据库
mysqldump --database db_name > db_name.sql
用MySQL程序重新加载转储文件
mysql < db_name.sql - MySQL状态文件和日志文件
- 进程ID文件
- 服务器进程ID
- 相关配置选项:pid = path
- 默认名为HOSTNAME.pid
- 错误日志
- 启动或关闭事件和查询信息
- 相关配置选项:log-error [=file_name]
- 默认名为HOSTNAME.err
- 常规查询日志
- 连接或断开事件的查询信息
- 相关配置选项:log [=file_name]
- 默认名为HOSTNAME.log
- 二进制日志
- 修改数据的语句的二进制表示,使用mysqlbinlog实用工具查看
- 相关配置选项:log-bin [=file_name]
- 默认名为HOSTNAME-bin.nnnnnn
- 二进制日志的索引文件
- 现有二进制日志文件的清单
- 相关配置选项:log-bin-index [=file_name]
- 默认名为HOSTNAME-bin.index
- 延迟日志
- 从属服务器从主服务器收到的数据修改信息,使用mysqlbinlog实用工具查看
- 相关配置选项:log-relay [=file_name]
- 默认名为HOSTNAME-relay-bin.nnnnnn
- 延迟日志索引
- 当前延迟日志文件清单
- 相关配置选项:relay-log-index [=file_name]
- 默认名为HOSTNAME-relay-bin.index
- 主服务器信息
- 用于连接服务器的参数
- 默认名为master.info
- 延迟信息
- 延迟日志处理的状态
- 默认名为relay-log.info
- 慢查询日志
- 耗时很长的语句的文本
- 相关配置选项:log-slow-queries [=file_name]
- 默认名为HOSTNAME-slow.log
- 进程ID文件
重新安置数据目录的内容
- 具体思路
- 备份服务器数据，关闭服务器
- 在服务器启动时指定一个选项：使用--datadir = dir_name选项可以指定数据库目录的位置 或者 在配置文件的mysqld块下配置datadir = dir_name
- 移动要安置的文件或目录，然后在指定新位置处生成一个符号链接
- 重新安置整个数据目录直接使用以上具体思路进行即可
- 重新安置各个数据库
mysqladmin -p -u root shutdown cd source_path tar cf - db_name | ( cd target_path; tar xf -) rm -rf db_name ln -s target_path/db_name db_name mysqld_safe & - 重新安置各个数据表的两个条件
- 使用的是unix操作系统，数据表是一个MyISAM数据表
- SHOW VARIABLE LIKE 'HAVE_SYMLINK'的结果是YES
- 重置安置InnoDB共享表空间修改的配置选项为innodb_data_home_dir和innodb_data_file_path
MySQL数据库系统的日常管理
安装MySQL软件后的初始安防设置
- 设置口令
- UPDATE user SET password = PASSWORD('your_password') WHERE user = 'username';
FLUSH PRIVILEGES; - SET PASSWORD FOR usrname@host = PASSWORD('your_password');
- UPDATE user SET password = PASSWORD('your_password') WHERE user = 'username';
- 查看用户权限
SHOW GRANTS FOR USERNAME@HOST - 撤销用户权限
REVOKE GRANT OPTION ON . FROM USERNAME@HOST - 删除用户
DROP USER USERNAME@HOST
安排MySQL服务器的启动和关停
- 使用一个低权限登录账号来运行MySQL服务器
- 关停正在运行的服务器
mysqladmin -p -u root shutdown - 选择用于运行mysqld的登录账号
- 使用系统通常建立账号的步骤建立你所选用户名的登录账号
groupadd -r user_group useradd -g username -r -d ~/username/ username chown -R username.user_group ~/username/- 修改MySQL数据目录、子目录和该目录下文件的用户和组的所有权，以便mysql用户占用
chown -R mysql data_path chgrp -R mysql data_path- 良好的安全预防措施是设置数据目录的访问模式，防止其它外人入侵
chmod -R go-rwx data_path - 关停正在运行的服务器
- 在unix上启动服务器的方式
- 直接调用mysqld，这大概是最不常见的方法
不过mysqld --verbose --help是一个寻找服务器支持什么启动选项的有用命令 - 调用mysqld_safe脚本程序
mysqld_safe调用服务器，然后监视服务器，当其意外中断时重新启动它
对于BSD格式的系统，为了让启动脚本在系统开机执行：在/etc目录中通常有几个文件用于启动服务器 这些文件的名字通常以rc开头，如rc.local名字的文件，专门用于启动本地安装的服务器，在这样基于rc的系统上，可以把如下所示添加到rc.local中，以便启动服务器 if [ -x mysqld_safe的全路径 ]; then mysqld_safe的全路径 & fi - 调用mysql.server脚本程序
对于SystemV格式的系统，可以安装mysql.server，并把它复制到相应/etc下的运行级别目录中，操作如下:
系统具有用于启动脚本管理用的chkconfig命令，可如下操作:cp mysql.server /etc/init.d/mysql cd /etc/init.d chmod +x mysql cd /etc/rc2.d ln -s ../init.d/mysql s99mysql把mysql.server脚本从其现有位置复制到init.d目录中，并使其可执行 cp mysql.server /etc/init.d/mysql chmod +x /etc/init.d/mysql 寄存该脚本并启动它 chkconfig --add mysql chkconfig mysql on 为了检查该脚本是否正确启用，运行加油--list选项的chkconfig chkconfig --list mysql - 如果需要协调启动多个MySQL服务器，请使用mysqld_multi脚本
- 直接调用mysqld，这大概是最不常见的方法
- 服务器程序使用的选项组
mysqld [mysqld]、[server]、[mysqld-X.Y] mysqld_safe [mysqld]、[server]、[mysqld_safe]、[safe_mysqld] mysql.server [mysqld]、[server]、[mysqld-server]、[mysql.server] mysqld [server]、[embedded]、[appname_server] - 关闭服务器
- mysqladmin -p -u root shutdown
- kill -TERM 服务器PID
- kill -9 服务器PID 这种方式不建议使用，因为内存中可能有未更新的修改，而且有数据表不一致的危险
- 忘记密码时可以使用如下操作进行重新设置密码：
使用--skip_grant_tables选项禁止使用验证连接用到的权限表格 mysql --skip_grant_tables 告知服务器要重新读取权限表，使用表格进行访问控制 flush privileges; 设置密码 SET PASSWORD FOR username@host = PASSWORD('your_password'); 修改口令之后，关闭服务器并按正常过程重新启动它即可
对MySQL服务器的连接监听情况进行控制
- --skip-networking选项可以使MySQL服务器都监听着供TCP/IP连接使用的网络接口
- --port选项可另行指定一个不同的端口号
- --bind-address选项对服务器在监听客户连接时使用的IP地址进行设定
- --protocol=socket选项尝试建立一个unix域套接字文件上的监听
管理MySQL用户账户
- MySQL的权限数据表(在mysql数据库内)
user 可连接到服务器的用户和他们的全局级权限
db 数据库级权限
tables_priv 数据表级权限
columns_priv 数据列级权限
procs_priv 与存储例程有关的权限
- 高级MySQL账户管理操作
- create user:创建一个新账号并为它设置一个口令,只创建账户不分配权限，权限分配工作由GRANT语句完成
CREATE USER account [IDENTIFIED BY 'your_password'];
e.g: create user 'joe'@192.168.128.3 IDENTIFIED BY 'your_password'
create user 'joe'@192.168.128.% IDENTIFIED BY 'your_password'
create user 'joe'@192.168.128.0/255.255.255.0 IDENTIFIED BY 'your_password' - drop user:删除一个现有的账户和与该账户相关联的全部权限
DROP USER username; - rename user:改变现有账户的名字
RENAME USER from_account TO to_account - 对账户授权
- grant语句语法
GRANT privileges [(columns)] // 账户可拥有的权限 ON what // 权限级别限定符 TO account [IDENTIFIED BY 'your_password'] [REQUIRE encryption requirements] [WITH grant or resource management options]- 账户可拥有的权限
数据库管理权限 create user 使用高级账户管理语句 file 读、写MySQL服务器主机上的文件 grant option 把本账户的权限授予其他账户 process 查看在服务器里运行的线程的信息 reload 重新加载权限数据表或者更新日志及缓存 replication client 查询主/从服务器的运行地点 replication slave 以复制的从服务器运行 show database 用show database语句查看全体数据库的名字 shutdown 关闭服务器 super 用kill命令终止线程以及进行其他超级用户操作 数据库对象操作权限 alter 更改数据表和索引的定义 alter routine 更改或删除存储函数和存储过程 create 创建数据库和数据表 create routine 创建存储函数和存储过程 create temporary tables 用temporary关键字创建临时数据表 create view 创建视图 delete 删除数据表里的现有数据行 drop 删除数据库、数据表和其他对象 event 为事件调度程序创建、删除或修改各种事件 execute 执行存储函数和存储过程 index 创建或者删除索引 insert 往数据表里插入新数据行 lock tables 用lock tables语句明确地锁定数据表 reference 未使用(保留供今后使用) select 检索数据表里的数据行 show view 用show create view语句查看视图的定义 trigger 创建或者删除触发器 update 修改数据行 其他权限 all 所有操作(但不包括grant权限) usage 一个特殊的“无权限”全权限 grant option grant option权限- 权限级别限定符
ON *.* 全局级权限，其作用范围是所有数据库及其中的所有对象 ON * 如果没有指定默认的数据库，这是全局级权限；否则，是默认数据库上的数据库级权限 ON db_name.* 数据库级权限，作用范围是指定数据库里的所有对象 ON db_name.tbl_name 数据表级权限，作用范围是指定数据库里指定数据表里的所有数据列 ON tbl_name 数据表级权限，作用范围是默认数据库里指定数据库的所有数据列 ON db_name.routine_name 存储例程权限，作用范围是指定数据库里的指定例程- 使用"无权限"的USAGE权限:保持其现有权限的情况下去修改某个账户与权限无关的特性(口令、是否使用SSL、资源占用量)
GRANT USAGE ON *.* TO account IDENTIFIED BY 'new_password'; GRANT USAGE ON *.* TO account REQUIRE SSL; GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50 MAX_USER_CONNECTIONS 10; - 查看账户的权限
SHOW GRANTS FOR username@host; SHOW GRANTS SHOW GRANTS FOR CURRENT_USER(); - 撤销权限
REVOKE privileges [(columns)] ON what FROM account e.g 撤销账户对数据行进行删除和修改的权限 reovke delete,update on db_name.tbl_name from username@host; 撤销账户的所有权限 revoke all,grant option on db_name.tbl_name from username@host; 不再要求使用ssl来连接 grant usage on *.* to account require none; 撤销资源限制 grant usage on *.* to account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0
维护日志文件
- 特殊用途的日志选项
innodb_log_group_home_dir = dir_name InnoDB日志文件子目录
falcon_serial_log_dir = dir_name Falcon日志文件子目录 - 刷新日志
mysqladmin flush-logs命令 或 flush logs语句将导致服务器先关闭再打开日志文件 mysqladmin refresh命令将刷新日志，并刷新数据表缓存 - 出错日志
- 出错日志的信息量可以通过log-warnings选项来调控，该选项的可取值是从0到2
- log-error选项用于指定出错日志文件，默认文件名为hostname.err，文件路径尽量使用全路径名，避免不必要的解析问题
- 用来启动MySQL服务器尽量使用固定的账户，避免不必要的权限问题
- mysqld程序的默认行为是不创建出错日志的
- mysql_safe程序的默认行为的是创建出错日志的
- 若想把错误日志发送到syslog,则在执行mysql_safe脚本时用syslog选项取代log-error选项
来自mysqld程序和mysql_safe脚本的消息将分别带有一个mysqld或mysql_safe字样的前缀标签
若使用了syslog-tag=str选项，则前缀标签将变成mysqld-str或mysql_safe-str
- 常规查询日志
- log选项用于指定常规查询日志文件，默认文件名为hostname.log
- 慢查询日志:记载着需要很长时间才能执行完毕的select查询
- 判断"很长时间"的标准是long_query_time系统变量的值，以秒为单位，默认值是10秒
- log-slow-queries选项用于指定慢查询日志文件，默认文件名为hostname-slow.log
- log-short-format选项将导致服务器把较少的信息写入日志
- log-queries-not-using-indexes选项将导致服务器把在执行时没有用到任何索引的查询也记载到慢查询日志里
- log-slow-admin-statements选项将导致服务器把"慢"的系统管理语句如analyze table或者alter table等也记载到慢查询日志
- 二进制日志和二进制日志索引文件
- 记载数据修改事件，比如insert、delete、update等会导致'数据发生变化'的语句
- 若把某个MySQL服务器设置为复制机制中的主服务器以便从服务器对之复制的话，就必须启动二进制日志
- log-bin选项用于指定二进制日志文件，默认文件名以hostname-bin作为基本名生成一组顺序编号
- log-bin-index选项用于指定二进制日志索引文件，默认文件名是二进制日志文件的基本名加上一个".index"扩展名
- 中继日志和中继日志索引文件
- relay-log选项用于指定中继日志文件，默认文件名以hostname-relay-bin作为基本名生成一组顺序编号
- relay-log-index选项用于指定中继日志索引文件，默认文件名是中继日志文件的基本名加上一个".index"扩展名
- 在从服务器上，事件的接收和执行是由两个线程分别负责处理的，I/O线程负责从主服务器接收事件并把它们写入中继日志，SQL线程负责读取中继日志文件、执行事件以及在处理完每个文件后删除
- 日志数据表的使用
-
log-output选项为日志信息挑选一个输出目的地，值由一个或多个以逗号分隔的可取值构成:FILE(写入文件)、TABLE(写入数据表)或NONE(两处地方都不写入)
-
若把日志信息写入到数据表，则服务器使用mysql数据库里的general_log和slow_log数据表来实现其TABLE日志功能(在升级MySQL数据库版本的时候，务必使用mysql_upgrade脚本更新，确保这些数据表存在)
-
- 日志管理
- 日志轮转：适用于具有固定文件名的日志文件，例如一般日志文件和慢查询日志文件
原理：假定一般查询日志文件名叫qlog，在第一次轮转时，将qlog改名为log.1,并且让服务器开始写一个新的日志文件，在第二次轮转时，把qlog.1改名为qlog.2，qlog改名为qlog.1,并且让服务器开始写一个新的qlog文件。- 最好使用logrotate实用工具程序来安装MySQL发行版本的mysql-log-rotate脚本，而不是使用自己写的脚本程序,然后查找mysql-log-rotate(对于RPM发行版本是在/usr/share/mysql中，对于二进制版本是在MySQL的support-files目录中，或者在MySQL源发行版本的share/mysql目录下)
- 当然也可以使用自己写的脚本程序，如下：
#!/bin/sh # argument 1: log filename if[ $# -ne 1 ]; then echo "Usage: $0 logname" 1>&2 exit 1 fi logfile = $1 mv $logfile.6 $logfile.7 mv $logfile.5 $logfile.6 mv $logfile.4 $logfile.5 mv $logfile.3 $logfile.4 mv $logfile.2 $logfile.3 mv $logfile.1 $logfile.2 mv $logfile $logfile.1 mysqladmin flush-logs 把以上程序保存为一个shell脚本文件，例如rotate_fixed_logs.sh 为了轮转/data1/mysql/data中的日志文件，执行rotate_fixed_logs.sh /data1/mysql/data/qlog 要保护脚本不被其它登录账户读取，只能让所属账户访问脚本：chmod go-rwx rotate_fixed_logs.sh 注:在运行mysqladmin flush-logs这条语句时，若配置文件my.cnf没有指定连接参数，则需要指定连接参数 创建一个只用于轮转日志的账户 create user 'flush'@'localhost' identified by 'flushpass'; grant reload on *.* to 'flush'@'localhost'; 然后把该语句修改为mysqladmin -uflush -pflushpass flush-logs - 基于工作期限的截止：适用于以编号序列建立、没有用于复制机制的编号日志文件，例如二进制日志文件
- 设置expire_logs_days系统变量，单位为天
对已经有一个星期没修改过的二进制日志文件进行失效处理 [mysqld] expire_logs_days=7 - 有关复制的过期失效：适用于中继日志
- 根据二进制日志是否还在使用来决断
- 复制从服务器按编号顺序创建中继日志文件，并在处理它们之后自动删除它们，为了减少中继日志信息占用的硬盘空间，可以通过设置max_relay_log_size系统变量来降低中继日志文件的最大可允许长度
- 删除所有编号比命名文件更小的二进制日志：purge master logs to 'binlog.000040';
- 日志数据表的截短和轮转：适用于把日志信息写入MySQL数据库中的数据表
- 截短数据表
use mysql; truncate table general_log; truncate table slow_log;- 对那些数据表进行轮转
use mysql; create table general_log_tmp like general_log; rename table general_log to general_log_old, general_log_tmp to general_log; create table slow_log_tmp like slow_log; rename table slow_log to slow_log_old, slow_log_tmp to slow_log;
- 日志轮转：适用于具有固定文件名的日志文件，例如一般日志文件和慢查询日志文件
调整MySQL服务器
- 查看和设置系统变量的值
- 查看系统变量的值
show variables; show variables like 'keyword%'; show variables where Variable_name like '%keyword%' and value < keyValue; show global variables; show session variables; show local variables;- 可以通过"@@变量名"来访问变量的值,变量名可以去看information_schema数据库里的global_variables和session_variables数据表来获得
@@global.var_name @@session.var_name @@local.var_name @@var_name- 在启动服务器时设置系统变量的值
命令行 mysqld --max_connections=200 或者 my.cnf配置文件 [mysqld] max-connections=200- 在运行时设置系统变量的值
set global var_name = value; set @@global.var_name = value; set session var_name = value; set @@session.var_name = value; set var_name = value; set @@var_name = value; set global v1 = val1, v2 = val2, session v3 = val3, v4 = val4; - 通用型系统变量
- delayed_queue_size
在被实际插入到各有关数据表里去之前，来自insert delayed语句的数据行将在每个队列里等待MySQL来处理它们，delayed_queue_siz就是这个队列所能容纳的数据行的最大个数 - max_allowed_packet
MySQL服务器在与客户之间进行通信时使用的缓冲区的最大长度，默认值为1MB，最大可取值是1GB - max_connections
MySQL服务器允许同时处于打开状态的客户连接的最大个数
如果你的MySQL服务器很繁忙，你可能需要加大这个值 - table_cache
数据表缓存的尺寸。加大这个值，将使mysqld能够同时打开更多的数据表，从而减少文件打开/关闭操作的次数 - open_files_limit
增加文件描述符打开个数，使用分布式也可以增加文件描述符打开个数 - read_buffer_size
设置读操作所使用的缓冲区的尺寸 - sort_buffer_size
设置排序操作所使用的缓冲区的尺寸 - join_buffer_size
设置没有使用索引进行联结操作所使用的缓冲区的长度
- delayed_queue_size
- 查看状态变量的值
show variables; show variables like 'keyword%'; show variables where Variable_name like '%keyword%' and value < keyValue; show global variables; show session variables; show local variables; 状态变量只能由服务器设置，对于用户来说是只读的，不能进行设置和修改
存储引擎的配置
- 配置MyISAM存储引擎
- key_buffer_size
键缓冲区的尺寸，以字节为单位，默认值为8MB，最大取值为4GB - key_cache_block_size
键缓存块的单位长度，以字节为单位，默认值为1024个字节 - key_cacher_limit
暖缓存链键缓存百分比，值在1和100之间 - key_cache_age_threshold
指定键缓存区的热链里的某个缓存块的时间，若超过这个时间，将会移到暖链里去 - 创建一个键缓存，并且加载索引
创建一个键缓存 my.cnf配置文件 [mysqld] my_cache.key_buffer_size = 24M 或者 set global my_cache.key_buffer_size = 24*1024*1024 分配并加载索引 cache index member, president in my_cache; load index into cache member, president; - key_buffer_size
- 配置InnoDB存储引擎
- innodb_file_per_table选项可让InnoDB存储引擎为每个数据表分别创建一个表空间
- innodb_data_home_dir选项用来为构成InnoDB表空间的所有组成文件指定一个父目录，即所谓的“InnoDB主目录”，默认为MySQL数据目录
- innodb_data_file_path选项设置对InnoDB主目录下表空间各组成文件的说明
MySQL数据目录是/var/mysql/data,以下三组配置所指定的表空间文件都是完全相同的 [mysqld] innodb_data_home_dir= innodb_data_file_path=/var/mysql/data/ibdata1:50M;/var/mysql/data/ibdata2:50M innodb_data_home_dir=/var/mysql/data innodb_data_file_path=ibdata1:50M;ibdata2:50M innodb_data_file_path=ibdata1:50M;ibdata2:50M 若没有设置以上两个选项，默认InnoDB存储引擎将在服务器的数据目录里创建一个初始长度是10MB、名字是ibdata1的自扩展文件作为表空间- InnoDB共享表空间的设置步骤
- 把相应的语句添加到配置文件中
- 确认用来存放表空间组成文件的子目录已经存储。InnoDB存储引擎只能创建有关的文件，不能创建子目录
- 确认表空间组成文件都不存在
- 启动MySQL服务器
使用未经格式化的硬盘分区作为InnoDB共享表空间，操作如下： // 在这个原始硬盘分区的长度值后面加上一个newraw后缀以表明这个文件其实是一个未格式化的硬盘分区，需要初始化 [mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/rdsk8:20Gnewraw // 启动MySQL服务器。InnoDB存储引擎将注意到newraw后缀并对这个分区进行初始化。它将以只读方式去对待这个表空间，因为它知道你还没有完成第二个步骤 // 完成了硬盘分区的初始化工作之后，关停MySQL服务器 // 修改配置信息，把后缀newraw改为raw [mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/rdsk8:20Graw // 再次启动MySQL服务器。因为那个后缀现在是raw而不是newraw，InnoDB存储引擎将明白这个硬盘分区已初始化，它就会以读/写方式去使用这个表空间了 - 重新配置InnoDB共享表空间
MySQL允许在现有的InnoDB表空间组成文件清单的末尾追加一个新文件，这一招在表空间快被数据填满时非常有用
InnoDB表空间快被填满的征召之一是一些本应该成功的InnoDB事务操作无法完成并且回滚 - 与InnoDB存储引擎有关的系统变量
- innodb_buffer_pool_size
如果有足够的内存，加大这个变量的值可以减少因为访问InnoDB数据表的数据和索引而引起的硬盘读写操作 - innodb_log_buffer_size
InnoDB存储引擎会尽量把关于每一个事务的信息缓冲在内存里，等整个事务结束时才一次性地把它们写到硬盘上。
加大这个缓冲区的尺寸可以让更大的事务被缓冲在内存里面而无需提前写入硬盘，默认值为1MB，最大可取值为8MB - innodb_log_group_home_dir
为InnoDB存储引擎指定一个用来存放其日志文件的子目录路径 - innodb_log_file_size
改变InnoDB日志文件的长度 - innodb_log_files_in_group
改变InnoDB日志文件的个数
- innodb_buffer_pool_size
- 配置Falcon存储引擎
- falcon_page_size
页面长度的大小，可取值是1KB、2KB、4KB、8KB、16KB、32KB - falcon_serial_log_dir
指定创建日志文件的根目录，默认为MySQL服务器的数据目录 - 为Falcon数据表创建一个新的表空间需要使用create tablespace语句
create tablespace myts add datafile '/data1/mysql/falcon_myts.fts' engine=falcon - 把一个数据表加到新的表空间里
create table mytbl(i int) engine=Falcon tablespace myts;
- falcon_page_size
启用或者禁止LOAD DATA语句的LOCAL能力
- 在MySQL服务器的编译阶段
可以在运行configure脚本时用--enable-local-infile或--disable-local-infile选项把客户库的LOCAL能力设置为默认启用或默认禁用状态 - 在MySQL服务器的启动阶段
可以用--local-infile或者--skip-local-infile选项来启用或者禁用MySQL服务器端的local支持
国际化和本地化问题
- 设置MySQL服务器的地理时区
- 关于时区信息的两个系统变量
- system_time_zone
服务器在启动时确认的服务器主机所在的地理时区
可以在配置文件[mysqld_safe]块使用timezone选项指定 - time_zone
MySQL服务器的默认时区，默认为SYSTEM 可以使用default-time-zone选项来设置time_zone变量 time_zone的取值可以有三种： 1. 把SYSTEM赋值给time_zone变量，这将把它设置为system_time_zone变量的值 set session time_zone = 'SYSTEM'; 2. 把一个带正负号的“小时加分钟”时间值赋值给time_zone变量，这是相对于UTC标准时间的偏移值 set session time_zone = '00:00' set session time_zone = '+03:00' set session time_zone = '-11:00' 3. 把一个地理时区名赋值给time_zone变量，这将把它设置为给定地理时区 set session time_zone = 'Asia/Jakarta' 但是这种办法，必须先让MySQL服务器理解那些地理时区的名字，这需要从操作系统的时区文件里把有关信息加载到mysql数据库里的一组数据库里， 这就需要我们手动运行mysql_tzinfo_to_sql程序去填充那些数据表：运行mysql_tzinfo_to_sql 时区文件的目录路径 | mysql -p -u root mysql，然后重启服务器 - system_time_zone
- 关于时区信息的两个系统变量
- 选择用来显示出错信息的语言
可用language选项指定语言的名称或者路径名
MySQL安装路径下的share/mysql子目录里有几个以语言名称作为名字的下级子目录 - 配置MySQL服务器的字符集支持
- 编译时配置
--with-extra-charsets用于增加MySQL服务器支持的字符集，以逗号为分隔符的字符集名单，有两个特殊值：all、complex --with-charset用于指定字符集 --with_collation用于指定排序方式- 配置文件的设置
character-set-server选项用于指定服务器字符集 collation-server选项用于指定服务器排序方式 default-character-set选项用于指定客户端的字符集 character-set-dir选项把字符集文件的存放位置告知客户端
运行多个服务器
- 运行多个服务器的问题:最重要的就是把各个服务器的配置区分开来，每个服务器都使用独立的配置
--prefix选项用于编译时为每个分配指定不同的安装位置 basedir=dir_name MySQL安装根目录的路径名 datadir=dir_name 数据目录的路径名 以下配置必须不同 port=port_num TCP/IP连接用的端口号 socket=file_name unix区域套接字文件的路径名 pid-file=file_name 服务器写入其进程ID的文件的路径名 若开启日志，使用的任何日志名对每一个服务器一定要是不相同的 log-error[=file_name] 错误日志文件 log[=file_name] 一般日志文件 log-slow-queries[=file_name] 慢查询日志文件 log-output[=destination] 一般/查询日志目标 log-bin[=file_name] 二进制日志文件 log-bin-index[=file_name] 二进制日志索引文件 relay-log[=file_name] 中继日志文件 relay-log-index=file_name 中继日志索引文件 innodb_log_group_home_dir=dir_name InnoDB日志文件目录 falcon-serial-log-dir=dir_name Falcon日志文件目录 - 用于服务器管理的mysqld_multi
mysqld_multi根据你给每个你要建立的服务器配置赋予的专用号码进行工作，然后把服务器选项列在一个选项文件组[mysqldn]内，其中n就是那个号码，选项文件也可包括一组[mysqld_multi]，列出专用于mysqld_multi自己的选项。
升级MySQL
在考虑是否要升级MySQL时，一定要想清除，升级之后会有什么好处，若无，那么就不要贸然升级MySQL，避免一些不必要的麻烦。
在升级之前要查看新功能有哪些，可以去查看MySQL参考手册的release note或change notes。
访问控件和安全
内部安全性：防止未经授权的文件系统访问
- 做好sock和配置文件的访问权限
- 跟踪并在链接指向的位置改变所有权
find /usr/local/mysql -follow -print | xargs chown mysql
外部安全性：防止未经授权的网络访问
- MySQL权限表
user表列出可以连接服务器的用户账号、用户口令以及每个用户有的全局权限 db表列出哪个账号有权限使用哪个数据库 tables_priv表指定数据表级的权限，其中指定的权限用于数据表中所有列 columns_priv表指定数据列级的权限，其中指定的权限用于数据表中的特定数据列 procs_priv数据表记录着各种存储例程(存储函数和存储过程)的权限 - 管理权限
create user：有权使用create user、drop user、rename user和revoke all privileges语句 file：可让服务器在服务器主机上读或写文件 grant option：有权向其他用户授予你自己的权限 process：有权同时服务多个客户连接 reload：执行各种服务器管理操作，例如reload、refresh、flush-hosts、flush-logs、flush-privileges、flush-status、flush-tables和flush-threads replication client：有权使用show master status和show slave status查询主服务器和从服务器的地点 replication slave：客户可连接至主服务器，并请求从服务器修改，以及使用show master status和show slave status，这一权限必须授给用来连接主服务器的从服务器账户 show databases：有权通过show databases语句列出所有数据库的名字 shutdown：有权使用mysqladmin shutdown命令或通过其他手段关停MySQL服务器 super：有权使用kill语句或mysqladmin kill命令“杀死”服务器进程，这个权限的权力很大，谨慎授权！！！ - 数据库和数据表权限
alter：有权使用alter table语句 alter routine：有权修改或删除存储函数和过程 create：有权建立数据库和表 create routine：有权创建从存储函数和过程 create temporary tables：有权利用create temporary table语句创建临时表 create view：有权创建视图 delete：有权删除从数据表中去除现有的记录 drop：有权删除数据库和数据表，不允许删除索引 event：有权控制事件调度程序去管理各种事件 execute：有权执行存储函数和存储过程 index：有权为数据表创建或丢弃索引、为索引分配键缓存、把索引预加载到键缓存等等 insert：有权把新数据行插入到数据表 lock tables：有权通过发出LOCK TABLES语句来锁定数据表 references：有权定义谁有权制定外键约束条件 select：有权使用select语句从数据表检索数据 show view：有权使用show create view语句去查看视图的定义 trigger：有权添加或丢弃触发器 update：有权对数据表里的现有数据行进行修改 - SSL相关权限列
ssl_type：指名是否要安全连接和需要什么类型的安全连接 取值： ' '表示不需要安全连接，这是默认值，当没有指定require子句或者明确指定require none时 'ANY'表示必须安全连接，而且可以是任何一种安全连接，当指定require ssl时 'X509'表示需要安全连接，但是客户必须提供一个有效的X509证书，当指定require X509时 'SPECIFIED'表示安全连接必须满足专门的要求，当指定require issuer、require subjuct或者require cipher时 ssl_cipher：表示在连接时客户必定要使用密码方法 X509_issuer：表示在客户提供的X509证书中一定可找到发出者的值 X509_subject：表示在客户提供的X509证书中一定可找到题目值 - 资源管理权限列
max_connections：允许某给定账户在一小时内连接到服务器的最大次数 max_questions：允许某给定账户在一小时内发出的语句的最大个数 max_updates：允许某给定账户在一小时内发出的修改语句的最大个数 max_user_connections：允许某给定账户同时保有的客户连接的最大个数 - 在匹配权限时,MySQL服务器将把文字值排在匹配模式的前面，比较具体的模式排在比较宽松的模式的前面
- 应该回避的权限数据表风险
避免创建匿名账户 设置账号时，避免在主机名字段里是使用匹配模式 授予超级用户权限的时候一定要谨慎再谨慎 千万不要把容纳着权限数据表的mysql数据库的访问权限授予无关人员 慎重对待grant option权限 FILE权限特别危险 不要用root账户去运行服务器 不要把reload权限授权给不需要的人
加密连接的建立
步骤一：服务器和客户程序都已经编译有SSL支持机制
在源码编译时加上--with-*ssl选项
使用show variables like 'have_ssl';查看是否支持ssl
步骤二：在启动MySQL服务器时用有关选项告诉它到哪里能找到它的证书文件和密钥文件
[mysqld]
ssl-ca=ca-cert.pem(证书签发机构证书)
ssl-cert=server-cert.pem(证书文件)
ssl-key=server-key.pem(密钥文件)
步骤三：在调用客户程序时用有关选项告诉它到哪里能找到自己的证书文件和密钥文件
[mysql]
ssl-ca=ca-cert.pem(证书签发机构证书)
ssl-cert=client-cert.pem(证书文件)
ssl-key=client-key.pem(密钥文件)
注意：只有客户端库(API和API的底层客户端库)具备ssl支持，编写出来的应用程序才可以使用ssl连接服务器
MySQL数据库的维护、备份和复制
在MySQL服务器运行时维护数据库
- 以只读方式锁定一个数据表
步骤一：在窗口A里执行mysql程序，然后用以下语句申请一个读操作锁并把数据表在内存里的信息写入磁盘 mysql db_name lock table tbl_name read; flush table tbl_name; 步骤二：闲置mysql程序，切换到窗口B操作数据表文件 比如检查一个MyISAM数据表：myisamchk tbl_name 步骤三：完成对数据表的处理之后，切换回窗口A里的mysql会话任务并解除对数据表的锁定 unlock table; - 以读/写方式锁定一个数据表
步骤一：在窗口A里执行mysql程序，然后用以下语句申请一个写操作锁并刷新数据表 mysql db_name lock table tbl_name write; flush table tbl_name; 步骤二：闲置mysql程序，切换到窗口B去操作数据表文件 比如修复一个MyISAM数据表：myisamchk --recover tbl_name 步骤三：完成对数据表的处理之后，切换你回窗口A里的mysql会话任务，再次刷新数据表，然后解除对数据表的锁定 flush table tbl_name; unlock table; - 以只读方式锁定所有的数据库
锁定操作： flush tables with read lock; set global read_only = on; 解锁操作： set global read_only = off; unlock tables;
预防性维护
-
启用MyISAM数据表自动恢复能力
使用myisam-recover=level选项，以逗号分隔，由一个或者多个值构成的列表 level的取值： BACKUP 如果自动恢复工作需要修改某个数据表，先为它创建一个备份 FORCE 强行恢复，哪怕会因此而丢失一个以上的数据行 QUICK 快速恢复 DEFAULT 不进行任何其他特殊处理的普通恢复，这与把该选项设置为空的效果完全一样 -
使用crontab文件进行定期进行预防性维护
制作数据库备份
- 用mysqldump程序制作文本备份
mysqldump db_name [tbl_name...] > backup_name.sql --opt 默认开启，优化备份操作，会把转储中的数据表全部锁定 --databases 将把命令行上给出的名字全部解释为数据库名，并依次转储这些数据库里所有的数据表 --all-databases 转储全部数据库，包括权限表 --no-create-info 不转储数据表的结构 --no-data 不转储数据表的数据 --flush-logs 关闭再重新打开日志文件 --lock-all-tables 申请一个全局级读操作锁 --single-transaction 把转储操作放在一个事务里执行(在转储有事务的数据表时) --routines 把存储例程包括到转储输出中 --triggers 把触发器包括到转储输出中 --events 把事件包括到转储输出中 - 制作二进制数据库备份(需要关停MySQL服务器)
去到数据目录 cd /data/server/mysql/data 压缩文件 tar czf /bak/backup_name.tar.gz .
把数据库复制到另一个服务器
- 使用一个备份文件来复制数据库
步骤一：创建一个转储文件
mysqldump --databases db_name > db_name.sql
步骤二：把转储文件复制到远程主机
// 使用scp程序把文件复制到boa.snake.net主机上的/tmp子目录里
scp db_name.sql boa.snake.net:/tmp
步骤三：登录进入远程主机，把转储文件加载到该主机上的MySQL服务器里
mysql < /tmp/db_name.sql
- 把数据库从一个服务器复制到另一个
使用通道的概念代替转储文件
mysqldump --databases db_name | ssh boa.snake.net mysql --compress -h boa.snake.net
数据库的检查和修复
- 用服务器检查和修复数据表
用check table 语句检查数据表，先列出一组数据表名称，然后给出一个或多个可选的限定符来表明进行何种类型的检查 check table tbl_name_1,tbl_name_2[,tbl_name_3...] [changed | extended | fast | medium | quick] 用repair table语句修复数据表，先列出一组数据表名称，然后给出一个或多个可选的限定符来表明你想进行何种类型的修复 repair table tbl_name_1,tbl_name_2[,tbl_name_3...] [extended | quick | use_frm] - 用mysqlcheck程序检查和修复数据表
mysqlcheck [option] db_name [tbl_name...] 检查选项： --check-only-changed --extended --fast --medium-check --quick 修复选项： --repair --repair --extended --repair --quick --repair --use-frm - 用myisamchk程序检查和修复数据表(需要关停服务器)
检查数据表 myisamchk [--check | --medium-check | --extended-check] tbl_name 修复数据表 myisamchk [--recover | --quick | --safe-recover] tbl_name
使用备份进行数据恢复
- 恢复整个数据库
步骤一：为数据库子目录制作备份 步骤二：使用最新的备份文件重新加载你打算恢复的数据库 步骤三：根据二进制日志重新执行在制作备份后对数据进行修改的SQL语句 - 重新执行二进制日志文件里的语句
// 把二进制日志文件转换回文本形式的SQL语句 mysqlbinlog --database=db_name --start-datetime="2019-01-12 20:38:40" --stop-datetime="2019-02-12 20:39:12" binlog.[0-9]* > sql_file // 剔除错误SQL语句 vi sql_file // 导入 mysql < sql_file - InnoDB存储引擎的自动恢复功能
一般InnoDB存储引擎的自动恢复功能都能解决大多数问题 如果万一失败了，可以尝试使用innodb_force_recovery=[1 | 2 | 3 | 4 | 5 | 6]进行强行启动，再使用备份文件进行数据恢复
设置复制服务器
- 部署主从分布式服务器
步骤一：配置主从服务器分配的ID值(ID值彼此不同，取值范围：1~2^32-1),并且开启主服务器上的二进制日志功能
主服务器
[mysqld]
server-id=master_server_id
log-bin=binlog_name
从服务器
[mysqld]
server-id=slave_server_id
步骤二：在主服务器上，创建一个账户供从服务器连接主服务器并请求修改信息
create user 'slave_user'@'slave_host' identified by 'slave_pass';
grant replication slave on *.* to 'slave_user'@'slave_host';
步骤三：连接到主服务器并通过执行show master status语句确定当前的复制坐标和二进制日志文件
flush tables;
show master status;
步骤四：在主服务器上为将被复制的数据库建立一份完备的副本，把副本复制到从服务器上并加载，完成主从服务器之间最初的同步
若主服务器还没创建过任何数据库或数据表，可以省略这一步
步骤五：连接到从服务器并使用change master语句进行配置，这包括把用来连接主服务器的参数和初始复制坐标告诉从服务器
change master to
master_host='master_host',
master_port='master_port',
master_user='slave_user',
master_password='slave_pass',
master_log_file='log_file_name',
master_log_pos='log_file_pos';
步骤六：让从服务器开始复制,可使用show slave status语句来查看工作状态
start slave;
注意：可用stop/start slave [io_thread | sql_thread]控制复制活动
- 使用复制机制制作备份
步骤一：使用stop slave sql_thread语句暂停从服务器上的复制活动并刷新日志
步骤二：开始制作备份
步骤三：用start slave sql_thread语句重新开始复制活动