- 总结mysql常见的存储引擎以及特点
MYSQL 常见的存储印象有MyISAM和InnoDB.
- 理解transactions,一个重要的特性原子性:一个事务是由多个小的步骤组合而成,这些步骤要么统统全部完成,要么全不不做,必须作为一个整体出现
- Lock: MyiSAM 只支持表级别 Lock, InnoDB 支持行级别 lock
- MyISAM 每个表有三个文件: frm/myd/myl
- InnoDB 数据库对应 ftm 和 ibd;
2. MySQL日志各类总结
Mysql各类日志
- 事务日志
InnoDB支持事务,理解为write ahead logging
Redo log
Undo log
MariaDB [(none)]> show variables like '%innodb_log%';
- 错误日志
主要记录为服务在运行时产生的错误日志,其文件路径为:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error';
- 通用日志
记录对数据库的通用操作,包括:错误的SQL语句
vim /etc/my.cnf.d/server.cnf
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
- 慢查询日志
记录执行查询时长超出指定时长的操作,用于优化SQL语句
慢查询分析工具: mysqldumpslow
- 二进制日志(最为重要,用于备份恢复)
一共有三种模式:statement,row,mixed
查看其使用的模式
MariaDB [(none)]> show variables like 'binlog_format';
开启二进制日志
sql_log_bin=ON|OFF:#是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项
log_bin=/PATH/BIN_LOG_FILE:#指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED:#二进制日志记录的格式,默认STATEMENT
vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=mariadb-bin
max-binlog-size=1G
binlog_format=row
MariaDB [(none)]> show variables like 'binlog_format';
MariaDB [(none)]> show binlog events;
Mysqlbinlog 客户端工具, 可查看bin log.
3. 主从复制及主主复制的实现
3.1 数据库复制 replication 实现原理
mysql 的主从复制功能是基于 binlog 操作日志的,其过程如下:
- 从数据库执行 start salve ,开启主从复制
- 从数据库的 IO 线程会使用 master上授权的用户请求连接主数据库,并请求指定的 binlog 日志。
- 主数据接收到来自从数据库的 IO 线程的请求后,主数据库上负责复制的 io 线程根据从数据库的请求信息,读取指定的 binlog 文件的指定位置,返回给从数据库的io线程,返回的信息除了本次请求的日志内容外,还是有本次返回的日志内容后在主数据上新的 binlog 文件名称及在 binlog 中的位置(供从数据库下次请求 binlog 使用)。
- 从数据的 IO 线程获取到来自主数据上的 IO 线程发送的 binlog 后,将 binlog 中的内容依次写入到从数据库自身的 Relaylog(中继日志)文件(Mysql-info-realy-bin.XXXX) 的最末端,并将新的 binlog 文件名和位置记录到 Master-info 文件中,以便下一次读取主数据库的新 binlog 日志时,能够告诉 master 服务器需要从新 binlog 日志的那个文件那个位置,开始返回给从数据库。
- 从数据库的 sql 线程会实时的检测本地 relay log 中新增的日志内容,然后及时把 log 文件中的内容解析成在主数据库曾经执行的 sql 语句,并在自身从数据库上按顺序执行这些 sql 语句。
- 至此,正常情况下,主从数据库,就可以实现同步。
3.2 常用相关指令
show master status; //查看master的状态,尤其是当前的日志及位置
show slave status; //查看slave的状态
reset slave; reset salve all //重置slave状态
start slave; //启动slave状态(开启监听master的变化)
stop slave; //暂停salve状态
主从基本配置步骤
主服务器:192.168.0.100
从服务器:192.168.0.101
1. 主服务器打开二进制功能
配置my.cnf
# 给服务器起一个唯一的id
server-id=1
# 开启二进制日志
log-bin=mysql-bin
# 指定日志格式
binlog-format=mixd/row/statement
# 重启mysqld
# 查看状态
mysql>show master status;
2. 主服务器建立从服务器的账号,并授权
mysql> grant replication slave,replication client on *.* to repluser@'192.168。0.%' identified by 'xxxxx';
mysql> flush privileges;
3. 从服务器打开二进制日志和relay日志功能
配置my.cnf
# 服务器id
server-id=12
# 从服务器中继日志
relay-log=mysql-relay
# 指定只读
read-only=1
# 重启服务器
从服务器指定对应的主服务器,开启从服务器
mysql> change master to
-> master_host='192.168.0.100'
-> master_user='repluser',
-> master_password='xxxx',
-> master_log_file='mysql-bin.00001',
-> master_log_pos=0;
mysql> start slave;
mysql> show slave status \G;
3.3 . 主主复制 master-master setup
两台服务器上都开启二进制日志和relay日志,都设置replication账号,都设置对方为自己的master。
可以在F5 上做一个VIP,F5上指向一个 master SQL.
SQL客户端指向 F5 MYQSL-DB-VIP。 避免直接连接 Server1/Server2 IP.
Check:
Server1:
show master status\G;
show slave statuss;
server2:
show master status;
show slave status;
4. xtrabackup实现全量+增量+binlog恢复库