Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

Netkiller MySQL 手札

MySQL, MariaDB, Percona ...

Mr. Neo Chan, 陈景峯(BG7NYT)



中国广东省深圳市望海路半岛城邦三期
518067
+86 13113668890


文档始创于 2010-11-18

电子书最近一次更新于 2023-03-08 22:23:29

版权声明

转载请与作者联系,转载时请务必标明文章原始出处和作者信息及本声明。

http://www.netkiller.cn
http://netkiller.github.io
http://netkiller.sourceforge.net
微信公众号: netkiller
微信:13113668890 请注明“读者”
QQ:13721218 请注明“读者”
QQ群:128659835 请注明“读者”
知乎专栏 | 多维度架构

$Date: 2013-04-10 15:03:49 +0800 (Wed, 10 Apr 2013) $


致读者

Netkiller 系列手札 已经被 Github 收录,并备份保存在北极地下250米深的代码库中,备份会保留1000年。

Preserving open source software for future generations

The world is powered by open source software. It is a hidden cornerstone of modern civilization, and the shared heritage of all humanity.

The GitHub Arctic Code Vault is a data repository preserved in the Arctic World Archive (AWA), a very-long-term archival facility 250 meters deep in the permafrost of an Arctic mountain.

We are collaborating with the Bodleian Library in Oxford, the Bibliotheca Alexandrina in Egypt, and Stanford Libraries in California to store copies of 17,000 of GitHub’s most popular and most-depended-upon projects—open source’s “greatest hits”—in their archives, in museum-quality cases, to preserve them for future generations.

https://archiveprogram.github.com/arctic-vault/

目录

1. 自述
1.1. 写给读者
1.2. 作者简介
1.3. 如何获得文档
1.4. 打赏(Donations)
1.5. 联系方式
1. MySQL Server
1.1. MySQL 安装
1.1.1. Rocky Linux 9.0
1.1.2. CentOS
1.1.3. Docker
1.1.4. Ubuntu/Debian
1.1.5. 源码安装
1.1.6. 二进制版本
1.1.7. Installing MySQL on Linux Using the MySQL Yum Repository
1.1.8. Firewall
1.1.9. Mac OS
1.1.10. MariaDB
1.1.11. Percona
1.2. MySQL Plugin
1.2.1. validate_password
1.2.2. MySQL Images manager
1.2.3. MySQL fifo
1.2.4. 内容输出到文本插件
1.3. Replication
1.3.1. Master Slave
1.3.2. Master Master(主主)
1.3.3. Semisynchronous Replication
1.3.4. multi-master replication
1.3.5. multi-source replication
1.3.6. 与复制有关的问题
1.3.7. GTID
1.4. MySQL Custer
1.4.1. Management node (MGM node)
1.4.2. Data node
1.4.3. SQL node
1.4.4. Starting
1.4.5. Shutdown
1.4.6. Testing
1.5. MySQL Proxy
1.5.1. Ubuntu
1.5.2. CentOS
1.6. MySQL Router
1.6.1. 安装 MySQL Router
1.6.2. 配置 MySQL Router
1.6.3. MySQL Router , Haproxy,LVS 的选择
1.7. my.cnf
1.7.1. bind-address
1.7.2. 禁用TCP/IP链接
1.7.3. 配置字符集
1.7.4. 最大链接数 max_connections
1.7.5. 默认引擎 storage-engine
1.7.6. max_allowed_packet
1.7.7. skip-name-resolve
1.7.8. timeout
1.7.9. 与复制有关的参数
1.7.10. 与 InnoDB 有关的配置项
1.7.11. EVENT 设置
1.7.12. 日志
1.7.13. MySQL 5.7 my.cnf 实例
1.7.14. Example for my.cnf
1.8. variables
1.8.1. 查询多个变量
1.8.2. time_zone
1.8.3. sql_mode
1.8.4. wait_timeout
1.8.5. table_lock_wait_timeout
1.8.6. low_priority_updates
1.8.7. collation_server
1.8.8. character_set
1.8.9. datadir
1.8.10. plugin_dir
1.8.11. storage_engine
1.8.12. timeout
1.8.13. max_connections
1.8.14. 自动提交 autocommit
1.9. Monitoring
1.9.1. Analysis and Optimization
1.9.2. Cacti
1.9.3. Monitoring MySQL with SNMP
2. Client and Utility Programs
2.1. mysql - the MySQL command-line tool
2.1.1. ~/.my.cnf
2.1.2. 屏幕输出到文件
2.1.3. 终端编码
2.1.4. Unix Socket
2.1.5. 重定向巧用
2.1.6. --sigint-ignore 忽略 Ctrl + C
2.1.7. mysql log
2.2. mysqldump - a database backup program
2.2.1. 备份数据库并压缩文件
2.2.2. 备份数据库/表
2.2.3. 备份到文件
2.2.4. 备份数据库,无结构,只有数据
2.2.5. 使用完整的insert插入数据
2.2.6. --extended-insert / --skip-extended-insert
2.2.7. --skip-lock-tables
2.2.8. --skip-add-locks
2.2.9. --where
2.2.10. 注释信息--comments /--skip-comments
2.2.11. 不导出注释信息
2.2.12. 字符集设置
2.3. mysqladmin - client for administering a MySQL server
2.3.1. reload
2.3.2. 更改密码
2.3.3. status
2.3.4. process list
2.4. myisamchk — MyISAM Table-Maintenance Utility
2.5. mysqlcheck — A Table Maintenance and Repair Program
2.6. mysqlslap - load emulation client
2.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
2.8. mysql-shell
2.9.
2.10. MySQL慢查询日志(Slow Query Log)
2.10.1. MySQL 8.x
2.10.2. MySQL 5.x
2.11. mysql-admin
2.12. MySQL Workbench 数据库恢复
3. 数据库管理(Database Administration)
3.1. 用户管理(User Account Management)
3.1.1. 创建用户
3.1.2. 删除用户
3.1.3. 修改用户名
3.1.4. 修改密码
3.2. Access Privilege System
3.2.1. SHOW GRANTS
3.2.2. show privileges
3.2.3. Grant privileges
3.2.4. Revoke privileges
3.2.5. Show Privileges
3.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
3.2.7. Table Privileges
3.2.8. Column Privileges
3.3. 字符集转换
3.3.1. 转换 latin1 到 UTF-8
3.4. 重新整理AUTO_INCREMENT字段
3.5. 数据库内容替换
3.6. Kill 脚本
3.7. MySQL 时区管理
3.8. SHOW COMMAND
3.8.1. 查看版本
3.8.2. status
3.8.3. show processlist
3.8.4. 线程的使用情况
3.8.5. DATABASES
3.8.6. 排序统计信息
3.8.7. Key 状态
3.8.8. FUNCTION
3.8.9. PROCEDURE
3.8.10. TRIGGERS
3.8.11. EVENTS
3.8.12. 引擎(ENGINES)
3.8.13. 字符集(Collation)
3.8.14. SHOW GRANTS
3.8.15. validate_password
3.9. Maintenance 数据库维护
3.9.1. CHECK 检查表
3.9.2. ANALYZE 分析表
3.9.3. CHECKSUM
3.9.4. OPTIMIZE 优化表
3.9.5. REPAIR 修复
3.10. INFORMATION_SCHEMA
3.10.1. 查询表字段
3.10.2. 列出所有触发器
3.10.3. 查看表数据尺寸
3.11. Backup and Recovery
3.11.1. Import / Export
3.11.2. Snapshot Backup
4. DDL - Data Definition Language
4.1. 数据库管理(Database)
4.1.1. 创建数据库
4.1.2. 删除数据库
4.1.3. 修改数据库
4.1.4. 重命名数据库
4.1.5. 修改字符集
4.1.6. 查看数据库创建语句
4.2. 表管理(Table)
4.2.1. 数据类型
4.2.2. 基于现有表结构创建新空表
4.2.3. 基于已存在表创建新表
4.2.4. 修改表
4.2.5. 临时表
4.2.6. CHARACTER
4.2.7. DEFAULT
4.2.8. KEY
4.2.9. AUTO_INCREMENT 定义初始值
4.2.10. COMMENT
4.2.11. 修改表名
4.2.12. Engine 存储引擎
4.3. Partitioning
4.3.1. RANGE
4.3.2. LIST
4.3.3. HASH
4.3.4. KEY分区
4.3.5. Subpartitioning
4.3.6. 分区管理
4.3.7. EXPLAIN PARTITIONS
4.3.8. SHOW CREATE TABLE
4.3.9. INFORMATION_SCHEMA.partitions 表
4.3.10. 分区数据操作
4.4. Index
4.4.1. SHOW INDEX
4.4.2. CREATE INDEX
4.4.3. DROP INDEX
4.4.4. rebuild
4.5. 外键(Foreign Key)
4.5.1. FOREIGN KEY (RESTRICT)
4.6. 视图(View)
4.7. 存储过程(PROCEDURE)
4.7.1. 存储程序
4.7.2. EXECUTE 执行 SQL
4.7.3. PREPARE 传递参数
4.7.4. 存储过程返回数据
4.7.5. 结果集转JSON
4.7.6. 例子·过程返回结果
4.8. 函数
4.8.1. TIMESTAMP TO ISO8601
4.9. 触发器(Trigger)
4.9.1. create trigger
4.9.2. drop trigger
4.9.3. show triggers
4.9.4. EXAMPLE
4.10. 事件调度器(EVENT)
4.10.1. 启用 EVENT
4.10.2. 创建 EVENT
4.10.3. 禁用/启用
4.10.4. 查看 events
4.10.5. 删除 EVENT
4.10.6. EVENT 应用案例
5. DML (Data Manipulation Language)
5.1. INSERT
5.1.1. INSERT INTO ... SELECT
5.1.2. INSERT IGNORE
5.1.3. INSERT...ON DUPLICATE KEY UPDATE
5.2. REPLACE
5.3. DELETE
5.3.1. 删除重复数据
6. SQL Statement Syntax
6.1. DISTINCT
6.2. group by
6.3. HAVING
6.4. REGEXP
6.5. IN / NOT IN
6.6. ALL / Any
6.7. exists, not exists
6.8. UNION
6.8.1. UNION ALL
6.8.2. 两张表字段不对等解决方法
6.9. OUTFILE/LOAD DATA INFILE
6.9.1. Export data to CSV from MySQL
6.9.2. Import data from CSV file.
6.10. CASE Syntax
6.11. 查询结果放入变量
6.12. MySQL 专有命令
6.12.1. SQL_NO_CACHE
6.12.2. SIGNAL Syntax
6.13. SQL 92
7. Functions and Operators
7.1. COUNT
7.2. group_concat() 列传行
7.3. UUID()
7.4. String
7.4.1. LEFT/RIGHT
7.4.2. RPAD/LPAD
7.4.3. CONCAT
7.4.4. CONCAT_WS
7.4.5. 链接所有字段
7.4.6. GROUP_CONCAT
7.4.7. replace
7.4.8. SUBSTRING
7.4.9. SUBSTRING_INDEX
7.4.10. AES_ENCRYPT / AES_DECRYPT
7.5. Date and Time
7.5.1. year/month/day hour:minite:second
7.5.2. Unix time
7.5.3. DATE_FORMAT
7.5.4. DATE_SUB/DATE_ADD
7.5.5. datediff / timediff
7.6. 数值函数
7.6.1. cast 类型转换
7.6.2. truncate 保留小数位数
7.6.3. MOD 求余
7.7. Control Flow Functions
8. DCL (Data Control Language)
8.1. 锁
8.1.1. 共享锁
8.1.2. 排他锁
8.1.3. 锁
8.1.4. 锁等待与超时
8.2. 事务处理和锁定语句
8.2.1. 事务隔离级别
8.2.2. 事务所用到的表
8.2.3. 解决更新冲突
8.2.4. 共享锁
8.2.5. SAVEPOINT
9. Optimization
9.1. Limit 状态
9.2. 使用 Btrfs 文件系统存储mysql数据
9.3. 打开表的数量
9.4. Buffering and Caching
9.4.1. Query Cache SELECT Options
9.5. where 优化
9.6. SHOW PROFILE Syntax SQL性能分析器
9.7. PROCEDURE ANALYSE()
10. MySQL Connectors
10.1. JDBC
10.2. ODBC
10.3. MySQL native driver for PHP - mysqlnd
10.4. python-mysqldb
11. MySQL GUI/Web Manager
11.1. HeidiSQL
11.2. Toad for MySQL Freeware
11.3. phpMyAdmin - MySQL web administration tool
11.4. Maatkit Essential command-line utilities for MySQL
12. Miscellaneous
12.1. Multi-Master Replication Manager for MySQL
12.2. MHA
12.3. HandlerSocket
12.4. Maatkit
12.5. Mroonga
12.6. Amoeba
13. FAQ
13.1. Reset root password 重置MySQL root密码
13.1.1. MySQL 5.7.x
13.1.2. MySQL 8.0
13.2. 查看错误代码
13.2.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
13.2.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
13.3. 临时表是否需要建索引
13.4. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
13.5. Table 'performance_schema.session_variables' doesn't exist
13.6. SQL Error (1038): Out of sort memory, consider increasing server sort buffer size
13.7. this is incompatible with sql_mode=only_full_group_by
13.8. ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes
13.9. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
13.10. ERROR 1114 (HY000): The table 'your_table' is full
13.11. Error Code: 1146. Table 'test.CACHE_UPDATE' doesn't exist
13.12. Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
13.13. ERROR 1273 (HY000) at line 3364: Unknown collation: 'utf8mb4_0900_ai_ci'
13.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
13.15. ERROR 1364: 1364: Field 'id' doesn't have a default value
13.16. Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'create_time' at row 95692
13.17. ERROR 1415: Not allowed to return a result set from a trigger
13.18. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
13.19. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
13.20. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
13.21. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
13.22. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
13.23. ERROR 2013 (HY000): Lost connection to MySQL server during query
13.24. ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
13.25. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
13.26. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
13.27. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
13.28. mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
13.29. this is incompatible with sql_mode=only_full_group_by
13.30. mysqldump: [Warning] Using a password on the command line interface can be insecure.
13.31. mysql: [Warning] Using a password on the command line interface can be insecure.
13.32. 时间自动被加一秒

表格清单

8.1. 更新丢失演示
8.2. 防止更新丢失加锁演示

范例清单

1.1. MySQL 8 创建root账号
1.2. my.cnf
1.3. my.cnf
1.4. my.cnf
4.1. BEFORE/AFTER
4.2. uuid()
6.1. SQL ANY example
9.1. SQL_CACHE 测试
9.2. SHOW PROFILE Syntax