Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | Email

部分 II. MySQL

目录

8. MySQL Server
8.1. MySQL Installation
8.1.1. Installation by apt-get under debian/ubuntu
8.1.2. Installation by source code
8.1.3. MySQL binary distribution
8.1.4. mysql-5.5.21-debian6.0-i686.deb
8.1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)
8.1.6. mysql-admin
8.1.7. Installing MySQL on Linux Using the MySQL Yum Repository
8.1.7.1. MySQL 5.6
8.1.7.2. MySQL 5.7
8.1.8. Firewall
8.1.9. Limit 状态
8.1.10. 使用 Btrfs 文件系统存储mysql数据
8.2. MariaDB
8.2.1. CentOS YUM 安装 MariaDB
8.2.2. MariaDB Audit Plugin
8.3. Percona
8.3.1. Percona yum Repository
8.3.2. Percona XtraBackup
8.3.2.1. 安装 XtraBackup
8.3.2.2. innobackupex
8.3.2.3. xbstream
8.3.2.4. xtrabackup
8.3.3. Percona Toolkit - MySQL Management Software
8.4. my.cnf
8.4.1. bind-address
8.4.2. 禁用TCP/IP链接
8.4.3. 配置字符集
8.4.4. 最大链接数 max_connections
8.4.5. 默认引擎 storage-engine
8.4.6. max_allowed_packet
8.4.7. skip-name-resolve
8.4.8. timeout
8.4.9. 与复制有关的参数
8.4.9.1. 用于主库的选项 Master
8.4.9.2. 用于从库的选项 Slave
8.4.9.3. 逃过错误
8.4.10. 与 InnoDB 有关的配置项
8.4.11. EVENT 设置
8.4.12. 日志
8.4.13. MySQL 5.7 my.cnf 实例
8.4.14. Example for my.cnf
8.5. SHOW COMMAND
8.5.1. 查看版本
8.5.2. status
8.5.2.1. show status
8.5.2.2. show master status
8.5.2.3. show slave status
8.5.2.4. show plugins
8.5.3. show processlist
8.5.4. variables
8.5.4.1. sql_mode
8.5.4.2. wait_timeout
8.5.4.3. table_lock_wait_timeout
8.5.4.4. low_priority_updates
8.5.4.5. character_set
8.5.4.6. datadir
8.5.4.7. plugin_dir
8.5.4.8. storage_engine
8.5.4.9. timeout
8.5.4.10. max_connections
8.5.5. binary 日志
8.5.6. DATABASES
8.5.7. TABLE
8.5.8. FUNCTION
8.5.9. PROCEDURE
8.5.10. TRIGGERS
8.5.11. EVENTS
8.5.12. 引擎(ENGINES)
8.5.13. 字符集(Collation)
8.5.14. SHOW GRANTS
8.6. MySQL Plugin
8.6.1. validate_password
8.6.2. MySQL Images manager
8.6.3. MySQL fifo
8.7. Replication
8.7.1. Master Slave
8.7.1.1. Master
8.7.1.2. Slave
8.7.1.3. Testing
8.7.1.4. 将现有数据库迁移到主从结构数据库
8.7.1.5. 主从复制安全问题
8.7.2. Master Master(主主)
8.7.2.1. Master A
8.7.2.2. Master B
8.7.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
8.7.2.4. Master A - B 同步两端数据库
8.7.2.5. Master A 数据库解除只读权限
8.7.2.6. 查看主主的工作状态
8.7.3. Semisynchronous Replication
8.7.3.1. Master
8.7.3.2. Slave 配置
8.7.3.3. 卸载插件
8.7.3.4. my.cnf
8.7.4. multi-master replication
8.7.5. multi-source replication
8.7.6. 与复制有关的问题
8.7.6.1. 主从不同步问题
8.7.6.2. mysql-bin 清理问题
8.7.6.3. 跳过 Last_Errno
8.7.6.4. 重置Slave
8.7.7. GTID
8.7.7.1. Master
8.7.7.2. Slave
8.8. MySQL Custer
8.8.1. Management node (MGM node)
8.8.2. Data node
8.8.3. SQL node
8.8.4. Starting
8.8.5. Shutdown
8.8.6. Testing
8.9. MySQL Proxy
8.9.1. Ubuntu
8.9.2. CentOS
8.9.2.1. FAQ
8.10. MySQL Router
8.10.1. 安装 MySQL Router
8.10.2. 配置 MySQL Router
8.10.2.1. 主备配置
8.10.2.2. 负载均衡配置
8.10.3. MySQL Router , Haproxy,LVS 的选择
8.11. Monitoring
8.11.1. Analysis and Optimization
8.11.1.1. mytop - top like query monitor for MySQL
8.11.1.2. mtop - MySQL terminal based query monitor
8.11.1.3. innotop
8.11.1.4. mysqlreport - A friendly report of important MySQL status values
8.11.1.5. mysqltuner - MySQL configuration assistant
8.11.2. Munin
8.11.3. Cacti
8.11.4. Monitoring MySQL with SNMP
9. Client and Utility Programs
9.1. mysql - the MySQL command-line tool
9.1.1. ~/.my.cnf
9.1.2. 屏幕输出到文件
9.1.3. 终端编码
9.1.4. Unix Socket
9.1.5. 重定向巧用
9.1.6. --sigint-ignore 忽略 Ctrl + C
9.2. mysqldump - a database backup program
9.2.1. 备份数据库,无结构,只有数据
9.2.2. 备份数据库结构(不备份数据)
9.2.3. 使用完整的insert插入数据
9.2.4. --extended-insert / --skip-extended-insert
9.2.5. --skip-lock-tables
9.2.6. --skip-add-locks
9.2.7. --where
9.2.8. 注释信息--comments /--skip-comments
9.3. mysqladmin - client for administering a MySQL server
9.3.1. reload
9.3.2. 更改密码
9.3.3. status
9.3.4. process list
9.4. myisamchk — MyISAM Table-Maintenance Utility
9.5. mysqlcheck — A Table Maintenance and Repair Program
9.6. mysqlslap - load emulation client
9.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
9.8. mysql log
10. Database Administration
10.1. User Account Management
10.1.1. Create User
10.1.2. Drop User
10.1.3. Rename User
10.1.4. SET PASSWORD
10.2. Access Privilege System
10.2.1. SHOW GRANTS
10.2.2. show privileges
10.2.3. Grant privileges
10.2.4. Revoke privileges
10.2.5. Show Privileges
10.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
10.2.7. Table Privileges
10.2.8. Column Privileges
10.3. Maintenance 数据库维护
10.3.1. CHECK 检查表
10.3.2. ANALYZE 分析表
10.3.3. CHECKSUM
10.3.4. OPTIMIZE 优化表
10.3.5. REPAIR 修复
10.4. Backup and Recovery
10.4.1. Import / Export
10.4.1.1. Export(Backup)
10.4.1.2. Import(Recovery)
10.4.1.3. xml
10.4.1.4. 备份表数据
10.4.1.5. source
10.4.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
10.4.1.7. AutoMySQLBackup
10.4.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
10.4.2. Snapshot Backup
10.4.2.1. LVM Snapshot
10.4.2.2. Btrfs Snapshot
11. DDL - Data Definition Language
11.1. 数据库管理(Database)
11.1.1. create
11.1.2. drop
11.1.3. Alter
11.1.4. Rename
11.1.5. CHARACTER
11.1.6. show create database
11.2. 表管理(Table)
11.2.1. 数据类型
11.2.1.1. SET 集合类型
11.2.2. create table ... select
11.2.3. modifiy table
11.2.4. TEMPORARY Table
11.2.5. Collate
11.2.6. CHARACTER
11.2.7. DEFAULT
11.2.7.1. AUTO_INCREMENT
11.2.7.2. 表存储位置(DATA DIRECTORY)
11.2.8. KEY
11.2.8.1. PRIMARY KEY
11.2.9. COMMENT
11.2.10. Engine
11.2.10.1. FEDERATED
11.2.10.2. BLACKHOLE
11.3. Partitioning
11.3.1. RANGE
11.3.2. LIST
11.3.3. HASH
11.3.3.1. LINEAR HASH
11.3.4. KEY分区
11.3.5. Subpartitioning
11.3.6. 分区管理
11.3.6.1. 新增分区
11.3.6.2. 删除分区
11.3.6.3. 重建分区
11.3.6.4. 分区维护
11.3.7. EXPLAIN PARTITIONS
11.3.8. SHOW CREATE TABLE
11.3.9. INFORMATION_SCHEMA.partitions 表
11.3.10. 分区数据操作
11.4. Index
11.4.1. SHOW INDEX
11.4.2. CREATE INDEX
11.4.3. DROP INDEX
11.4.4. rebuild
11.5. 外键(Foreign Key)
11.5.1. FOREIGN KEY (RESTRICT)
11.6. 视图(View)
11.7. 存储过程(PROCEDURE)
11.7.1. 存储程序
11.7.2. 函数
11.8. 触发器(Trigger)
11.8.1. create trigger
11.8.2. drop trigger
11.8.3. show triggers
11.8.4. EXAMPLE
11.8.4.1. BEFORE/AFTER
11.8.4.2. UUID
11.8.4.3. CALL PROCEDURE
11.9. 事件调度器(EVENT)
11.9.1. 启用 EVENT
11.9.2. 创建 EVENT
11.9.3. 禁用/启用
11.9.4. show events
12. DML (Data Manipulation Language)
12.1. INSERT
12.1.1. INSERT INTO ... SELECT
12.1.2. INSERT IGNORE
12.1.3. INSERT...ON DUPLICATE KEY UPDATE
12.2. REPLACE
12.3. DELETE
12.3.1. 删除重复数据
13. SQL Statement Syntax
13.1. DISTINCT
13.2. group by
13.3. HAVING
13.4. REGEXP
13.5. IN / NOT IN
13.6. ALL / Any
13.7. exists, not exists
13.8. UNION
13.8.1. UNION ALL
13.9. OUTFILE/LOAD DATA INFILE
13.9.1. Export data to CSV from MySQL
13.9.2. Import data from CSV file.
13.10. MySQL 专有命令
13.10.1. SQL_NO_CACHE
13.10.2. SIGNAL Syntax
13.11. SQL 92
14. Functions and Operators
14.1. COUNT
14.2. UUID()
14.3. String
14.3.1. LEFT/RIGHT
14.3.2. RPAD/LPAD
14.3.3. CONCAT
14.3.4. GROUP_CONCAT
14.3.5. replace
14.3.6. SUBSTRING
14.3.7. SUBSTRING_INDEX
14.3.8. AES_ENCRYPT / AES_DECRYPT
14.4. Date and Time
14.4.1. year/month/day hour:minite:second
14.4.2. Unix time
14.4.3. DATE_FORMAT
14.4.4. DATE_SUB/DATE_ADD
14.4.4.1. DATE_ADD
14.4.5. datediff / timediff
14.5. 数值函数
14.5.1. cast 类型转换
14.5.2. truncate 保留小数位数
14.5.3. MOD 求余
14.6. Control Flow Functions
15. DCL (Data Control Language)
15.1. 锁
15.1.1. 共享锁
15.1.2. 排他锁
15.1.3. 锁
15.1.3.1. 表的加锁与解锁
15.1.3.2. 禁止查询
15.1.4. 锁等待与超时
15.1.4.1. 超时设置
15.1.4.2. select for update nowait
15.2. 事务处理和锁定语句
15.2.1. 事务隔离级别
15.2.2. 事务所用到的表
15.2.3. 解决更新冲突
15.2.4. SAVEPOINT
16. Optimization
16.1. Buffering and Caching
16.1.1. Query Cache SELECT Options
16.2. where 优化
16.3. SHOW PROFILE Syntax SQL性能分析器
16.4. PROCEDURE ANALYSE()
17. MySQL Connectors
17.1. JDBC
17.2. ODBC
17.3. MySQL native driver for PHP - mysqlnd
17.4. python-mysqldb
18. MySQL GUI/Web Manager
18.1. HeidiSQL
18.2. Toad for MySQL Freeware
18.3. phpMyAdmin - MySQL web administration tool
18.4. Maatkit Essential command-line utilities for MySQL
19. Miscellaneous
19.1. Multi-Master Replication Manager for MySQL
19.2. MHA
19.3. HandlerSocket
19.4. Maatkit
19.5. Mroonga
19.6. Amoeba
20. FAQ
20.1. Reset root password 重置MySQL root密码
20.2. 数据库内容替换
20.3. 查看错误代码
20.3.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
20.3.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
20.4. 临时表是否需要建索引
20.5. Kill 脚本
20.6. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
20.7. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
20.8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
20.9. 重新整理AUTO_INCREMENT字段
20.10. 转换 latin1 到 UTF-8
20.11. this is incompatible with sql_mode=only_full_group_by
20.12. [Warning] Changed limits: max_open_files: 5000 (requested 20480)