一、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
./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;
八、将日志文件归整到从001
登录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);
评论关闭。