001_MySQL 常用操作

MySQL 常用操作

1. MySQL 初始配置

1.1 修改环境变量

添加mysql到环境变量中去

vim /etc/profile
##### 增加
export PATH=$PATH:/usr/local/mysql/bin
##### 刷新环境变量
source /etc/profile

1.2 创建、设置、重置密码

1.2.1 创建、设置密码

• MySQL 5.6 版本未生成初始密码，有初始密码的直接修改

#### 设置密码
mysqladmin -uroot password '123456' //这里123456不填，稍后会弹出密码输入框输
#### 修改密码
mysqladmin -uroot -p'123456' password '123abc'
#### 登录MySQL
mysql -uroot -p  //`-u`指定用户、`-p`指定密码

• MySQL 5.7 及以后的版本root有默认密码，必须重设密码后，才能进行mysql的操作，以下是设置操作步骤：

#### 查看默认密码
cat /root/.mysql_secret
# The random password set for the root userat Fri Jan 10 20:00:34 2014 (local time): aJqZsA2m
#### 这里 aJqZsA2m 就是系统生成的随机密码

#### 登录MySQL
mysql -u root -p
#### 更改密码
SET PASSWORD  FOR 'root'@localhost = PASSWORD ('123456');

1.2.2 密码重置

vim /etc/my.cnf
##### [mysqld]下增加：
skip-grant
#### 重启mysql服务
/etc/init.d/mysqld restart
#### 用root用户登录mysql，更新user表中的密码
mysql -uroot
use mysql;
update user set password=password('aminglinux') where user='root';
#### 删除/etc/my.cnf中添加的skip-grant 参数
####重启mysql服务
/etc/init.d/mysqld restart

1.3 连接 MySQL

#### 指定用户、密码连接（-u 指定用户、-p 指定密码）
mysql -uroot -p'123456'
#### 指定IP、端口连接（-h 指定来源IP -P 指定端口）
mysql -uroot -p123456 -h127.0.0.1 -P3306
#### 指定 socket 文件连接（ -S 指定socket文件）
mysql -uroot -p123456 -S/tmp/mysql.sock
#### 指定登录后任务（-e 指定登录后执行的命令，show databases 查看库）
mysql -uroot -p123456 -e "show databases"

2. MySQL 常用命令

命令	注释	命令	注释
select version();	查看当前数据库版本	use mysql;	切换库（选择默认库，可以不用;结尾）
show databases;	查询库	show tables;	查看库里的表
desc tb_name;	查看表里的字段	show create table tb_name\G;	查看建表语句
select user();	查看当前用户	select database();	查看当前使用的数据库
create database db1;	创建库	create table t1(`id` int(4), `name` char(40));	创建表（use db1;需先选择库）
show status;	查看数据库状态	show variables like 'max_connect%';	查询某个参数‘max_connect%’
show variables	列出所有参数信息	set global max_connect_errors=1000;	临时修改参数（永久生效修改my.cnf）
show processlist;	查看队列	show full processlist;	查看队列（显示内容更完整）

3. MySQL 创建用户以及授权

3.1 创建用户&授权

• grant all on *.* to 'user1'@'%' identified by 'passwd';
all 所有操作权限，第一个 *表示所有的库，第二个*表示所有的表，user为创建的用户，%所有终端（@'%'不指定，默认%），passwd为用户密码

• grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
  user2 权限 （select``update``insert，db1的所有表，来源IP：192.168.133.1，密码：passwd）

• grant all on db1.* to 'user3'@'%' identified by 'passwd';
  user3 权限（所有操作权限，db1库的所有表，所有终端，密码：passwd

3.2 查看用户授权

• 查看当前用户的授权：show grants;

• 查询特定用户的授权，可用于复制用户 show grants for user2@192.168.133.1;

4. 常用 SQL 语句

4.1 查看表的行数

select count(*) from mysql.user;

• MyISAM 保存了表的具体行数
• InnoDB 没有保存表的具体行数，需要逐行扫描统计，速度就会慢

4.2 查看表的所有内容

select * from mysql.db;

4.3 查询表的某列内容

• 查询一列内容
  select db from mysql.db;

• 查询多列内容，多列用,分隔
  select db,user from mysql.db;

4.4 按条件查询表内容

select * from mysql.db where host like '192.168.%';

4.5 表插入内容（写表）

insert into db1.t1 values (1, 'abc');i

4.6 更新表内容

update db1.t1 set name='aaa' where id=1;

4.7 清空表

清空表内容，表结构不变
truncate table db1.t1;

4.8 删除表、删除库

• 删除表：drop table db1.t1;
• 删除库：drop database db1;

5. MySQL 数据库的备份与恢复

5.1 备份库

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

5.2 恢复库

mysql -uroot -p123456 mysql < /tmp/mysql.sql

5.3 备份表

mysqldump -uroot -p123456 mysql user > /tmp/user.sql

5.4 恢复表

mysql -uroot -p123456 mysql < /tmp/user.sql

5.5 备份所有库

mysqldump -uroot -p -A > /tmp/123.sql

5.6 只备份表结构

mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql