mysql数据库维护和优化_MySQL之数据库维护和优化篇

1.数据库维护物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于大型的重要数据库，这些数据库在出现问题时需要快速恢复。逻辑备份保存表示为逻辑数据库结构(CREATE DATABASE，CREATE TABLE语句)和内容(INSERT语句或定界文本文件)的信息。这种备份类型适用于少量数据，您可以在其中编辑数据值或表结构，或在其他计算机体系结构上重新创建数据。

1.1、备份数据

1.1.1、数据库备份方式使用mysqldump进行备份可以通过复制表文件(.MYD，.MYI和关联*.sdi文件)来备份MyISAM表。要获得一致的备份，请停止服务器或锁定并刷新相关表：FLUSH TABLES tbl_list WITH READ LOCK;进行分隔文本文件备份通过启用二进制日志进行增量备份

1.1.2、使用mysqldump进行备份要转储所有数据库，请使用以下选项调用mysqldump的--all-databases选项：shell> mysqldump --all-databases > dump.sql要转储单个数据库，可以忽略--databases选项，但转储输出不包含CREATE DATABASE和USE 语句：shell> mysqldump test > dump.sql要转储特定多个数据库，请在命令行上命名它们并使用--databases选项：shell> mysqldump --databases db1 db2 db3 > dump.sql要仅转储数据库中的特定表，请在数据库名称后的命令行中将其命名：shell> mysqldump test t1 t3 t7 > dump.sql

1.2、重载SQL格式的备份要重新加载由mysqldump编写的包含SQL语句的转储文件，请将其用作mysql客户端的输入。如果转储文件是由mysqldump使用--all-databases或--databases选项创建的，则它包含CREATE DATABASE和USE语句，并且无需指定默认数据库以将数据加载到其中:shell> mysql < dump.sql或者，从mysql内部，使用 source命令:mysql> source dump.sql如果文件是不包含CREATE DATABASE和USE语句的单数据库转储，请首先创建数据库(如有必要)：shell> mysqladmin create db1然后在加载转储文件时指定数据库名称:shell> mysql db1 < dump.sql或者，从mysql内部，创建数据库，将其选择为默认数据库，然后加载转储文件:mysql> CREATE DATABASE IF NOT EXISTS db1;

mysql> USE db1;

mysql> source dump.sql

1.3、恢复数据使用二进制日志进行时间点(增量)恢复时间点恢复是指恢复自给定时间点以来所做的数据更改。通常，这种类型的恢复是在还原完整备份后执行的，该备份将使服务器在进行备份时进入其状态。然后，时间点恢复将使服务器从完全备份时起逐步更新到最新状态、最近的时间。二进制日志包含描述数据库更改(例如表创建操作或表数据更改)的“事件”。

1.3.1、查看二进制日志功能MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | OFF |

+---------------+-------+

1 row in set (0.01 sec)

1.3.2、启用二进制日志从MySQL 8.0开始，默认情况下启用二进制日志记录。# vim etc/my.cnf

log_bin=ON

log-bin=bin

log-bin-index=/var/log/mariadb/bin.index

# mysql -u root -p

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)log_bin=ON : 启用二进制日志记录log-bin=bin : 指定用于二进制日志文件的名称log-bin-index=/var/log/mariadb/bin.index : 指定二进制日志索引文件的名称

1.3.3、查看所有二进制日志文件MariaDB [(none)]> SHOW BINARY LOGS;

+------------+-----------+

| Log_name | File_size |

+------------+-----------+

| bin.000001 | 129175 |

+------------+-----------+

1 row in set (0.00 sec)

1.3.4、确定当前二进制日志文件的名称MariaDB [(none)]> SHOW MASTER STATUS;

+------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------+----------+--------------+------------------+

| bin.000001 | 138891 | | |

+------------+----------+--------------+------------------+

1 row in set (0.00 sec)

1.3.5、使用事件时间进行时间点恢复为了指示恢复的开始时间和结束时间，使用mysqlbinlog的--start-datetime和--stop-datetime选项:shell> mysqlbinlog --stop-datetime="2019-09-09 9:59:59" \

var/log/mysql/bin.123456 | mysql -u root -p该命令将恢复所有数据，直到该--stop-datetime选项指定的日期和时间为止。shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \

var/log/mysql/bin.123456 | mysql -u root -p恢复从--start-datetime选项指定的日期和时间开始的数据

1.3.6、显示日志文件内容# mysqlbinlog var/lib/mysql/bin.000001 > tmp/test.sql

1.3.7、使用事件位置进行时间点恢复使用位置可以使您更精确地了解要恢复日志的哪一部分。shell> mysqlbinlog --stop-position=368312 var/log/mysql/bin.123456 \

| mysql -u root -p

shell> mysqlbinlog --start-position=368315 var/log/mysql/bin.123456 \

| mysql -u root -p第一条命令将恢复所有事务，直到给出停止位置为止。第二个命令从给定的起始位置恢复所有事务，直到二进制日志结束。

1.4、进行数据库维护

1.4.1、检查表键是否正确MariaDB [test]> ANALYZE TABLE mytable;

+--------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+---------+----------+----------+

| test.mytable | analyze | status | OK |

+--------------+---------+----------+----------+

1 row in set (0.00 sec)

1.4.2、对表进行检查MariaDB [test]> CHECK TABLE notes,mytable;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.notes | check | status | OK |

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

2 rows in set (0.02 sec)

1.4.3、检查自最后一次检查以来改动过的表MariaDB [test]> CHECK TABLE mytable CHANGED;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

1.4.4、执行最彻底的检查MariaDB [test]> CHECK TABLE mytable EXTENDED;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

1.4.5、只检查未正常关闭的表MariaDB [test]> CHECK TABLE mytable FAST;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

1.4.6、检查所有被删除的链接并进行键检验MariaDB [test]> CHECK TABLE mytable MEDIUM;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

1.4.7、进行快速扫描MariaDB [test]> CHECK TABLE mytable QUICK;

+--------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------+-------+----------+----------+

| test.mytable | check | status | OK |

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

2.优化

2.1、优化的方向

2.1.1、在数据库级别进行优化表格的结构是否正确？特别是，这些列是否具有正确的数据类型，并且每个表是否都具有适用于工作类型的列？例如，执行频繁更新的应用程序通常具有许多表而具有很少的列，而分析大量数据的应用程序通常具有较少的表而具有很多列。是否安装了正确的索引以提高查询效率？是否为每个表使用了适当的存储引擎，并利用了所使用的每个存储引擎的优势和功能？每个表都使用适当的行格式吗？该选择还取决于表使用的存储引擎。应用程序是否使用适当的锁定策略？例如，通过在可能的情况下允许共享访问，以便数据库操作可以同时运行，并在适当的时候请求独占访问，以使关键操作获得最高优先级。用于缓存的 所有内存区域大小是否正确？也就是说，足够大以容纳经常访问的数据，但又不能太大以至于它们会使物理内存过载并导致分页。要配置的主要内存区域是InnoDB缓冲池和MyISAM键高速缓存。

2.1.2、在硬件级别进行优化随着数据库变得越来越繁忙，任何数据库应用程序最终都会达到硬件极限。DBA必须评估是否有可能调整应用程序或重新配置服务器以避免这些 瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源：磁盘搜索磁盘读写CPU内存。当CPU需要的数据超出CPU缓存的容量时，主内存将成为瓶颈。

2.2、优化SQL语句优化SELECT语句优化子查询，视图引用和公用表表达式优化INFORMATION_SCHEMA查询优化性能架构查询优化数据更改语句优化数据库权限

2.3、优化索引主键优化SPATIAL索引优化外键优化列索引多列索引验证索引使用情况

2.4、优化数据库结构优化数据大小优化MySQL数据类型优化多表MySQL中内部临时表的使用数据库和表数限制表格大小限制表格列数和行大小的限制

2.5、优化InnoDB优化InnoDB表的存储布局优化InnoDB事务管理优化InnoDB只读事务优化InnoDB重做日志InnoDB表的批量数据加载优化InnoDB查询优化InnoDB DDL操作优化InnoDB磁盘I/O优化InnoDB配置变量为具有多个表的系统优化InnoDB

2.6、优化MyISAM优化MyISAM查询MyISAM表的批量数据加载优化REPAIR TABLE语句

2.7、缓冲和缓存InnoDB缓冲池优化MyISAM密钥缓存缓存准备好的语句和存储的程序

2.8、优化锁定操作内部锁定方法表锁定问题并发插入元数据锁定外部锁

2.9、优化MySQL服务器优化磁盘I/O使用符号链接优化内存使用优化网络使用资源组