MySQL 常用命令整理与收集专题贴

MySQL · ruyi · 于 2年前发布 · 1690 次阅读

滴水成河,方便你我!

欢迎大家陆续补充和收藏!


如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

共收到 12 条回复 MySQL 常用命令 专题
ruyi#12年前 0 个赞

启动和关闭数据库方式

mysqld_safe方式启动数据库,通过mysqld_safe  --help可以查看相关帮助
# /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf  &  

mysql脚本启动数据库
# /etc/init.d/mysqld start (需要配脚本)

mysqladmin命令关闭数据库
# mysqladmin -S  /tmp/mysql.sock  -uroot -p123456 shutdown  (通用方式)

mysql脚本关闭数据库
# /etc/init.d/mysqld stop (需要配脚本)

ruyi#22年前 0 个赞

登录和退出数据库

登录数据库(TCP连接方式)
# mysql -h127.0.0.1 -P3306 -uroot –p

登录数据库(Socket方式)
# mysql -S /tmp/mysql.sock -uroot –p

退出数据库
mysql> quit 或者 Ctrl+D快捷键
ruyi#32年前 0 个赞

用户和权限管理

查看数据库所有用户
mysql> select user,host,password from mysql.user;

创建新用户并赋权示例
mysql> grant select on *.* to 'user1'@'localhost' identified by 'password';
mysql> grant select,insert,update,delete on test.* to 'user2'@'%' identified by 'password';
mysql> grant all  on test.* to 'user3'@'192.168.10.100' identified by 'password';                                                                            mysql> grant select on *.* to 'user4'@'localhost' identified by 'password' with grant option;

刷新权限表
mysql> flush privileges;

查看某用户的权限
mysql> select * from mysql.db where user='user1'\G

查询某用户的授权语句
mysql> show grants for 'user4'@'localhost';

回收某用户的权限
mysql> rovoke delete,update on test.* from 'user1'@'%';

快速收回权限
mysql>update user set Drop_priv='N',Grant_priv='N',Super_priv='N' where user!='root';
mysql>update db set Drop_priv='N',Grant_priv='N' where user!='root'; 
mysql>flush privileges;


修改密码方式1
# mysqladmin -uroot -p123456  password  newpassword
修改密码方式2
mysql> set password for 'user1'@'%' = password('12345678');
修改密码方式3
mysql> update mysql.user set password=password('12345678') where host='%' and user='user1';

ruyi#42年前 0 个赞

数据库常用操作命令

查看所有数据库
mysql> show databases;
mysql> show schemas;

创建一个数据库
mysql> create database testdb;    
mysql> create database testdb  default character set utf8;

选择一个数据库
mysql> use testdb;

删除数据库,不提醒
mysql> drop database testdb;

ruyi#52年前 0 个赞

表操作常用命令

查看当前库的所有表
mysql> show tables;

查看表的结构信息
mysql> desc tablename;

查看表的建表语句
mysql> show create table tablename;

删除表
mysql> drop table  tablename;   
mysql> drop table if exists tablename;

清空表数据
mysql> truncate table tablename;

ruyi#62年前 0 个赞

索引管理

创建索引
Create index  idx_user_so_id on product_experience_mall(END_USER_ID,SO_ID);
Create index  idx_marchent_and_create on  PIS.T_YHD_PRODUCT( marchent_id,create_time); 
删除索引
Drop index END_USER_ID on product_experience_mall;
ruyi#72年前 0 个赞

数据备份和恢复

备份整个数据库
# mysqldump -uroot -pPassword  mydb > mydb.sql

备份数据库表结构
# mysqldump -uroot -pPassword  -d  mydb > mydb.sql

自动备份crontab
00 00 * * 5  mysqldump  -uroot -p123456 -S /tmp/mysql.sock mydb > /home/ruzuojun/dbbak/mydb_`date -d "today" +"$Y%m%d%H%M"`.sql

恢复整个数据库
# mysql -uroot -pPassword mydb < mydb.sql

带where条件查询备份
# mysqldump -uroot -pPassword -t -c mydb mytable  -w "ID=28169531" > data.sql

备份数据为文本文件
mysql>select * from table into outfile '/tmp/data.txt' fields terminated by ',';

备份数据为CSV文件
mysql>select * from GC_Crawler into outfile '/tmp/GC_Crawler.csv'  FIELDS TERMINATED BY ',' ENCLOSED BY '\"'   ESCAPED BY '\\'  LINES TERMINATED BY '\n';

恢复CSV到数据库(load方式)
mysql>load data infile '/tmp/GC_Crawler.csv' into table GC_Crawler FIELDS TERMINATED BY ',' ENCLOSED BY '\"'   ESCAPED BY '\\'  LINES TERMINATED BY '\n';

ruyi#82年前 0 个赞

binlog维护管理和自动清理

查看主库binlog日志信息
mysql> show binary logs;

删除某个文件之前的binlog日志(主库)
mysql> purge binary log to 'mysql-bin.000116';   

删除某个时间之前的binlog日志(主库)
mysql> purge binary logs before '2013-08-01 00:00:00';

binlog_cache_size
当使用事务的表存储引擎(如InnoDB存储引擎)时,所有未提交(uncommitted)的二进制日志会被记录到一个缓存中,等该事务提交时(committed)时直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由binlog_cache_size决定,默认大小为32KB。此外,binlog_cache_size是基于会话(session)的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小为binlog_cache_size的缓存,因此该值的设置需要相当小心,不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。

Row模式log解析成语句
# mysqlbinlog -vv --start-datetime="2014-06-21 07:00:00" --stop-datetime="2014-06-21 10:00:00" mysql-bin.001865 > log.sql


自动清理脚本
$ vim /data/mysql/sh/purgelogs.sh

#!/bin/sh
/data/mysql/bin/mysql –h127.0.0.1 –P3306 -uroot -pxxx -e 'PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 Day);'

$ crontab -l
01 01 * * sh /data/mysql/sh/purgelogs.sh > /data/mysql/sh/purgelog.log 2>&1
ruyi#92年前 0 个赞

字符集设置

查看数据库字符集
mysql> show variables like '%character%';

在配置文件里面修改字符集
#vim my.cnf
  在[client]下添加
  default-character-set=utf8
  在[mysqld]下添加
  default-character-set=utf8
重启数据库

创建数据库时指定当前数据库的字符集
mysql> create database dili character set utf8;

更改数据库的字符集
mysql> alter database dili character set utf8;

在命令窗口里更改客户端字符集
mysql> set character_set_client = utf8 ;
mysql> set character_set_connection = utf8 ;
mysql> set names utf8;

ruyi#102年前 0 个赞

进程管理

查看mysql系统进程是否存在
# ps -ef|grep mysql

查看mysql占有的网络端口
# netstat -nutpl |grep mysql

查看mysql数据库进程
mysql> show processlist;  
mysql> show full processlist;
查看mysql数据库进程(shell里面)
# mysql -uroot -pPassword -e "show full processlist" |grep -v "Sleep"

杀掉进程
mysql > kill 10000;

ruyi#112年前 0 个赞

查看执行计划

explain查看执行计划和索引使用情况
mysql> explain select * from gos_so_log_alert where id is not null;

开启profile查看SQL执行资源消耗时间
mysql> set profiling=1;
mysql> show variables like 'profiling';
mysql> select count(*) from gos_so_log_alert where id is not null; 
mysql> show profiles;
mysql> show profile for query Query_ID;
ruyi#1211个月前 0 个赞

导入txt文本到MySQL

mysql> load data infile '/tmp/result-0802.txt' INTO TABLE report_zabbix  fields terminated by ',' lines terminated by '\n';
Query OK, 13474 rows affected, 26948 warnings (0.14 sec)
Records: 13474  Deleted: 0  Skipped: 0  Warnings: 26948
回复本帖 (需要登录)