目录
1、mysql查看数据大小
怎样查询总数据量
SELECT table_schema AS `Database`, CONCAT(ROUND(SUM((data_length + index_length) / (1024 * 1024)), 2), ' M') AS `Total Size` FROM information_schema.TABLES GROUP BY table_schema;
怎样查询某个库中表的数据量
SELECT table_schema AS `Database`, table_name AS `Table`, CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024), 2), ' MB') AS `Size` FROM information_schema.TABLES WHERE table_schema = 'your_database_name';
2、mysql配置优化
内存配置优化： innodb_buffer_pool_size：将此参数设置为适当的值，以确保InnoDB存储引擎能够缓存常用的数据和索引。建议分配给innodb_buffer_pool_size的内存大小为总内存的70-80%。 key_buffer_size：对于使用MyISAM存储引擎的表，将此参数设置为合适的值，以便缓存索引。建议分配给key_buffer_size的内存大小为总内存的10-25%。 并发连接优化： max_connections：根据数据库的负载情况和可用的系统资源，适当调整最大连接数。确保设置的值能够满足并发连接需求，但不会过度消耗系统资源。 查询缓存优化： query_cache_type：将此参数设置为1以启用查询缓存。但请注意，查询缓存在高并发环境下可能带来争用和性能问题，因此在特定情况下可能需要禁用查询缓存。 query_cache_size：根据查询缓存的使用情况和可用内存，适当设置查询缓存的大小。建议将查询缓存大小限制在128MB至512MB之间。 日志优化： log_slow_queries：启用慢查询日志以记录执行时间超过设定阈值的查询语句。根据应用的需要和性能要求，设置适当的阈值。 log_queries_not_using_indexes：启用此参数可以记录未使用索引的查询语句，以帮助优化查询性能。 log_error：配置错误日志路径，用于记录MySQL的错误和警告信息。 临时表优化： tmp_table_size：设置临时表的最大内存大小。如果常见的查询需要大量使用临时表，可以适当增加此参数的值，以减少磁盘临时表的使用。 max_heap_table_size：设置基于内存的临时表的最大大小。如果使用了大型临时表，可以适当调整此参数的值。 InnoDB存储引擎优化： innodb_file_per_table：启用此选项可以使每个InnoDB表使用独立的表空间文件，有助于优化磁盘空间的使用和维护。 innodb_flush_log_at_trx_commit：根据应用的需要和数据安全性要求，调整此参数以控制事务日志的刷新频率。 硬件和基础设施优化： 使用更快的存储设备：考虑使用SSD或NVMe等更快的存储设备来提高磁盘I/O性能。 分布式架构：对于高负载和大规模应用，可以考虑使用MySQL主从复制、MySQL集群或MySQL分片等分布式架构，以提高可扩展性和性能。
3、mysql压力测试
MySQL提供了一个官方的基准测试工具称为MySQL Benchmark。
# 自动生成sql语句测试 mysqlslap -uroot -pwangxia@123 --auto-generate-sql # --only-print 来打印实际的测试过程（不会实际执行） # 解析 Average number of seconds to run all queries：运行所有语句的平均秒数 Minimum number of seconds to run all queries：运行所有语句的最小秒数 Maximum number of seconds to run all queries：运行所有语句的最大秒数 Number of clients running queries：客户端数量 Average number of queries per client：每个客户端运行查询的平均数 --concurrency=100：指定同时有 100 个客户端连接 --number-of-queries=1000：指定总的测试查询次数（并发客户端数 * 每个客户端的查询次数），这样本样例平均每个客户端查询 10 次 # 指定并发连接的客户端和测试查询次数 mysqlslap -uroot -pwangxia@123 --concurrency=100 --number-of-queries=10000 --auto-generate-sql --iterations 参数可以设置迭代执行的次数，即重复的次数（相同的测试进行 N 次，求一个平均值）。该次数指的是整个步骤的重复次数，包括准备数据、测试 load、清理。 # 添加迭代次数配置 mysqlslap -uroot -pwangxia@123 --iterations=3 --concurrency=100 --number-of-queries=10000 --auto-generate-sql --number-int-cols=5：指定生成 5 个 int 类型的列 --number-char-cols=20：指定生成 20 个 char 类型的列 # 指定列类型 mysqlslap -uroot -pwangxia@123 --iterations=3 --concurrency=100 --number-of-queries=10000 --auto-generate-sql --number-int-cols=5 --number-char-cols=20 --create-schema=test：指定的测试库是 test（测试完毕不会自动删除） --query="SELECT * FROM t1;"：使用自定义的测试语句 # 指定库 mysqlslap -uroot -pwangxia@123 --concurrency=3 --number-of-queries=100 --create-schema=test --query="SELECT * FROM t1" # 生成数据 mysqlslap -uroot -pwangxia@123 --concurrency=3 --number-of-queries=1000000 --create-schema=test --query="INSERT INTO t1(name,age) VALUES (uuid(), RAND() * 100)"
4、mysql主从复制原理
MySQL的主从复制是一种常用的数据复制和数据同步技术，它允许将一个MySQL数据库服务器（主服务器）上的数据复制到其他一个或多个MySQL服务器（从服务器）。主从复制提供了数据冗余、读写分离、数据备份等重要功能。下面是MySQL主从复制的工作原理：
主服务器（Master）：主服务器是数据源，它负责处理客户端的读写请求，并记录所有的数据变更操作（如插入、更新、删除）到二进制日志（Binary Log）中。
从服务器（Slave）：从服务器是主服务器的复制目标，它从主服务器获取二进制日志文件，并将这些日志文件中的数据变更操作依次应用到自己的数据库中，从而保持与主服务器的数据一致性。
复制过程：MySQL主从复制过程分为三个关键步骤： a. 同步初始化（Sync Initialization）：在初始阶段，从服务器与主服务器建立连接，并请求获取主服务器当前的数据快照（Snapshot）。主服务器将快照的数据以二进制日志的形式发送给从服务器，从服务器将这些数据应用到自己的数据库中。 b. 主服务器二进制日志传输：一旦同步初始化完成，主服务器将持续记录数据变更操作到二进制日志中，并将这些日志文件以连续的方式传输给从服务器。主服务器将二进制日志文件发送给从服务器的过程中，使用的是基于TCP/IP的二进制日志传输协议。 c. 从服务器应用日志：从服务器接收到主服务器的二进制日志文件后，将这些日志文件中的数据变更操作逐条应用到自己的数据库中，保持与主服务器的数据一致性。从服务器会在本地记录已经应用的二进制日志位置，以便在后续的复制过程中从正确的位置继续应用数据变更操作。
复制延迟和主从同步：由于网络延迟、主服务器负载等原因，从服务器的数据更新可能会有一定的延迟，即复制延迟。MySQL提供了主从同步机制，确保从服务器尽可能与主服务器保持数据的一致性。通过主从同步，从服务器会定期向主服务器发送心跳信号，主服务器会记录从服务器的复制位置，并确保从服务器在断线重连后可以继续从正确的位置进行复制。
高可用性和读写分离：通过配置多个从服务器，可以实现MySQL的高可用性和读写分离。当主服务器故障时，可以将其中一个从服务器提升为新的主服务器，确保系统的可用性。同时，通过将读操作分发到从服务器，可以减轻主服务器的负载，提高系统的性能和吞吐量。
总结起来，MySQL的主从复制通过将主服务器上的数据变更操作记录到二进制日志中，并将这些日志文件传输给从服务器，实现了数据的复制和同步。主从复制提供了数据冗余、读写分离、高可用性等重要功能，是MySQL中常用的数据复制和数据同步技术。
5、mysql主从延迟如何解决
查看MySQL主从复制的延迟情况，可以采取以下方法：
使用命令查看延迟：在从服务器上使用MySQL命令
SHOW SLAVE STATUS\G可以查看主从复制的状态信息。在输出结果中，可以查看到 Seconds_Behind_Master 字段，该字段表示从服务器当前与主服务器之间的复制延迟时间（以秒为单位）。如果该值为0，则表示没有延迟；如果该值大于0，则表示存在延迟。使用工具监控延迟：可以使用一些第三方的MySQL监控工具，如Percona Toolkit、pt-heartbeat等，来监控主从复制的延迟情况。这些工具可以提供更详细的延迟信息和统计数据，并支持设置告警机制，以便及时发现和解决延迟问题。
要解决MySQL主从复制的延迟问题，可以考虑以下几个方面：
网络优化：延迟问题可能与网络带宽和延迟有关。确保主从服务器之间的网络连接稳定，并优化网络带宽，可以减少复制延迟。
优化复制线程：MySQL主从复制中有一个I/O线程负责读取主服务器的二进制日志，另一个SQL线程负责应用日志到从服务器。优化这些复制线程的性能参数和配置，如增加线程数、调整缓冲区大小等，可以提高复制的效率。
减少主服务器的负载：如果主服务器的负载较高，可能会导致复制延迟。考虑优化主服务器的性能和负载，如调整查询优化、增加硬件资源等，以减轻主服务器的负载，提高复制性能。
优化从服务器的配置：从服务器的性能和配置也会影响复制延迟。确保从服务器具备足够的资源，如CPU、内存、磁盘等，并根据实际情况进行适当的配置优化，以提高复制性能。
监控和调优：定期监控主从复制的延迟情况，并分析延迟发生的原因。根据监控结果进行调优，如调整复制参数、优化查询语句、增加硬件资源等，以缩小复制延迟。
6、主从切换操作
选取新的主服务器：在进行主从切换之前，需要选择一个新的主服务器。这可以是当前的从服务器或者是另一个备用服务器。确保选取的服务器具备足够的资源和性能来承担新的主服务器的角色。
停止主服务器的写操作：在进行主从切换之前，需要停止当前的主服务器上的写操作，以确保在切换过程中不会有新的数据变更操作。可以使用MySQL命令
FLUSH TABLES WITH READ LOCK来锁定所有表，阻止写操作。确认主从复制的同步状态：在主服务器停止写操作之后，需要确认主从复制的同步状态。使用MySQL命令
SHOW SLAVE STATUS\G检查从服务器的 Seconds_Behind_Master 字段是否为0，确认数据已经与主服务器同步。修改从服务器的配置：将之前的从服务器配置为新的主服务器。需要修改从服务器的配置文件（如my.cnf）中的
server-id参数为一个新的唯一值，并确保其他相关的复制参数配置正确。启动新的主服务器：启动新的主服务器，并确保数据库服务正常运行。在确认新的主服务器正常运行后，可以继续进行后续的步骤。
配置其他从服务器：对于之前的主服务器，现在需要将其配置为从服务器，并指向新的主服务器。需要修改其配置文件中的
server-id参数和主服务器的连接信息。开放写操作：在新的主服务器和其他从服务器都正常配置后，可以解除之前停止的写操作。在新的主服务器上使用MySQL命令
UNLOCK TABLES解锁所有表，允许写操作继续。检查主从复制状态：在主从切换完成后，使用MySQL命令
SHOW SLAVE STATUS\G再次检查主从复制的状态，确保复制正常运行并且没有延迟。
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
