{"id":"1903bd45-d3c6-4b2f-9da5-1ba323435869","shortId":"9bnCdf","kind":"skill","title":"mysql","tagline":"Use when writing MySQL or MariaDB SQL, editing MySQL-flavored .sql files, using mysql CLI, mysqldump, connection strings, InnoDB settings, replication, stored procedures, JSON, or query tuning.","description":"# MySQL / MariaDB\n\nMySQL is the world's most popular open-source relational database, powering applications from small web apps to large-scale internet services. This skill covers MySQL 8.0+ (and MariaDB where noted).\n\n## Quick Reference\n\n### Connection Patterns\n\n```python\n# Python (PyMySQL) -- always parameterized, always utf8mb4\nimport pymysql\n\nconn = pymysql.connect(\n    host=\"localhost\",\n    user=\"app_user\",\n    password=\"secret\",\n    database=\"mydb\",\n    charset=\"utf8mb4\",\n    cursorclass=pymysql.cursors.DictCursor,\n)\n\nwith conn:\n    with conn.cursor() as cursor:\n        cursor.execute(\"SELECT * FROM users WHERE id = %s\", (42,))\n        user = cursor.fetchone()\n    conn.commit()\n```\n\n### Key SQL Patterns\n\n```sql\n-- CTE (8.0+)\nWITH active_users AS (\n    SELECT id, name FROM users WHERE status = 'active'\n)\nSELECT au.name, COUNT(o.id) AS order_count\n  FROM active_users au\n  JOIN orders o ON o.user_id = au.id\n GROUP BY au.name;\n\n-- Window function\nSELECT customer_id, order_date, total,\n       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn\n  FROM orders;\n\n-- Upsert\nINSERT INTO counters (key_name, value)\nVALUES ('page_views', 1)\nON DUPLICATE KEY UPDATE value = value + VALUES(value);\n```\n\n### InnoDB Essentials\n\n- **Clustered index** -- the primary key IS the table; rows stored in PK order.\n- **Secondary index lookup** -- two B+tree traversals (secondary -> PK -> row).\n- **Sequential PKs** (AUTO_INCREMENT) are fast; random PKs (UUIDs) cause page splits.\n- **UUID workaround** -- use `UUID_TO_BIN(UUID(), 1)` for ordered UUIDs in MySQL 8.0+.\n- **Row format** -- DYNAMIC (default in 8.0+) is the best general-purpose choice.\n- **Buffer pool** -- size to ~70-80% of available RAM on dedicated servers.\n\n<workflow>\n\n## Workflow\n\n### Step 1: Schema Design\n\nChoose InnoDB (always). Use AUTO_INCREMENT integer PKs unless UUIDs are required (then use ordered UUID v7). Set `CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci` at the database and table level.\n\n### Step 2: Write Queries\n\nUse parameterized queries in application code -- never string interpolation. Use CTEs for readability. Use window functions instead of self-joins for ranking/running totals.\n\n### Step 3: Index Strategy\n\nCreate indexes to support WHERE, JOIN, and ORDER BY clauses. Use composite indexes following the leftmost-prefix rule. Check coverage with `EXPLAIN`.\n\n### Step 4: Performance Tuning\n\nRun `EXPLAIN ANALYZE` on slow queries. Check the slow query log (`long_query_time = 1`). Tune buffer pool size, redo log size, and `innodb_flush_log_at_trx_commit` for the workload.\n\n### Step 5: Validate\n\nConfirm query plans use indexes (no unexpected full table scans). Verify `utf8mb4` encoding. Test with realistic data volumes.\n\n</workflow>\n\n<guardrails>\n\n## Guardrails\n\n- **Always use parameterized queries** -- never concatenate user input into SQL strings. Use `%s` placeholders (Python) or `?` (Node/Java).\n- **InnoDB by default** -- never use MyISAM for new tables. InnoDB provides transactions, row-level locking, and crash recovery.\n- **utf8mb4 encoding** -- always specify `charset=utf8mb4` in connections and `CHARACTER SET utf8mb4` in DDL. Plain `utf8` is a 3-byte subset that cannot store emoji or some CJK characters.\n- **Avoid SELECT \\*** -- name columns explicitly to prevent breakage when schema changes and to enable covering indexes.\n- **AUTO_INCREMENT for PKs** -- avoids clustered index fragmentation. If UUIDs are required, use `UUID_TO_BIN(UUID(), 1)` for ordered storage.\n- **Test with EXPLAIN before deploying** -- verify index usage and join strategies on production-like data.\n\n</guardrails>\n\n<validation>\n\n### Validation Checkpoint\n\nBefore delivering MySQL code, verify:\n\n- [ ] All queries use parameterized placeholders (no string interpolation)\n- [ ] Tables use InnoDB engine\n- [ ] Character set is utf8mb4 (not utf8 or latin1)\n- [ ] Primary keys are defined (AUTO_INCREMENT or ordered UUID)\n- [ ] Indexes exist for WHERE/JOIN/ORDER BY columns\n- [ ] EXPLAIN output shows index usage for critical queries\n\n</validation>\n\n<example>\n\n## Example\n\n**Task:** Parameterized query with index creation for an orders lookup.\n\n```sql\n-- Create table with proper encoding and engine\nCREATE TABLE orders (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n    user_id BIGINT NOT NULL,\n    status ENUM('pending', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',\n    total DECIMAL(10, 2) NOT NULL,\n    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    INDEX idx_orders_user_status (user_id, status),\n    INDEX idx_orders_created (created_at)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n-- Verify index usage\nEXPLAIN SELECT id, total, created_at\n  FROM orders\n WHERE user_id = 42\n   AND status = 'shipped'\n ORDER BY created_at DESC\n LIMIT 20;\n```\n\n```python\n# Application code -- parameterized query\nasync def get_user_orders(conn, user_id: int, status: str) -> list[dict]:\n    async with conn.cursor() as cursor:\n        await cursor.execute(\n            \"SELECT id, total, created_at FROM orders \"\n            \"WHERE user_id = %s AND status = %s \"\n            \"ORDER BY created_at DESC LIMIT 20\",\n            (user_id, status),\n        )\n        return await cursor.fetchall()\n```\n\n</example>\n\n---\n\n## References Index\n\nFor detailed guides and code examples, refer to the following documents in `references/`:\n\n- **[SQL Patterns](references/sql_patterns.md)** -- Window functions, CTEs, recursive queries, JSON_TABLE, upserts, generated columns.\n- **[Stored Procedures & Functions](references/stored_procedures.md)** -- CREATE PROCEDURE/FUNCTION, control flow, cursors, error handling, triggers.\n- **[Performance Tuning](references/performance.md)** -- EXPLAIN/EXPLAIN ANALYZE, index strategies, slow query log, buffer pool tuning.\n- **[Connection Patterns](references/connections.md)** -- Python, Node.js, Java, Go connectors; connection pooling; SSL/TLS.\n- **[JSON in MySQL](references/json.md)** -- JSON data type, extraction operators, JSON_TABLE, multi-valued indexes.\n- **[InnoDB Internals](references/innodb.md)** -- Clustered index, row formats, buffer pool, redo log, MVCC, deadlock detection.\n- **[Security](references/security.md)** -- User/role management, authentication plugins, SSL/TLS, encryption at rest.\n- **[Administration](references/admin.md)** -- Backups (mysqldump, XtraBackup), binary logs, PITR, table maintenance, upgrades.\n- **[Replication & HA](references/replication.md)** -- Binary log replication, GTID, Group Replication, InnoDB Cluster.\n- **[MySQL CLI & Tools](references/mysql_cli.md)** -- mysql client, mycli, MySQL Shell, Percona Toolkit, gh-ost.\n\n---\n\n## Official References\n\n- MySQL 8.0 Reference Manual: <https://dev.mysql.com/doc/refman/8.0/en/>\n- MySQL 8.4 Reference Manual: <https://dev.mysql.com/doc/refman/8.4/en/>\n- MariaDB Knowledge Base: <https://mariadb.com/kb/en/>\n- MySQL Shell User Guide: <https://dev.mysql.com/doc/mysql-shell/8.0/en/>\n- Percona Toolkit: <https://docs.percona.com/percona-toolkit/>\n\n## Shared Styleguide Baseline\n\n- Use shared styleguides for generic language/framework rules to reduce duplication in this skill.\n- [General Principles](https://github.com/cofin/flow/blob/main/templates/styleguides/general.md)\n- [MySQL/MariaDB](https://github.com/cofin/flow/blob/main/templates/styleguides/databases/mysql_mariadb.md)\n- Keep this skill focused on tool-specific workflows, edge cases, and integration details.","tags":["mysql","flow","cofin","agent-skills","ai-agents","beads","claude-code","codex","cursor","developer-tools","gemini-cli","opencode"],"capabilities":["skill","source-cofin","skill-mysql","topic-agent-skills","topic-ai-agents","topic-beads","topic-claude-code","topic-codex","topic-cursor","topic-developer-tools","topic-gemini-cli","topic-opencode","topic-plugin","topic-slash-commands","topic-spec-driven-development"],"categories":["flow"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/cofin/flow/mysql","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add cofin/flow","source_repo":"https://github.com/cofin/flow","install_from":"skills.sh"}},"qualityScore":"0.455","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 11 github stars · SKILL.md body (7,452 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:07:37.976Z","embedding":null,"createdAt":"2026-04-23T13:04:00.159Z","updatedAt":"2026-05-18T19:07:37.976Z","lastSeenAt":"2026-05-18T19:07:37.976Z","tsv":"'-80':261 '/cofin/flow/blob/main/templates/styleguides/databases/mysql_mariadb.md)':939 '/cofin/flow/blob/main/templates/styleguides/general.md)':935 '/doc/mysql-shell/8.0/en/':909 '/doc/refman/8.0/en/':889 '/doc/refman/8.4/en/':896 '/kb/en/':902 '/percona-toolkit/':914 '1':183,236,270,377,515 '10':630 '2':305,631 '20':689,735 '3':333,471 '4':360 '42':106,679 '5':396 '70':260 '8.0':60,115,242,248,884 '8.4':891 'activ':117,127,136 'administr':845 'alway':72,74,275,417,455 'analyz':365,786 'app':49,83 'applic':45,312,691 'async':695,708 'au':138 'au.id':145 'au.name':129,148 'authent':839 'auto':219,277,498,566,609 'avail':263 'avoid':482,502 'await':713,740 'b':211 'backup':847 'base':899 'baselin':917 'best':251 'bigint':608,615 'bin':234,513 'binari':850,859 'breakag':489 'buffer':256,379,792,828 'byte':472 'cancel':623 'cannot':475 'case':950 'caus':226 'chang':492 'charact':291,462,481,554 'charset':89,457,659 'check':355,369 'checkpoint':536 'choic':255 'choos':273 'ci':297,664 'cjk':480 'claus':345 'cli':17,868 'client':872 'cluster':194,503,824,866 'code':313,540,692,748 'collat':294,661 'column':485,576,769 'commit':391 'composit':347 'concaten':422 'confirm':398 'conn':78,94,700 'conn.commit':109 'conn.cursor':96,710 'connect':19,67,460,795,803 'connector':802 'control':776 'count':130,134 'counter':176 'cover':58,496 'coverag':356 'crash':451 'creat':336,597,604,634,653,654,672,685,718,731,774 'creation':591 'critic':583 'cte':114 'ctes':318,762 'current':640 'cursor':98,712,778 'cursor.execute':99,714 'cursor.fetchall':741 'cursor.fetchone':108 'cursorclass':91 'custom':152,162 'data':414,534,811 'databas':43,87,300 'date':155,167 'ddl':466 'deadlock':833 'decim':629 'dedic':266 'def':696 'default':246,436,626,639,658 'defin':565 'deliv':538,622 'deploy':523 'desc':168,687,733 'design':272 'detail':745,953 'detect':834 'dev.mysql.com':888,895,908 'dev.mysql.com/doc/mysql-shell/8.0/en/':907 'dev.mysql.com/doc/refman/8.0/en/':887 'dev.mysql.com/doc/refman/8.4/en/':894 'dict':707 'docs.percona.com':913 'docs.percona.com/percona-toolkit/':912 'document':754 'duplic':185,927 'dynam':245 'edg':949 'edit':9 'emoji':477 'enabl':495 'encod':410,454,601 'encrypt':842 'engin':553,603,656 'enum':619 'error':779 'essenti':193 'exampl':585,749 'exist':572 'explain':358,364,521,577,668 'explain/explain':785 'explicit':486 'extract':813 'fast':222 'file':14 'flavor':12 'flow':777 'flush':387 'focus':943 'follow':349,753 'format':244,827 'fragment':505 'full':405 'function':150,323,761,772 'general':253,931 'general-purpos':252 'generat':768 'generic':922 'get':697 'gh':879 'gh-ost':878 'github.com':934,938 'github.com/cofin/flow/blob/main/templates/styleguides/databases/mysql_mariadb.md)':937 'github.com/cofin/flow/blob/main/templates/styleguides/general.md)':933 'go':801 'group':146,863 'gtid':862 'guardrail':416 'guid':746,906 'ha':857 'handl':780 'host':80 'id':104,121,144,153,163,607,614,648,670,678,702,716,724,737 'idx':643,651 'import':76 'increment':220,278,499,567,610 'index':195,208,334,337,348,402,497,504,525,571,580,590,642,650,666,743,787,820,825 'innodb':21,192,274,386,434,443,552,657,821,865 'input':424 'insert':174 'instead':324 'int':703 'integ':279 'integr':952 'intern':822 'internet':54 'interpol':316,549 'java':800 'join':139,328,341,528 'json':26,765,806,810,815 'keep':940 'key':110,177,186,198,563,612 'knowledg':898 'language/framework':923 'larg':52 'large-scal':51 'latin1':561 'leftmost':352 'leftmost-prefix':351 'level':303,448 'like':533 'limit':688,734 'list':706 'localhost':81 'lock':449 'log':373,383,388,791,831,851,860 'long':374 'lookup':209,595 'mainten':854 'manag':838 'manual':886,893 'mariadb':7,31,62,897 'mariadb.com':901 'mariadb.com/kb/en/':900 'multi':818 'multi-valu':817 'mvcc':832 'myc':873 'mydb':88 'myisam':439 'mysql':1,5,11,16,30,32,59,241,539,808,867,871,874,883,890,903 'mysql-flavor':10 'mysql/mariadb':936 'mysqldump':18,848 'name':122,178,484 'never':314,421,437 'new':441 'node.js':799 'node/java':433 'note':64 'null':617,625,633,638 'number':158 'o':141 'o.id':131 'o.user':143 'offici':881 'open':40 'open-sourc':39 'oper':814 'order':133,140,154,164,166,172,206,238,287,343,517,569,594,606,644,652,675,683,699,721,729 'ost':880 'output':578 'page':181,227 'parameter':73,309,419,545,587,693 'partit':160 'password':85 'pattern':68,112,758,796 'pend':620,627 'percona':876,910 'perform':361,782 'pitr':852 'pk':205,215 'pks':218,224,280,501 'placehold':430,546 'plain':467 'plan':400 'plugin':840 'pool':257,380,793,804,829 'popular':38 'power':44 'prefix':353 'prevent':488 'primari':197,562,611 'principl':932 'procedur':25,771 'procedure/function':775 'product':532 'production-lik':531 'proper':600 'provid':444 'purpos':254 'pymysql':71,77 'pymysql.connect':79 'pymysql.cursors.dictcursor':92 'python':69,70,431,690,798 'queri':28,307,310,368,372,375,399,420,543,584,588,694,764,790 'quick':65 'ram':264 'random':223 'ranking/running':330 'readabl':320 'realist':413 'recoveri':452 'recurs':763 'redo':382,830 'reduc':926 'refer':66,742,750,756,882,885,892 'references/admin.md':846 'references/connections.md':797 'references/innodb.md':823 'references/json.md':809 'references/mysql_cli.md':870 'references/performance.md':784 'references/replication.md':858 'references/security.md':836 'references/sql_patterns.md':759 'references/stored_procedures.md':773 'relat':42 'replic':23,856,861,864 'requir':284,509 'rest':844 'return':739 'rn':170 'row':157,202,216,243,447,826 'row-level':446 'rule':354,924 'run':363 'scale':53 'scan':407 'schema':271,491 'secondari':207,214 'secret':86 'secur':835 'select':100,120,128,151,483,669,715 'self':327 'self-join':326 'sequenti':217 'server':267 'servic':55 'set':22,290,292,463,555 'share':915,919 'shell':875,904 'ship':621,682 'show':579 'size':258,381,384 'skill':57,930,942 'skill-mysql' 'slow':367,371,789 'small':47 'sourc':41 'source-cofin' 'specif':947 'specifi':456 'split':228 'sql':8,13,111,113,426,596,757 'ssl/tls':805,841 'status':126,618,646,649,681,704,727,738 'step':269,304,332,359,395 'storag':518 'store':24,203,476,770 'str':705 'strategi':335,529,788 'string':20,315,427,548 'styleguid':916,920 'subset':473 'support':339 'tabl':201,302,406,442,550,598,605,766,816,853 'task':586 'test':411,519 'time':376 'timestamp':636,641 'tool':869,946 'tool-specif':945 'toolkit':877,911 'topic-agent-skills' 'topic-ai-agents' 'topic-beads' 'topic-claude-code' 'topic-codex' 'topic-cursor' 'topic-developer-tools' 'topic-gemini-cli' 'topic-opencode' 'topic-plugin' 'topic-slash-commands' 'topic-spec-driven-development' 'total':156,331,628,671,717 'transact':445 'travers':213 'tree':212 'trigger':781 'trx':390 'tune':29,362,378,783,794 'two':210 'type':812 'unexpect':404 'unicod':296,663 'unless':281 'updat':187 'upgrad':855 'upsert':173,767 'usag':526,581,667 'use':2,15,231,276,286,308,317,321,346,401,418,428,438,510,544,551,918 'user':82,84,102,107,118,124,137,423,613,645,647,677,698,701,723,736,905 'user/role':837 'utf8':468,559 'utf8mb4':75,90,293,295,409,453,458,464,557,660,662 'uuid':225,229,232,235,239,282,288,507,511,514,570 'v7':289 'valid':397,535 'valu':179,180,188,189,190,191,819 'verifi':408,524,541,665 'view':182 'volum':415 'web':48 'where/join/order':574 'window':149,322,760 'workaround':230 'workflow':268,948 'workload':394 'world':35 'write':4,306 'xtrabackup':849","prices":[{"id":"41dc5fed-0905-4f05-bf9c-ba2189677853","listingId":"1903bd45-d3c6-4b2f-9da5-1ba323435869","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"cofin","category":"flow","install_from":"skills.sh"},"createdAt":"2026-04-23T13:04:00.159Z"}],"sources":[{"listingId":"1903bd45-d3c6-4b2f-9da5-1ba323435869","source":"github","sourceId":"cofin/flow/mysql","sourceUrl":"https://github.com/cofin/flow/tree/main/skills/mysql","isPrimary":false,"firstSeenAt":"2026-04-23T13:04:00.159Z","lastSeenAt":"2026-05-18T19:07:37.976Z"}],"details":{"listingId":"1903bd45-d3c6-4b2f-9da5-1ba323435869","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"cofin","slug":"mysql","github":{"repo":"cofin/flow","stars":11,"topics":["agent-skills","ai-agents","beads","claude-code","codex","context-driven-development","cursor","developer-tools","gemini-cli","opencode","plugin","slash-commands","spec-driven-development","subagents","tdd","workflow"],"license":"apache-2.0","html_url":"https://github.com/cofin/flow","pushed_at":"2026-04-27T19:07:26Z","description":"Context-Driven Development toolkit for AI agents — spec-first planning, TDD workflow, and Beads integration.","skill_md_sha":"2f6e0f454de20ed76a21ee4ddf5a2d20663878e4","skill_md_path":"skills/mysql/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/cofin/flow/tree/main/skills/mysql"},"layout":"multi","source":"github","category":"flow","frontmatter":{"name":"mysql","description":"Use when writing MySQL or MariaDB SQL, editing MySQL-flavored .sql files, using mysql CLI, mysqldump, connection strings, InnoDB settings, replication, stored procedures, JSON, or query tuning."},"skills_sh_url":"https://skills.sh/cofin/flow/mysql"},"updatedAt":"2026-05-18T19:07:37.976Z"}}