Netkiller MySQL 手札
		下一頁

Netkiller MySQL 手札

MySQL MariaDB...

Mr. Neo Chan, 陳景峰(BG7NYT)

中國廣東省深圳市龍華新區民之街道溪山美地
518109
+86 13113668890
+86 755 29812080
<netkiller@msn.com>

文檔始創于2010-11-18

轉載請與作者聯繫，轉載時請務必標明文章原始出處和作者信息及本聲明。

文檔出處:

http://netkiller.sourceforge.net

http://netkiller.github.com

$Date: 2013-02-04 09:33:18 +0800 (Mon, 04 Feb 2013) $

我的系列文檔

Netkiller Architect 手札	Netkiller Developer 手札	Netkiller Testing 手札	Netkiller Cryptography 手札	Netkiller Version 手札
Netkiller Linux 手札	Netkiller Debian 手札	Netkiller CentOS 手札	Netkiller FreeBSD 手札	Netkiller Security 手札
Netkiller Web 手札	Netkiller Monitoring 手札	Netkiller Storage 手札	Netkiller Mail 手札	Netkiller Shell 手札
Netkiller Database 手札	Netkiller PostgreSQL 手札	Netkiller MySQL 手札	Netkiller NoSQL 手札	Netkiller LDAP 手札
Netkiller Installation 手札	Netkiller Cisco IOS 手札	Netkiller Intranet 手札	Netkiller Multimedia 手札	Netkiller Docbook 手札
Netkiller Management 手札	Netkiller 開源軟件手札

您可以使用iBook閲讀當前文檔

自述

1. 系列文檔
2. 作者簡介
3. 寫給讀者

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.1. my.cnf
5.1. BEFORE/AFTER
5.2. uuid()
7.1. SQL_CACHE 測試

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/

		下一頁
		自述