MySQL基礎(chǔ)

zhiming.yk

===MySQL基礎(chǔ)====
mysql下載: http://www.mysql.com/
mysqld 常規(guī)MySQL服務(wù)器
mysqld-opt 優(yōu)化mysql服務(wù)器,提供一些功能可以挖掘更好的功能
mysqld-max 與mysqld一樣,但可以支持更新,更具實驗性質(zhì)的功能(更不穩(wěn)定)
++安裝mysql
參見自帶的INSTALL-SOURCE文件
./configure –prefix=/app/mysql-5.0.51a –localstatedir=/data/mysql –with-charset=utf8 –with-extra-charsets=gbk,gb2312,utf8
++查看mysql安裝編譯參數(shù)
cat $PATH/mysql/bin/mysqlbug | grep CONFIGURE_LINE
++啟動/關(guān)閉mysql
$path/mysqld_safe -user=mysql &
$/mysqladmin -p shutdown
++修改root口令
mysqladmin -u root -p password ‘新密碼’
++查看服務(wù)器狀態(tài)
$path/mysqladmin version -p
++連接遠(yuǎn)端mysql服務(wù)器
$path/mysql -u 用戶名 -p #連接本機(jī)
$path/mysql -h 遠(yuǎn)程主機(jī)IP -u 用戶名 -p#連接遠(yuǎn)程MYSQL服務(wù)器
++創(chuàng)建/刪除 數(shù)據(jù)庫或表
mysqladmin -u root -p create xxx
mysql>create database 數(shù)據(jù)庫名;
create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql>drop database [if exists] 數(shù)據(jù)庫名
mysql>create table 表名;
mysql>drop table 表名;
++復(fù)制表結(jié)構(gòu)
create table target_table like source_table
select * into NEW_TABLE from OLD_TABLE where 1=2
++查看數(shù)據(jù)庫和查看數(shù)據(jù)庫下的表
mysql>show databases;
mysql>show tables;
mysql>desc 表名; #查看具體表結(jié)構(gòu)信息
SHOW CREATE DATABASE db_name #顯示創(chuàng)建db_name庫的語句
SHOW CREATE TABLE tbl_name  #顯示創(chuàng)建tbl_name表的語句
++創(chuàng)建用戶
mysql>grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”;
mysql>grant all privileges on *.* to test1@’%’ identified by “abc”;
mysql>flush privileges;
++用戶管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密碼為111111
DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #刪除用戶帳號
SHOW GRANTS FOR user1@localhost; #顯示創(chuàng)建user1用戶的grant語句
revoke all privileges on *.* from ‘user1′@’%’ ; #回收user1對所有庫表的權(quán)限
++mysql數(shù)據(jù)庫的備份和恢復(fù)
mysqldump –all-databases -h 數(shù)據(jù)庫服務(wù)器地址 -u 用戶名 -p密碼 > xxx.sql #備份所有數(shù)據(jù)庫
mysqldump –databases 數(shù)據(jù)庫名 -h 數(shù)據(jù)庫服務(wù)器地址 -u 用戶名 -p密碼 >  xxx.sql #備份指定數(shù)據(jù)庫
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 表名> 導(dǎo)出的文件名 ##導(dǎo)出單獨的表
mysql -uroot -p xxx  xxxx.sql  #導(dǎo)出一個數(shù)據(jù)庫結(jié)構(gòu)
-d 沒有數(shù)據(jù) –add-drop-table 在每個create語句之前增加一個drop table
++忘記mysql密碼
先停止所有mysql服務(wù)進(jìn)程
mysqld_safe –skip-grant-tables & mysql
mysql>use mysql;
mysql>update user set password=password(’111111′) where user=’root’;
mysql>flush privileges;
然后重啟mysql并以新密碼登入即可
++當(dāng)前使用的數(shù)據(jù)庫
select database();
===數(shù)據(jù)庫日常操作維護(hù)====
++創(chuàng)建表
create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),
…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age int(3),career varchar(10));
# desc guest可查看表結(jié)構(gòu)信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)對應(yīng)不同的時間格式
SHOW CREATE TABLE tbl_name  #顯示創(chuàng)建tbl_name表的語句
++創(chuàng)建索引
可以在建表的時候加入index indexname (列名)創(chuàng)建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
CREATE INDEX number ON guest (number(10));
SHOW INDEX FROM tbl_name [FROM db_name] #顯示現(xiàn)有索引
mysql> repair TABLE date QUICK;  #索引列相關(guān)變量變化后自動重建索引
++查詢及常用函數(shù)
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT ……  [DISTINCT關(guān)鍵字可以除去重復(fù)的記錄]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”);   #加密密碼用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序
* 函數(shù)count,AVG,SUM,MIN,MAX,LENGTH字符長度,LTRIM去除開頭的空頭,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左邊/右邊x個字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,'Joey’, ‘Ross’); #BINARY嚴(yán)格檢查大小寫
* 比較運(yùn)算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user  GROUP by user [DESC|ASC] HAVING user=root; #分組并統(tǒng)計次數(shù)/平均值
++UNIX_TIMESTAMP(date)
返回一個Unix時間戳記(從’1970-01-01 00:00:00′GMT開始的秒數(shù))
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580);   #根據(jù)時間戳記算出日期
++控制條件函數(shù)
mysql> select if(1100,’true’,'false’);
#IF()函數(shù)有三個參數(shù),第一個是被判斷的表達(dá)式,如果表達(dá)式為真,返回第二個參數(shù),如果為假,返回第三個參數(shù).
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)4 THEN ‘NOT OK’ END AS status;
++系統(tǒng)信息函數(shù)
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一個測試mysql運(yùn)算性能工具
++將wp_posts表中post_content字段中文字”old”替換為”new”
update wp_posts set post_content=replace(post_content,’old’,'new’)
++改變表結(jié)構(gòu)
alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
這樣，就在表dbname中添加了一個字段userid，類型為int(11)。
++建表后新增主鍵
alter  table tablename add primary key(id);
++調(diào)整列順序
alter table tablename CHANGE id id int(11) first;
++修改表中數(shù)據(jù)
insert [into] table_name [(column(s))] values (expression(s))
例：mysql>insert into mydatabase values(’php’,'mysql’,'asp’,’sqlserver’,'jsp’,'oracle’);
mysql> create table user select host,user from mysql.user where 1=0;
mysql> insert into user(host,user) select host,user from mysql.user;
++更改表名
命令：rename table 原表名 to 新表名;
++表的數(shù)據(jù)更新
mysql>update table01 set field04=19991022[, field05=062218] where field01=1;
++刪除數(shù)據(jù)
mysql>delete from table01 where field01=3;
#如果想要清空表的所有紀(jì)錄,建議用truncate table tablename而不是delete from tablename.
++SHELL提示符下運(yùn)行SQL命令
mysql -e “show slave status\G ”
++壞庫掃描修復(fù)
# cd /var/lib/mysql/xxx && myisamchk table_name
myisamchk -r table_name.MYI
#mysql> repair table tablename ;
++insert into a (x) values (’11a’)
出現(xiàn): ata truncated for column ‘x’ at row 1
解決辦法:
在my.ini里找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把其中的STRICT_TRANS_TABLES,去掉,然后重啟mysql就ok了
++innodb支持事務(wù)
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
舊表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #標(biāo)記一個事務(wù)的開始
mysql> insert into…..      #數(shù)據(jù)變更
mysql> ROLLBACK或commit  #回滾或提交
mysql> SET  AUTOCOMMIT=1; #設(shè)置自動提交
mysql> select @@autocommit; #查看當(dāng)前是否自動提交
++表鎖定相關(guān)
mysql> LOCK TABLE users READ; # 對user表進(jìn)行只讀鎖定
mysql> LOCK TABLES user READ, pfolios WRITE #多表鎖控制
mysql> UNLOCK TABLES; #不需要指定鎖定表名字, MySQL會自動解除所有表鎖定
++如何清除mysql的mysql-bin日志
mysql> show master logs; #查詢musql-bin,mysql操作日志
mysql> reset master;全部刪除
mysql> purge master logs to ‘mysql-bin.00001′; #刪除,保留最新
mysql> purge master logs to “date” ;刪除除date以前的日志
#直接刪除也可以，然后記得FLUSH LOGS
#也可以修改my.cnf
–expire_logs_days=3
=====一些mysql優(yōu)化與管理======
++mysql大量的 unauthenticated user錯誤解決
編輯服務(wù)控制腳本,在啟動命令行加入–skip-name-resolve ,然后重啟MysqlD服務(wù)
++管理用命令
show variables #查看所有變量值
– max_connections 數(shù)據(jù)庫允許的最大可連接數(shù),
#需要加大max_connections可以在my.cnf中加入 max_connections=1000,可以對與下面的threads_connected值決定是否需要增大.
show status [like ....];
– threads_connected 數(shù)據(jù)庫當(dāng)前的連接線程數(shù)
#FLUSH STATUS 可以重置一些計數(shù)器
show processlist;
kill id;
++加初始啟動SQL
修改啟動腳本
MYSQL_INIT_FILE=/blog.zhangjianfeng.com/app/mysql/conf/xxx_init.sql
$bindir/mysqld_safe –datadir=$datadir –init-file=$MYSQL_INIT_FILE –pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
++my.cnf配置
[mysqld]
datadir=/jeffrey/app/data/mysql
–Enable Slow Query Log
long_query_time=3
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes
# mysqldumpslow -s c -t 20 host-slow.log  #訪問次數(shù)最多的20個sql語句
# mysqldumpslow -s r -t 20 host-slow.log  #返回記錄集最多的20個sql
–others
max_connections=500  #用過的最大連接數(shù)SHOW Status like ‘max_used_connection’;
wait_timeout=10   #終止所有空閑時間超過 10 秒的連接
table_cache=64 #任何時間打開表的總數(shù)
max_binlog_size=512M #循環(huán)之前二進(jìn)制日志的最大規(guī)模
expire_logs_days = 7 #僅保留7天的binlog
max_connect_errors = 100
query_cache_size = 256M #查詢緩存,,默認(rèn)是0
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置計數(shù)器,  FLUSH QUERY CACHE清緩存
thread_cache = 40 #線程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的話考慮加大
key_buffer = 16M #show status like ‘%key_read%’;  Key_reads 代表命中磁盤的關(guān)鍵字請求個數(shù)
#A: 到底 Key Buffer 要設(shè)定多少才夠呢? Q: MySQL 只會 Cache 索引(*.MYI)，因此參考所有 MYI文件的總大小
sort_buffer_size = 4M  #查詢排序時所能使用的緩沖區(qū)大小,每連接獨享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大
sort_buffer_sizesort_buffer_size = 6M #查詢排序時所能使用的緩沖區(qū)大小,這是每連接獨享值6M
read_buffer_size = 4M　#讀查詢操作所能使用的緩沖區(qū)大小
join_buffer_size = 8M    #聯(lián)合查詢操作所能使用的緩沖區(qū)大小
skip-locking  #取消文件系統(tǒng)的外部鎖
skip-name-resolve
thread_concurrency = 8　　# 最大并發(fā)線程數(shù),cpu數(shù)量*2
thread_cache_size = 64      # 按照內(nèi)存大小來設(shè)置, 1G=8, 2G=16, 3G=32, >3G=64
tmp_table_size = 256M  #臨時表大小
back_log = 300 #MySQL暫時停止響應(yīng)新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。
附my.cnf
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
[mysqld]
datadir=/blog.zhangjianfeng.com/data/mysql
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 128M
max_allowed_packet = 2M
table_cache = 128
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
query_cache_size= 256M
thread_concurrency = 4
max_binlog_size=512M
tmp_table_size = 256M
log_slow_queries = /blog.zhangjianfeng.com/logs/mysql-slow.log
long_query_time = 2
log-bin=mysql-bin
server-id       = 1
max_connections = 1000
back_log = 300
wait_timeout = 30
open_files_limit = 5000
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 500M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
檢查my.cnf設(shè)置是否合理的一個工具  http://www.day32.com/MySQL/tuning-primer.sh
mysqlreport  http://hackmysql.com/mysqlreport
++ 雜
* 發(fā)現(xiàn)問題：系統(tǒng)響應(yīng)慢、load avg >=5、IO wait >= 10、swap使用情況、mysql status、mysql report、mysql 5.1 profline
* xfs：如果可能，mysql數(shù)據(jù)文件系統(tǒng)分區(qū)采用xfs，效果高30％～50％
* innodb_buffer_pool_size：如果是專用的數(shù)據(jù)庫服務(wù)器，設(shè)定為內(nèi)存的80％吧
* MyIsam適合低并發(fā)、低更新、高讀取的需求，InnoDB適合高并發(fā)、高更新、高讀取的需求，MyIsam讀取的速度比InnoDB快許多。
* Explain：查詢檢查、查詢優(yōu)化
* 聯(lián)合索引：貌似MySQL一次查詢只用到一個索引，聯(lián)合索引要注意先后順序的問題，“左派”比較吃香。
* 字段按需配置，能用TinyINT的不用INT，字段越短越好，具體差別可以參見：http://chaoqun.17348.com/2008/11/mysql-data-types-int/
* 把大表拆成小表，如果表字段里面有Text，盡量拆開吧。
* 縮短事務(wù)周期
* 字符型的字段，最好采用前綴索引。
* 復(fù)雜的查詢拆成小的簡單的查詢，比如用循環(huán)查詢替代。這點不敢茍同，還是按照自己的業(yè)務(wù)測試一下吧。一條sql語句總比N條要快吧，除非有問題。
* left join：把條目數(shù)少的放左邊，如果你了解left join的話，這個是肯定的。
* 實時備份：用slave做實時備份吧，一個slave就是一個備份。
轉(zhuǎn)://http://wap.zhangjianfeng.com/index-wap2.php?p=208



本文來自ChinaUnix博客，如果查看原文請點：http://blog.chinaunix.net/u2/66227/showart_2105621.html