运维
日志
错误日志
错误日志是MySQL中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log。
show variables like '%log_error%'二进制日志
二进制日志(BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）
- 灾难时的数据恢复
- MySQL的主从复制
show variables like '%log_bin%'当一个日志文件写满时候，会自动开辟一个新的日志文件
日志格式
| 日志格式 | 含义 |
|---|---|
| STATEMENT | 基于sQL语句的日志记录，记录的是sQL语句，对数据进行修改的sQL都会记录在日志文件中。 |
| ROW | 基于行的日志记录，记录的是每一行的数据变更。（默认) |
| MIXED | 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 |
SHOW VARIABLES LIKE '%binlog_format%';查看
基于行的模式需要重构为SQL 语句，需要加上参数 -v
删除
| 指令 | 含义 |
|---|---|
| reset master | 删除全部 binlog日志，删除之后，日志编号，将从 binlog.000001 |
| purge master logs to 'binlog.****' | 重新开始删除******编号之前的所有日志 |
| purge master logs before 'yyyy-mm-dd hh24:mi:ss' | 删除日志为"yyyy-mm-dd hh24:mi:ss"之前产生的所有日志 |
也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。
show variables '%binlog_expire_logs_seconds%';查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置
show variables like '%general%';[mysqld]
general=1
general_log_file=/var/log/mysql/mysql_query.log慢日志
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。long query_time默认为10秒，最小为0，精度可以到微秒。
# 开启慢日志
slow_query_log=1
# 执行时间参数
long_query_time=2默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes，如下所述。
# 记录较慢的的管理语句
log_slow_admin_statements=1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1分库分表
随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储
存在以下性能瓶颈:
- IO瓶颈:热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈
- CPU瓶颈:排序、分组、连接查询☆聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈
拆分策略
- 垂直拆分
- 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
- 每个库的表结构都不一样。
- 每个库的数据也不一样。
- 所有库的并集是全量数据。
- 垂直分库：以表为依据，根据业务将不同表拆分到不同库中。
- 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
- 每个表的结构都不一样。
- 每个表的数据也不一样，一般通过一列（主键/外键）关联。
- 所有表的并集是全量数据。
- 水平拆分
- 水平分库：水平分库:以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
- 每个库的表结构都一样。
- 每个库的数据都不一样。
- 所有库的并集是全量数据。
- 水平分库：水平分库:以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
- 水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
- 每个表的表结构都一样。
- 每个表的数据都不一样。
- 所有表的并集是全量数据。
shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。
需要自行编码配置实现，只支持java语言，性能较高。
MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。
MyCat
MyCat 是开源的、活跃的、基于Java语言编写的 MySQL 数据库中间件。可以像使用 MySQL 一样来使用MyCat，对于开发人员来说根本感觉不到 MyCat 的存在。
备份和恢复
select * from stu wher age > 200 into file '/out/stu.sql';
load data infile '/tmp/stud.txt' into table students;# 施加锁，表示把位于内存上的表统统都同步到磁盘上去，然后施加读锁
flush tables with read lock;
flush tables with read lock;释放读锁 mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql读写分离
一主一从
基于 binlog 同步
- 主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做)，从而使得从库和主库的数据保持同步。
- 主库出现问题，可以快速切换到从库提供服务。
- 实现读写分离，降低主库的访问压力。
- 可以在从库中执行备份，以避免备份期间影响主库服务。
- MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制
- 读写分离，简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。
binlog 会在服务器启动生成，用于记录主库数据库变更记录，当binlog发生变更时，主结点的log dump线程会将其内容发给各个从结点，从结点的 IOthread 接收 binlog 内容，并写入relay log(从节点上)，从结点的 SQL线程读取 relay log内容对数据库数据进行更新重放，保证主从一致性。
搭建
- 主库
- 配置
# mysql服务ID，保证整个集群环境中唯一，取值范围:1-232-1，默认为1server-id=1
server-id=1
# 是否只读,1代表只读,0代表读写read-only=O
read-only=0
# 忽略的数据,指不需要同步的数据库
# binlog-ignore-db=mysql
# 指定同步的数据库
# binlog-do-db=db01b. 创建账号
create user 'master'@'%' identified with mysql_native_password by 'master';
-- 未用于分配复制权限
grant perlication slave on *.* to 'master'@'%';
-- 查看 主节点日志
show master status;
-- file :从哪个日志文件开始推送日志文件
-- position:从哪个位置开始推送日志
-- binlog_ignore_db:指定不需要同步的数据库- 从库
server-id=2
-- 是否只读,1代表只读,0代表读写read-only=O
-- 如果是管理员还是可读的
read-only=1
-- 0 管理员不可读
super-read-only=1CHNGE REPLCATION SOURCE TO SOURCE_HOST='host', SOURCE_USER='user',SOURCE_PASSWORD='passwd',SOURE_LOG_FILE='xxx', SOURCE_LOG_POS=xxx;CHANGE MASTER TO MASTER HOST='host', MASTER_USER='xx',NASTER_PASSWORD='xx', MASTER_LOG_FILE='xx', MSTER_LOG_POS=xxx;
-- 8.0
start replica;
-- 8.0 之前
start slave;
show slave status;双主双从
一个主机Master1用于处理所有写请求，它的从机Slave1和另一台主机Master2还有它的从机Slave2负责所有读请求。当Master1主机宕机后，Master2主机负责写请求，Master1 、Master2互为备机
搭建
- 主库
server-id=1
-- 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates创建账号
- 主备库
server-id=2
-- 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates创建账号
- 从库
server-id=3关联自己的主库
- 从备库
server-id=4关联自己的主库
- 两个主库之间相互复制
一样的命令
突然断电导致的mysql启动失败解决方式
Assertion failure in thread 140368565749504 in file fut0lst.ic line 93
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:07:22 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=8000
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3187317 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7faa100008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7faa1a7fbcb0 thread_stack 0x40000
mysqld(my_print_stacktrace+0x3b)[0xf1183b]
mysqld(handle_fatal_signal+0x486)[0x79f716]
/lib64/libpthread.so.0(+0xf630)[0x7faabf5d0630]
/lib64/libc.so.6(gsignal+0x37)[0x7faabd8e3387]
/lib64/libc.so.6(abort+0x148)[0x7faabd8e4a78]
mysqld[0x76f6b8]
mysqld[0x76f1c8]
mysqld[0x10adcc5]
mysqld[0x10b0631]
mysqld(_Z9trx_purgemmb+0x3e9)[0x10b3919]
mysqld(srv_purge_coordinator_thread+0xded)[0x10890dd]
/lib64/libpthread.so.0(+0x7ea5)[0x7faabf5c8ea5]
/lib64/libc.so.6(clone+0x6d)[0x7faabd9abb2d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.从日志中可以看出是innodb引擎出了问题。日志里提示到 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html查看强制恢复的方法。在mysql的配置文件my.cnf里找到 [mysqld]字段下，添加 innodb_force_recovery=1
配置my.cnf
配置innodb_force_recovery = 1或2——6几个数字，重启MySQL
值为1：(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
值为2：(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
值为3：(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
值为4：(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
值为5：(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
值为6：(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。导出数据脚本
mysqldump -uroot -p123 test > test.sql
导出SQL脚本。或者用Navicat将所有数据库/表导入到其他服务器的数据库中。
注意：这里的数据一定要备份成功。然后删除原数据库中的数据。
删除ib_logfile0、ib_logfile1、ibdata1
备份MySQL数据目录下的ib_logfile0、ib_logfile1、ibdata1三个文件，然后将这三个文件删除（可选）
配置my.cnf
将my.cnf中innodb_force_recovery = 1或2——6几个数字这行配置删除或者配置为innodb_force_recovery = 0，重启MySQL服务
将数据导入MySQL数据库
mysql -uroot -p123 test < test.sql; 或者用Navicat将备份的数据导入到数据库中。
此种方法下要注意的问题：
1、ib_logfile0、ib_logfile1、ibdata1这三个文件一定要先备份后删除；
2、一定要确认原数据导出成功了
3、当数据导出成功后，删除原数据库中的数据时，如果提示不能删除，可在命令行进入MySQL的数据目录，手动删除相关数据库的文件夹或者数据库文件夹下的数据表文件，前提是数据一定导出或备份成功。
用户权限管理
-- 撤销原有用户的权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'ip_address';
-- 删除原有用户
DROP USER 'username'@'ip_address';
-- 添加新用户并授予新权限
CREATE USER 'new_username'@'new_ip_address' IDENTIFIED BY 'new_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'new_username'@'new_ip_address';
FLUSH PRIVILEGES;