Mysql基本管理

Mysql管理

一、基本

单进程，多线程。连接线程。连接线程，在数据库启动时，建立线程池。线程不会被销毁，

线程重用，避免重复建立、删除线程。

词法、语法、语义分析器对整个语句分析，语法无误，权限无误，优化器。

查询是广义上的查询，包括insert等都算是查询。

query cache只对select有效

二、锁

并发访问控制，需要锁的支持。不建议使用手动施加锁。在备份时才使用手动施加锁。

锁类型：read | wirte

选择策略：在锁粒度和并行性间取得一个平衡点。

修改存储引擎：后台会将原来的数据导出，删除原来的表，重新建立新的表，产生大量I/O 。

三、事务

transaction，一组原子性的查询语句，将多个查询当作一个独立的工作单元。（同时执行，或者同时都不执行）

mysql怎样实现事务的？

MVCC：多版本并发控制，每个事务启动时，InnoDB为每个启动的事务提供一个当下时刻的快照。为了实现此功能，InnoDB会为每个表提供两个隐藏的字段，一个用于保存行的创建时间，一个用户保存行的失效时间。保存内容是系统版本号：（system version number）。只在两个隔离级别有效：read committed、repeatable read

脏读	读到别人未提交的数据（导致不可重复读）。可重读解决脏读。
幻读	读取数据跟实际数据不一直。
不可重读

四、存储引擎

1. 查看表的状态信息

 

2. 存储引擎

补充：1、表空间，table space ，由InnoDB管理的特有格式的数据文件，内部 可同时存储数据和索引（聚簇索引）。

2、mysql> show global variables where variable_name='innodb_file_per_table'; 查看是否启用独立表空间

3、在InnoDB中，聚簇索引外的都使用辅助索引。因此，在使用InnoDB的情况，如果搜索没有直接使用聚簇索引，那么一般情况下先使用辅助索引，从辅助索引找到聚簇索引，然后再找到具体的磁盘块。

3. 怎样选择搜索引擎

是否需要事务

备份的类型的支持

崩溃后的数据恢复

是否依赖特有特性

注：如果有多个存储引擎都符合业务需求，那么就需要进行基准性能测试。选择在环境中表现最优的解决方案。

索引

五、用户管理

帐号格式：username@hostname

 

与用户授权相关的表

db:库级别的权限

host:主机级别权限，已废弃

talbles_pirv：表级别权限

colomns_priv:列级别的权限

procs_priv:存储过程和存储函数相关的权限

proxies_pirv:代理用户权限

flush相关

flush privileges; 刷新权限

flush hosts 用户多次尝试登录失败，系统会拒绝再登录，需要刷新host缓存。

flush table with read lock

六、 查询缓存

1. 查询缓存是什么？

用于保存Mysql查询语句返回的完整结果，被命中时，Mysql会立即返回结果，省去解析、优化、和执行等阶段。

2. 如何检查缓存？

Mysql保存结果在缓存中，把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value。

注：在写sql语句的时候最好遵循统一的格式，这样才能与缓存中的语句相符，提高命中率。

3. 什么样的语句不会被缓存？

1) 查询语句中有一些不确定数据时，不会缓存；eg：now(),current_time()

2) 如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysq库中系统表或者任何包含权限的表，一般都不会缓存；

4. 缓存总是好的吗？

缓存并不是一直都是正面作用。缓存会带来额外开销。

1) 每个查询都先检查是否名字

2) 查询结果需要先缓存

5. 关于缓存的一些全局变量

query_cache_type	查询缓存类型；是否开启缓存功能，开启方式有三种{ON|OFF|DEMAND}； DEMAND：按需缓存。意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；（在使用select时指定修符sql_cache才进行缓存）
query_cache_size	总空间，单位为字节，大小必须是1024整数倍，mysql启动时，会一次分配并立即初始化这里指定大小的空间；如果修改此大小，会清空缓存并重新初始化（谨慎使用）。
query_cache_min_res_unit	存储缓存的最小内存块 （query_cahce_size - Qcache_free_memory）/ Q cache_queries_in_cahce
query_cache_limit	单个缓存对象的最大值，超出时不缓存；手动使用sql_no_cache（select 语句修饰符）可以认为的避免尝试缓存返回结果超出此参数限定值的语句
query_cache_wlock_invalidate	如果某个表被其他用户连接锁住，是否仍然从缓存中返回结果。off表示返回。

6. 如何判断缓存命中率（次数命中率）

Qcache相关的状态变量（状态变量与系统变量是不同的概念）

show global status like 'Qcache%';

Qcache_hits	命中次数
Qcache_insert	缓存插入次数
Qcache_free_memory	空闲缓存空间大小
Qcache_free_blocks	空闲块数
Qcache_total_block	总块数（实现申请好的内存好，存储缓存）
Qcache_not_cache	没有被缓存的次数
Qcache_queries_in_cache
Qcache_lowmem_prunes	因为内存太少，而修剪内存的次数（腾空内存）

碎片整理：flush query_cache

清空缓存：reset query_cache

命中率计算：

show global status where variable_name='Qcache_hits' or variable_name='Com_select';

命中率= Qcache_hits/(Com_select+Qcache_hits)

也应该参考另外一个指标：命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的。能达到10:1，为比较理想的情况。如果有大量缓存抖动，则关闭缓存。

7. 缓存优化

1、批量写入而非多次单个写入；

2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；

3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；

4、对写密集型的应用场景来说，禁用缓存反而能提高性能；

七、 日志

默认不开启日志

八、备份和恢复

个人总结：备份还原的一般思路：有两种主要的备份思路，一，使用mysqldump等工具对数据库进行逻辑备份，在进行逻辑备份的时候大部分情况下需要对数据库进行锁定，导出成数据库脚本，然后再根据二进制相关文件，进行时点还原。为了能够精确的定位二进制日志，还需要对二进制日志回滚。

二，使用快照的方式，物理备份，将数据库锁定的一瞬间的数据拷贝至其他的位置，然后释放数据库锁，之后产生的新的数据仍然使用二进制日志的方式进行还原。

三、使用其他的第三方工具进行热备。这里主要是使用到Xtraback，也主要是对InnoDB进行备份。进行一次完全备份，多次的热备。其他没有备热备份的数据只能通过二进制日志来完成。

通过这些可以看出，二进制日志在其中扮演了重要的角色，因此一定要特别保护好二进制日志，它是进行时点还原的中国要依据。

在存储引擎中，这里以InnoDB为例，将数据分成数据块，自我维护（这里的数据库不是文件系统级别的数据块，是存储引擎自己维护的）。每个数据库都有一个序列号，每当数据块中的数据发生变化时，序列号就会自动增加。我们这里做的增量被备份就是根据序列号来进行的，例如：当前一个时点上序列号是10，那么备份10以后的序列上的数据，就是增量备份。完全备份是找到最大号，备份1-10的数据。

1. 为什么要备份和恢复

1) 灾难恢复

2) 审计

3) 测试

注意：要对备份数据定期做恢复测试。在实际生产环境中应该定期进行备份恢复演习。

2. 备份类型

这里要注意增量备份与差异备份的区别！

3. 备份策略

4. 备份对象

备份的不只是数据，还有配置文件等。

5. 常用备份工具

1) mysqldump

? 备份前一定要加锁。备份数据库集较小的备份。数据超过10G时慎重使用。逻辑备份。可以连接远程服务器备份，但是必须要有账户及备份权限。还原时数据库必须存在，否则必须手工创建数据库。

使用格式 mysqldump [options] [db_name [tbl_name ...]]

还原时，需要先在数据库中创建要还原成的数据库，然后才能够还原。在恢复的时候临时关掉二进制日志，

eg： mysql> mysql –uroot -hlocalhost –p test < test.sql 这里test为新建立的空数据库，名字可以与原来的数据库的名字不同。

? 备份的一般步骤。在备份前一定要加锁

1. 请求锁：--lock-all-tables或使用—single-transaction进行热备

2. 滚动日志 –flush-logs。在备份前、请求到锁之后滚动日志。在做备份之后（锁解除之后），数据库仍然会有数据写入等操作，这个时候发生故障，单靠备份无法恢复到故障点，因此需要股东日志，获取增量的二进制日志，然后才能实时恢复。

3. 选定要备份的数据库 –databases

4. 恢复操作不用记录到二进制日志，可以关闭，服务器离线（关闭其他用户对服务器的写操作）

5. 指定二进制日志文件及其位置 ---master-data

eg：

完整示例：使用mysqldump备份数据库，然后滚动日志，使用二进制日志进行实时还原。

具体步骤见附件：备份

? mysqldump的备份、还原策略

备份	mysqldump+二进制日志文件 周日做一次完全备份：备份的同时滚动日志 周一至周六：备份二进制日志；
恢复	完全备份+各二进制日志文件中至此刻的事件

2) lvm-snapshot 备份

基于lvm快照的备份。快照仅仅是访问源数据的通路。无法进行增量备份，只能使用二进制日志操作。

A. （在基于快照备份）事务日志跟数据文件必须在同一个卷上（需要对数据库文件和事务日志做备份，如果二者不在一个上，需要两个快照，那样就很难保快照信息一致）

B. 创建快照卷之前，要请求mysql的全局锁，在快照创建完成之后释放锁

C. 请求锁完成后，做日志滚动：做二进制日志文件及位置标记（手动进行）

3) Xtrabackup

一定要注意：支持对InnoDB和xtardb的热备

innobackupex: 需要MySQL服务处于运行状态。是Precona公司体提供的热备份软件。依据最开始的序列号理论，Xtrabackup实现备份。这里有一个准备的概念。正常情况下恢复是先将完全备份恢复，然后再恢复增量的。如果使用准备，那么在准备的时候就会将发生变化的数据块替换掉，还原的时候一步到位，直接就是需要恢复到的状态。

# innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

这里会在bacup-dir中自动创建一个以日期为名称的目录，包括了数据库文件，还有一些诶Xtrabackp独有的文件

backup-my.cnf	备份命令用到的配置选项信息
xtrabackup_binary	记录Xtrabackup使用的是哪个工具进行备份。Xtrabackup是一个备份套件。为不同版本的mysql提供不同的工具
xtrabackup_checkpoints	备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息。每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
xtrabackup_binlog_info	ysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。（这一次备份后的二进制日志pos位置）
xtrabackup_binlog_pos_innodb	二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

具体操作步骤见附件 。

备份注意点

1、将数据和备份放在不同的磁盘设备上；异机或异地备份存储较为理想；

2、备份的数据应该周期性地进行还原测试；

3、每次灾难恢复后都应该立即做一次完全备份；

4、针对不同规模或级别的数据量，要定制好备份策略；

5、二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件；

还原思路

1、停止MySQL服务器；

2、记录服务器的配置和文件权限；

3、将数据从备份移到MySQL数据目录；其执行方式依赖于工具；

4、改变配置和文件权限；

5、以限制访问模式重启服务器；mysqld的--skip-networking选项可跳过网络功能；

方法：编辑my.cnf配置文件，添加如下项：

skip-networking

socket=/tmp/mysql-recovery.sock

6、载入逻辑备份（如果有）；而后检查和重放二进制日志；

7、检查已经还原的数据；

8、重新以完全访问模式重启服务器；注释前面在my.cnf中添加的选项，并重启；

4) 简单的备份工具

SELECT clause INTO OUTFILE ''

备份出来的只有数据，没有表结构，因此在使用下面命令还原数据时一定一起要实现建立与原表相同的表结构。

还原LOAD DATA INFILE '' INTO TABLE tb_name

九、其他知识

mysql>help content;

mysql>show processlist;

flush host 多次尝试登录失败后，情况host缓存表。

修改存储引擎mysql> alter table classes engine 'InnoDB';

mysql> select database(); 当前使用的数据是哪个

mysql> SELECT @@ .autocommit; 查看当前事务是否自动提交

mysql>show global variables like ‘tx_isolation’; mysql>select @@global.tx_isoation; 查看事务类型级别

default_storage_engine 默认存储引擎 InnoDB

eg: mysql> set global variables default_storage_engine='MyISAM'; 修改默认存储引擎

查看权限：show grants for ‘username’@’hostname’

show processlist; 显示进程`

show master status;显示主服务器状态

flush logs 手工滚动二进制日志和中继日志

重启数据库服务也会滚动日志

查看当前能够被mysql管理使用的二进制列表 show binary logs

十、一些小结

1. 将二进制日志文件同数据文件放在不同的磁盘目录下，修改vim /etc/my.conf  log-bin= /mydata/logbin/master-bin

2. 一般情况下，对与InnoDB需要开启（在配置文件中设置）innodb_file_per_table=on，整个选项将数据和结构进行分离，用以支持很多高级特性

十一、 练习题

1、 授予testuser能够通过172.16.0.0/16网络内的任意主机访问当前mysql服务器的权限；

create user 'testuser'@'172.16.%.%';

2、 让此用户能够创建及删除testdb数据库，及库中的表；

grant create,drop on testdb.* to 'testuser'@'172.16.%.%';

3、 让此用户能够在testdb库中的t1表中执行查询、删除、更新和插入操作；

grant select,delete,update,insert on testdb.t1 to 'testuser'@'172.16.%.%';

4、 让此用户能够在testdb库上执行创建和删除索引；

grant index on testdb.* to 'testuser'@'172.16.%.%';

5、 让此用户能够在testdb.t2表上查询id和name字段，并允许其将此权限转授予其他用户；

grant grant option ,select (id,name) on testdb.t1 to 'testuser'@'172.16.%.%';