sql常用命令

mysql -utest -p -h 192.168.100.10      oracle的用户只有sysdba sysoper可以启动停止数据库
/etc/my.cnf   skip-grant-tables
log-bin=mysql-bin  开启bin-log日志
set password=password('123qwe');
set password for test@'%'=password('123qwe');

update mysql.user set password=password('123qwe') where use='root';
create table people(id int(10),name char(32),sex char(6))
describe people
insert into people (id,name,sex) values (1,'Rain','man'),(2,'Alex','w')
insert into a1 (id,name) select id,name from a2  表间数据复制
delete from a2 where id=3
delete from a2 where age between 20 and 30; a2中删除年纪在20到30的记录
update a2 set age=17 where id=2; 修改表数据，将年龄改为17
alter table a2 rename table2  改表名
alter table a1 modify name char(30)  修改表的字段的属性
alter table a1 change name usernam(1) after id;在id字段后添加sex字段
alter table a1 drop time;删除time字段
grant all on . to test@'%' identified by '123qwe';
grant select on redis.* to test@'%' identified by '123qwe'
show grants for test@'%'
添加权限使用grant，取消权限使用revoke
mysql -uroot -p aa>~/1.sql 备份aa数据库
mysql -uroot -p --databases aa test>2.sql 同时备份aa和test数据库
mysql -uroot -p aa<1.sql 将备份的数据导入数据库
mysqlhotcopy --flushlog -u='root' -p='456' --regexp=^a /mnt
备份以a开头的数据库到/mnt下
还原的时候直接将备份文件copy -a 到/var/lib/mysql/下
mysqlbinlog mysql-bin.00001 查看sql的bin-log日志
mysqlbinlog --start-position 264 --stop-position 341 mysql-bin.00001|mysql

start slave; start master;show slave status\G;

查看原文：http://www.chenqmc.com/?p=451