常用Mysql命令

浏览数：33 / 时间：2015年06月12日

查看已存在的数据库 show databases;
创建数据库并查看 create database 数据库名;
删除数据库 drop database 数据库名;
使用指定的数据库 use 数据库名;
查看数据库中的表 show tables;
创建表 create tables 表名;
查看表结构 describe 表名;
删除表 drop table 表名;
数据库备份 mysqldump --opt 数据库名>存放位置/文件名
数据库的恢复 drop database db1;->careate database db1;->mysql db1</tmp/db1.bak;->测试 use db1-> select ...
设置密码 set password=password(‘123‘);
删除匿名用户 delete from user where user=‘‘;
更新授权表 flush privileges;
给用户授权的标准语法：
grant 权限 on 数据库/表 to 用户名 identified by ‘密码‘ with ‘特殊权限‘

如：grant all on *.* to yuejide@localhost identified by ‘123‘ with grant option;
授权不限定主机 grant all on *.* to root@‘%‘ identified by ‘123‘ with grant option;
授权用户指定ip登录，对所有数据库有全部的权限

grant all on *.* [email protected]%.% identified by ‘123‘;

测试用户dbau2
mysql -u dbau2 -p 123

撤销用户权限标准语法
revoke insert,delete on *.* from dbau1@localhost;
撤销指定用户的所有权限
revoke all on *.* from dbau3@‘%‘;

复制表结构
create table new_table(id int(20) not null auto_increment primary key) select * from old_table

TRUNCATE TABLE `666`

mysql -u root -p
mysqldump -u root -p omsadmin > word.sql; 导出表
mysql -u root -p ucms_2_0 < hubei.txt 执行一个命令

导出某个特定表结构和数据
mysqldump -uroot -p omsadmin evaluating_data > /home/yuejd/evaluating_data.sql;

如果导出为空可以加host
mysqldump -h 192.168.42.129 -u root -p vip bt_config > /apps/sql/bt_config.sql;

导出某个特定表结构
mysqldump -uroot -p -d omsadmin evaluating_data>/home/yuejd/evaluating_data.sql;

建索引
ALTER TABLE `log_analysis`.`wap_query_pv_week_2011_28` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `id` );
ALTER TABLE `wap_query_pv_week_2011_28` ADD INDEX ( `query` );

ALTER TABLE `offical` ADD `ww` INT( 12 ) NOT NULL AFTER `link_wap`
==================================================================================
启动mysql：
/usr/mysql/bin/mysqld_safe --user=mysql &
mysql授权:
grant all on *.* to [email protected] identified by "456";
查看授权表:
select user,host,password from mysql.user
刷新日志:
flush logs;
查看最后一条日志:
show master status;
清除binlog日志:
reset master;
查看二进制binlog日志：
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/logs/mysql-bin.000001(后面加绝对路径)
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000001(忽略编码问题)

socket登录
mysql -uroot -p -S /usr/local/mysql/3307/mysql.sock --port=3307
备份：
/usr/local/mysql/bin/mysqldump -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin -l -F > /usr/local/temp/test.sql
恢复：
/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin < /usr/local/temp/test.sql
恢复binlog日志：
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/3307/logs/binlog/mysql-bin.000020 |/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin(数据库)
mysqlbinlog后跟主要参数：
--start-position="200"
--stop-position="500"
--start-date="2012-03-29 21:20:20"
--stop-date="2012-03-29 22:00:00"
按照位置恢复：
/usr/local/mysql/bin/mysqlbinlog --stop-position="1501" /usr/local/mysql/3307/logs/binlog/mysql-bin.000021 |/usr/local/mysql/bin/mysql -uroot -p1qazxsw2 -S /usr/local/mysql/3307/mysql.sock --port=3307 testbin(后面跟数据库登录)
mysql主从同步:
配置从服务：
/etc/my.cnf的slave
server-id=2
master-host=192.168.10.1
master-user=user
master-password=pass
master-port=3306
log-bin=mysql-bin
#replicate-do-db=test
#replicate-do-table=test.t1
重新启动:
service mysqld restart
查看：
mysql>show slave status\G;
Slave_IO_Running:YES
Slave_SQL_Running:YES
造成无法同步：
Slave_SQL_Running:NO
原因：a,程序可能在slave上进行了写操作 b,也可能是slave重启后，进行了事务回滚
解决办法:
(1)
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;
(2)
查看binlog，然后实行手动同步binlog日志
mysql>slave stop;
mysql>show master status;
mysql>change master to
master-host=192.168.10.1,
master-user=user,
master-password=pass,
master-port=3306,
master_log_file="mysql-bin.000003",
master_log_pos=98;
mysql>slave start;
Mysql分区：
分区优点缺点共性
RANG分区适合于日期类型，支持复合分区有限分区一般只针对某一列

LIST分区适合于有固定取值的列，支持复合分区有限分区，插入的值
不在LIST中，则数据丢失一般只针对某一列

HASH分区线性hash使得增加、删除和分区合并线性hash的数据分布不均匀，
更快捷一般的hash比较均匀一般只针对某一列
KEY分区列为字符型等其他非Int类型效率较之前较低，因为有复
杂的函数计算一般只针对某一列
查看使用的引擎
show engines;
查看是否支持分区
show plugins;
example by hash:
create table t2(
id int)
engine=myisam
partition by hash(id)
partitions 5;
存储过程加数据
mysql>\d // 换定界符
mysql>create procedure p3()
->begin
->set @i=1;
->while @i<10000 do
->insert into t3 values(@i);
->set @i=@i+1;
->end while;
->end //

执行存储:call p3();

innodb做分区必须设置
innodb_file_per_table = 1
才可以做分区。否则索引和数据都存在ibdata1里面。
因为innodb分共享表空间和独立表空间

Mysql基础操作：
mysql表复制
结构表复制：create table t3 like t1;
数据表复制：insert into t3 select * from t1;
一条语句数据结果和数据：CREATE TABLE user1 AS SELECT * FROM `user`
只有复制数据：CREATE TABLE user1 AS SELECT * FROM `user` WHERE 1=2
取随机数
SELECT * FROM FetionSquareContent WHERE id < 420 ORDER BY RAND() LIMIT 5;
使用rollup统计出更多的信息，不可以和order by 一起使用
SELECT GroupID,COUNT(groupId) AS cnt FROM FG_GroupTopic GROUP BY GroupID WITH rollup

设置字段默认为null 这样会用到索引
and 和 or 前后面的字段都的加索引，不然索引无效
设置的是字符串索引，如果搜索整数型时，最好加上双引号，不然用不到索引

optimize:优化表空间、碎片
optimize table t4;

导入和导出数据库的某一列
导出:select name from t4 into outfile "/tmp/test.txt";
导入：load data infile "/tmp/test.txt" into table t4(name);
导入前关闭普通索引：alter table t1 disable keys;
导入后打开普通索引：alter table t1 enable keys;
关闭唯一索引: set unique_checks=0;
打开唯一索引：set unique_checks=1;

关掉分组排序提高性能
select * from t2 group by class order by null
读锁，只能读不能做其它操作
lock table t1 read;
解锁：unlock tables;
写锁：其它用户读修改删除都不能操作
lock table t1 write

日志:
vi /etc/my.cnf
log-bin=mysql-bin
log_slow_queries=slow.log
忘记密码如何修改
service mysqld stop
跳过登录
/user/local/mysql/mysqld_safe --skip-grant-tables --user=mysql &

==================2014======================
授权user用户从服务器登录权限密码为456
grant all on *.* to [email protected] identified by "456";

从192.168.10.2这台服务器登录到10.1
/usr/local/mysql/bin/mysql -uuser -p456 -h192.168.10.1
===================mysql分区========================

//RANG分区，根据ID的范围分区
CREATE TABLE `exployee`.`exployeeRANGE` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,store_id)
) ENGINE = InnoDB PARTITION BY RANGE (store_id)(
PARTITION p0 VALUES less than( 6 ) ,
PARTITION p1
VALUES less than( 11 ) ,
PARTITION p2
VALUES less than( 16 ) ,
PARTITION p3
VALUES less than( 21 )
);

//LIST分区，根据区域分区
CREATE TABLE `exployee`.`exployeeLIST` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,store_id)
) ENGINE = myisam PARTITION BY LIST (store_id)(
PARTITION pNorth VALUES in( 3,5,6,9,17 ) ,
PARTITION pEast VALUES in( 1,2,10,11,19,20 ) ,
PARTITION pWest VALUES in( 4,12,13,14,18 ) ,
PARTITION pCenter VALUES in( 7,8,15,16 )
);

//HASH分区，根据年份分区
CREATE TABLE `exployee`.`exployeeHASH` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
`fname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`lname` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`hired` DATE NOT NULL ,
`separated` DATE NOT NULL ,
`job_code` INT( 12 ) NOT NULL ,
`store_id` INT( 10 ) NOT NULL,
PRIMARY KEY(id,hired)
) ENGINE = myisam PARTITION BY HASH(YEAR(hired))(
PARTITIONS 4
);

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

常用Mysql命令