MySQL表管理
MySQL数据导入
把系统文件的内容存储到数据库的表里
/etc/passwd studb.user
用户名密码占位符uidgid描述信息家目录shell
creat database studb；
create table studb.user（
name varchar（50），
password char(1),
uid int(2），
gid int(2),
comment varchar(100),
homedir char(100),
shll char(25),
index(name),
);
导入数据格式：
msyql> load data infile "目录/文件名" into table 库.表名 fields terminated by “字段间隔符号” lines terminated by “\n”；
查看默认使用目录及目录是否存在
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
将数据信息拷贝进默认使用目录
cp /etc/passwd /var/lib/mysql-files/
ls /var/lib/mysql-files/
load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines treminated by "\n";
alter table studb.user add> 修改目录及查看修改结果
mkdir /myfile
chown mysql /myfile 赋予权限,让所有者变为mysql
vim /etc/my.cnf 修改配置文件，默认使用目录
secure_file_priv="/myfile"
数据导出：把表记录存储到系统文件里
into outfile “目录名/文件名”
eg：
mysql> select name，uid from user into outfile “/myfile/user1.txt”
mysql> select name，uid from user into outfile “/myfile/user2.txt” fields terminated by "#" 让导出文件间设立间隔号#
mysql> select name，uid from user limit 5 into outfile “/myfile/user3.txt”前五行
mysql> select> 管理表记录
增
insert into 库.表 values（字段值列表）；
insert into 库.表 values（字段值列表），（字段值列表）
查
select 字段名列表 from 库.表；
select 字段名列表 from 库.表 where 条件；
eg：select * from user where name=“mysql”；查找user表中所有name=mysql的记录
单表查询
条件匹配的表示方式：
数值比较 > >= < 范围内比较
字段名 in （值列表） 在。。。。里
select>
select> 字段名 between 值1 and 值2 在。。。之间
selectfrom user where>
字段名 not in （列表置）不再。。。里
select name from user where uid not in（0，1，7，8）；
selectfrom user where name not in（“root”，“mysql“，”bin“）；
匹配空 is null
匹配非空 is not null
select>
select> insert into user（name）values（“”），（“null”），（null）；
select>
select> distinct 不显示重复值
select distinct shell from user；
逻辑匹配：有多个条件
逻辑与 and 多个条件必须都成立
逻辑或 or 多个条件有一个条件成立即可
逻辑非 ！ 取反
select name from user where name=“zhangfei” and uid=500 and shell=“/bin/bash”；
select name from user where name=“zhangfei” or uid=500 or shell=“/bin/bash”；
运算
select （uid，gid）uid+gid as（可省略） he（自定义命名） from user where name=“root”
模糊查询like
where 字段名 like ‘表达式’；
匹配单个字符
% 匹配多个字符
eg：select name from user where name like ‘’；
select name from user where name like ‘%’；
select name from user where name like ‘a’;
正则匹配
eg
insert into user（name）values（“bob9”)，(“j7im”)，(“1yaya”）；
select name from user where name regexp ‘’;
select name from user where name regexp ‘^’;
select name from user where uid regexp ‘..’; uid2位及以上
select name,uid from user where name regexp '^a.*t';
select name,uid from user where name regexp '^r|t$';以r开头或以t结尾
常用的统计函数
-avg（）：集合的平均值
-sum（）：对集合中的各参数求和
-min（）：集合中的最小值
-max（）：集合中的最大值
-count（）：记录的个数
select count（name） from user where shell="/bin/bash";
select max(uid) from user;
select min(gid) from user;
select avg() from user;
select sum() from user;
查询排序
sql查询 order by 字段名 asc/desc；（升序/降序，默认升序）
select name,uid from user where uid between 10 and 50;
select name,uid from user where uid between 10 and 50 order by uid;
查询分组
sql查询 group by
select shell from user group by shell;
限制查询显示行数limit
sql查询 limit 数字；显示查询结果的前几行
sql查询 limit 数字1，数字2；设置显示行的范围
select from user;
selectfrom user limit 2;
select * from user limit 2 2; 显示第2行后的2行,即(3,4两行)
复制表：快速备份
create table yyy select * from xxx； 将源表xxx复制为新表yyy（键值无法复制）
create table zzz
create database dbbak;
create table dbbak.user2 select from studb.user;
create table dbbak.user3 selectfrom studb.user where 1=2;
create table dbbak.user4 select name,uid from studb.user limit 3;
多表查询
select 字段名列表 from 表名列表； 迪卡尔集
select 字段名列表 from 表名列表 where 条件；
create table studb.t1 select name,uid,shell from user limit 3;
create table studb.t2 select name,uid,homedir from user limit 4;
show tables
select from t1;seletct from t2; 迪卡尔集会出现12行
select from t1,t2 where t1.uid=t2.uid and t1,name=t2.name;
select t1.,t2.homedir from t1,t2 where t1.uid=t2.uid and t1.name=t2.name;
嵌套查询
select name from user where name not in(select user from mysql.user);
select name from user where name not in(select user from mysql.user where user="zhangsan");
连接查询
左连接查询
select 字段名列表 from 表A left join 表B on 条件；
右连接查询
select 字段名列表 from 表A right join 表B on 条件；
create table studb.t3 select name,uid,shell from user limit 3;
create table studb.t4 select name,uid,shell from user limit 5;
select * from t3 left join t4 on t3.uid=t4.uid;
改
修改表记录字段的值
update 库.表 set 字段名=值 where 条件；
删
以行为删除单位
delete from 库.名 where 条件； 删除指定行
数据库管理员root用户密码的设置
mysqladmin -hlocalhost -uroot -p password "新密码"
恢复MySQL管理密码（忘记密码时,管理员有权限更改）
#vim /etc/my.cnf
......
skip-grant-tables 跳过权限
#systemctl restart mysqld
#mysql
mysql> update mysql.user set authentication_string=password("888888")
-> where user="root" and host="localhost";
mysql.user表内有用户登陆的信息密码记录，可更改表内记录改密码
mysql> flush privileges; 刷新
退出mysql后再修改配置文件，删除跳过权限
用户授权 grant
在数据库服务器添加新的连接用户
mysql> grant 权限列表 on 库名.表名 to 用户@"客户端地址"> grant all on .
当库名.表名 为.时，匹配所有库所有表
授权设置放在mysql库的user表
grant all on . to root@"192.168.4.12"> 数据库服务器IP为192.168.4.11
新建虚拟机mysql12，IP为192.168.4.12
客户端测试授权：
#which mysql
#yum -y install mariadb
mysql>-h数据库服务器的IP地址 -u用户名 -p密码
mysql> select @@hostname； 查看当前登陆的数据服务器
mysql> select user（）； 查看当前正在访问的终端
mysql> show grants； 查看自己的权限
允许从网站服务器上使用bbsuser用户连接，密码是123456，只对bbsdb库下的所有表有完全权限，没有授权权限
grant all on bbsdb.* to bbsuser@"192.168.4.30"> 只有192.168.4.30可使用用户bbsuser登陆，在数据库中只能对bbsdb库进行增删改。
grant select on . to admin@"localhost"> 本机用户admin只拥有读权限
管理员查看其他用户权限
show grants for 用户名@客户端地址
权限撤销 revoke
mysql> revoke 权限列表 on 库名.表名 from 用户名@客户端地址；
撤销记录信息
revoke delete,update on . from 用户名@客户端地址；
删除授权用户drop user 用户名@客户端地址
use mysql;
show tables;
user 已有授权用户信息
db 授权用户对库的访问权限
数据备份
1 为什么要备份数据？
数据丢失或误删除时，使用备份文件恢复数据。
2 数据备份方式？
物理备份？ 备份库或表对应文件
cp-r /var/lib/mysql/mysql /opt/mysql.bak
cp /var/lib/mysql/mysql/user.* /opt/
tar-zcvf/opt/mysql.tar.gz /var/lib/mysql/mysql/*
164cp-r /mydata/mysql.bak//var/lib/mysql/mysql
165chown -R mysql:mysql /var/lib/mysql/mysql
166systemctlrestart mysqld
逻辑备份?备份时根据已有的库表及记录生成对应的sql命令，把
sql保存到指定的备份文件里
3数据备份策略？
完全备份 备份所有数据（一台服务器 一个库 一张表）
差异备份 备份自完全备份后所有新产生
增量备份 备份自上一次备份后所有新产生
完全备份+差异备份
完全备份+增量备份
4在生成环境下如何实现数据备份
周期性计划任务 执行 备份脚本
0018 1 sh/shell/allbak.sh
5 数据备份时要考虑因素？
备份方式逻辑备份
备份策略 ？完全 差异增量
数据备份频率？1 小时 1天 1周
数据备份的时间？数据访问量小的时候执行备份
存储空间可扩展？LV
备份文件命名要有标识性? 使用日期做备份文件名
完全备份
#mysqldump-hlocalhost -uroot -p123qqq 数据库名
目录名/名.sql
数据库名的表示方式？
--all-databases备份一台服务上的所有数据
数据库名 备份一个库里的所有表
数据库名表名 备份一张表里的所有数据
-B 数据库名1 数据库名2数据库名N 备份某几个库的所有数据
#mkdir/databak
#mysqldump -uroot -p123qqq userdb >
/databak/userdb.sql
#mysqldump -uroot -p123qqq teadb >
/databak/teadb.sql
完全恢复
#mysql-hlocalhost-uroot-p123qqq 数据库名<目录
名/名.sql
mysql>drop databaseteadb;
mysql>create databaseteadb;
#mysql -uroot -p123qqq teadb < /databak/teadb.sql
mysql> use teadb ; show tables;
#crontab-e
0018 1 /opt/teadbbak.sh &> /dev/null
0018 2-7/opt/baknewbinlogfile.sh
vim /opt/baknewbinlogfile.sh
#!/bin/bash
备份每天新生成的binlog日志文件且正在使用的binlog日志文
件不备份
:wq
vim /opt/teadbbak.sh
#!/bin/bash
if [!-e /databak];then
mkdir /databak
fi
day=date+%F
mysqldump -uroot-p123qqq--flush-logsteadb >
/databak/teadb-${day}.sql
:wq
chmod+x/opt/teadbbak.sh
只使用完全备份策略备份数据的缺点：
a 使用完全备份文件恢复数据时只能把数据恢复到备份时的状态
，完全备份新产生的数据无法恢复。
b 备份和恢复数据时都会对表加写锁。
+++++++++++++++++++++++++++++++++++二
、增量备份（启用mysql服务binlog日志做时时增量备份、安装
第3方软件提供增量备份命令做备份）
2.1启用mysql服务binlog日志做时时增量备份
binlog日志又叫二进制日志，是mysql数据服务日志文件的
一种，记录客户端连接数据库服务后，执行的除查询之外的sql
命令。
mysql-hx.x.x.x -uroot-p123456
mysql>select desc showtables
mysql> create insert update delete grant revoke
启用binlog日志
mysql > show variables like "binlog_format";
vim/etc/my.cnf
server_id=12
log_bin
binlog_format="mixed"
:wq
#systemctl restartmysqld
mysql > show variables like "binlog_format";
ls/var/lib/mysql/主机名-bin.000001 500M+
ls /var/lib/mysql/localhost-bin.index 索引文件
查看binlog日志文件内容
#mysqlbinlog/var/lib/mysql/localhost-bin.000001
binlog日志文件记录sql命令的方式？
时间点
--start-datetime="yyyy-mm-ddhh:mm:ss"
--stop-datetime="yyyy-mm-ddhh:mm:ss"
pos点
--start-position=数字
--stop-position=数字
执行binlog日志里的sql命令恢复数据
#mysqlbinlog[选项]日志文件名|mysql-uroot-
p123qqq
#mysqlbinlog --start-position=300 --stop-
position=1006 /var/lib/mysql/localhost-bin.000001|
mysql-uroot-p123qqq
手动生成新的binlog日志？
mysql> flushlogs;
mysql -uroot -p123qqq -e "flush logs"
systemctlrestart mysqld
#mysqldump -uroot -p123qqq --flush-logsteadbt7>
/databak/t7.sql
删除已有的binlog日志文件
mysql> resetmaster;
mysql> purgemasterlogsto"binlog文件名";
#rm-rf binlog日志文件
自定义binlog日志文件存储的目录和文件名
mkdir /logdir
chownmysql /logdir
setenforce0
#vim /etc/my.cnf
server_id=12
#log_bin
log_bin=/logdir/plj
binlog_format="mixed"
:wq
#systemctlrestartmysqld
#ls/logdir/
++++++++++++++++++++++++++++
2.3安装第3方软件percona提供增量备份命令做备份
一款强大的在线热备份工具
备份过程中不锁库表，适合生产环境
由专业组织Percona提供（改进MySQL分支）
主要含两个组件
xtrabackup：C程序，支持InnoDB/XtraDB
innobackupex：以Perl脚本封装xtrabackup，还支持
MyISAM
#yum -yinstallperl-DBD-MySQL perl-Digest-MD5
#rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
rpm -ivhpercona-xtrabackup-24-2.4.7-
1.el7.x86_64.rpm
rpm -qa| greppercona
rpm -ql percona-xtrabackup-24
#maninnobackupex
#manxtrabackup
#innobackupex
1 innobackupex完全备份与恢复
#mkdir/pljdir
innobackupex --user root--password 123qqq--
databases="teadb"/pljdir --no-timestamp
innobackupex--user root --password123qqq--
databases="teadb" --apply-log /pljdir
完全恢复
cp-r/var/lib/mysql/mysql/opt/mysql.bak
#rm-rf /var/lib/mysql
#mkdir /var/lib/mysql
innobackupex--user root --password 123qqq --
databases="teadb" --copy-back/pljdir
cp -r /opt/mysql.bak/var/lib/mysql/mysql
chown-R mysql:mysql /var/lib/mysql
#systemctlrestart mysqld
备份目录下配置文件说明
backup-my.cnf
xtrabackup_checkpoints
xtrabackup_logfile
ibdata1
数据库 /var/lib/mysql/
事务日志文件
lsn日志序列号
ib_logfile0
ib_logfile1
ibdata1
重新初始化数据库目录下的初始数据
#systemctlstop mysqld
#rm-rf /var/lib/mysql
vim /etc/my.cnf
#validate_password_policy=0
#validate_password_length=6
:wq
#mysql_install_db --datadir=/var/lib/mysql --
user=mysql
#ls /var/lib/mysql/
#rm -rf /var/lib/mysql/mysql
#cp-r/opt/mysql.bak /var/lib/mysql/mysql
#chown-R mysql:mysql /var/lib/mysql/mysql
#systemctlstart mysqld
2 innobackupex增量备份
完全备份 db101.t14---999
#innobackupex--user root--password123456--
databases="db101.t1" /fullbak --no-timestamp
第1次增量备份8888
#innobackupex--user root--password123456--
databases="db101.t1"--incremental /new1dir --
incremental--basedir=/fullbak --no-timestamp
第2次增量备份7777
#innobackupex--user root--password123456--
databases="db101.t1" --incremental /new2dir --
incremental--basedir=/new1dir--no-timestamp
增量恢复步骤
1 rm-rf/var/lib/mysql/
2 恢复日志信息
3 恢复数据
4 重启数据库服务
5 登录查看
增量恢复步骤
1 rm-rf/var/lib/mysql/
2 mkdir/var/lib/mysql
2 恢复日志信息
#innobackupex --user root --password 123456 --
databases="db106.t1" --apply-log --redo-only/onedir
#innobackupex --user root --password 123456 --
databases="db106.t1"--apply-log --redo-only /onedir
--incremental-dir="/dir2"
#innobackupex --user root --password 123456 --
databases="db106.t1"--apply-log --redo-only /onedir
--incremental-dir="/dir3"
3 恢复数据
#innobackupex --user root --password 123456 --
databases="db106.t1" --copy-back /onedir
4 重启数据库服务
#cp-r/root/mysql.plj /var/lib/mysql/mysql
#systemctl start mysqld
#chown-Rmysql:mysql /var/lib/mysql
#systemctlstopmysqld
#systemctl startmysqld
5 登录查看
mysql -uroot-p123456
mysql> select* from db1.t1;
+++++++++++++++++++++++++++++++
3 使用完全备份文件恢复某个表的记录。
db106.a/b/t1
完全备份
#innobackupex--userroot--password123456 --databases="db106" /db106all --no-timestamp
#ls /db106all
#mysql-uroot-p123456
#drop table db106.a;
恢复某个表的记录
#innobackupex --user root --password 123456 --databases="db106" --apply-log --export /db106all
#ls /db106all/a.*
mysql> createtable db106.a(id int);
mysql>> mysql> system cp/db106all/db106/a.{ibd,cfg,exp} /var/lib/mysql/db106/
mysql> systemchown mysql:mysql /var/lib/mysql/db106/a.*
mysql>> mysql > select* fromdb106.a;
页:
[1]