mysql 命令 ex_常用Mysql命令

查看已存在的数据库 show databases;

创建数据库并查看 create database 数据库名;

删除数据库 drop database 数据库名;

使用指定的数据库 use 数据库名;

查看数据库中的表 show tables;

创建表 create tables 表名;

查看表结构 describe 表名;

删除表 drop table 表名;

数据库备份 mysqldump --opt 数据库名>存放位置/文件名

数据库的恢复 drop database db1;->careate database db1;->mysql db1测试 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@'%';

查看软件包

rpm -qa | grep mysql

安装软件包

rpm -ivh

启动|停止|重启mysql服务

service mysqld start|stop|restart

/etc/init.d/mysqld start|stop|restart

安全后台启动mysql守护进程

safe_mysqld&

查看mysql守护进程

service msyqld status

关闭mysql

mysqladmin shutdown

不用用户名密码登录 mysql

指定root用户登录

mysql -u root

输入root密码后登录

mysql -u root -p

复制表结构

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

);