邵珠庆の博客 生命只有一次,你可以用它来做些更多伟大的事情–Make the world a little better and easier

1511月/114

MySQL常用命令总结

++安装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 status\G ”

++坏库扫描修复
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记数器的查询时间阀值

邵珠庆推荐文章

博文加载中...

喜欢这个文章吗?

考虑订阅我们的RSS Feed吧!

发布在 邵珠庆

评论 (4) 引用 (0)
  1. want something to eat but scared it will make me feel sick again

  2. The other posters are wrong. This is a GREAT piece. Thanks for sharing it to the interWebs. I love the second paragraph and the last couple. In my opinion, this is something that should be shared and if you have a blog, you should link to this. Thanks again.


Leave a comment

*

还没有引用.