mysql 命令大全 超强总结

分类:CentOS教程 阅读:81527 次

一:数据库启动/停止
启动

service mysqld start
/etc/init.d/mysqld start


关闭
service mysqld stop
/etc/init.d/mysqld start
mysqladmin -u root -p shutdown

二:mysql数据库修改用户密码,赋权
2.1 修改mysql用户密码

方法1
mysqladmin -u用户名 -p'就密码' password '新密码'

方法2

通过修改(插入)数据库mysql中user表记录达到目的
# /var/mysql5/bin/mysql -h localhost -uroot -p
Enter password:不输入任何东西(新装数据库root密码为空,直接回车即可)
mysql> use mysql
mysql> update user set password=password('新密码') where USER='用户名' and HOST='主机名';
根据你的需要修改sql语句的where条件,host 可以为%(任意主机),ip地址,localhost

##修改密码生效,一定要刷新权限哦
mysql> flush privileges;

mysqladmin -h localhost -uroot -ppassword reload
刷新系统权限表,这个不能少,否则不生效。

方法3
通过grant赋权命令
# /var/mysql5/bin/mysql -h localhost -uroot -p
Enter password:不输入任何东西(新装数据库root密码为空,直接回车即可)
mysql>grant 权限1,权限2,…权限n on 库.表 to 用户@主机名 identified by '用户密码';
如:
mysql> grant all on *.* to root@'%' identified by '123456'
mysql> grant all on *.* to root@localhost identified by '123456'

--------------------------------------
权限1,权限2,…权限n代表:
select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
当权限1,权限2,…权限n被all privileges或者all代替,表示赋予用户全部权限。
当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。
‘连接口令’不能为空,否则创建失败。
-------------------------------------
mysql>flush privileges;

mysqladmin -h localhost -uroot -ppassword reload
刷新系统权限表,这个不能少,否则不生效。

三:mysql常用数据库、表、字段、索引管理操作

3.1 创建数据库命令,建库

#当数据库databasename不存在就增加,并指定字符集utf8和校验字符集utf8_general_ci
create database IF NOT EXISTS databasename default charset utf8 COLLATE utf8_general_ci;

#直接创建数据库databasename,字符集是mysql默认配置指定的(取决于my.cnf的配置)
create database databasename;

#当数据库databasename不存在就增加,并指定字符集utf8
create database IF NOT EXISTS databasename default charset utf8

3.2 建表
#表存在就删除重建(无提示哦)
DROP TABLE IF EXISTS `表名`;
CREATE TABLE `表名` (
`字段值1` int(11) NOT NULL AUTO_INCREMENT,
`字段值2` varchar(30) DEFAULT NULL,
……
PRIMARY KEY (`字段值1`)
) ENGINE=存储引擎(如MyISAM,innodb) AUTO_INCREMENT=1 DEFAULT CHARSET=字符集(如utf8);

#直接建表(表存在的话会提示表已经存在)
CREATE TABLE `表名` (
`字段值1` int(11) NOT NULL AUTO_INCREMENT,
`字段值2` varchar(30) DEFAULT NULL,
……
PRIMARY KEY (`字段值1`)
) ENGINE=存储引擎(如MyISAM,innodb) AUTO_INCREMENT=1 DEFAULT CHARSET=字符集(如utf8);

如:
DROP TABLE IF EXISTS `yuanshi`;
CREATE TABLE `yuanshi` (
`goods_no` varchar(30) DEFAULT NULL,
`pid_color` varchar(30) DEFAULT NULL,
`products_no` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM AUTO_INCREMENT=724 DEFAULT CHARSET=utf8;

CREATE TABLE `yuanshi` (
`goods_no` varchar(30) DEFAULT NULL,
`pid_color` varchar(30) DEFAULT NULL,
`products_no` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=724 DEFAULT CHARSET=utf8;

3.3字段管理
##增加字段
alter table 表名 add 字段名 字段类型

##增加整数性字段
alter table table_name add filed_name int(11) DEFAULT '0';
##增加日期行字段
alter table yuanshi add up_time datetime DEFAULT NULL;
##增加微整形字段
alter table yuanshi add is_del tinyint(1) NOT NULL DEFAULT '0';
##增加字符串字段
alter table yuanshi add unit char(10) DEFAULT NULL;
alter table yuanshi add description varchar(255) DEFAULT NULL;

#在end_time字段后面增加一个字段
alter table table_name add filed_name int(11) DEFAULT '0' AFTER `end_time`;

##修改字段point
ALTER TABLE `linuxshizhan` MODIFY COLUMN `point` int(11) NOT NULL DEFAULT 0 COMMENT '积分' ;
#修改weight字段后面的这个point字段
ALTER TABLE `linuxshizhan` MODIFY COLUMN `point` int(11) NOT NULL DEFAULT 0 COMMENT '积分' AFTER `weight`;

##删除字段tag_ids
ALTER TABLE `linuxshizhan` DROP COLUMN `tag_ids`;

3.4 create index 给某个字段增加索引
语法:
create [unique | fulltext |spatial] index index_name
[using index_type]
on tbl_name (index_col_name,...)
其中:
index_name:索引的名称,索引在一个表中名称必须是唯一的。
USING index_type:部分存储引擎允许在创建索引时指定索引的类型。index_type为存储引擎支持的索引类型的名称,MySQL支持的索引类型有BTREE和HASH。如果不指定USING子句,MySQL自动创建一个BTREE索引。
index_col_name:col_name表示创建索引的列名。length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小,从而节省磁盘空间。在某些情况下,只能对列的前缀进行索引。例如,索引列的长度有一个最大上限,因此,如果索引列的长度超过了这个上限,那么就可能需要利用前缀进行索引。BLOB或TEXT列必须用前缀索引。前缀最长为255字节,但对于MyISAM和InnoDB表,前缀最长为1000字节。另外还可以规定索引按升序(ASC)还是降序(DESC)排列,默认为ASC。如果一条SELECT语句中的某列按照降序排列,那么在该列上定义一个降序索引可以加快处理速度。
UNIQUE | FULLTEXT | SPATIAL:UNIQUE表示创建的是唯一性索引;FULLTEXT表示创建全文索引;SPATIAL表示为空间索引,可以用来索引几何数据类型的列

重要提示:
CREATE INDEX语句并不能创建主键。
在一个索引的定义中包含多个列,中间用逗号隔开,但是它们要属于同一个表。这样的索引叫做复合索引。

如:
CREATE INDEX idx_firstLetter on tb_drug_base(firstLetter);

3.5 通过alter table增加索引

1.PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column` )

3.INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

5.多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

3.6 查看索引
SHOW INDEX FROM tbl_name [FROM db_name]

四:设置数据库编码(字符集)

4.1.设置整个数据库编码

a.启动mysql的时候,mysqld_safe命令行加入
--default-character-set=gbk
gbk为字符集编码

b.修改my.cnf,在 [mysqld] 中加入
default-character-set=gbk
gbk为字符集编码

4.2.改变某个库的编码格式:在mysql提示符后输入命令
# mysql安装目录/bin/mysql -hlocalhost -uroot -p
mysql>alter database 数据库名 default character set gbk;
gbk为字符集编码
显示字符集编码相关变量
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
………………………………………………
+----------------------+-----------------+
mysql>show variables like 'character_set%'
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
………………………………………………
+--------------------------+--------+
设置字符集相关变量
mysql>SET character_set_client = gbk;
mysql>SET character_set_connection = gbk;
mysql>SET character_set_results = gbk;

五、mysql常用管理命令

mysql>shell下
show databases; 显示所有数据库
show processlist; 查看mysql进程列表
show full processlist; 查看mysql进程列表,含详细SQL
show tables; 显示所有表
show variables; 显示变量
use databasename; 切换数据库
desc tablename; 查看表结构
show table status; 查看表状态
show status; 查看系统状态
show slave status; 查看从库状态
FLUSH TABLES WITH READ LOCK; 锁定数据库,一般执行备份钱或者建立主从快照前
UNLOCK TABLES; 解除锁定,一般执行完备份的时候或者建立主从快照时

mysqladmin -h localhost -uroot -ppassword processlist 查看mysql进程列表


六、mysql日志管理
mysql>flush logs;
# 按文件:删除mysql-bin.000354之前的日志,不包含mysql-bin.000354
MYSQL>purge binary logs to 'mysql-bin.000354';

Query OK, 0 rows affected (0.16 sec)

# 按时间:删除2013-08-10 00:00:00 之前的日志

MYSQL>purge binary logs before '2013-08-10 00:00:00';

# 按时间:请理三天之前的日志

MYSQL> purge master logs before date_sub(now(), interval 3 day);

自动清理日志 :

# 修改my.cnf文件配置bin-log过期时间,如日志保留7天

[mysqld]

expire-logs-days=7

当然,做了flush logs;动作后,直接到mysql数据文件夹,直接用rm -f mysql-bin.相关的文件(最新的那个日志文件不能删除哦!!!)
假设最新的日志文件是mysql-bin.000354这个,那么你就可以直接到mysql数据文件夹
rm -f mysql-bin.000353
rm -f mysql-bin.000352
如你不确定可以通过ls -lht 确认mysql数据文件夹的最新日志文件到底是那个。
当然本人还是建议你使用purge binary logs 去删除比较保险哦


七、常用mysql数据库备份
7.1.免费工具

a.直接拷贝数据文件,需要停止数据库,不适应生成系统。
直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。 为了保证数据的一致性,需要在备份文件前,执行以下 SQL 语句:
mysql>FLUSH TABLES WITH READ LOCK; (或者停止数据库,不适应生产系统。)
也就是把内存中的数据都刷新到磁盘中,同时锁定数据表, 以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单, 直接拷贝回原来的数据库目录下即可。
b.对mysqlisam存储方式的,可以使用hotcopy,mysqldump
c.对innodb存储方式的,使用mysqldump
d.对同时使用innodb和mysqlisam的mysqldump
e.主从复制(当主库数据被意外或者恶意删除后,作为备份的从库也就不能用了。)

7.2.商业软件
IBBackup

7.3 mysql数据库物理备份:直接拷贝mysql数据库文件
先停止mysql数据库
service mysqld stop

运行系统命令
# cp -r mysql数据库的data目录 备份文件存放路径
或者
# tar -cvzf 备份文件名.tar.gz mysql数据库的data目录

mysql数据库物理备份的还原:
直接把文件覆盖到相应数据库data目录即可

7.4 mysql hotcopy热备份,只针对mysiam存储引擎
MySQLHotCopy是一个perl脚本,执行时候锁定数据库表,然后使用系统命令cp或者scp做的一种备份。等备份完成的时候,才释放表锁定,并刷新日志,他是一种非常快捷的方式,但是数据备份只能在通一台机器上,只适合mysqlisam存储方式
命令
# mysql安装目录/bin/mysqlhotcopy --user=用户 --password=密码 -q "数据库名" 备份文件存放目录

还原:
直接把文件覆盖到相应数据库data目录即可


7.5 mysqldump备份
适合mysqlisam和innodb存储方式,常用于数据备份,迁移。
mysqldump -h host -u user -p pass -B 数据库名 > 数据库备份名.sql
将数据库“数据库名”中的所有表备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。
该命令备份出来的sql中带有create dabatase信息,就是说备份出来的sql在还原时会把存在的库删除重建


mysqldump -uroot -pdcbicc106 -B mysql > mm.sql

##不含建库信息的mysqldump
mysqldump -h host -u user -p pass -B 数据库名 > 数据库备份名.sql

mysqldump -uroot -pdcbicc106 -B -n mysql > mm2.sql

# mysqldump -h host -u user -p pass --opt 数据库名 表1 表2 表3……表n > 数据库备份名.sql
将数据库“数据库名”中的表1 表2 表3……表n 备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。

# mysqldump -h host -u user -p pass --databases 数据库1 数据库1 > 数据库备份名.sql
将数据库“数据库1”和“数据库2”备份到“数据库备份名.sql”文件,
“数据库备份名.sql”是一个文本文件,文件名任取。

# mysqldump -hhostname -uusername -ppassword -no-data --databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构

# mysqldump -h host -u user -p pass --opt --all-databases > all-databases.sql
将所有数据库备份到all-databases.sql文件,all-databases.sql是一个文本文件,文件名任取。)

还原MySQL数据库

# mysql -hhostname -uusername -ppassword databasename < backupfile.sql
# mysql --default-character-set=utf8 -u xxx [database] < xxx.sql

还原压缩的MySQL数据库
# gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
# gunzip < backupfile.sql.gz | mysql --default-character-set=utf8 -u xxx [database]

将数据库转移到新服务器
# mysqldump -uusername -ppassword databasename | mysql ?host=*.*.*.* -C databasename


备份有innodb存储方式的
dump参考选项:
--opt --default-character-set=utf8 \
--triggers -R --hex-blob --all-databases \
--flush-logs \
--single-transaction \
--delete-master-logs \
-x


7.6 导出表结构
mysqldump -h192.168.1.30 -uroot -123456 --default-character-set=utf8 -B backdb -d >/bak/biaojiegou.sql

针对mysql5.6使用了全局事务GTID的库,需要加--set-gtid-purged=OFF参数。否则报错
mysqldump -h192.168.1.30 -uroot -123456 --default-character-set=utf8 --set-gtid-purged=OFF -B backdb -d >/bak/biaojiegou.sql

7.7 只导出trigger,只备份trigger
mysqldump -h127.0.0.1 -uroot -p123456 --skip-opt --trigger -d -n -t -B backdb > /bak/backdb_trigger

7.8 导出函数funtcion和event
mysqldump -h192.168.1.30 -uroot -p123456 --default-character-set=utf8 -n -d -t -R -E dbname > /bak/funcation.sql
针对mysql5.6使用了全局事务GTID的库,需要加--set-gtid-purged=OFF参数。否则报错
mysqldump -h192.168.1.30 -uroot -p123456 --default-character-set=utf8 --set-gtid-purged=OFF -n -d -t -R -E dbname > /bak/funcation.sql

7.9 只备份数据库的数据,不带任何表结构和函数,触发器
mysqldump -uroot -pdcbicc106 -n -t -B mysql > mydata.sql

8.0 以分表方法导出数据,这种方式只适合备份和还原只能在mysql所在的服务器上执行哦,丙炔备份目录{BACKUPDIR} 必须有mysql运行用户(一般为mysql用户)的权限,切记切记
如果没有请chown mysql:mysql {BACKUPDIR}
分表导出适合:NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

mysqldump -uroot -p${MYSQLPASSWORD} -T${BACKUPDIR} ${DATABASENAME}
mydump -h localhost -uroot -ppassword -T 备份目录 数据库名 表名

分表方式的数据还原:
mysqlimport -h local -uroot -p`123456` backdb `find /data/backup/database/backdb/2010-02-27/ -name "*.txt"`

八、mysql慢查询日志分享工具

mysqldumpslow :perl工具,mysql 官方自带
mysqlsla :perl工具 功能强大,数据报表齐全,定制化能力强.
mysql-explain-slow-log :perl 无
mysql-log-filter: python or php 不失功能的前提下,保持输出简洁
myprofi :php工具,非常精简


这里推荐mysql慢查询日志分析工具mysqlsla

使用举例
将慢日志mysqlslow.log中执行时间最长的10条sql显示并写到sql_10.log中。
mysqlsla -lt slow -sf "+select" -top 10 mysqlslow.log >sql_10.log

将慢日志mysqlslow.log中数据库为mydb的所有select和update的慢sql,显示并将查询次数最多的100条写到sql_su100.sql中。
mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db mydb mysqlslow.log >sql_su100.log

mysqlsla -lt slow -sf "+select" -top 100 -sort c_sum mysqld_slow_query.log >sql_su100.log

九:binlog日志分析,通过mysqlbinlog工具解析写操作的sql语句,常用于mysql通过binlog日志恢复数据。
举例:
进入mysql的数据文件夹,如/var/lib/mysql/
cd /var/lib/mysql/
mysqlbinlog --start-datetime="2013-08-14 00:00:00" --stop-datetime="2013-08-14 23:59:59" mysql-bin.000098