本文转自：http://www.duyaofei.com/mysql-%E5%91%BD%E4%BB%A4%E8%A1%8C%E5%B8%B8%E7%94%A8%E7%BB%B4%E6%8A%A4sql/

不习惯用PHPMyAdmin，总是觉得这个东西会让别人很容易的就拿走我的数据，所以没用这个。但是维护真的很繁琐，所以整理日常使用的一些SQL。然后方便复制粘贴进ssh，嘎嘎。

一、MySql添加用户

GRANT ALL PRIVILEGES ON qizhao.* TO 用户名@’%’ IDENTIFIED BY ‘密码’ WITH GRANT OPTION;

二、删除用户

DELETE FROM user WHERE User=’user_name’ and Host=’host_name’;
FLUSH PRIVILEGES;

三、MySql4与mysql5共存

启动mysql5
/usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql5 –datadir=/usr/local/mysql5/data –user=mysql –socket=/tmp/mysql5.sock –port=3307

四、显示MySQL的建表语句

show create table tablename;
alter table t add column name varchar(20) after age;
show full processlist; –可以查看完整的执行sql;

五、安装MySQL

手动编译MySQL，强烈不建议采用，呵呵，升级起来麻烦。我一般会通过apt-get来安装。

./configure –prefix=/usr/local/mysql –without-debug –without-bench –enable-thread-safe-client –enable-assembler –enable-profiling –with-mysqld-ldflags=-all-static
–with-client-ldflags=-all-static –with-charset=latin1 –with-extra-charset=utf8,gbk –with-innodb –with-csv-storage-engine –with-federated-storage-engine
–with-mysqld-user=mysql –without-embedded-server –with-server-suffix=-community –with-unix-socket-path=/tmp/mysql.sock

六、重置mysql密码

service mysql stop
mysqld_safe –skip-grant-table&
mysql
use mysql;
update user set password = password(‘test’) where user = ‘root’;
flush privileges;
service mysql stop
service mysql start

七、修改自增id的初始值

ALTER TABLE movie_url AUTO_INCREMENT=15195;

八、将日志文件归整到

登录mysql的服务器
mysql > flush logs;
mysql > reset master;

九、快速备份数据量比较大的库

mysqldum- -uroot -pwoaini123984 –opt –quick pscenter > /var/www/dbback/.pscenter20071228.sql

十、更改表的字符集

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name;

十一、更改表名

ALTER TABLE table_name RENAME TO new_table_name

十二、索引建立与删除

create unique index uidx_usgg on table_name (field1,field2,field3);
ALTER TABLE `table_name` ADD INDEX index_name ( `field1` ) ;
ALTER TABLE `table_name` ADD UNIQUE (`column`) ;
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) ;
drop index uidx_usgg on table_name ;

十三、列的修改

增加列：
alter table table_name add new_field varchar(30) not null default ”;
修改列：
alter table table_name modify column field1 varchar(4000);