1.MySQL常见的存储引擎及特点
Innodb：现主流的mysql存储引擎。
1.支持事务特性
2.使用B+树索引的数据结构
3.具有外键特性
4.支持事务，默认事务隔离级别为第三等级的可重复读，但事务无法隔离DDL语句
5.支持热备、温备、冷备
6.支持行级锁
7.数据库存储文件格式为2种： .ibd（数据与索引合并文件） .frm文件（表格式定义文件）
8.支持聚集索引
MyISAM：mysql5.5.5版本前的默认存储引擎
1.适用于数据量少，读写快的场景
2.不具有外键
3.支持温备、冷备，不支持热备
4.支持表级锁
5.不支持聚集索引，存储文件格式为3中：.MYD .MYI （数据与索引分开存放）
2.MySQL日志各类总结
二进制日志文件：常用于备份还原恢复数据库的重要文件，记录数据库操作SQL语句、事务提交日志等数据。 文件格式为 ： mysql-bin.000001
相关命令 - show master logs; ##查看二进制日志信息 - show master status; ##查看使用中的二进制文件 - mysqlbinlog mysql-bin.00001 > xx.sql ##还原二进制日志文件 相关配置项 - vi /etc/my.cnf [mysqld] log_bin=/data/bin-log/ ##开启二进制文件功能并指定存储位置 binlog_format=row ##定义二进制文件存储类型，row表示基于“行”，可记录实时数据即事务未提交的数据也 能记录，但数据量大，生产推荐使用格式 - set sql_log_bin=off; ##关闭二进制功能 - set sql_log_bin=on; ##开启二进制功能，常配套使用于 利用二进制日志文件还原数据库时，防止多次 写入重复数据慢查询日志：用于记录执行超过定于的慢查询阈值的SQL语句信息,文件格式为：yy-slow.log
相关命令 - mysqldumpslow -s c -t 10 /data/mysql-slow.log ##返回超过10s慢查询日志语句的 相关配置 [mysqld] slow_query_log=ON ##开启慢查询 slow_query_log_file=/data/mysql-slow/mysql-slow.log ##慢查询日志文件 long_query_time=10 ##慢查询语句阈值设置为10s错误日志：用于记录日常数据库时warning或报错的信息,可用于排错。文件格式为： yy.error
相关命令 - show global variables like 'log_error' ##查询错误日志存储位置 相关配置事务日志：用于记录事务操作，用于记录事务操作处理过程信息。可用于恢复至checkpoint,事务回滚等操作。 分为redo log(数据更新前预先写的日志) 和undo log（用于回滚，保存操作相反的操作） 文件格式为： redo.log：ib_logfile0 ； undo.log：table.ibd
相关命令： - show variables like '%innodb_log%'; 相关配置： （事务针对innodb，myisam没有事务不考虑相关优化配置） [mysqld] innodb_flush_log_at_trx_commit = 0 | 1 | 2 ##事务日志性能优化，默认为1 ## 0： 事务提交与未提交均写入log buffer，定时将从log buffer里提交事务 数据写入刷新到磁盘，但数据库服务崩溃将可能丢失数据（优化速度最快） ## 1： 事务未提交时数据写入log buffer,事务提交后数据立即执行刷新到磁 盘。（速度最慢，但最安全适用于生产，遵守ACID） ## 2： 事务提交与未提交均写入OS buffer，定时从OS buffer的提交事务的数据写入刷新 到磁盘，但OS系统崩溃时可能丢失数据（0和2适用于数据不太重要场景，优化提性能）通用日志：记录对数据库操作日志，通常用户查询或统计数据库使用的SQL语句操作记录。文件格式为：yy.general_log.CSV yy.general_log.frm等
相关命令 - select * from mysql.general_log\G ##查看通用日志记录信息 相关配置 [mysqld] general_log=yy.log ##开启通用日志，指定通用日志文件日志
3.主从复制和主主复制的实现
主从复制配置思路：（通常搭配半同步复制）
主节点：1.修改my.cnf配置文件，添加Server_id，开启二进制功能log_bin，添加semisync_master半同步插件，开启半同步功能，选做设置半同步超时时间
2.添加并授权复制的用户账号repluser
从节点：1.修改my.cnf配置文件，添加Server_id，添加semisync_slave半同步插件，开启半同步功能。启用relay-log中继日志。
2.使用复制权限用户账号连接主节点，启动复制线程
主节点： [root@master22 ~]# grant replication slave on *.* to 'repluser'@'192.168.149.%' identified by '123456'; [mysqld] server-id=22 log-bin=/data/log/mysql-bin MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1080 | +------------------+-----------+ 从节点： [mysqld] server_id=23 read_only=ON relay_log=relay-log MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.149.22'， MariaDB [(none)]> MASTER_USER='repluser', MariaDB [(none)]> MASTER_PASSWORD='123456', MariaDB [(none)]> MASTER_LOG_FILE='mariadb-bin.00001', MariaDB [(none)]> MASTER_LOG_POS=1080; MariaDB [(none)]>start slave; MariaDB [(none)]>show slave status\G; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.149.22 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1080 Relay_Log_File: slave23-relay-bin.000002 Relay_Log_Pos: 1379 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1080 Relay_Log_Space: 1690 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 22 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec) 从节点： [mysqld] server_id=24 read_only=ON主主复制：
主节点：1.my.cnf 配置上 server_id ，2.启用log-bin和relay-bin ，3.创建拥有复制权限的账号 4.均把对方指定主节点change master to master_host，并启动复制线程 ，5.选做将主节点自增长字段幅度不同
主节点22： [mysqld] server-id=22 log-bin=/data/log/mysql-bin auto_increment_offset=2 auto_increment_increment=2 MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1103 | | mysql-bin.000002 | 342 | +------------------+-----------+ MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.149.23', -> MASTER_USER='repluser', -> MASTER_PASSWORD='shaodeng', -> MASTER_LOG_FILE='slave23-bin.000001', -> MASTER_LOG_POS=330; MariaDB [(none)]> start slave; MariaDB [(none)]> start slave status\G; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.149.23 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: slave23-bin.000001 Read_Master_Log_Pos: 330 Relay_Log_File: master22-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: slave23-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 330 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec) 主节点23： [mysqld] server-id=23 log-bin auto_increment_offset=1 auto_increment_increment=2 MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1103 | | mysql-bin.000002 | 342 | +------------------+-----------+ CHANGE MASTER TO MASTER_HOST='192.168.149.22', MASTER_USER='repluser', MASTER_PASSWORD='shaodeng', MASTER_LOG_FILE='mysql-bin.00002', MASTER_LOG_POS=342; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.149.22 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.00002 Read_Master_Log_Pos: 342 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.00002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 342 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 1 row in set (0.00 sec)
4.xtrabackup实现全量+增量+binlog恢复
思路：完全备份-1次增量备份-2次增量备份-未备份修改数据-还原完全备份-还原1次增量备份-还原2次增量备份（不带--apply-log-only）--还原属性（若root账号操作)--还原二进制文件
环境:
percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
mysql Ver 15.1 Distrib 10.2.40-MariaDB
阿里云轻量应用服务器CentOS 7.6
AWS
--------【在MariaDB 10.2和MariaDB 10.1 中，仅部分支持Percona XtraBackup 】-------- ----在MariaDB 10.2.4执行xtrabackup2.4版本会出现以下错误 InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.40. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html 在InnoDB中使用备份安全的TRUNCATE TABLE实现和崩溃安全的重命名操作。这与Mariabackup以外的热备份工具不兼容。需要使用此类工具的用户可将此设置为OFF。 --------【MariaDB 10.3以后不支持xtraBackup，可用Mariabackup 类似工具代替 】-------- 临时解决方法：将修改my.cnf配置文件 [mysqld] innodb_safe_truncate=OFF systemctl restart mysql ---------------------------------------------------------- 完全备份-1次增量备份-2次增量备份-未备份修改数据-还原完全备份-还原1次增量备份-还原2次增量备份（不带--apply-log-only） ##安装xtrabackup2.4版本 [root@master ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm ## 1.第一次完全备份 [root@master ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base xtrabackup: Transaction log of lsn (1666591) to (1666600) was copied. 210826 11:17:23 completed OK！ MariaDB [yyy]> select * from y1; +----+-------------+ | id | name | +----+-------------+ | 1 | haha | | 2 | yaya | | 3 | backup-all | +----+-------------+ ## 2.第一次增量备份 ##修改插入数据 MariaDB [yyy]> select * from y1; +----+-------------+ | id | name | +----+-------------+ | 1 | haha | | 2 | yaya | | 3 | backup-all | | 4 | backup-inc1 | +----+-------------+ [root@master ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-dir=/backup/base xtrabackup: Transaction log of lsn (1677617) to (1677626) was copied. 210826 13:22:03 completed OK ## 3.第二次增量备份 ##修改插入数据 MariaDB [yyy]> select * from y1; +----+-------------+ | id | name | +----+-------------+ | 1 | haha | | 2 | yaya | | 3 | backup-all | | 4 | backup-inc1 | | 5 | backup-inc2 | +----+-------------+ [root@master ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-dir=/backup/inc1 xtrabackup: Transaction log of lsn (1678701) to (1678710) was copied. 210826 14:13:19 completed OK! ## 4.未备份时修改表数据 MariaDB [yyy]> drop table y1; MariaDB [yyy]> create table y2 (id int primary key auto_increment,name varchar(20))engine=innodb; ## 5.拷贝备份此时最新二进制文件 [root@master inc2]# cat /backup/inc2/xtrabackup_binlog_info ##找到开始点842 mysql-bin.000007 842 0-1-36 MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 4487 | | mysql-bin.000002 | 719 | | mysql-bin.000003 | 432 | | mysql-bin.000004 | 622 | | mysql-bin.000005 | 432 | | mysql-bin.000006 | 1473 | | mysql-bin.000007 | 1180 | +------------------+-----------+ [root@master ~]# mysqlbinlog /data/mysql/mysql-bin.000007 --start-position=842 > /backup/last-bin.sql ##备份指定位置的二进制日志文件 ## 6.还原过程 **************************************** 1）预准备 3个目录 [root@master backup]# tree -d /backup /backup ├── base ##完全备份 │ ├── hellodb │ ├── mysql │ ├── performance_schema │ └── yyy ├── inc1 ##1次增量备份 │ ├── hellodb │ ├── mysql │ ├── performance_schema │ └── yyy └── inc2 ##2次增量备份 ├── hellodb ├── mysql ├── performance_schema └── yyy 2) 预准备完全备份 [root@master ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1666609 InnoDB: Number of pools: 1 210826 14:34:39 completed OK! 3）合并第一次增量备份至完全备份 （带--apply-log-only） [root@master ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1677635 InnoDB: Number of pools: 1 ... copying ... 210826 14:38:00 completed OK! 4）合并最后一次增量备份至完全备份 （不带--apply-log-only） [root@master ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2 5）还原----复制到数据库目录 [root@master data]# xtrabackup --copy-back --target-dir=/backup/base --datadir=/data/mysql xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39) ... copying ... 210826 15:23:33 completed OK! [root@master data]# chown -R mysql.mysql /data/mysql ##因为root权限执行还原，需还原mysql属性至目录，否则mysql无法启动 ##出现以下错误表明你的数据库目录不为空，导致命令不能执行下去，数据不能回写。 ##解决方法：1.将数据库目录备份改名 - mv /data/mysql 2.xtrabackup添加参数还原新的数据库目录 - xtrabackup --data-dir=/data/mysql [root@master data]# xtrabackup --copy-back --target-dir=/backup/base xtrabackup: recognized server arguments: --log_bin=mysql-bin --server-id=1 xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql.sock --copy-back=1 --target-dir=/backup/base xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39) 6）还原二进制文件 MariaDB [yyy]> set sql_log_bin=0; ## MariaDB [yyy]> source /backup/last-bin.sql MariaDB [yyy]> set sql_log_bin=1;
4886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
