ITPub博客

首页 > 数据库 > MySQL > mysql 命令,超级强悍的总结

mysql 命令,超级强悍的总结

MySQL 作者:pygnzj 时间:2013-08-16 15:19:00 0 删除 编辑

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

本文主要讲解centoslinux(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,只备份trigger7.8 导出函数funtcion和event7.9 只备份数据库的数据,不带任何表结构和函数,触发器8.0 以分表方法导出数据八、mysql慢查询日志分享工具九:binlog日志分析一:数据库启动/停止启动service mysqld start/etc/init.d/mysqld start关闭service mysqld stop/etc/init.d/mysqld startmysqladmin -u root -p shutdown二:mysql数据库修改用户密码,赋权2.1 修改mysql用户密码方法1mysqladmin -u用户名 -p'就密码' password '新密码'方法2通过修改(插入)数据库mysql中user表记录达到目的# /var/mysql5/bin/mysql -h localhost -uroot -pEnter password:不输入任何东西(新装数据库root密码为空,直接回车即可)mysql> use mysqlmysql> 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 -pEnter 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_cicreate database IF NOT EXISTS databasename default charset utf8 COLLATE utf8_general_ci;#直接创建数据库databasename,字符集是mysql默认配置指定的(取决于my.cnf的配置)create database databasename;#当数据库databasename不存在就增加,并指定字符集utf8create database IF NOT EXISTS databasename default charset utf83.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`;  ##修改字段pointALTER 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_idsALTER 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=gbkgbk为字符集编码b.修改my.cnf,在 [mysqld] 中加入default-character-set=gbkgbk为字符集编码4.2.改变某个库的编码格式:在mysql提示符后输入命令# mysql安装目录/bin/mysql -hlocalhost -uroot -pmysql>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进程列表,含详细SQLshow 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.000354MYSQL>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.000353rm -f  mysql-bin.000352如你不确定可以通过ls -lht 确认mysql数据文件夹的最新日志文件到底是那个。当然本人还是建议你使用purge binary logs 去删除比较保险哦七、常用mysql数据库备份7.1.免费工具a.直接拷贝数据文件,需要停止数据库,不适应生成系统。  直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。  为了保证数据的一致性,需要在备份文件前,执行以下 SQL 语句:  mysql>FLUSH TABLES WITH READ LOCK; (或者停止数据库,不适应生产系统。) 也就是把内存中的数据都刷新到磁盘中,同时锁定数据表, 以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单, 直接拷贝回原来的数据库目录下即可。b.对mysqlisam存储方式的,可以使用hotcopy,mysqldumpc.对innodb存储方式的,使用mysqldumpd.对同时使用innodb和mysqlisam的mysqldumpe.主从复制(当主库数据被意外或者恶意删除后,作为备份的从库也就不能用了。)7.2.商业软件IBBackup7.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##不含建库信息的mysqldumpmysqldump -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 -x7.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.sql7.7  只导出trigger,只备份triggermysqldump -h127.0.0.1 -uroot -p123456 --skip-opt --trigger -d -n -t  -B backdb > /bak/backdb_trigger7.8 导出函数funtcion和eventmysqldump -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.sql7.9 只备份数据库的数据,不带任何表结构和函数,触发器mysqldump -uroot -pdcbicc106 -n -t  -B mysql  > mydata.sql8.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.logmysqlsla -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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21870485/viewspace-1108937/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-06-11