mysql 命令，前无古人后无来者，超强总结-CSDN博客

mysql 命令，前无古人后无来者，超强总结,罗列了从常用建库建表增删字段，mysqldump备份，管理索引，核心参数设置

本文主要讲解centos linux(redhat linux )下的 mysql命令
如果你的mysql是源码安装，我假设你已经把“mysql安装目录/bin“加到了path环境变量中，并且mysql的启动脚本已经复制到了/etc/init.d目录下并且有执行权限
一：数据库启动/停止
 二：mysql数据库修改用户密码，赋权
 三：mysql常用数据库、表、字段、索引管理操作
 3.1 创建数据库命令，建库
 3.2 建表
 3.3字段管理
 3.4 create index 给某个字段增加索引
 3.5 通过alter table增加索引
 3.6 查看索引
 四：设置数据库编码（字符集）
4.1.设置整个数据库编码
 4.2.改变某个库的编码格式
 五、mysql常用管理命令
 六、mysql日志管理
 七、常用mysql数据库备份
 7.1.免费工具
 7.2.商业软件
 7.3 mysql数据库物理备份
 7.4 mysql hotcopy热备份
 7.5 mysqldump备份
 7.6 导出表结构
 7.7 只导出trigger，只备份trigger
7.8 导出函数funtcion和event
7.9 只备份数据库的数据，不带任何表结构和函数，触发器
 8.0 以分表方法导出数据
 八、mysql慢查询日志分享工具
 九：binlog日志分析
 一：数据库启动/停止
启动
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