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. Replication
8.6.1. Master Slave
8.6.1.1. Master
8.6.1.2. Slave
8.6.1.3. Testing
8.6.1.4. 將現有資料庫遷移到主從結構資料庫
8.6.1.5. 主從複製安全問題
8.6.2. Master Master(主主)
8.6.2.1. Master A
8.6.2.2. Master B
8.6.2.3. 將Master A 資料庫 同步到 Master B 兩端資料庫內容保持一致
8.6.2.4. Master A - B 同步兩端資料庫
8.6.2.5. Master A 資料庫解除只讀權限
8.6.2.6. 查看主主的工作狀態
8.6.3. Semisynchronous Replication
8.6.3.1. Master
8.6.3.2. Slave 配置
8.6.3.3. 卸載插件
8.6.3.4. my.cnf
8.6.4. multi-master replication
8.6.5. multi-source replication
8.6.6. 與複製有關的問題
8.6.6.1. 主從不同步問題
8.6.6.2. mysql-bin 清理問題
8.6.6.3. 跳過 Last_Errno
8.6.6.4. 重置Slave
8.6.7. GTID
8.6.7.1. Master
8.6.7.2. Slave
8.7. MySQL Custer
8.7.1. Management node (MGM node)
8.7.2. Data node
8.7.3. SQL node
8.7.4. Starting
8.7.5. Shutdown
8.7.6. Testing
8.8. MySQL Proxy
8.8.1. Ubuntu
8.8.2. CentOS
8.8.2.1. FAQ
8.9. MySQL Router
8.9.1. 安裝 MySQL Router
8.9.2. 配置 MySQL Router
8.9.2.1. 主備配置
8.9.2.2. 負載均衡配置
8.9.3. MySQL Router , Haproxy,LVS 的選擇
8.10. Monitoring
8.10.1. Analysis and Optimization
8.10.1.1. mytop - top like query monitor for MySQL
8.10.1.2. mtop - MySQL terminal based query monitor
8.10.1.3. innotop
8.10.1.4. mysqlreport - A friendly report of important MySQL status values
8.10.1.5. mysqltuner - MySQL configuration assistant
8.10.2. Munin
8.10.3. Cacti
8.10.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)