Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | 云栖社区 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Github | Search | About

部分 III. MySQL

目录

19. MySQL Server
19.1. MySQL Installation
19.1.1. Installation by apt under debian/ubuntu
19.1.1.1. mysql-5.5.21-debian6.0-i686.deb
19.1.2. Installation by source code
19.1.3. MySQL binary distribution
19.1.4. CentOS
19.1.4.1. CentOS 6.2 + MySQL 5.5.25 (RPM)
19.1.4.2. MySQL 8.0
19.1.5. mysql-admin
19.1.6. Installing MySQL on Linux Using the MySQL Yum Repository
19.1.6.1. MySQL 5.6
19.1.6.2. MySQL 5.7
19.1.7. Firewall
19.1.8. Limit 状态
19.1.9. 使用 Btrfs 文件系统存储mysql数据
19.1.10. Mac OS
19.2. MariaDB
19.2.1. CentOS YUM 安装 MariaDB
19.2.2. MariaDB Audit Plugin
19.3. Percona
19.3.1. Percona yum Repository
19.3.2. Percona XtraBackup
19.3.2.1. 安装 XtraBackup
19.3.2.2. innobackupex
19.3.2.2.1. 备份数据库
19.3.2.2.2. 恢复数据库
19.3.2.3. xbstream
19.3.2.4. xtrabackup
19.3.3. Percona Toolkit - MySQL Management Software
19.4. my.cnf
19.4.1. bind-address
19.4.2. 禁用TCP/IP链接
19.4.3. 配置字符集
19.4.4. 最大链接数 max_connections
19.4.5. 默认引擎 storage-engine
19.4.6. max_allowed_packet
19.4.7. skip-name-resolve
19.4.8. timeout
19.4.9. 与复制有关的参数
19.4.9.1. 用于主库的选项 Master
19.4.9.2. 用于从库的选项 Slave
19.4.9.3. 逃过错误
19.4.10. 与 InnoDB 有关的配置项
19.4.11. EVENT 设置
19.4.12. 日志
19.4.13. MySQL 5.7 my.cnf 实例
19.4.14. Example for my.cnf
19.5. MySQL Plugin
19.5.1. validate_password
19.5.2. MySQL Images manager
19.5.3. MySQL fifo
19.5.4. 内容输出到文本插件
19.6. Replication
19.6.1. Master Slave
19.6.1.1. Master
19.6.1.2. Slave
19.6.1.3. Testing
19.6.1.4. 将现有数据库迁移到主从结构数据库
19.6.1.5. 主从复制安全问题
19.6.2. Master Master(主主)
19.6.2.1. Master A
19.6.2.2. Master B
19.6.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
19.6.2.4. Master A - B 同步两端数据库
19.6.2.5. Master A 数据库解除只读权限
19.6.2.6. 查看主主的工作状态
19.6.3. Semisynchronous Replication
19.6.3.1. Master
19.6.3.2. Slave 配置
19.6.3.3. 卸载插件
19.6.3.4. my.cnf
19.6.4. multi-master replication
19.6.5. multi-source replication
19.6.6. 与复制有关的问题
19.6.6.1. 主从不同步问题
19.6.6.2. mysql-bin 清理问题
19.6.6.3. 跳过 Last_Errno
19.6.6.4. 重置Slave
19.6.7. GTID
19.6.7.1. Master
19.6.7.2. Slave
19.7. MySQL Custer
19.7.1. Management node (MGM node)
19.7.2. Data node
19.7.3. SQL node
19.7.4. Starting
19.7.5. Shutdown
19.7.6. Testing
19.8. MySQL Proxy
19.8.1. Ubuntu
19.8.2. CentOS
19.8.2.1. FAQ
19.9. MySQL Router
19.9.1. 安装 MySQL Router
19.9.2. 配置 MySQL Router
19.9.2.1. 主备配置
19.9.2.2. 负载均衡配置
19.9.3. MySQL Router , Haproxy,LVS 的选择
19.10. variables
19.10.1. time_zone
19.10.2. sql_mode
19.10.2.1. 设置 sql_mode
19.10.2.2. 查看 sql_mode
19.10.2.3. 兼容早起 MySQL 版本
19.10.2.4. 5.7.16
19.10.3. wait_timeout
19.10.4. table_lock_wait_timeout
19.10.5. low_priority_updates
19.10.6. collation_server
19.10.7. character_set
19.10.8. datadir
19.10.9. plugin_dir
19.10.10. storage_engine
19.10.11. timeout
19.10.12. max_connections
19.11. SHOW COMMAND
19.11.1. 查看版本
19.11.2. status
19.11.2.1. show status
19.11.2.2. show master status
19.11.2.3. show slave status
19.11.2.4. show plugins
19.11.3. show processlist
19.11.4. binary 日志
19.11.5. 线程的使用情况
19.11.6. DATABASES
19.11.7. TABLE
19.11.8. 临时表
19.11.9. 排序统计信息
19.11.10. Key 状态
19.11.11. FUNCTION
19.11.12. PROCEDURE
19.11.13. TRIGGERS
19.11.14. EVENTS
19.11.15. 引擎(ENGINES)
19.11.16. 字符集(Collation)
19.11.17. SHOW GRANTS
19.12. Monitoring
19.12.1. Analysis and Optimization
19.12.1.1. mytop - top like query monitor for MySQL
19.12.1.2. mtop - MySQL terminal based query monitor
19.12.1.3. innotop
19.12.1.4. mysqlreport - A friendly report of important MySQL status values
19.12.1.5. mysqltuner - MySQL configuration assistant
19.12.2. Munin
19.12.3. Cacti
19.12.4. Monitoring MySQL with SNMP
20. Client and Utility Programs
20.1. mysql - the MySQL command-line tool
20.1.1. ~/.my.cnf
20.1.2. 屏幕输出到文件
20.1.3. 终端编码
20.1.4. Unix Socket
20.1.5. 重定向巧用
20.1.6. --sigint-ignore 忽略 Ctrl + C
20.2. mysqldump - a database backup program
20.2.1. 备份数据库,无结构,只有数据
20.2.2. 备份数据库结构(不备份数据)
20.2.3. 使用完整的insert插入数据
20.2.4. --extended-insert / --skip-extended-insert
20.2.5. --skip-lock-tables
20.2.6. --skip-add-locks
20.2.7. --where
20.2.8. 注释信息--comments /--skip-comments
20.3. mysqladmin - client for administering a MySQL server
20.3.1. reload
20.3.2. 更改密码
20.3.3. status
20.3.4. process list
20.4. myisamchk — MyISAM Table-Maintenance Utility
20.5. mysqlcheck — A Table Maintenance and Repair Program
20.6. mysqlslap - load emulation client
20.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
20.8. mysql log
21. Database Administration
21.1. User Account Management
21.1.1. Create User
21.1.2. Drop User
21.1.3. Rename User
21.1.4. SET PASSWORD
21.2. Access Privilege System
21.2.1. SHOW GRANTS
21.2.2. show privileges
21.2.3. Grant privileges
21.2.4. Revoke privileges
21.2.5. Show Privileges
21.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
21.2.7. Table Privileges
21.2.8. Column Privileges
21.3. Maintenance 数据库维护
21.3.1. CHECK 检查表
21.3.2. ANALYZE 分析表
21.3.3. CHECKSUM
21.3.4. OPTIMIZE 优化表
21.3.5. REPAIR 修复
21.4. INFORMATION_SCHEMA
21.4.1. 查询表字段
21.4.2. 列出所有触发器
21.5. Backup and Recovery
21.5.1. Import / Export
21.5.1.1. Export(Backup)
21.5.1.2. Import(Recovery)
21.5.1.3. xml
21.5.1.4. 备份表数据
21.5.1.5. source
21.5.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
21.5.1.7. AutoMySQLBackup
21.5.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
21.5.1.8.1. Percona yum Repository
21.5.1.8.2. Creating an Incremental Backup
21.5.2. Snapshot Backup
21.5.2.1. LVM Snapshot
21.5.2.2. Btrfs Snapshot
22. DDL - Data Definition Language
22.1. 数据库管理(Database)
22.1.1. create
22.1.2. drop
22.1.3. Alter
22.1.4. Rename
22.1.5. CHARACTER
22.1.6. show create database
22.2. 表管理(Table)
22.2.1. 数据类型
22.2.1.1. SET 集合类型
22.2.2. create table ... select
22.2.3. modifiy table
22.2.4. TEMPORARY Table
22.2.5. Collate
22.2.6. CHARACTER
22.2.7. DEFAULT
22.2.7.1. AUTO_INCREMENT
22.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
22.2.7.3. 表存储位置(DATA DIRECTORY)
22.2.8. KEY
22.2.8.1. PRIMARY KEY
22.2.9. COMMENT
22.2.10. Engine 存储引擎
22.2.10.1. 显示当前数据库支持引擎
22.2.10.2. 切换引擎
22.2.10.3. FEDERATED
22.2.10.4. BLACKHOLE
22.2.10.5. ARCHIVE
22.2.10.6. CSV
22.3. Partitioning
22.3.1. RANGE
22.3.2. LIST
22.3.3. HASH
22.3.3.1. LINEAR HASH
22.3.4. KEY分区
22.3.5. Subpartitioning
22.3.6. 分区管理
22.3.6.1. 新增分区
22.3.6.2. 删除分区
22.3.6.3. 重建分区
22.3.6.4. 分区维护
22.3.7. EXPLAIN PARTITIONS
22.3.8. SHOW CREATE TABLE
22.3.9. INFORMATION_SCHEMA.partitions 表
22.3.10. 分区数据操作
22.4. Index
22.4.1. SHOW INDEX
22.4.2. CREATE INDEX
22.4.3. DROP INDEX
22.4.4. rebuild
22.5. 外键(Foreign Key)
22.5.1. FOREIGN KEY (RESTRICT)
22.6. 视图(View)
22.7. 存储过程(PROCEDURE)
22.7.1. 存储程序
22.7.2. EXECUTE 执行 SQL
22.7.3. PREPARE 传递参数
22.7.4. 存储过程返回数据
22.7.5. 结果集转JSON
22.7.6. 例子·过程返回结果
22.8. 函数
22.8.1. TIMESTAMP TO ISO8601
22.9. 触发器(Trigger)
22.9.1. create trigger
22.9.1.1. Update 更新出发
22.9.1.2. Delete 删除出发
22.9.1.3. Insert 插入出发
22.9.2. drop trigger
22.9.3. show triggers
22.9.3.1. SHOW CREATE TRIGGER
22.9.4. EXAMPLE
22.9.4.1. BEFORE/AFTER
22.9.4.2. UUID
22.9.4.3. CALL PROCEDURE
22.10. 事件调度器(EVENT)
22.10.1. 启用 EVENT
22.10.2. 创建 EVENT
22.10.3. 禁用/启用
22.10.4. show events
22.10.5. 实例·每月创建一个表
23. DML (Data Manipulation Language)
23.1. INSERT
23.1.1. INSERT INTO ... SELECT
23.1.2. INSERT IGNORE
23.1.3. INSERT...ON DUPLICATE KEY UPDATE
23.2. REPLACE
23.3. DELETE
23.3.1. 删除重复数据
24. SQL Statement Syntax
24.1. DISTINCT
24.2. group by
24.3. HAVING
24.4. REGEXP
24.5. IN / NOT IN
24.6. ALL / Any
24.7. exists, not exists
24.8. UNION
24.8.1. UNION ALL
24.8.2. 两张表字段不对等解决方法
24.9. OUTFILE/LOAD DATA INFILE
24.9.1. Export data to CSV from MySQL
24.9.2. Import data from CSV file.
24.10. CASE Syntax
24.11. MySQL 专有命令
24.11.1. SQL_NO_CACHE
24.11.2. SIGNAL Syntax
24.12. SQL 92
25. Functions and Operators
25.1. COUNT
25.2. group_concat() 列传行
25.3. UUID()
25.4. String
25.4.1. LEFT/RIGHT
25.4.2. RPAD/LPAD
25.4.3. CONCAT
25.4.4. CONCAT_WS
25.4.5. 链接所有字段
25.4.6. GROUP_CONCAT
25.4.7. replace
25.4.8. SUBSTRING
25.4.9. SUBSTRING_INDEX
25.4.10. AES_ENCRYPT / AES_DECRYPT
25.5. Date and Time
25.5.1. year/month/day hour:minite:second
25.5.2. Unix time
25.5.3. DATE_FORMAT
25.5.4. DATE_SUB/DATE_ADD
25.5.4.1. DATE_ADD
25.5.5. datediff / timediff
25.6. 数值函数
25.6.1. cast 类型转换
25.6.2. truncate 保留小数位数
25.6.3. MOD 求余
25.7. Control Flow Functions
26. DCL (Data Control Language)
26.1. 锁
26.1.1. 共享锁
26.1.2. 排他锁
26.1.3. 锁
26.1.3.1. 表的加锁与解锁
26.1.3.2. 禁止查询
26.1.4. 锁等待与超时
26.1.4.1. 超时设置
26.1.4.2. select for update nowait
26.2. 事务处理和锁定语句
26.2.1. 事务隔离级别
26.2.2. 事务所用到的表
26.2.3. 解决更新冲突
26.2.4. SAVEPOINT
27. Optimization
27.1. 打开表的数量
27.2. Buffering and Caching
27.2.1. Query Cache SELECT Options
27.3. where 优化
27.4. SHOW PROFILE Syntax SQL性能分析器
27.5. PROCEDURE ANALYSE()
28. MySQL Connectors
28.1. JDBC
28.2. ODBC
28.3. MySQL native driver for PHP - mysqlnd
28.4. python-mysqldb
29. MySQL GUI/Web Manager
29.1. HeidiSQL
29.2. Toad for MySQL Freeware
29.3. phpMyAdmin - MySQL web administration tool
29.4. Maatkit Essential command-line utilities for MySQL
30. Miscellaneous
30.1. Multi-Master Replication Manager for MySQL
30.2. MHA
30.3. HandlerSocket
30.4. Maatkit
30.5. Mroonga
30.6. Amoeba
31. FAQ
31.1. Reset root password 重置MySQL root密码
31.2. 数据库内容替换
31.3. 查看错误代码
31.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
31.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
31.4. 临时表是否需要建索引
31.5. Kill 脚本
31.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
31.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
31.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
31.9. 重新整理AUTO_INCREMENT字段
31.10. 转换 latin1 到 UTF-8
31.11. this is incompatible with sql_mode=only_full_group_by
31.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
31.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
31.14. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
31.15. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
31.16. ERROR 1415: Not allowed to return a result set from a trigger
31.17. 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