Total Pageviews

Thursday, 17 October 2024

awesome-mysql

 awesome-mysql involves mysql index, performance optimization and general problem solving(awesome-mysql涉及mysql的索引,性能优化以及常规的问题解决) 

from https://github.com/leo985/awesome-mysql

------------

A curated list of awesome MySQL software, libraries, tools and resources  

awesome-mysql

A curated list of awesome MySQL free and opensource software, libraries and resources. Awesome

This list accepts and encourages pull requests. See CONTRIBUTING

Contents

Analysis

Performance, structure & data analysis tools

  • Anemometer - Box SQL slow query monitor.
  • innodb-ruby - A parser for InnoDB file formats, in Ruby.
  • innotop - a 'top' clone for MySQL with many features and flexibility.
  • MySQL Explain Analyzer - A web-based analyzer of EXPLAIN FORMAT=JSON output, providing comments, scalability analysis and permalinks for saved samples.
  • mysql-statsd - A Python daemon to collect information from MySQL and send it via StatsD to Graphite.
  • MySQLTuner-perl - A script that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability.
  • Prometheus/mysqld_exporter - Time series database for real-time monitoring and alerting.
  • pstop - a top-like program for MySQL, collecting, aggregating and displaying information from performance_schema.
  • Wireshark - a protocol analyzer that can decode the MySQL protocol.
  • Dolphie - a modern terminal tool for real-time analytics into MySQL/MariaDB & ProxySQL

Backup

Backup/restore/recovery tools

  • Dumpling - Logical, parallel backup/dumper tool for MySQL/TiDB written in GoLang - support csv format output and integrated as library
  • MyDumper - Logical, parallel backup/dumper tool for MySQL
  • Percona Xtrabackup - an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup.

Benchmarking

Tools to stress your servers

  • go-tpc - A golang port of TPCC and TPCH benchmark for MySQL.
  • iibench-mysql - Java based version of the Index Insertion Benchmark for MySQL/Percona/MariaDB.
  • Sysbench - a modular, cross-platform and multi-threaded benchmark tool.
  • TPCC-MySQL (archived) - A port of the popular TPCC benchmark for MySQL.

Binlog-Replication

  • DM - A High-Availability data migration platform which supports migrating data from MySQL/MariaDB to TiDB and merging shard tables
  • Kingbus - A distributed MySQL binlog storage system built on Raft
  • mysql-ripple (archived) - Ripple, a server that can serve as a middleman in MySQL replication

ChatOps

Scripts integrated into chat rooms

Configuration

MySQL sample configuration and advisors

Connectors

MySQL connectors for various programming languages

Deployment

MySQL deployment tools

  • dbdeployer (archived) - A tool that installs one or more MySQL servers within seconds, easily, securely, and with full control.
  • MariaDB4j - A Java launcher to run MariaDB without installation or external dependencies.
  • MySQL Docker - Official Docker images.

Development

Tools to support MySQL-related development

  • Flywaydb - Database migrations; Evolve your database schema easily and reliably across all your instances
  • Liquibase - Source control for your database
  • Shift - An application that helps you run schema migrations on MySQL databases
  • Skeema - Declarative pure-SQL schema management system for MySQL and MariaDB, with support for sharding and external online schema change tools
  • SQLE - SQLE is a SQL audit platform for DBA or developer
  • Test database - A sample MySQL database with an integrated test suite, used to test applications and servers

GUI

GUI frontends & applications

  • Adminer - Database management in a single PHP file.
  • DBeaver - A cross-platform SQL and NoSQL database client.
  • HeidiSQL - MySQL GUI frontend for Windows.
  • ILLA Cloud - Low-code internal tool builder integrated with Mysql, can be used as GUI for Mysql.
  • mycli - A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.
  • MySQL Shell - Advanced client and code editor for MySQL that supports development and administration for the MySQL Server and MySQL InnoDB cluster (AdminAPI) with an interactive JavaScript, Python, or SQL interface.
  • MySQL Workbench - provides DBAs and developers an integrated tools environment for database design & modeling; SQL devleopment; database administration.
  • Ocelot GUI - GUI client for MySQL or MariaDB, including debugger.
  • OmniDB: Web tool for database management
  • Percona Monitoring and Management - An open-source platform for managing and monitoring MySQL performance.
  • phpMyAdmin - a free software tool written in PHP, intended to handle the administration of MySQL over the Web.
  • pspg - provides a pager with enhanced visualization and navigation for tabular data. Originally implemented for PostgreSQL, but also supports MySQL.
  • Sequel Ace - a Mac database management application for working with MySQL databases.
  • SQLyog Community edition - SQLyog Community edition. For Windows, works fine under wine in Mac and Linux
  • WebDB – Open Source and Efficient Database IDE. Featuring Easy server connection, Modern ERD, Intelligent data generator, AI assistant, NoSQL structure manager, Time machine and Powerful query editor

HA

High availability solutions

  • Galera Cluster - a true Multimaster Cluster based on synchronous replication.
  • mha4mysql-node and mha4mysql-manager (both unmaintained) - Master High Availability Manager and tools for MySQL.
  • Orchestrator - MySQL replication topology management and High Availability solution.
  • Percona Replication Manager - Asynchronous MySQL replication manager agent for Pacemaker. Supports file and GTID based replication, geo-distributed clusters using booth.
  • replication-manager - a high availability solution to manage MariaDB 10.x and MySQL & Percona Server 5.7 GTID replication topologies.

Proxy

Proxies to MySQL

  • MySQL Proxy (deprecated) - A simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication.
  • MySQL Router - MySQL Router is part of InnoDB cluster, and is a lightweight middleware that provides transparent routing between your application and back-end MySQL Servers.
  • ProxySQL - High performance proxy for MySQL.

Replication

Replication related software

  • data-diff - Command-line tool and Python library to efficiently diff rows across two different databases.

Schema

Add-on schemas

  • common_schema - DBA's framework for MySQL, providing a function library, views library and QueryScript interpreter.
  • sys - A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

Security

Tools that prevents leaking of sensitive data from database (encryption, masking and tokenization, honey-pots, etc)

  • Acra - SQL database protection suite: strong selective encryption, SQL injections prevention, intrusion detection system.

Server

MySQL server flavors

  • MariaDB - Community developed fork of MySQL server.
  • MySQL Server & MySQL Cluster - Official Oracle's MySQL server & MySQL Cluster distribution.
  • Percona Server - An enhanced, drop-in MySQL replacement.
  • TiDB - A distributed HTAP database compatible with the MySQL protocol.

Sharding

Sharding solutions/frameworks

  • Jetpants - An automation suite for managing large range sharding clusters, by Tumblr.
  • Vitess - vitess provides servers and tools which facilitate scaling of MySQL databases for large scale web services.

Toolkits

Toolkits, general purpose scripts

  • gh-ost - GitHub's online schema migration for MySQL.
  • go-mysql - A pure go library to handle MySQL network protocol and replication.
  • MySQL Utilities (deprecated) - a collection of command-line utilities, written in Python, that are used for maintaining and administering MySQL servers, either individually, or within Replication hierarchies.
  • Percona Toolkit - a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
  • UnDROP - a tool to recover data from dropped or corrupted InnoDB tables.

Resources

At this stage "resources" will not include websites, blogs, slides, presentation videos, etc. in fear of list size

e-books

e-books as well as relevant materials on and around MySQL

  • Database Systems Lecture Notes - lecture notes on Database Systems (available in pdf, html, odt and markdown) including a Chapter on SQL that covers basic set-up, exercises and problems.
  • SQL-exercise - contains several SQL exercises, including the schema description figure, SQL code to build schema, questions and solutions in SQL. Based on wikibook SQL Exercises.

from https://github.com/shlomi-noach/awesome-mysql

-----------

A curated list of awesome links related to MySQL / MariaDB / Percona performance tuning  .

Awesome-MySQL-performance

🔥 A curated list of awesome links related to MySQL / MariaDB / Percona configuration tuning for improved performance.

Common MySQL Tuning articles

MySQL/MariaDB Variables tuning

aria_pagecache_buffer_size

innodb_buffer_pool_chunk_size

innodb_buffer_pool_instances

innodb_buffer_pool_size

innodb_flush_log_at_trx_commit

innodb_flush_method

innodb_log_file_size

innodb_read_io_threads

innodb_write_io_threads

join_buffer_size

key_buffer_size

max_allowed_packet

max_connections

max_heap_table_size

read_rnd_buffer_size

sort_buffer_size

thread_cache_size

thread_pool_size

tmp_table_size

table_open_cache

bulk_insert_buffer_size

Talks

Configuration

  • MySQLTuner-perl - A script that allows you to review a MySQL installation quickly and make recommendations to increase performance and stability.
  • Releem - MySQL Performance Tuning as a Service. Releem helps you to automatically monitor MySQL metrics, and tune MySQL configuration to improve performance and reduce costs of server resources.

from https://github.com/Releem/awesome-mysql-performance

----------

A curated list of awesome MySQL useful queries and commands.  

Awesome MySQL Queries and Commands

A curated list of awesome MySQL useful queries and commands. Inspired by awesome-mysql and awesome-bash-commands.

🏅 Of course, this document needs your help, so consider contributing.

Table of Contents

Commands

Data import

Script file

mysql -h host -P 3306 -u username -p --default_character_set utf8 database_name < mysql_script.sql

Data export

Script file

mysqldump -h localhost -u username -p database_name > ./mysql_script.sql

Or

mysqldump \
    --user=username \
    --host=127.0.0.1 \
    --protocol=tcp \
    --port=3306 -p \
    --default-character-set=utf8 \
    --skip-triggers \
    "database_name" > database_script.sql

GZIP script file

mysqldump -h localhost -u username -p database_name | gzip -c > tables.sql.gz

Or

mysqldump \
    --user=username \
    --host=127.0.0.1 \
    --protocol=tcp \
    --port=3306 -p \
    --default-character-set=utf8 \
    --skip-triggers \
    "database_name" | gzip -c > tables.sql.gz

Use --single-transaction if you got an mysqldump error (because you lack privileges to lock the tables)

mysqldump -h localhost -u username -p database_name --single-transaction | gzip -c > tables.sql.gz

Script file with tables only

mysqldump -h localhost -u username -p database_name table_name1 table_name2 > mydb_tables.sql

Or

mysqldump \
    --user=username \
    --host=127.0.0.1 \
    --protocol=tcp \
    --port=3306 -p \
    --default-character-set=utf8 \
    --skip-triggers \
    "database_name" "table_name1" "table_name2" > mydb_tables.sql

Queries

Users and privileges

Create a root user equivalent for backward compatibility

CREATE USER `my_root_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_root_pwd';

GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, 
    Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, 
    Index, Insert, Lock Tables, Process, References, Reload, Replication Client, 
    Replication Slave, Select, Show Databases, Show View, Shutdown, Trigger, Update,
    Super, Create Tablespace
    ON *.* TO `my_root_user`@`%`;

Note: The above query creates a user using Native Pluggable Authentication. It can useful for backward compatibility MySQL clients. Due Caching SHA-2 Pluggable Authentication is the default authentication plugin on MySQL 8.

Create a user with specific database privileges

CREATE USER `my_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE
      ON `my_database`.* TO `my_user`@`%` WITH GRANT OPTION;

Note: User above is an example-purpose only.

Modify specific user privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'root2'@'%';

Change an user password

ALTER USER 'username'@'localhost' IDENTIFIED BY 'my_new_password';

Select

Finding duplicated values

SELECT code, COUNT(code) duplicates FROM client GROUP BY code HAVING duplicates > 1;

Select one day ago records

SELECT * 
FROM users
WHERE
  `registered` >= CONCAT(SUBDATE(CURDATE(), 1), ' 00:00:00') AND 
  `registered` < CONCAT(CURDATE(), ' 00:00:00')

Utilities

Clean all tables of one existing database

Those queries create a database if doesn't exist (optional) and then removes all tables of one specified database. No root privileges are required, only make sure that the user which executes those queries has enough privileges for that particular database.

Warning: This process cleans up the database removing all existing tables permanently. So make sure to do all necessary tests in a development environment first.

-- -----------------------------------------------------
-- `my_database` clean up process
-- -----------------------------------------------------

-- -----------------------------------------------------
-- 1. Create a new `my_database` database if doesn't exits 
-- This is optional but requires extra privileges
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `my_database` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- -----------------------------------------------------
-- 2. Remove all tables of `my_database` database
-- -----------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;

USE `my_database`;

SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());

SELECT IFNULL(@tables, 'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

Show databases size in MB or GB

Databases size in GBs

SELECT
    TABLE_SCHEMA "DB_NAME",
    SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2)) AS "GB"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;

Databases size in MBs

SELECT
    TABLE_SCHEMA "DB_NAME",
    SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2)) AS "MB"
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;

Show status and open database connections

SHOW GLOBAL STATUS LIKE "%conn%";
SHOW GLOBAL STATUS LIKE '%onn%';
SHOW GLOBAL STATUS LIKE '%Connection_errors%';

Show performance schema information per query digest

SELECT
    SCHEMA_NAME AS "Database",
    DIGEST_TEXT AS "Query diggest",
    COUNT_STAR AS "Executed times",
    AVG_TIMER_WAIT AS "Executed average (picoseconds)",
    ROUND((AVG_TIMER_WAIT / 1000 / 1000 / 1000 / 1000), 2) AS "Executed average (seconds)",
    QUERY_SAMPLE_TEXT AS "Query sample",
    QUERY_SAMPLE_SEEN AS "Query sample seen"
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;

Show a summary of current and recent statement events information (picoseconds)

SELECT * FROM performance_schema.events_statements_summary_global_by_event_name AS t
ORDER BY t.COUNT_STAR DESC;

Show tables size of current database in GBs

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024/ 1024), 2) TABLE_SIZE_GB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SIZE_GB DESC
LIMIT 300;

Summary of all statements executed on each host, along with their associated latencies

SELECT * FROM sys.host_summary_by_statement_type;

Other Awesome Lists

from https://github.com/joseluisq/awesome-mysql-queries-commands

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)