MySQL常用操作

基础环境准备：

配置好基本的centos7环境

使用的MySQL为二进制免编译包安装

# 永久生效
[root@lnmp ~]# vim /etc/profile
PATH=$PATH:/usr/local/mysql/bin
#刷新参数
[root@lnmp ~]# source /etc/profile

设置密码

# 在知道或者是没有密码的情况下配置密码
# 二进制免编译安装的MySQL是没有密码的，首先设置一个密码
[root@lnmp ~]# mysqladmin -uroot password '000000'
# 重置密码
[root@lnmp ~]#  vim /etc/my.cnf
# 在mysqld下添加skip-grant这一行
[mysqld]
skip-grant  # 忽略用户认证
# 重启服务生效
[root@lnmp ~]# service mysqld restart 
# 免密登录
[root@lnmp ~]# mysql -uroot
mysql> use mysql
mysql> select * from user;
# 更新密码
mysql> update user set password=password('000000') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
[root@lnmp ~]# vim /etc/my.cnf
# 去掉skip-grant字段
# 重启生效
[root@lnmp ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
# 使用新密码登录
[root@lnmp ~]# mysql -uroot -p000000

链接MySQL

# 直接连接
[root@lnmp ~]# mysql -uroot -p000000
# 远程连接
[root@lnmp ~]# mysql -uroot -p000000 -h ip -P3306
# 和直接连接一样
[root@lnmp ~]# mysql -uroot -p000000 -S /tmp/mysql.sock
# 链接以后进行一些操作  
[root@lnmp ~]# mysql -uroot -p000000 -e "show databases;"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

MySQL常用命令

MySQL命令不区分大小写， 注意查看字母最后是否＋s

# 查询库
mysql> show databases;
# 切换库 
mysql> use mysql;
# 查看库里的表 
mysql> show tables;
# 查看表里的字段 
mysql> desc tb_name;
# 查看建表语句 
mysql> show create table tb_name\G;
# 查看当前用户 
mysql> select user();
# 查看当前使用的数据库 
mysql> select database();
# 创建库 
mysql> create database db1;
# 创建表 
mysql> use db1;
create table t1(`id` int(4), `name` char(40));
# 删除表
mysql> drop table table_name;
# 查看当前数据库版本 
mysql> select version();
# 查看数据库状态 
mysql> show status;
# 查看各参数 
mysql> show variables; 
# 查看指定参数
mysql> show variables like 'max_connect%';
# 修改参数 
mysql> set global max_connect_errors=1000;
# 查看队列 
mysql> show processlist; show full processlist;

MySQL用户管理

root用户为超级管理员，不能让所有的连接都是用root，所以需要授权
创建以一个用户，对某一个库，或者对某一个表授权。

# grant 授权
# all  所有权限  增删改查 
# on  在哪个库   *.* 表示对所有的库，所有的表
# to  对哪一个用户， 也可以指定来源IP
# identified  认证方式， 密码
grant all on *.* to 'user1' identified by 'passwd';
# 使用ip才能登陆（单个IP） 
grant all on *.* to 'user1'@'127.0.0.1' identified by 'passwd';
# 使用某个段登录
grant all on *.* to 'user1'@'192.168.200.%' identified by 'passwd';
# 使用sock登录
grant all on *.* to 'user1'@'localhost' identified by 'passwd'; 
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
grant all on db1.* to 'user3'@'%' identified by 'passwd';
# 默认查看root的授权
show grants;
# 查看某个用户的授权
show grants for user2@192.168.133.1;
# 命令历史文件
/root/.mysql_history

MySQL常用命令

如果不是想专业从事DBA可以不用学太细，
查询少使用’* ‘ 影响效率。
要掌握基本的增删改查。
删除数据需要谨慎！

 # 查看行数
 mysql> select count(*) from mysql.user;
 # 查看mysql数据库db表所有内容
 mysql> select * from mysql.db;
 # 查看 db这一个字段
 mysql> select db from mysql.db;
 # 查看 db，user这两个字段
 mysql> select db,user from mysql.db;
 # 查看 host字段 满足地址为192.168.字段的信息
 mysql> select * from mysql.db where host like '192.168.%';
 # 在db1数据库t1表插入数据
 mysql> insert into db1.t1 values (1, 'abc');
 # 更新 改
 mysql> update db1.t1 set name='aaa' where id=1;
 # 删除一行数据
 mysql> delete from db1.t1 where id=3;
 # 清空表,但是保存表结构
 mysql> truncate table db1.t1;
 # 删除表
 mysql> drop table db1.t1;
 # 删除库
 mysql> drop database db1;

MySQL备份恢复

mysqldump备份
mysql恢复
恢复过程 删除表，创建表，插入数据

 # 备份库  
 mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
 # 恢复库 
 mysql -uroot -p123456 mysql < /tmp/mysql.sql
 # 备份表 
 mysqldump -uroot -p123456 mysql user > /tmp/user.sql
 # 恢复表 
 mysql -uroot -p123456 mysql < /tmp/user.sql
 # 备份所有库 
 mysqldump -uroot -p123456 -A >/tmp/123.sql
 # 只备份表结构 
 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql