phpMyAdmin经常出问题,所以不得不学习一些命令行的操作
注
数据库版本不同,命令有差异,以下环境(Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu))
很多时候是命令输错了
命令不区分大小
mysql>内命令以;结尾,不要漏了,命令写一半,可以换行书写
建议配合phpMyAdmin一块学习
sql语句中 如果字段/数据库名/数据表名含有空格,mysql中使用重音符括起来,一般推荐不含空格也用重音符括起来
先复习下数据库的结构
备份与导入数据库
参考
直接导出所有数据库
#备份数据库
$ mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > all.sql
#导入数据库
$ mysql -u用户名 -p密码 < all.sql
注意-p后面直接跟密码,不能有空格或者不写密码,回车后输入(推荐)
-u可直接跟用户名,也可空格后跟用户名
这个是直接使用mysqldump命令导出MySQL所有数据库的命令,包括MySQL本身的一些库,例如数据库mysql(里面包含,mysql的每个用户的密码账户权限等信息,参见后面的用户管理会更清晰的理解这里)
优点:备份完整,用户名和密码也一并备份
缺点:导入之后可能会改动原有的数据库用户信息(覆盖/重复/修改密码)
--lock-all-tables参数
指定--lock-all-tables参数,那么从一开始就对整个mysql实例加global read lock锁。
这整个全局读锁会一直持续到导出结束。
所以在这个过程中,数据库实际严格处于read only状态。
所以导出的数据库在数据一致性上是被严格保证的,也就是数据是一致性的。
导出某个数据库
$ mysqldump -u用户名 -p --databases 数据库名 > name.sql
bash脚本
#!/bin/bash
USER="用户名"
PASSWORD="密码"
databases=`mysql -u$USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
mysqldump -u$USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
# gzip $OUTPUT/`date +%Y%m%d`.$db.sql
fi
done
优点:因为未导出数据库mysql不影响MySQL本身的信息,不会改动原有数据库的用户名和密码
缺点:导入之后需要手动建立用户名并赋予权限,需重新修改网站配置文件
遇到的问题
1.使用mysqldump备份时提示(errno: 24) when using LOCK TABLES
添加--lock-tables=false解决,如
mysqldump -u root -p --lock-tables=false --databases baotuquan > baotuquan.sql
Mysql常用命令
登陆
mysql -u 用户名 -p
之后就进入了mysql命令界面
mysql>
查看
查看数据库
mysql> show databases;
查看数据表
mysql> use 数据库名 ;
mysql> show tables;
查看字段
show columns from `数据库名`.`数据表名`;
查看记录,更多关于记录的操作,后面会介绍
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;
Mysql用户管理
参考
mysql数据表说明
Mysql的每个用户存储在mysql数据库中的user数据表中,也就是我们可以从这里快速更改用户名和权限,host,用户密码是以passwd()加密之后的所以不能直接更改
因此对用户的管理,基本上就是对数据库mysql的修改
以下内容引用自MySQL用户管理
MySQL授权系统主要通过五个表(user、db、host、tables_priv和columns_priv)来实现,其中用于访问数据库的各种用户信息都保存在mysql库的user表中。账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。
user表存储用户的全局权限,如登陆地址,用户名,全局不同权限,密码等数据分别对应Hosts,User,权限.priv,authentication_string等字段
db表存储用户对某数据库的权限,如登陆地址,数据库名,用户名,各个权限等数据分别对应Hosts,Db,User,权限.priv。包含在db表中的权限适用于这个表标识的数据库。
host表——当您想在db表的范围之内扩展一个条目时,就会用到这个表。举例来说,如果某个db允许通过多个主机访问的话,那么超级用户就可以让db表内将host列为空,然后用必要的主机名填充host表。
tables_priv表——该表与db表相似,不同之处是它存储用户对某表的权限。
columns_priv——该表作用几乎与db和tables_priv表一样,存储的是针对某些表特定列的权限。
权限表的存取过程是:
1) 先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中,存在则通过身份验证;
2) 通过权限验证,进行权限分配时,按照user、db、tables_priv、columns_priv的顺序进行分配。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db,tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检 查tables_priv中此数据库对应的具体表,取得表中的权限Y,以此类推。
添加用户
mysql> create user 用户名@'host地址' identified by '密码';
此时可以通过phpMyAdmin或者mysql> select Host,User from mysql.user;可查看到以添加的用户
@'host地址'缺省为@'%'所有地址,localhost代表本地地址即127.0.0.1,具体每个数据库.数据表的权限由以下决定
给用户权限
mysql> grant 权限 on 数据库.数据表 to 用户名@'host地址' identified by '密码';
mysql> flush privileges
flush privileges刷新MySQL的系统权限相关表
权限(不分大小写)
SELECT 提取/查看
INSERT 插入
UPDATE 更新/改变
等
ALL 表示所有权限
数据库/数据表不存在会报错,可以数据库.*表示整个数据库
用户地址密码
如果不存在该用户,则自动创建用户
@'host地址'缺省为@'%'所有地址
一个用户支持不同的访问地址(一个host地址只有一个密码,如果已存在密码不用输入,输入则覆盖),每执行一次命令就在mysql.user内添加相应Hsot,User,各种权限priv,密码`等字段
例:添加库.表的权限
添加netstu1.jia_netq表select权限为已有用户chen,地址为%,密码为chen默认密码
mysql> grant select on netstu1.jia_netq to chen;
可用phpMyAdmin在mysql.tables_priv里查看,或命令查看,(以后统一命令查看,不再提及phpMyAdmin,两种方法通用)
mysql> select Host,Db,User,Table_name from mysql.tables_priv where User = 'chen';
例:添加库的权限
添加netstu1.jia_netq库权限为已有用户chen,地址为%,密码为chen默认密码
mysql> grant select on netstu1.* to chen;
查看mysql.db表中
mysql> select Host,Db,User from mysql.db where User = 'chen';
例:添加全局权限
添加所有数据库的select权限为已有用户chen,地址为%,密码为chen默认密码
mysql> grant select on *.* to chen;
查看mysql.user表中
mysql> select Host,User,Select_priv from mysql.user where User = 'chen';
创建root级别账户(全局权限+授权权限)
mysql> grant all on *.* to 用户名@'localhost' identified by '密码';
之后发现,创建的用户除了给其他用户授权外有所有的权限
又
mysql> update `mysql`.`user` set `Grant_priv`='Y' where `mysql`.`user`.`Host`='localhost' AND `mysql`.`user`.`User`='用户名';
更新权限
flush privileges;
取消/修改权限
可参考修改字段,修改/删除mysql.相关数据表中的值
修改用户密码
就是更新mysql.user,修改命令参考下面的修改字段,具体命令
#使用PASSWD()函数生成密码的密文并更新到mysql数据库的密码字段
#MySQL 5.7 以前版本密码字段为Password
mysql> update mysql.user set password = password('密码') where user = '用户名' and host = 'host地址';
#MySQL 5.7 以后版本(Password字段改为了authentication_string)
mysql> update mysql.user set authentication_string=PASSWORD('密码') where user='用户名' and host = 'host地址';
更新权限
mysql> flush privileges;
删除用户
drop user zhangsan@'host地址';
drop user命令会删除用户以及对应的权限,执行命令后你会发现mysql.user表和mysql.db等表的相应记录都消失了。
mysql 找回root密码
环境
ubuntu 16.04 Mysql 5.7.19
操作
1.首先确认服务器出于安全的状态
也就是没有人能够任意地连接MySQL数据库。 因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的 状态下,其他的用户也可以任意地登录和修改MySQL的信息。
2.修改mysql登陆设置
修改配置文件,我服务器(ubuntu16.04 Mysql 5.7.19)文件在/etc/mysql/mysql.conf.d/mysqld.cnf,其他教程都说在/etc/mysql/my.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下面添加skip-grant-tables
使得:任何的帐号用任何的密码(当然也包括空)都可以登录到mysql数据库
3.重新启动mysqld
sudo /etc/init.d/mysql restart
4. 登陆并修改Mysql root 密码
就是替换mysql.user中root用户对应的密码字段
按照登陆,修改用户密码修改root密码
如
#登陆
mysql -u root
#使用mysql数据表
mysql> use mysql;
#使用PASSWD()函数生成密码的密文并更新到mysql数据库的密码字段
#MySQL 5.7 以前版本密码字段为Password
mysql> update user set Password=PASSWORD('new_password') where user='root';
#MySQL 5.7 以后版本(Password字段改为了authentication_string)
mysql> update user set authentication_string=PASSWORD('new_password') where user='root';
刷新MySQL的系统权限相关表
mysql> flush privileges;
退出
mysql> exit;
5. 修改/etc/mysql/mysql.conf.d/mysqld.cnf文件为默认内容,重启Mysql
sudo /etc/init.d/mysql restart
问题
1.新建用户时遇到密码不合适
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
将安全级别降低
set global validate_password_policy=0;
还可以设置长度
set global validate_password_length=4;
数据库管理
创建数据库
mysql视图中创建
mysql> create database 数据库名;
mysqladmin创建
$mysqladmin -u root -p create 数据库名
Enter password:
php脚本创建
语法
mysqli_query(connection,query,resultmode);
其中
connectio 为建立的mysql连接
query 查询字符串(即mysql命令,创建为create database 数据库名)
resultmode可选,默认为MYSQLI_STORE_RESULT,如果需要检索大量数据,使用MYSQLI_USE_RESULT
php脚本
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接错误: ' . mysqli_error($conn));
}
echo '连接成功
';
$sql = 'CREATE DATABASE 数据库名';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
die('创建数据库失败: ' . mysqli_error($conn));
}
echo "数据库 数据库名 创建成功\n";
mysqli_close($conn);
?>
删除数据库
mysql命令行中删除
mysql> drop database 数据库名;
mysqladmin删除
$mysqladmin -u root -p drop 数据库名
Enter password:
php脚本删除
语法
mysqli_query(connection,query,resultmode);
其中
connectio 为建立的mysql连接
query 查询字符串(即mysql命令,删除为drop database 数据库名)
resultmode可选,默认为MYSQLI_STORE_RESULT,如果需要检索大量数据,使用MYSQLI_USE_RESULT
php脚本
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接错误: ' . mysqli_error($conn));
}
echo '连接成功
';
$sql = 'DROP DATABASE 数据库名';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
die('删除数据库失败: ' . mysqli_error($conn));
}
echo "数据库 数据库名 删除成功\n";
mysqli_close($conn);
?>
选择数据库
mysql命令
mysql> use 数据库名;
php脚本
mysqli_select_db(connection,dbname);
connectio 为建立的mysql连接
dbname 为数据库名
脚本
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功';
mysqli_select_db($conn, '数据库名' );
mysqli_close($conn);
?>
数据表管理
复习数据库的结构
创建数据表时,要指明数据表中的各个字段,和不同字段的相关属性
创建数据表
mysql视图
mysql> use 数据库名 ;
Database changed
mysql> create table 数据表名(字段1 字段1的相关属性,字段2 字段2的相关属性,字段3 字段3的相关属性,更多字段和属性);
或者
mysql> create table 数据库名.数据表名(字段1 字段1的相关属性,字段2 字段2的相关属性,字段3 字段3的相关属性,更多字段和属性);
如果字段/数据库名/数据表名含有空格,使用重音符括起来即
create table `数据库名`.`数据表名`(`字段1` 字段1的相关属性,`字段2` 字段2的相关属性,`字段3` 字段3的相关属性,更多字段和属性);
推荐以重音符括起来数据库名和数据表名方式创建
以下所有涉及数据库名和数据表名的命令,都可以加重音符
例1
mysql> use cndaqiangdb ;
Database changed
mysql> create table cn_table(字段1 INT);
例2
字段名
数字类型
数据宽度
是否为空
是否主键
自动增加
默认值
id
int
4
否
primary key
auto_increment
name
char
20
否
sex
int
4
否
0
degree
double
16
是
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
php脚本,及下面内容的php脚本,先略
删除数据表
mysql> drop table 数据库名.数据表名;
或
mysql> use 数据库名;
mysql> drop table 数据表名;
修改数据表名
mysql> alter table `数据库名`.`数据表名` rename to `数据库名`.`数据表名`
字段管理
添加字段
alter table `数据库名`.`数据表名` add (`字段3` 属性,`字段4` 属性);
删除字段
alter table `数据库名`.`数据表名` drop `字段名`;
修改字段
alter table `数据库名`.`数据表名` change `旧段名` `新字段名` 修改属性;
记录/数据管理
添加记录
mysql> insert into `数据库名`.`数据表名` (`字段1`,`字段2`,`字段3`,`更多字段`) value (值1,值2,值3,更多值);
如果数据是字符型,必须使用单引号或者双引号,如:”value”。
查询记录/数据
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;
字段1,字段2为显示的字段
[ where 条件] 可选,展示符合条件的字段,如where `字段1`=`值`
[LIMIT n]和[OFFSET M]配合使用,LIMIT确定最多显示的记录数,OFFSET决定从查询到的记录数偏移几个开始显示,默认0,不偏移,从第一个显示
示例
mysql> select `字段1`,`字段2` from `cndaqiangdb`.`table1` where `字段1`=1 limit 2 offset 1;
+-------+-------+
| 字段1 | 字段2 |
+-------+-------+
| 1 | 2 |
| 1 | 2 |
+-------+-------+
2 rows in set (0.00 sec)
删除记录/数据
mysql> delete from `数据库名`.`数据表名` [where 条件];
[where 条件]如果不写,则删除数据表内的所有记录/数据
更新/修改记录
mysql> update `数据库名`.`数据表名` set `字段1`=值1,`字段2`=值2 [where 条件];
[where 条件]如果不写,则替换所有记录的相应字段
批量替换
mysql> update `数据库名`.`数据表名` set `字段`=replace(`字段`,旧值,新值);
可以替换,数字,字符串等,替换字符串时,注意加引号
示例
mysql> select `1`,`2`,`3`,`4` from `cndaqiangdb`.`table1`;
+-----+-----+-----+-----+
| 1 | 2 | 3 | 4 |
+-----+-----+-----+-----+
| 222 | 222 | 333 | 444 |
| 222 | 222 | 3 | 444 |
+-----+-----+-----+-----+
2 rows in set (0.00 sec)
mysql> update `cndaqiangdb`.`table1` set `1`=replace(`1`,2,222);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select `1`,`2`,`3`,`4` from `cndaqiangdb`.`table1`;
+-----------+-----+-----+-----+
| 1 | 2 | 3 | 4 |
+-----------+-----+-----+-----+
| 222222222 | 222 | 333 | 444 |
| 222222222 | 222 | 3 | 444 |
+-----------+-----+-----+-----+
2 rows in set (0.00 sec)
命令总结
备注
所有在mysql>后面输入的命令,都可以在phpMyAdmin的SQL选项卡中输入
create
创建用户
mysql> create user 用户名@'host地址' identifide by '密码';
创建数据库
mysql> create database `数据库名`;
创建数据表
mysql> create table `数据库名`.`数据表名` ( `字段1` 属性,`字段2` 属性);
drop
删除数据库,数据表
mysql> drop database `数据库名`;
mysql> drop table `数据库名`.`数据表名`;
use
进入数据库视图,之后本数据库名可以不输入
use `数据库名` ;
alter
数据表重命名
mysql> alter table `数据库名`.`数据表名` rename to `数据库名`.`数据表名`;
添加字段
alter table `数据库名`.`数据表名` add (`字段3` 属性,`字段4` 属性);
删除字段
alter table `数据库名`.`数据表名` drop `字段名`;
修改字段
alter table `数据库名`.`数据表名` change `旧段名` `新字段名` 修改属性;
where
之前很多操作都有where 条件
语法
where 条件1 and/or 条件2
条件子句语法
字段 操作符 数据
操作符有:
=等于
<>,!=不等于
> < >= <= 大小关系
update
修改数据
mysql> update `数据库名`.`数据表名` set `字段1`=值1,`字段2`=值2 [where 条件];
delete
删除数据
mysql> delete from `数据库名`.`数据表名` [where 条件];
insert
添加数据
mysql> insert into `数据库名`.`数据表名` (`字段1`,`字段2`,`字段3`,`更多字段`) value (值1,值2,值3,更多值);
select
查询/展示数据
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;