mysqld 常规MySQL服务器
mysqld-opt 优化mysql服务器,提供一些功能可以挖掘更好的功能 mysqld-max 与mysqld一样,但可以支持更新,更具实验性质的功能(更不稳定)
- ===常用命令====
1) 安装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
2) 创建/删除 数据库或表
$ mysqladmin-u root -p create xxx
mysql>create database 数据库名;
mysql> create table table_name(column_name datatype {identity |null|not null},f_timeTIMESTAMP(8),…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)对应不同的时间格式
例: CREATE TABLE guest (name varchar(10),sexvarchar(2),age int(3),career varchar(10));
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 表名;
3) 查看数据库和查看数据库下的表
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表的语句
4) 当前使用的数据库
mysql> select database();
5) 创建索引
可以在建表的时候加入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; #索引列相关变量变化后自动重建索引
6) 查询及常用函数
mysql> select t1.name, t2.salary from employee ASt1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed fromtournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHEREcol_name > 0;
mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) ASTIME;
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,ISNOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,userfrom mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值
7) UNIX_TIMESTAMP(date)
返回一个Unix时间戳记(从’1970-01-0100:00:00′GMT开始的秒数)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #根据时间戳记算出日期
8) 控制条件函数
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;
9) 改变表结构
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)。
10) 调整列顺序
mysql> alter table tablename CHANGE id id int(11) first| after …;
11) 添加表中数据
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 frommysql.user where 1=0;
mysql> insert into user(host,user) select host,user from mysql.user;
12) 复制表结构
mysql> create table target_table like source_table
13) 复制整个表
mysql> Create table table_name as select …
14) 更改表名
命令:rename table 原表名 to 新表名;
15) 表的数据更新
mysql> update table01 set field04=19991022[, field05=062218]where field01=1;
16) 将wp_posts表中post_content字段中文字”old”替换为”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)
17) 删除数据
mysql> delete from table01 where field01=3;
#如果想要清空表的所有纪录,建议用truncatetable tablename而不是delete from tablename.
- ===数据库日常操作维护====
18) 修改root口令
$ mysqladmin -u root -p password ‘新密码’
19) 查看服务器状态
$ path/mysqladmin version –p
20) 连接远端mysql服务器
$ path/mysql -u 用户名 -p #连接本机
$ path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器
21) 创建用户
mysql> grant select,insert,update,delete,alter onmydb.* to test2@localhost identified by“abc”;
mysql> grant all privileges on *.* to test1@”%” identified by “abc”;
mysql> flush privileges;
22) 用户管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111
mysql> DELETE FROM user WHERE User=”testuser” andHost=”localhost”; #删除用户帐号
mysql> SHOW GRANTS FOR user1; #显示创建user1用户的grant语句
23) 忘记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并以新密码登入即可
24) mysql数据库的备份和恢复
$ mysqldump -uuser -ppassword -B DB_name [--tablestable1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #导入表
$ mysqldump -u 用户名 -p 数据库名表名> 导出的文件名 ##导出单独的表
25) 导出一个数据库结构
$ mysqldump -u wcnc -p -d ?add-drop-tablesmgp_apps_wcnc >wcnc_db.sql
-d 没有数据 ?add-drop-table 在每个create语句之前增加一个drop table
26) SHELL提示符下运行SQL命令
$ mysql -e “show slave status\G ”
27) 坏库扫描修复
cd /var/lib/mysql/xxx && myisamchkplaylist_block
28) 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了
29) 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; #查看当前是否自动提交
30) 表锁定相关
mysql> LOCK TABLE users READ; # 对user表进行只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
mysql> UNLOCK TABLES; #不需要指定锁定表名字, MySQL会自动解除所有表锁定
31) 系统信息函数
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) ASPERFORMANACE; #一个测试mysql运算性能工具
- =====一些mysql优化与管理======
32) 管理用命令
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;
33) 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 #用过的最大连接数SHOWStatus 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重置计数器, FLUSHQUERY 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记数器的查询时间阀值