管理MySQL表

发布时间：2023-11-03 15:01:29 所属栏目：MySql教程来源：

导读：MySQL

数据导入

把系统文件的内容存储到数据库的表里

/etc/passwd studb.user

用户名密码占位符 uid gid 描述信息家目录 shell

creat database studb；

create table studb.use

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),

);

导入数据格式：

如何管理MySQL表

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;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!