mysql学习
DBMS
RDBMS 关系型数据库管理系统
C/S: 通过专有协议
关系模型： 表（行，列），二维关系
范式： 第一范式，第二范式，第三范式
关系运算： 选择 和 投影
数据库： 表、索引、视图、
DDL，DML：
编程接口：
存储过程： 没有返回值的函数
存储函数：有返回值的函数
触发器：
事件调度器（crontab）
过程式编程：选择、循环
三层模型：
物理层
逻辑层
视图表
查看存储引擎
root@localhost:(none)>show engines;
单进程多线程：
连接线程
守护线程
配置文件： 能够为mysql的各应用程序提供配置信息
[mysqld] 能够专门对mysqld 这个应用程序
[mysqld_safe] 线程安全
[mysqld_multi] 多实例模型的mysql,多实例共享的资源
[server] mysql服务器端的程序都有效
[mysql] 专用于mysql 客户端的
[mysqldump] 专用于mysql备份
配置文件：查找路径： /etc/my.cnf ---> /etc/mysql/my.cnf ----> $MYSQL_HOME/my.cnf -----> --default-extra-file=/path/to/somedir/my.cnf ----> ~/.my.cnf
安装方式：
mysql： rpm ， 展开可用， 源码编译
安装后的设定：
1. 为所有root用户设定密码：
a. mysql> set password for root@localhost=password('123456');
b. mysql> update mysql.user set password=PASSWORD('you password') where user='root'; flush privileges;
c. # mysqladmin -uroot -p123456 password 123
2. 删除所有匿名用户
mysql > drop user ''@'localhost';
############################################
上述两步骤可运行命令：# mysql_secure_installation 实现
############################################
3. 建议关闭主机名反解功能：
修改MySQL的配置文件my.cnf
[mysqld]
skip-name-resolve
元数据数据库：mysql，
user ，host等
查看mysql 默认配置
[root@openfalcon_mysql bin]# ./mysql --print-defaults
./mysql would have been started with the following arguments:
--port=3306 --socket=/data/mysql/tmp/mysql.sock --no-auto-rehash --prompt=\u@\h:\d> --max_allowed_packet=128M --comments=1
[root@openfalcon_mysql bin]# ./mysqld --print-defaults
./mysqld would have been started with the following arguments:
--basedir=/data/mysql/ --datadir=/data/mysql/data/ --tmpdir=/data/mysql/tmp/ --socket=/data/mysql/tmp/mysql.sock --pid-file=/data/mysql/data/mysql.pid --port=3306 --default-storage-engine=INNODB --skip-name-resolve --skip-symbolic-links --max_connections=2000 --max_connect_errors=10000 --thread_cache_size=1000 --wait_timeout=28800 --interactive-timeout=28800 --connect_timeout=30 --max_allowed_packet=256M --tmp_table_size=128M --max_heap_table_size=128M --query_cache_type=0 --query_cache_size=128M --query_cache_limit=2M --sort_buffer_size=16M --lower_case_table_names=1 --innodb_buffer_pool_size=3072M --innodb_buffer_pool_instances=10 --innodb_additional_mem_pool_size=100M --innodb_data_home_dir=/data/mysql/data/ --innodb_data_file_path=ibdata1:1G:autoextend --innodb_file_per_table=1 --innodb_log_group_home_dir=/data/mysql/data/ --innodb_log_file_size=1900M --innodb_log_files_in_group=2 --innodb_flush_log_at_trx_commit=1 --innodb_lock_wait_timeout=50 --innodb_flush_method=O_DIRECT --innodb_autoinc_lock_mode=2 --innodb_purge_threads=1 --innodb_use_native_aio=1 --innodb_thread_concurrency=40 --innodb_concurrency_tickets=5000 --innodb_max_dirty_pages_pct=90 --innodb_old_blocks_time=1000 --key_buffer_size=128M --server-id=1 --log-bin=mysql-bin --expire_logs_days=10 --max_binlog_size=1000M --binlog_format=row --sync_binlog=1 --transaction-isolation=READ-COMMITTED --skip-slave-start --relay-log=mysql-relay --relay-log-info-file=mysql-relay.info --log-error=/data/mysql/log/mysql.err --long-query-time=0.5 --slow-query-log=1 --slow-query-log-file=/data/mysql/log/mysql-slow.log --general-log=0 --general-log-file=/data/mysql/log/mysql.log --event_scheduler=1
客户端类应用程序的可用选项：
-u 使用的用户名
-h 连接mysqlserver ip地址
-p 密码
-P 连接的端口
-s socket 位置，
--protocol 指定的协议（tcp | sock 客户端、服务器端在同一台机器上）
-D 连接到mysql 后切换的 数据库
-C 是否需要压缩
-e 不连入mysql，直接在shell 中执行
获取可用参数列表:
[root@openfalcon_mysql bin]# ./mysql --verbose --help
[root@openfalcon_mysql bin]# ./mysqld --verbose --help
获取运行中的mysql 进程使用各服务器参数及其值：
mysql > show global variables; 有些才是支持在线修改生效，全局参数
mysql > show session variables; 当前会话的参数
修改服务器参数
mysql > help set 全局(global)： mysql > set global system_var_name = value; mysql > set @@global.system_var_name = value; 会话(session): mysql > set [SESSION] system_var_name = value; mysql > set @@[session.]system_var_name = value;
状态变量： 用于保存mysqld 运行中的统计数据的变量。
mysql > show global status; 全局 mysql > show session status; 会话
sql: DDL, DML
DDL：数据定义语言, create alter drop
DB组件： 数据库、表、索引、视图、用户、存储过程、存储函数、触发器、事件调度等
create 相关的命令：
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DML：数据操作语言： inster into delete update select
数据库：
create，alter，drop
创建表的3种方式
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]
例子：create table if not exists feng (id int,name varchar(60)); Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement
> create table if not exists su select * from feng; Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
> create table if not exists zhang like feng;
查看存储引擎
> show global variables like '%engin%'
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | OFF |
| storage_engine | InnoDB |
+---------------------------+--------+
storage_engine 是指在创建时 指明其使用的存储引擎
查看表结构
{DESCRIBE | DESC} tbl_name [col_name | wild]
> describe feng
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
显示表的信息
> help show tables Name: 'SHOW TABLES' Description: Syntax: SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
> show table status like 'feng'\G;
mysql 数据文件类型：
数据文件、索引文件
重做日志、撤销日志、二进制日志、错误日志、查询日志、慢查询日志
DDL&& DML：
索引管理：
按照特定数据结构存储的数据
创建索引
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ... index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' > create index feng_index on feng (id) ;
显示索引
使用explain 显示是否使用了索引
使用alter 在表中添加索引
删除索引
视图：view
虚表，就是存储下来的虚表
MariaDB [fengjian]> help create view Name: 'CREATE VIEW' Description: Syntax: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
例子： create view test_view as select id,name from su;
查看view
> show tables;
查看表的状态：
> show table status like 'test'\G;
数据表具体状态：
删除视图
DML:
insert , delete update, select
insert:
一次插入一行或者多行
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]
第一种插入方式：
插出多行
>insert into feng values(3,'orange'),(4,'red');
插入一样
>insert into feng values(5,'green')
第二种插入方式select
> insert into su select * from feng;
第三种插入方式set
> insert into su set id=10,name='purple'；
delete 删除
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
限制条件：
where
limit
> delete from su order by id desc limit 1;
update语句
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
> update su set id=10 where name='red' order by id desc limit 1;
select 语句
查询执行路径中的组件： 查询缓存、解析器、预处理器、优化器、查询执行引擎。
1. select ... from .... order by .... 2. select ....from ....group by .... having .... 3 select .... from ..... where..... 4. select ..... select now() 5.select ... from ... where ..... group by .... limit.... 6. select ..... from ..... having......
select语句执行流程：
from ----> where -------> group by ---------> having---------> order by -----------select ------------limit
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)]
distinct 数据去重
> select distinct(name) from user;
sql_cache 显示指定存储查询结果保存在缓存之中
sql_no_cache 显示查询结果不缓存
只有 query_cache_type值为ON时， 查询功能才可以使用， select的结果符合缓存条件即会缓存，否则，不缓存，
query_cache_type的值为'demand'时，查询缓存功能按需进行。显示指定sql_cache的 select局域才会缓存： 其他均不缓存。
查看其命中的次数
select查询的次数
命中率 为 Qcache / Com_select *100%
字段在显示时可以显示别名
where 子句
>select * from host where id between 1 and 1000;
>select * from host where id is not null;
>select * from host where id is null;
>select * from host where id like ‘%100’；
逻辑操作符：
NOT
AND
OR
XOR
GROUP：根据指定的条件把查询结果进行“分组”以便于用“聚合”运算
avg(), max(),min(),count(),sum()
having: 对分组聚合运算的结果指定过滤条件：
order by 以指定的字段对长训结果进行排序：
升序：asc
降序：desc
limit ：对查询的输入结果进行控制。
>select avg(agent_version) from host group by plugin_version; >select avg(agent_version) as agent from host group by plugin_version having agent >20; >select count(id),agent_version from host group by plugin_version;
使用limit的控制偏移量， 取10 和 下一个10之间的数据。
>select * from host order by id limit 10,10；
FOR UPDATE | LOCK IN SHARE MODE] 对查询结果中的数据请求施加锁操作，
for update：写锁，排他锁。
lock in share mode： 读锁，共享锁
多表查询：
交叉连接： 笛卡尔乘机：
内连接：
等值连接：让表之间的字段以“等值”建立连接关系。
不等值连接：
自然连接：
外连接
左连接
右连接
内连接：
select s.name,c.class from student as s, class as c where s.id = c.id
左连接：
from tb1 left join tb2 on tb1.col=tb2.col
from tb1 right join tb2 on tb1.col=tb2.col
子查询：在查询语句中嵌套查询语句， 基于某语句的查询结果再次查询。
用在where 子句中的子查询：
1. 用与比较表达式中的子查询： 子查询仅能返回单个值。
select name,age from student where age > (select avg(age) from student);
2. 用户in中的子查询：
select name,agent from student where age in (select age from teacher);
用与from 子句中的子查询：
格式： select tb_alias.colume1,tb_alias.colume2 from (select clause) AS tb_alias where clause;
select team.id from (select id from team) as team;
连接2个select 语句 (union)
>select id,name from user union select id,name from team;
mysql 用户和权限管理
权限级别：
对库权限
对表权限
对字段权限
管理类
程序类： 是否可以运行存储过程或者存储函数。
管理类：
create temporary tables 创建临时表
create user
file 导出数据，或者导入输入到数据库
super
show databases
reload 重新加载授权表
shutdown
replication slave
relication client 是否有权限复制主数据的访问
lock tables
process
程序类：
function create，alter，drop excute
procedure create，alter，drop excute
trigger create，alter，drop excute
库和表：
alter database/table
create
create view
drop
create index
show view
grant
数据操作：
select, insert,delete,update
字段级别
select（col1,col2....）
update(col1,col2...)
insert(col1,col2...)
所有权限: all privileges 简写成 all
元数据库 ： mysql
授权表：
db, host, user
columns_priv, procs_priv,tables_priv,proxies_priv
用户账号： 'username'@'host'
创建用户 create user ‘username’@‘host’ [identified by 'password']
查看用户获得授权： show grants for 'username'@'host'
重命名用户， rename user 'fengjian'@'localhost' to 'fengjian1'@'localhost';
删除用户： drop user 'fengjian1'@'localhost';
修改密码：
1. set password for
2. update mysql.user set password=PASSWORD('your_password') where user='root' ; flush privileges;
3. mysqladmin password
1. set password for 'fengjian'@'localhost' = PASSWORD('111111');
2. update mysql.user set password=PASSWORD('your_password') where user='root' ; flush privileges;
3. mysqladmin -u root -p111111 password 123456
忘记管理员密码的解决办法：
启动mysqld进程是， 为其使用 --skip-grant-tables --skip-networking
授权： grant
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...] GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION] 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 | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count
取消授权：
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
添加权限：grant all privileges on *.* to 'feng'@'localhost' identified by '123456';
删除权限： revoke all privileges on *.* from 'feng'@'localhost';
查询缓存：
如何判断是否命中：
通过查询语句的哈希值判断： 哈希值考虑的因素包括
查询本身、要查询的数据库、客户端使用协议版本。
查询语句任何字符上的不同，都会导致缓存不能命中。
那些查询不能被缓存：
查询中包括UDF,存储过程、用户自定义变量、临时表、mysql库中系统表、或者包含列级权限的表、有着不确定值的函数now（）
查询缓存相关的服务器变量：
query_cache_min_res_unit： 查询缓存中内存块的最小分配单位。较小的值会减少访问，但会导致更频繁的内存分配操作，更大的值，会导致碎片过多。
query_cache_limit： mysql能够缓存的最大查询结果。对于有较大结果的查询语句，建议在select中使用 sql_no_cache.
query_cache_size: 查询缓存总共可用的内存空间，单位是字节，必须是1024的倍数。
query_cache_type: on off DEMAND 明确写明sql_cache,才缓存，不写名sql_cache,不缓存。是否把查询结果放到查询缓存中，on表示打开。
query_cache_wlock_invalidate： 如果某表被其他的连接锁定，是否仍然可以从查询缓存中返回结果。默认值为OFF，表示可以在表被其他连接的场景中继续从缓存返回数据。
查询变量获取方式：
root@localhost:(none)>show global status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 130531864 | | Qcache_hits | 1 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 16 | | Qcache_queries_in_cache | 42 | | Qcache_total_blocks | 95 | +-------------------------+-----------+
root@localhost:(none)>show global status like '%select%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_insert_select | 0 |
| Com_replace_select | 0 |
| Com_select | 99 |
| Select_full_join | 6 |
| Select_full_range_join | 0 |
| Select_range | 5 |
| Select_range_check | 0 |
| Select_scan | 69 |
+------------------------+-------+
缓存命中率： Qcache_hits/(Qcache_hits+Com_select)
MYSQL 索引：
基本法则： 索引应该构建在被用作查询条件的字段上：
索引类型：
BTree索引： 顺序存储，每一个叶子节点到根节点的距离是相同的。左前缀索引，适合查询范围类的数据。
可以使用BTree索引的查询类型：全键值、键值范围或前缀查找：
全值匹配，精确某个值 “abc”
匹配最左前缀：只精确匹配起头部分，“abc%”
匹配范围值：
精确匹配某一列并范围匹配另一列：
只访问索引的查询。
不适合BTree索引的场景：
如果不从最左侧开始：索引无效
不能跳过索引中的列：
如果查询中某个列是为范围查询，那么其右测的列都无法再使用索引优化查询
HASh索引：基于哈希表实现，特别适用于精确匹配索引中的所有列， 只有memory 存储引擎支持显示hash索引；
索引的有点：
索引可以降低服务需要扫描的数据量，减少了IO次数。
索引可以帮助服务器避免排序和使用临时表。
索引可以帮助将随机I/o 转为顺序I/o
高性能索引策略：
独立使用列， 避免其参与运算 ( select * from user where age +30 >50;)
组合索引：当一个select 包含 and 条件时， 适合使用组合索引。如果是or的条件，适合单建索引。
选择合适的索引列次序： 将原则性最高放左侧。
通过explain 来分析索引的有效性：
explain select *.....
mysql 存储引擎
表类型： 存储引擎是表级别的概念，创建的每一张表，都可以单独指定表的存储引擎类型，不建议在同一库中表使用不同存储引擎，因为存储引擎的特性各不相同。
create table ........ engine=innodb
InnoDB ： 处理大量短期事物。短期事物都是正常提交，很少回滚。mysql崩溃，能够自动恢复。
数据存储于"表空间（table space）"中
1. 所有Innodb表的数据和索引放置于同一个表空间中。表空间文件： datadir目录下 数据文件（数据文件+索引） ibddata1,ibddata2...... ,在my.cnf 定义 innodb_data_file_path = ibdata1:1G:autoextend
2. 每个表单 单独使用一个表空间存储表的数据和索引， innodb_file_per_table = on ,
数据文件（存储数据+索引): tbl_name.idb 表.idb
表格式 : tbl_name.frm ，清除数据，不会清除表格式。
基于MVCC来支持高并发，支持所有的4个隔离级别，默认级别为repeatable read; 间隙锁防止幻读。
使用聚集索引， 支持“自适应hash索引” ，
支持行级锁（锁粒度），如果对同一行数据修改，先操作的会使用行级锁
MariaDB(XtraDB) innodb的改进版
innodb特性总结：
数据存储： 表空间
并发： MVCC，间隙锁实现
索引： 聚集索引，辅助索引
性能： 预读操作，自适应hash索引，插入缓存区提高性能
备份： 支持热备份(xtrabackup)
MYISAM：
特性： 支持全文索引, 压缩、空间函数， 不支持事物，表级锁。
崩溃后，无法安全恢复。
适应场景： 只读、 表较小（可以接受长时间修复操作）
文件：
tbl_name.frm : 表格式
tbl_name.MYD：数据文件
tbl_name.MYI: 索引文件
特性：
加锁和并发： 表级锁
修复：手工或自动修复，可能修复数据
索引：非聚集索引
延迟更新索引键:
支持把表压缩后存储
查看表使用的存储引擎是： use databases
show table status\G;
查看数据库支持的存储引擎是：
show engines;
查看当前环境使用的存储引擎是：
show global variables like ‘%engine%’;
并发控制
锁:
读锁： 共享锁，一个资源可以多次访问
写锁： 排他锁，当一个资源被一个线程施加写锁后，其他线程既不能施加写锁，也不能施加读锁。
锁粒度 越精细，并发越高
锁粒度：
表级锁 myisam
行级锁 innodb
锁策略： 在锁粒度及数据安全性 寻求的平衡机制。 每种存储引擎都可以自行实现其锁策略和锁粒度。
mysql在服务器级也实现了锁，表级锁。
root@localhost:uic>help lock Name: 'LOCK' LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE UNLOCK TABLES
添加读锁 ：lock tables t1 read ， flush tables with read lock；
添加写锁： lock tables t1 write
解锁： unlock tables
Mysql 事物：
事物： 一组原子性的sql 查询，或者说是一个独立工作单元。
事物日志 ：使用来实现崩溃恢复。oracle是redolog，undolog。
ACID 测试：
A： 原子性，整个事物中的所有操作要么全部执行，要么执行失败回滚。
C：一致性。数据库从事从一个一致性状态转换成另一个一致性状态。
I：隔离性。一个事物所做的操作在提交之前，是不能为其它所见。隔离级别。
D：持久性，一旦事物提交了，其所做的修改会永久保存在数据库中。
事物使用流程
start transaction ------- sql语句（insert、update、delete） --------commit 或者 rollback
启动事物 start trancation
结束事物 commit or rollback
只有事物型存储引擎才能使用。
innodb存储引擎 建议： 显示请求和提交事物，而不要使用“自动提交”功能。
show global variables like 'autocommit';
会话级别关闭 autocommit
set session autocommit = 0
查看 会话级别是否关闭
show session varables like ‘autocommit’；
MySQL数据库内置了帮助文档，通过help contents即可查看。
root@localhost:graph>help contents; You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Table Maintenance Transactions User-Defined Functions Utility
事物专用帮组命令
root@localhost:graph>help transactions; You asked for help about help category: "Transactions" For more information, type 'help <item>', where <item> is one of the following topics: CHANGE MASTER TO DEALLOCATE PREPARE EXECUTE STATEMENT ISOLATION LOCK PREPARE PURGE BINARY LOGS RESET MASTER RESET SLAVE SAVEPOINT SET GLOBAL SQL_SLAVE_SKIP_COUNTER SET SQL_LOG_BIN START SLAVE START TRANSACTION STOP SLAVE
事物支持savepoint 保存点：
root@localhost:graph>help savepoint; Name: 'SAVEPOINT' Description: Syntax: SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK.
start transaction
select * from endpoint;
delete from endpoint where id=10;
savepoint a1
insert into endpoint values(11,'feng')
savepoint a2
返回a1 的节点数据
rollback to a1
返回所有
rollback
事物隔离级别:
READ-UNCOMMITTED (读未提交)
READ-COMMITTED（读提交）
REPEATABLE-READ (可重读)
SERIALIZABILE(可串行化)
可能存在问题：
脏读： 读别人未提交的数据。
不可重复读：2次读到的数据，不同。
幻读：
加锁读：
查看mysql 默认隔离级别：READ-COMMITTED
root@localhost:graph>show global variables like '%tx_isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec)
死锁：
两个或者多个事物在同一个资源上相互占用，并请求锁定对方占用的资源的状态。
事物日志：
事物日志的写入类型为“追加”，因此其操作为“顺序IO”，此日志通常也被成为"预写式日志"， 如果把事物日志放在 ssd硬盘上，系统崩溃恢复较快。
事物日志，在 my.cnf 定义 innodb_log_file_size=190M， 位置在/data/mysql/data/ib_logfile0 /data/mysql/data/ib_logfile1 2个文件
innodb_log_file_size | 199229440|
innodb_log_files_in_group | 2 |
innodb_log_group_home_dir | /data/mysql/data/
mysql 日志：
查询日志： 默认不开启 query log
慢查询： slow query log，
错误日志： error log
二进制日志： binary log
中继日志： reley log
事物日志：transaction log
show global variables like '%log%';
1. 查询日志， 记录查询操作。 general_log 默认关闭的
可以记录到 (1)文件中，(2) 表中.
general_log ON|OFF
general_log_file
log_output = FILE|TABLE|NONE
2 .慢查询日志
执行时长超出指定时长的查询操作：
慢查询时长， 查询超过10秒的，属于慢查询，可以自定义。
MariaDB [feng]> show global variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
或使用
MariaDB [feng]> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
@@ 是 全局global
@ 是当前会话 session
慢查询的相关参数
slow_query_log ON|OFF
slow_query_log_file
log_slow_filter 慢查询记录器
admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_queries 是否记录慢查询日志的，通常使用slow_query_log设置
log_slow_rate_limit 按照速率记录
log_slow_verbosity
3.错误日志
会记录mysqld启动和关闭过程中输出的时间信息
mysqld 运行中产生的错误信息
event scheduler 运行一个event时产生的日志信息。
在从服务器的日志信息
log_error /data/mysql/mysql.err
log_warnings 1 是否记录警告信息到错误日志文件。
4.二进制日志
导致数据改变的语句
root@localhost:(none)> help show
root@localhost:(none)>show BINARY logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000013 | 126 | | mysql-bin.000014 | 126 | | mysql-bin.000015 | 126 | | mysql-bin.000016 | 560221408 | +------------------+-----------+ 4 rows in set (0.00 sec) root@localhost:(none)>show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000013 | 126 | | mysql-bin.000014 | 126 | | mysql-bin.000015 | 126 | | mysql-bin.000016 | 560221408 | +------------------+-----------+ 4 rows in set (0.00 sec)
查看 event 事件：
root@localhost:(none)>SHOW BINLOG EVENTS in 'mysql-bin.000016';
查看master 正在使用的日志
root@localhost:(none)>SHOW master status;
服务器变量：
sql_log_bin ON 是否记录二进制日志
log_bin ON 通常为ON， 记录的文件位置：
binlog_format STATEMENT 二进制日志记录的格式（STATEMENT|ROW|MIXED）
max_binlog_size 单个二进制日志文件的最大值:默认1G
sync_binlog 1 等于1会影响性能，等于0 会影响数据，如果是0数据未从内存写入磁盘，机器down机，会丢失数据。设定是否启动二进制日志同步功能。
二进制日志格式
命令：/data/mysql/sbin/mysqlbinlog ， 客户端程序。
参数：
--start-position
--stop-postion
--start-datetime
--stop-datetime
at 317
#170815 17:27:03 server id 1 end_log_pos 426 Query thread_id=2 exec_time=0 error_code=0
use `fengjian`/*!*/;
SET TIMESTAMP=1502789223/*!*/;
insert into fengjian.feng values(999,'10')
/*!*/;
事件发生的日志和时间： 170815 17:27:03
事件发生的服务器标识： server id 1
事件结束位置： end_log_pos 426
事件的类型： Query
事件发生的所在服务器执行的线程ID thread_id=2
语句的时间戳和写入二进制时间的时间差 exec_time=0
错误代码： error_code=0
事件内容
GTID ： Global Transacation ID： 专属属性
5.中继日志：
复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件：
6. 事物日志： transacation log 事务性存储引擎自行管理和使用。
备份和恢复
备份工具 mysqldump , xtrabackup
shell> mysqldump [options] db_name [tbl_name ...] #不会备份数据库名，备份库下的表 shell> mysqldump [options] --databases db_name ... # 备份库和表 shell> mysqldump [options] --all-databases # 全备份
备份过个库：
[root@openfalcon_mysql ~]# mysqldump --databases feng graph senyint > 1111.sql
恢复
[root@openfalcon_mysql ~]# mysql < 1111.sql
MyiSAM: 锁定备份库，而后启动备份操作
锁表方法：
--lock-all-tables: 锁定整个数据库的表。
--lock-tables : 锁定单独表
Innodb 备份：
--single-transaction 事物
--master-data=2
1. 记录为 CHANGE MASTER TO 语句， 此语句不被注释。
2. 记录为注释的CHANGE MASTER TO 语句
通过二进制日志恢复数据， 使用--master-data记录 "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=561189922;" 二进制文件名和 pos 1. mysqldump --all-databases --master-data >all.sql 2. 数据上操作，create database 1111； 3.使用mysqlbinlog 查看pos=561189922 后的操作 mysqlbinlog --start-position=561189922 /data/mysql/data/mysql-bin.000016 > abcd.sql 4. 恢复数据库 mysql < all.sql 5. 恢复 binlog中新增加的数据 mysql < abcd.sql
--flush-logs
锁定表完成后，执行flush logs命令：
注意： 二进制日志文件不应该与数据文件放在同一硬盘。
备份思路：
1. 一天做一次全备份
mysqldump --all-databases --master-data >all.sql
2. 二个小时做一次增量备份，备份binlog，使用二进制备份恢复。
全备后，使用flush logs 滚动二进制文件。
mysql> show master logs;
mysql > flush logs;
mysql > show master logs;
定时备份 binlog 文件。
xtrabackup:
percona公司开发
下载地址： wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.8-r97330f7-el7-x86_64-bundle.tar
安装完成后，备份命令是：
/usr/bin/innobackupex
/usr/bin/xtrabackup
innnodb_file_per_table = OFF , 所有库的所有表 使用同一个表空间。如果要备份一个库中的一张表，热备将不能实现。
使用innobackupex备份时，其会调用xtrabackup备份所有的innoDB表，复制所有关于表结构定义的相关文件（.frm）、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件
同时还会备份触发器和数据库配置信息行管的文件。这些文件会保存至一个以时间命令的目录中。
在使用innobackupex进行备份时， 还可以还可以--no-timestamp选项来阻止自动创建一个以时间命令的目录、
一个完全备份：
1. 启动数据库
systemctl start mysqld.service
2. 备份数据库
innobackupex -u root /root/backup/
3. 拷贝到另一台机器，进行还原
scp -r /root/backup/* 172.168.230.169:/root/backup/
4. 整理事物（172.16.230.169）
一般情况下，在备份完成后，数据不能直接用于恢复操作，因为备份的数据中可能会包含尚未提交的事物或者已经提交但尚未同步至数据文件中的事物，因此，此数据文件文件扔处于不一致状态。
需要通过回滚未提交的事物 和 同步已经提交的事物至数据文件。 innobackuppex --apply-log 可用于解决此问题，整理事物。 innobackupex --apply-log /root/backup/2017......*
5. 关闭数据库
systemctl stop mariadb.service
6. 还原数据库
rm /data/mysql/data innobackupex --copy-back /path/to/backup-dir 7. 修改/data/mysql/data 权限 chown -R mysql.mysql /data/mysql/data
8.启动数据库
systemctl start mysqld.service
如果启动不了，看/data/mysql/data/ib_logfile0 和 ib_logfile1 大小与原mysql是否相同， 可以删除2个文件后，再启动。
rm /data/mysql/data/ib_logfile0 /data/mysql/data/ib_logfile1 -rf
systemctl start mysqld.service
使用innobackupex 进行增量备份
每一个innodb的页面都会包含一个LSN信息，每当数据发生变化，相关的页面LSN就会自动增长，要实现第一次增量备份命令为：
innobackupex --incremental /backup/mysql/(增量的备份路径) --incremental-basedir=BASEDIR(基于上一次完全备份)
注意： /backup/mysql/ 是增量保存的路径
--incremental-basedir=BASEDIR 是上一次 增量或全量的数据。
准备增量备份与完全备份区别：
1. 需要在每个备份(包括完全和各个增量备份)，仅提交事物
2. 基于所有的备份将未提交的事物进行“回滚”
操作：
#仅提交事物，不进行回滚
1. innobackupex --apply-log --redo-only BASE-DIR
#第一个增量
2. innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
#第二个增量
3. innobackupex -- apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
其中BASE-DIR 指的事完全备份所在的目录，而 INCREMEMTAL-DIR-1是指第一次增量备份的目录
例子： 1. 完全备份： innobackupex /root/backup/ 2. 增量备份1 数据库操作，增删改。 innobackupex --incremental /root/backup/ --incremental-dir=/root/backup/(完全备份) 3. 再次增量备份2 innobackupex --incremental /root/backup/ --incremental-dir=/root/backup/(增量备份1)
4. 模拟故障，删除数据库 systemctl stop mariadb.service rm /data/mysql/data/* -rf 5. 将所有的提交事物，全部合并到完全备份上 innobackupex --apply-log --redo-only /root/backup/BASE-DIR(完全备份) innobackupex --apply-log --redo-only /root/backup/BASE-DRI(完全备份) --incremental-dir=/root/backup/INCREMENTAL-DIR-1 innobackupex --apply-log --redo-only /root/backup/BASE-DRI(完全备份) --incremental-dir=/root/backup/INCREMENTAL-DIR-2
6. 还原数据库 innobackupex --copy-back /root/backup/BASE-DIR(完全备份) 7. 赋予权限 chown -R mysql.mysql /data/mysql/data 8. 启动mysql systemctl start mariadb.service
innobackupex 导入导出 单张表
必须启动 innodb_file_per_table innodb_expand_import 选项，
1. 导出表
必须要全备份， 然后--export导出所有表
innobackupex /root/backup/
innobackupex --apply-log --export /root/backup/ #导出所有的表
2.导入表
要在mysql服务器上 导入其来自其他服务器的innodb表，需要先在服务器上创建一个跟原表表结构一致的表，然后才能导入
mysql > create table aaaaa ENGINE=InnoDB;
删除表空间
mysql > alter table 库.表 DISCARD TABLESPACE;
导入表,把exp 和 idb 拷贝到新服务器/data/mysql/data/库
alter table 库.表 import tablespace
mysql 主从
主节点：
1. 启动二进制日志。log-bin = mysql-bin
2. 为当前节点设置一个全局的ID号，server-id
3. 创建有复制权限的用户账号。
从节点：
1. 启动中继日志。
relay-log=mysql-relay
relay-log-info-file=mysql-relay.info
2. 为当前节点设置一个全局的ID号， 大于主节点
3. 使用有复制权限的用户账号连接至主服务器，并启动复制线程。
复制架构中应该注意的问题：
1. 限制从服务器为只读， 在从服务器上设置 read_only = on ( show global variables like '%read_only%'),此限制对拥有super权限的用户均无效
2. 阻止所有用户， flush tables with read lock; 启动后 终端不退出。
3. 保证主从复制的事物安全。
在master节点要启动参数， 在事物提交时，将bin-log缓冲区中的事件，立刻刷新到二进制文件中去，从而赶快通知从节点，同步binlog
sync_binlog = on
innodb_flush_log_at_trx_commit=1 在事物提交时，立即同步到事物日志中
innodb_support_xa=1 支持分布式事物。
sync_master_info=1 每次给从节点dump event后，本地 master_info是否及时同步到本地磁盘上，本地磁盘master_info 能够及时更新,会增加IO压力。
在slave节点：
skip_slave_start = ON 建议手动启动
sync_relay_log = 1
sync_relay_log_info =1
mysql 主主复制， 互为主从
1. 数据不一致，
2. 自动增长id
配置第一个节点使用奇数id
auto_increment_offset = 1
auto_increment_increment =2 自动增长2个
配置第二个节点使用偶数id
auto_increment_offset = 2
auto_increment_increment =2 自动增长2个
root@localhost:(none)>show global variables like '%auto%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 2 | | sql_auto_is_null | OFF | +-----------------------------+-------+
配置步骤
1. 各节点使用一个唯一server id
2. 都启动binary log 和 relay log
3. 创建拥有复制权限的用户账号。
4. 定义自动增长id字段的数值范围为奇偶
5. 都把对方指定为主节点，并启动复制线程。
半同步复制：
master ：
mysql> install plugin repl_semi_sync_master SONAME 'semisync_master.so';
mysql> set global rel_sync_master_enabled=1;
mysql> show global status like '%sem%';
slave：
mysql> install plugin repl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> set global rel_sync_slave_enabled=1;
mysql> start slave;
复制过滤器：
让从节点仅复制指定的数据库，或指定数据库的指定表。
两种实现方式：
1. 主服务器仅向二进制日志中记录特定数据库相关的事件。
binlog_do_db # 数据库白名单列表
binlog_ingnore_db # 数据库黑名单列表
2. 从服务器sql_thread 在 replay 中继日志中的事件时， 仅读取与特定数据库 相关的时间并应用本地。
replicate_do_db =
replicate_ignore_db =
和复制相关的文件：
mater.info ：用于保存slave 连接至 master时 的相关信息，例如账号、密码、服务器地址
relay-log.info : 保存在当前slave 节点上已经复制的当前二进制日志和本地replay log 日志对应关系。
清理日志文件
mysql > help purge
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
监控：
show master status;
show binlog enents;
show binary logs
show slave status\G;
show processlist;
主从监控
show slave status\G; ------->seconds_Behind_Master: 0 #比master 落后多长时间。
