| 您的当前位置：首页 --> CentOS入门 |
| mysql 命令大全 超强总结 |
| 浏览次数:2715 关键词 （ 命令 大全 mysql ） |
|
一：数据库启动/停止 启动 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 |
| 下载次数：9 |
| 下载地址：点击下载 |
| 本资源为程序自动采集，如有侵权请联系我们移除 admin#80vps.com 来信请将#替换为@ |
| 下一条 centos系统中网络配置相关 上一条 Centos系统平均负载3个数字的含义 |
QQ:1668121212