部分 I. MySQL
上一頁		下一頁

部分 I. MySQL

1. MySQL Server

1. MySQL Installation

1.1. Installation by apt-get under debian/ubuntu
1.2. Installation by source code
1.3. MySQL binary distribution
1.4. mysql-5.5.21-debian6.0-i686.deb
1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)
1.6. mysql-admin

2. my.cnf

2.1. bind-address
2.2. Configuring Database Character Encoding
2.3. max_connections
2.4. storage-engine
2.5. max_allowed_packet
2.6. skip-name-resolve
2.7. 禁用TCP/IP連結
2.8. timeout
2.9. Example for my.cnf

3. mysql log

4. Security

5. Replication

5.1. Master Slave

5.1.1. Master
5.1.2. Slave
5.1.3. Testing
5.1.4. 將現有資料庫遷移到主從結構資料庫
5.1.5. 主從複製安全問題
5.1.6. 主從不同步問題

5.2. Master Master(主主)

5.2.1. Master A
5.2.2. Master B
5.2.3. 將Master A 資料庫同步到 Master B 兩端資料庫內容保持一致
5.2.4. Master A - B 同步兩端資料庫
5.2.5. Master A 資料庫解除只讀權限
5.2.6. 查看主主的工作狀態

5.3. 與複製有關的問題

5.3.1. expire-logs-days
5.3.2. Semisynchronous Replication

6. MySQL Custer

6.1. Management node (MGM node)
6.2. Data node
6.3. SQL node
6.4. Starting
6.5. Shutdown
6.6. Testing

7. Monitoring

7.1. Analysis and Optimization

7.1.1. mytop - top like query monitor for MySQL
7.1.2. mtop - MySQL terminal based query monitor
7.1.3. innotop
7.1.4. mysqlreport

7.2. Munin

7.3. Cacti

7.4. Monitoring MySQL with SNMP

8. Import / Export (Backup and Recovery)

8.1. Export(Backup)

8.2. Import(Recovery)

8.3. xml

8.4. 備份表數據

8.5. source

8.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫

8.7. AutoMySQLBackup

8.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.

8.8.1. Percona yum Repository
8.8.2. Creating an Incremental Backup

9. Snapshot Backup

3. Client and Utility Programs

1. mysql - the MySQL command-line tool

1.1. ~/.my.cnf
1.2. 屏幕輸出到檔案
1.3. 終端編碼
1.4. Unix Socket
1.5. 重定向巧用

2. mysqldump - a database backup program

2.1. 備份資料庫，無結構，只有數據
2.2. 備份資料庫結構（不備份數據）
2.3. 使用完整的insert插入數據
2.4. --extended-insert / --skip-extended-insert
2.5. --where

3. mysqladmin - client for administering a MySQL server

3.1. reload
3.2. 更改密碼
3.3. status
3.4. process list

4. myisamchk — MyISAM Table-Maintenance Utility

5. mysqlcheck — A Table Maintenance and Repair Program

6. mysqlslap - load emulation client

7. mysqldumpslow - Parse and summarize the MySQL slow query log.

8. mysql log

4. Database Administration

1. User Account Management

1.1. Create User
1.2. Drop User
1.3. Rename User
1.4. SET PASSWORD
1.5. Reset root password

2. Access Privilege System

2.1. SHOW GRANTS
2.2. show privileges
2.3. Grant privileges
2.4. Revoke privileges
2.5. Show Privileges
2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR

3. Maintenance 資料庫維護

3.1. CHECK 檢查表
3.2. ANALYZE 分析表
3.3. CHECKSUM
3.4. OPTIMIZE 優化表
3.5. REPAIR 修復

4. SHOW COMMAND

4.1. 查看版本

4.2. status

4.2.1. show status
4.2.2. show master status
4.2.3. show slave status
4.2.4. show plugins

4.3. variables

4.3.1. wait_timeout
4.3.2. table_lock_wait_timeout
4.3.3. low_priority_updates
4.3.4. character_set
4.3.5. datadir
4.3.6. storage_engine

4.4. binary

5. DDL - Data Definition Language

1. Database

1.1. create
1.2. drop
1.3. Alter
1.4. Rename
1.5. CHARACTER
1.6. show create database

2. Table

2.1. create table ... select

2.2. modifiy table

2.3. TEMPORARY Table

2.7.1. PRIMARY KEY

2.8. COMMENT

3. Index

3.1. SHOW INDEX
3.2. CREATE INDEX
3.3. DROP INDEX
3.4. rebuild

4. 外鍵(Foreign Key)

4.1. FOREIGN KEY (RESTRICT)

5. View

6. Trigger

6.1. create trigger
6.2. drop trigger
6.3. show triggers
6.4. EXAMPLE

7. Partitioning

7.1. EXPLAIN PARTITIONS

7.2. SHOW CREATE TABLE

7.3. range

7.4. list

7.5. HASH

7.5.1. LINEAR HASH

7.6. 添加分區

7.7. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

6. SQL Statement Syntax

1. DISTINCT

2. group by

3. replace

4. OUTFILE/LOAD DATA INFILE

4.1. Export data to CSV from MySQL

5. SQL 92

6. Functions and Operators

6.1. COUNT

6.2. UUID()

6.3. String

6.3.1. LEFT/RIGHT
6.3.2. RPAD/LPAD
6.3.3. CONCAT
6.3.4. GROUP_CONCAT
6.3.5. replace

6.4. Date and Time

6.4.1. year/month/day hour:minite:second
6.4.2. Unix time
6.4.3. DATE_FORMAT
6.4.4. DATE_SUB
6.4.5. datediff / timediff

7. 事務處理和鎖定語句

7.1. 排他鎖
7.2. 表的加鎖與解鎖
7.3. 事務所用到的表

8. MySQL 專有命令

8.1. SQL_NO_CACHE

7. Optimization

1. Buffering and Caching

1.1. Query Cache SELECT Options

2. SQL

8. MySQL Connectors

1. JDBC
2. ODBC
3. MySQL native driver for PHP - mysqlnd
4. python-mysqldb

9. MySQL GUI/Web Manager

1. HeidiSQL
2. Toad for MySQL Freeware
3. phpMyAdmin - MySQL web administration tool
4. Maatkit Essential command-line utilities for MySQL

10. FAQ

1. 資料庫內容替換

2. 查看錯誤代碼

2.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
2.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

3. 臨時表是否需要建索引

4. Kill 腳本

11. HandlerSocket

12. MariaDB

13. Maatkit

1. Import / Export (Backup and Recovery)

1.1. Export(Backup)

		
mysqldump -hlocalhost -proot -p**** mydb > mydb.sql

gzip

		
mysqldump -hlocalhost -proot -p**** mydb | gzip > mydb.sql.gz

1.2. Import(Recovery)

		
mysql -hlocalhost -proot -p**** mydb < mydb.sql

gunzip

		
gunzip mydb.sql.gz -c | mysql -hlocalhost -proot -p**** mydb

1.3. xml

export xml

$ mysqldump -uusrname -ppasswd -X -t database table -r filename.xml

1.4. 備份表數據

SELECT * INTO OUTFILE 'file_name' FROM tbl_name
LOAD DATA INFILE 'file_name' REPLACE INTO TABLE tbl_name

1.5. source

		
mysql> use your_db
mysql> SOURCE database.sql

1.6. 使用 mysqlhotcopy 備份 MyISAM 引擎的資料庫

shell> mysqlhotcopy db_name /path/to/some/dir

mysql:~# mysqlhotcopy --user=neo --password=chen shop /tmp/backup
Locked 100 tables in 0 seconds.
Flushed tables (`shop`.`account_log`, `shop`.`ad`, `shop`.`ad_custom`, `shop`.`ad_position`, `shop`.`admin_action`,
`shop`.`admin_log`, `shop`.`admin_message`, `shop`.`admin_user`, `shop`.`adsense`, `shop`.`affiliate_log`,
...
...
...
`shop`.`user_rank`, `shop`.`users`, `shop`.`virtual_card`, `shop`.`volume_price`, `shop`.`vote`, `shop`.`vote_log`,
`shop`.`vote_option`, `shop`.`wholesale`) in 0 seconds.
Copying 299 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 100 tables (299 files) in 0 seconds (0 seconds overall).

1.7. AutoMySQLBackup

https://sourceforge.net/projects/automysqlbackup/

1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.

https://launchpad.net/percona-xtrabackup

1.8.1. Percona yum Repository

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

# yum search xtrabackup
================================================================ N/S Matched: XtraBackup =================================================================
holland-xtrabackup.noarch : Xtrabackup plugin for Holland
percona-xtrabackup.x86_64 : XtraBackup online backup for MySQL / InnoDB
percona-xtrabackup-debuginfo.x86_64 : Debug information for package percona-xtrabackup
percona-xtrabackup-test.x86_64 : Test suite for Percona Xtrabackup

# yum install percona-xtrabackup

1.8.2. Creating an Incremental Backup

xtrabackup --backup --target-dir=/data/backups/base --datadir=/var/lib/mysql/

上一頁		下一頁
3. 寫給讀者	起始頁	第 1 章 MySQL Server