如何管理MySQL表

下面讲讲关于如何管理MySQL表，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完如何管理MySQL表这篇文章你一定会有所受益。

MySQL

数据导入
把系统文件的内容存储到数据库的表里
/etc/passwd     studb.user
用户名  密码占位符  uid  gid  描述信息  家目录  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 id int(2) primary key auto_increment first;         添加自增长行号

修改目录及查看修改结果
mkdir /myfile
chown mysql /myfile   赋予权限,让所有者变为mysql
vim /etc/my.cnf       修改配置文件，默认使用目录
[mysqld]
secure_file_priv="/myfile"

数据导出：把表记录存储到系统文件里
into outfile “目录名/文件名”[fields terminated by "符号" lines terminated by "符号"]
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 id,name from user limit 5 into outfile "/var/lib/mysql-files/4.txt" lines terminated by ":";

管理表记录

增
insert into 库.表 values（字段值列表）；
insert into 库.表 values（字段值列表），（字段值列表）
查
select 字段名列表 from 库.表；
select 字段名列表 from 库.表 where 条件；
eg：select * from user where name=“mysql”；  查找user表中所有name=mysql的记录

单表查询
条件匹配的表示方式：
数值比较 > >= < <= = !=
字段名 符号 值
select name from user where uid=15；   显示uid=15的用户
select name，shell from user where shell！=“/bin/bash”；
select id，name from user where name=“apache”；
范围内比较
字段名 in （值列表）    在。。。。里
select id，name from user where name in（“apache”，“root”）；
select id，name from user where uid in（10，15，9，12）；
字段名 between 值1 and 值2     在。。。之间
select  from user where id between 10 and 15；
字段名 not in （列表置）  不再。。。里
select name from user where uid not in（0，1，7，8）；
select  from user where name not in（“root”，“mysql“，”bin“）；

匹配空 is null
匹配非空 is not null
select id from user where name is null；
select id，name，shell from user where shell is not null；
insert into user（name）values（“”），（“null”），（null）；
select id，name from user where name=“”；
select id，name from user where name=“null”；

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 ‘[0-9]’;
select name from user where name regexp ‘^[0-9]’;
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;
select  from 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 select  from 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
[mysqld]
......
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 用户@"客户端地址" identified by "密码" 【with grant option可加，使得新用户拥有添加用户的权限】
grant all on .
当库名.表名 为.时，匹配所有库所有表
授权设置放在mysql库的user表
grant all on . to root@"192.168.4.12" identified by "123456" with grant option;  (对192.168.4.12授权，用户root，密码123456)
数据库云服务器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" identified by "123456";
只有192.168.4.30可使用用户bbsuser登陆，在数据库中只能对bbsdb库进行增删改。

grant select on . to admin@"localhost" identified by "123456";
本机用户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/*

164  cp  -r /mydata/mysql.bak/  /var/lib/mysql/mysql
165  chown -R mysql:mysql /var/lib/mysql/mysql
166  systemctl  restart mysqld

逻辑备份?备份时根据已有的库表及记录生成对应的sql命令，把

sql保存到指定的备份文件里

3数据备份策略？
完全备份   备份所有数据（一台云服务器   一个库    一张表）
差异备份   备份自完全备份后所有新产生
增量备份   备份自上一次备份后所有新产生

完全备份+差异备份
完全备份+增量备份

4在生成环境下如何实现数据备份
周期性计划任务 执行 备份脚本

00  18         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 database  teadb;
mysql>create database  teadb;
#mysql -uroot -p123qqq teadb < /databak/teadb.sql
mysql> use teadb ; show tables;
#crontab  -e
00  18          1      /opt/teadbbak.sh   &> /dev/null
00  18          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-logs  teadb   >

/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    show  tables  
mysql> create     insert    update    delete   grant   revoke

启用binlog日志
mysql > show variables like "binlog_format";
vim  /etc/my.cnf
[mysqld]
server_id=12
log_bin
binlog_format="mixed"
:wq
#systemctl   restart  mysqld
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-dd  hh:mm:ss"
--stop-datetime="yyyy-mm-dd  hh: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> flush  logs;

mysql -uroot -p123qqq -e "flush logs"

systemctl  restart mysqld

#mysqldump -uroot -p123qqq --flush-logs  teadb  t7  >

/databak/t7.sql

删除已有的binlog日志文件
mysql> reset  master;
mysql> purge  master  logs  to  "binlog文件名";
#rm  -rf   binlog日志文件

自定义binlog日志文件存储的目录和文件名

mkdir /logdir

chown  mysql /logdir

setenforce  0

#vim /etc/my.cnf
server_id=12
#log_bin
log_bin=/logdir/plj
binlog_format="mixed"
:wq
#systemctl  restart  mysqld
#ls  /logdir/
++++++++++++++++++++++++++++
2.3安装第3方软件percona提供增量备份命令做备份
一款强大的在线热备份工具
备份过程中不锁库表，适合生产环境
由专业组织Percona提供（改进MySQL分支）

主要含两个组件
xtrabackup：C程序，支持InnoDB/XtraDB
innobackupex：以Perl脚本封装xtrabackup，还支持

MyISAM

#yum -y  install  perl-DBD-MySQL   perl-Digest-MD5
#rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

rpm -ivh  percona-xtrabackup-24-2.4.7-

1.el7.x86_64.rpm

rpm -qa  | grep  percona

rpm -ql percona-xtrabackup-24

#man  innobackupex
#man  xtrabackup

#innobackupex   <选项>
1 innobackupex完全备份与恢复
#mkdir  /pljdir

innobackupex --user root  --password 123qqq  --

databases="teadb"  /pljdir   --no-timestamp

innobackupex  --user root --password  123qqq  --

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

#systemctl  restart mysqld

备份目录下配置文件说明
backup-my.cnf
xtrabackup_checkpoints
xtrabackup_logfile
ibdata1

数据库 /var/lib/mysql/
事务日志文件
lsn  日志序列号
ib_logfile0
ib_logfile1
ibdata1

重新初始化数据库目录下的初始数据
#systemctl  stop mysqld
#rm  -rf /var/lib/mysql
vim /etc/my.cnf
[mysqld]
#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
#systemctl  start mysqld

2 innobackupex增量备份
完全备份 db101.t1  4---999
#innobackupex  --user root  --password  123456  --

databases="db101.t1"   /fullbak   --no-timestamp

第1次增量备份  8888
#innobackupex  --user root  --password  123456  --

databases="db101.t1"  --incremental   /new1dir   --

incremental--basedir=/fullbak   --no-timestamp

第2次增量备份  7777
#innobackupex  --user root  --password  123456  --

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  -R  mysql:mysql   /var/lib/mysql
#systemctl  stop  mysqld
#systemctl   start  mysqld
5 登录查看
mysql -uroot  -p123456
mysql> select  * from db1.t1;

+++++++++++++++++++++++++++++++
3 使用完全备份文件恢复某个表的记录。
db106.a/b/t1
完全备份
#innobackupex  --user  root  --password  123456   --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> create  table db106.a(id int);
mysql> alter  table  db106.a  discard  tablespace;

mysql> system cp  /db106all/db106/a.{ibd,cfg,exp} /var/lib/mysql/db106/

mysql> system  chown mysql:mysql /var/lib/mysql/db106/a.*

mysql> alter  table  db106.a  import  tablespace;  
mysql > select  * from  db106.a;

对于以上如何管理MySQL表相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。