常用Mysql命令

古城微笑少年丶 2022-06-15 23:51 151阅读 0赞

查看已存在的数据库 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 \*.\* yuejide@10.4.%.% 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 user1@192.168.10.12 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 user@192.168.10.2 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  
);