mysqld 常規MySQL服務器
mysqld-opt 優化mysql服務器,提供一些功能可以挖掘更好的功能
mysqld-max 與mysqld一樣,但可以支持更新,更具實驗性質的功能(更不穩定)
++安裝mysql
參見自帶的INSTALL-SOURCE文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8 ?with-extra-charsets=utf8,gb2312,utf8
++啟動/關閉mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown
++修改root口令
$ mysqladmin -u root -p password ‘新密碼’
++查看服務器狀態
$ path/mysqladmin version -p
++連接遠端mysql服務器
$ path/mysql -u 用戶名 -p #連接本機
$ path/mysql -h 遠程主機IP -u 用戶名 -p#連接遠程MYSQL服務器
++創建/刪除 數據庫或表
$ mysqladmin -u root -p create xxx
mysql> create database 數據庫名;
mysql> 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] 數據庫名
mysql> create table 表名;
mysql> drop table 表名;
++查看數據庫和查看數據庫下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #查看具體表結構信息
mysql> SHOW CREATE DATABASE db_name #顯示創建db_name庫的語句
mysql> SHOW CREATE TABLE tbl_name #顯示創建tbl_name表的語句
++創建用戶
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
mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #刪除用戶帳號
mysql> SHOW GRANTS FOR user1; #顯示創建user1用戶的grant語句
++mysql數據庫的備份和恢復
$ mysqldump -uuser -ppassword -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #導入表
$ mysqldump -u 用戶名 -p 數據庫名 表名> 導出的文件名 ##導出單獨的表
++導出一個數據庫結構
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc >wcnc_db.sql
-d 沒有數據 ?add-drop-table 在每個create語句之前增加一個drop table
++忘記mysql密碼
先停止所有mysql服務進程
$ mysqld_safe ?skip-grant-tables & mysql
mysql> use mysql;
mysql> update user set password=password(’111111′) where user=’root’;
mysql> flush privileges;
然後重啟mysql並以新密碼登入即可
++當前使用的數據庫
mysql> select database();
===數據庫日常操作維護====
++創建表
mysql> 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可查看表結構信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)對應不同的時間格式
mysql> SHOW CREATE TABLE tbl_name #顯示創建tbl_name表的語句
++創建索引
可以在建表的時候加入index indexname (列名)創建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
mysql> CREATE INDEX number ON guest (number(10));
mysql> SHOW INDEX FROM tbl_name [FROM db_name] #顯示現有索引
mysql> repair TABLE date QUICK; #索引列相關變量變化後自動重建索引
++查詢及常用函數
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關鍵字可以除去重復的記錄]
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正序
* 函數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嚴格檢查大小寫
* 比較運算符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; #分組並統計次數/平均值
++UNIX_TIMESTAMP(date)
返回一個Unix時間戳記(從’1970-01-01 00:00:00′GMT開始的秒數)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #根據時間戳記算出日期
++控制條件函數
mysql> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函數有三個參數,第一個是被判斷的表達式,如果表達式為真,返回第二個參數,如果為假,返回第三個參數.
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;
++系統信息函數
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一個測試mysql運算性能工具
++將wp_posts表中post_content字段中文字”old”替換為”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)
++改變表結構
mysql> 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)。
++調整列順序
mysql> alter table tablename CHANGE id id int(11) first;
++修改表中數據
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 新表名;
++表的數據更新
mysql> update table01 set field04=19991022[, field05=062218] where field01=1;
++刪除數據
mysql> delete from table01 where field01=3;
#如果想要清空表的所有紀錄,建議用truncate table tablename而不是delete from tablename.
++SHELL提示符下運行SQL命令
$ mysql -e “show slave statusG ”
++壞庫掃描修復
cd /var/lib/mysql/xxx && myisamchk playlist_block
++insert into a (x) values (’11a’)
出現: 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了
++復制表
mysql> create table target_table like source_table
++innodb支持事務
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
舊表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #標記一個事務的開始
mysql> insert into….. #數據變更
mysql> ROLLBACK或commit #回滾或提交
mysql> SET AUTOCOMMIT=1; #設置自動提交
mysql> select @@autocommit; #查看當前是否自動提交
++表鎖定相關
mysql> LOCK TABLE users READ; # 對user表進行只讀鎖定
mysql> LOCK TABLES user READ, pfolios WRITE #多表鎖控制
mysql> UNLOCK TABLES; #不需要指定鎖定表名字, MySQL會自動解除所有表鎖定
=====一些mysql優化與管理======
++管理用命令
mysql> show variables #查看所有變量值
? max_connections 數據庫允許的最大可連接數,
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000,可以對與下面的threads_connected值決定是否需要增大.
show status [like ....];
? threads_connected 數據庫當前的連接線程數
#FLUSH STATUS 可以重置一些計數器
show processlist;
kill id;
++my.cnf配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes
# mysqldumpslow -s c -t 20 host-slow.log #訪問次數最多的20個sql語句
# mysqldumpslow -s r -t 20 host-slow.log #返回記錄集最多的20個sql
?others
max_connections=500 #用過的最大連接數SHOW Status like ‘max_used_connection’;
wait_timeout=10 #終止所有空閒時間超過 10 秒的連接
table_cache=64 #任何時間打開表的總數
ax_binlog_size=512M #循環之前二進制日志的最大規模
max_connect_errors = 100
query_cache_size = 256M #查詢緩存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置計數器, FLUSH QUERY CACHE清緩存
thread_cache = 40
#線程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的話考慮加大
key_buffer = 16M
#show status like ‘%key_read%’; Key_reads 代表命中磁盤的關鍵字請求個數
#A: 到底 Key Buffer 要設定多少才夠呢? Q: MySQL 只會 Cache 索引(*.MYI)，因此參考所有 MYI文件的總大小
sort_buffer_size = 4M #查詢排序時所能使用的緩沖區大小,每連接獨享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大
sort_buffer_sizesort_buffer_size = 6M #查詢排序時所能使用的緩沖區大小,這是每連接獨享值6M
read_buffer_size = 4M #讀查詢操作所能使用的緩沖區大小
join_buffer_size = 8M #聯合查詢操作所能使用的緩沖區大小
skip-locking #取消文件系統的外部鎖
skip-name-resolve
thread_concurrency = 8 #最大並發線程數,cpu數量*2
long_query_time = 10 #Slow_queries記數器的查詢時間閥值
