2018年9月27日第二十四节课笔记——MySQL常用操作

内容摘要

• 设置更改root密码
• 连接mysql
• mysql常用命令
• mysql用户管理
• 常用sql语句
• mysql数据库备份恢复
• 扩展

• 一、设置更改root密码

• 因为mysql编译安装时，命令没有加入环境变量中PATH种所以无法直接使用mysql命令。

1. 临时添加到环境变量中(可以直接使用，重启后失效）：export PATH=$PATH:/usr/local/mysql/bin/
2. 永久生效（需要source /etc/profile后才会立即生效）：echo 'export PATH=$PATH:/usr/local/mysql/bin/'  >> /etc/profile

• 首次进入数据库是不需要密码的，直接使用命令：mysql -uroot

1. 选项-u指定用户
2. root用户是mysql自带的管理员账户

• 设置用户登录密码：mysqladmin -uroot password '123456'

1. password后指定密码
2. 在实际生产环境中，密码不能过于简单
3. 密码一般会使用特殊符号，所以一般建议加上单引号。
4. 在命令运行后出现一条警告信息，意思为密码暴露在命令行下这样不安全，如图：

• 设置密码后，直接登录报错

• 在输入密码时需要加-p选项：mysql -uroot -p'123456'

1. 选项-p后不可以有空格。
2. 密码推荐加单引号，防止有特殊符号时出错。

• 不在选项-p后添加密码时，将以和用户交互的方式输入密码，如图：

• 修改用户密码：mysqladmin -uroot -p'123456' password 'zhaoyujie.123'

1. 选项-u指定要修改的用户名
2. 选项-p输入原密码
3. password后输入新的密码

• 遗忘原密码，需要重置密码时，步骤如下：

1. 修改mysql配置文件/etc/my.cnf,在[mysqld]下添加一下内容：skip-grant     （忽略授权,跳过密码）
2. 重启服务：/etc/init.d/mysqld restart
3. 无需密码即可登入mysql
4. 在mysql下切换至mysql库：user mysql；
5. 重置密码：update user set password=password('aminglinux') where user='root';
6. 退出mysql后将/etc/my.cnf文件中skip-grant配置语句删除

二、连接MySQL

mysql常用的连接方式一共有四种

• 本地连接仅指定用户及密码：mysql -uroot -p'shenxinyu.123'
• 远程连接指定IP及端口：mysql -uroot -p'shenxinyu.123' -h127.0.0.1 -P3306
• 指定sock文件连接：mysql -uroot -p'shenxinyu.123' -S/tmp/mysql.sock
• 连接数据库后，执行某个命令（常使用与shell脚本中）：mysql -uroot -p'shenxinyu.123' -e"show databases"

1. show databases列出所有的数据库

三、MySQL常用命令

• 查询库： show databases;

•  切换库： use mysql;

•  查看库里的表： show tables;

•  查看表里的字段： desc user;

•  查看建表语句： show create table user\G;

1. \G以竖排形式显示结果，更加简洁明了。

•  查看当前用户： select user();

•  查看当前使用的数据库： select databases();

• 创建库： create database db1;
• 创建表： use db1; create table t1(`id` int(4), `name` char(40));

1. t1为表的名称。
2. 其中`id`、`name`表示表里的字段,需要使用反引号。
3. int和char表示字符段的格式。
4. （4）和（40），表示最长的字符长度。

• 删除表：drop table t1；
•  查看当前数据库版本： select version();
•  查看数据库状态： show status;
•  查看各参数： show variables; show variables like 'max_connect%';

1. 其中%即mysql语句中的通配符

•  修改参数，重启后失效（在mysql配置文件中更改可以永久生效）： set global max_connect_errors=1000;
•  查看队列： show processlist; show full processlist;

四、MySQL用户管理

• 创建用户并授予全部权限：grant all on *.* to 'user1' identified by 'passwd';

1. all表示指定权限，也可以指定具体的一个或多个权限
2. *.*表示全部的库和表。第一个*即表示库，第二个即表示表。同样可以仅指定某一个库或某一个表的权限。
3. 此时登陆时需增加选项-h localhost

• 授予user2用户部分权限其中包括db1数据库下的查询，更新，插入的权限。：grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.157.1' identified by 'passwd';
• 限制可以从哪个ip登录访问：grant all on db1.* to 'user3'@'%' identified by 'passwd';

1. 百分号表示所有IP，也可以指定某个具体的IP
2. 也可以直接指定为localhost，这样在连接mysql时不需要使用-h选项和输入IP地址。

• 查看当前用户的所有授权：show grants;
• 查看指定用户的所有授权：show grants for [email protected];
• 当不知道用户密码，但需要重新授权时，可以使用show grants for [email protected];查看指定用户的授权，然后直接复制并分别修改上下两行授权的内容即可。

五、常用sql语句

• 查看某个数据库表的行数：select count(*) from mysql.user;

1. 其中mysql.user表示MySQL库的user表，count（*）表示表中有多少行。

• 查看某个数据库和表的所有数据： select * from mysql.db;
• 查询单个字段的数据：select db from mysql.db;
• 查询多个字段的数据： select db,user from mysql.db;
• 模糊查询某一些IP的数据：select * from mysql.db where host like '192.168.%';
•  插入一行数据：insert into db1.t1 values (1, 'abc');
•  更改数据：update db1.t1 set name='aaa' where id=1;

1. 匹配id=1的行，name更改为aaa

•  清空某个表的数据：truncate table db1.t1;
•  删除某个表：drop table db1.t1;
•  删除某个数据库：drop database db1;

六、MySQL数据库备份恢复

• 备份某个库：mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
• 恢复库： mysql -uroot -p123456 mysql < /tmp/mysql.sql
• 备份表： mysqldump -uroot -p123456 mysql user > /tmp/user.sql

1. 以空格分割数据库和表

• 恢复表： mysql -uroot -p123456 mysql < /tmp/user.sql

1. 恢复表的数据时，只需要指定需要恢复的数据库

• 备份所有库： mysqldump -uroot -p -A >/tmp/123.sql
• 只备份表结构： mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
• 使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份（参考网站） ：http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql

七、扩展

• mysql5.7 root密码更改 http://www.apelearn.com/bbs/thread-7289-1-1.html
• myisam 和innodb引擎对比 http://www.pureweber.com/article/myisam-vs-innodb/
• mysql 配置详解： http://blog.linuxeye.com/379.html
• mysql调优： http://www.aminglinux.com/bbs/thread-5758-1-1.html
• 同学分享的亲身mysql调优经历： http://www.apelearn.com/bbs/thread-11281-1-1.html
• SQL语句教程 http://www.runoob.com/sql/sql-tutorial.html
• 什么是事务？事务的特性有哪些？ http://blog.****.net/yenange/article/details/7556094
• 根据binlog恢复指定时间段的数据 https://blog.****.net/lilongsy/article/details/74726002
• mysql字符集调整 http://xjsunjie.blog.51cto.com/999372/1355013