如何管理MySQL表

下面講講關(guān)于如何管理MySQL表，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完如何管理MySQL表這篇文章你一定會(huì)有所受益。

MySQL

數(shù)據(jù)導(dǎo)入
把系統(tǒng)文件的內(nèi)容存儲(chǔ)到數(shù)據(jù)庫的表里
/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),
);
導(dǎo)入數(shù)據(jù)格式：

msyql> load data infile "目錄/文件名" into table 庫.表名 fields terminated by “字段間隔符號” lines terminated by “\n”；
查看默認(rè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)
將數(shù)據(jù)信息拷貝進(jìn)默認(rèn)使用目錄
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;         添加自增長行號

修改目錄及查看修改結(jié)果
mkdir /myfile
chown mysql /myfile   賦予權(quán)限,讓所有者變?yōu)閙ysql
vim /etc/my.cnf       修改配置文件，默認(rèn)使用目錄
[mysqld]
secure_file_priv="/myfile"

數(shù)據(jù)導(dǎo)出：把表記錄存儲(chǔ)到系統(tǒng)文件里
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 "#"   讓導(dǎo)出文件間設(shè)立間隔號#
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的記錄

單表查詢
條件匹配的表示方式：
數(shù)值比較 > >= < <= = !=
字段名 符號 值
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”；
范圍內(nèi)比較
字段名 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 不顯示重復(fù)值
select distinct shell from user；

邏輯匹配：有多個(gè)條件
邏輯與 and    多個(gè)條件必須都成立
邏輯或 or      多個(gè)條件有一個(gè)條件成立即可
邏輯非 ！     取反
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”；

運(yùn)算
select （uid，gid）uid+gid as（可省略） he（自定義命名） from user where name=“root”

模糊查詢  like
where 字段名 like ‘表達(dá)式’；
  匹配單個(gè)字符
%   匹配多個(gè)字符
eg：select name from user where name like ‘’；
select name from user where name like ‘%’；
select name from user where name like ‘a(chǎn)’;

正則匹配
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結(jié)尾
常用的統(tǒng)計(jì)函數(shù)
-avg（）：集合的平均值
-sum（）：對集合中的各參數(shù)求和
-min（）：集合中的最小值
-max（）：集合中的最大值
-count（）：記錄的個(gè)數(shù)

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；（升序/降序，默認(rèn)升序）
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;

限制查詢顯示行數(shù)limit
sql查詢 limit 數(shù)字；  顯示查詢結(jié)果的前幾行
sql查詢 limit 數(shù)字1，數(shù)字2；  設(shè)置顯示行的范圍
select  from user;
select  from user limit 2;
select * from user limit 2 2;          顯示第2行后的2行,即(3,4兩行)

復(fù)制表：快速備份
create table yyy select * from xxx；    將源表xxx復(fù)制為新表yyy（鍵值無法復(fù)制）
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;    迪卡爾集會(huì)出現(xiàn)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 條件；             刪除指定行

數(shù)據(jù)庫管理員root用戶密碼的設(shè)置
mysqladmin -hlocalhost -uroot -p password "新密碼"
恢復(fù)MySQL管理密碼（忘記密碼時(shí),管理員有權(quán)限更改）
#vim /etc/my.cnf
[mysqld]
......
skip-grant-tables     跳過權(quán)限
#systemctl restart mysqld
#mysql
mysql> update mysql.user set authentication_string=password("888888")
-> where user="root" and host="localhost";
mysql.user表內(nèi)有用戶登陸的信息密碼記錄，可更改表內(nèi)記錄改密碼
mysql> flush privileges;       刷新
退出mysql后再修改配置文件，刪除跳過權(quán)限

用戶授權(quán) grant
在數(shù)據(jù)庫云服務(wù)器添加新的連接用戶
mysql> grant 權(quán)限列表 on 庫名.表名 to 用戶@"客戶端地址" identified by "密碼" 【with grant option可加，使得新用戶擁有添加用戶的權(quán)限】
grant all on .
當(dāng)庫名.表名 為.時(shí)，匹配所有庫所有表
授權(quán)設(shè)置放在mysql庫的user表
grant all on . to root@"192.168.4.12" identified by "123456" with grant option;  (對192.168.4.12授權(quán)，用戶root，密碼123456)
數(shù)據(jù)庫云服務(wù)器IP為192.168.4.11
新建虛擬機(jī)mysql12，IP為192.168.4.12
客戶端測試授權(quán)：
#which mysql
#yum -y install mariadb
mysql>  -h數(shù)據(jù)庫云服務(wù)器的IP地址 -u用戶名 -p密碼
mysql> select @@hostname；   查看當(dāng)前登陸的數(shù)據(jù)云服務(wù)器
mysql> select user（）；      查看當(dāng)前正在訪問的終端
mysql> show grants；         查看自己的權(quán)限

允許從網(wǎng)站云服務(wù)器上使用bbsuser用戶連接，密碼是123456，只對bbsdb庫下的所有表有完全權(quán)限，沒有授權(quán)權(quán)限
grant all on bbsdb.* to bbsuser@"192.168.4.30" identified by "123456";
只有192.168.4.30可使用用戶bbsuser登陸，在數(shù)據(jù)庫中只能對bbsdb庫進(jìn)行增刪改。

grant select on . to admin@"localhost" identified by "123456";
本機(jī)用戶admin只擁有讀權(quán)限

管理員查看其他用戶權(quán)限
show grants for 用戶名@客戶端地址

權(quán)限撤銷 revoke
mysql> revoke 權(quán)限列表 on 庫名.表名 from 用戶名@客戶端地址；

撤銷記錄信息
revoke delete,update on . from 用戶名@客戶端地址；

刪除授權(quán)用戶drop user 用戶名@客戶端地址

use mysql;
show tables;
user    已有授權(quán)用戶信息
db      授權(quán)用戶對庫的訪問權(quán)限

數(shù)據(jù)備份
1 為什么要備份數(shù)據(jù)？
數(shù)據(jù)丟失或誤刪除時(shí)，使用備份文件恢復(fù)數(shù)據(jù)。

2 數(shù)據(jù)備份方式？
物理備份？ 備份庫或表對應(yīng)文件
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

邏輯備份?備份時(shí)根據(jù)已有的庫表及記錄生成對應(yīng)的sql命令，把

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

3數(shù)據(jù)備份策略？
完全備份   備份所有數(shù)據(jù)（一臺(tái)云服務(wù)器   一個(gè)庫    一張表）
差異備份   備份自完全備份后所有新產(chǎn)生
增量備份   備份自上一次備份后所有新產(chǎn)生

完全備份+差異備份
完全備份+增量備份

4在生成環(huán)境下如何實(shí)現(xiàn)數(shù)據(jù)備份
周期性計(jì)劃任務(wù) 執(zhí)行 備份腳本

00  18         1   sh  /shell/allbak.sh

5 數(shù)據(jù)備份時(shí)要考慮因素？
備份方式  邏輯備份
備份策略 ？完全 差異  增量
數(shù)據(jù)備份頻率？  1 小時(shí)   1天    1周
數(shù)據(jù)備份的時(shí)間？  數(shù)據(jù)訪問量小的時(shí)候執(zhí)行備份
存儲(chǔ)空間可擴(kuò)展？  LV
備份文件命名要有標(biāo)識(shí)性? 使用日期做備份文件名
完全備份
#mysqldump  -hlocalhost   -uroot   -p123qqq    數(shù)據(jù)庫名

目錄名/名.sql

數(shù)據(jù)庫名的表示方式？
--all-databases  備份一臺(tái)服務(wù)上的所有數(shù)據(jù)
數(shù)據(jù)庫名              備份一個(gè)庫里的所有表
數(shù)據(jù)庫名  表名     備份一張表里的所有數(shù)據(jù)
-B 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2  數(shù)據(jù)庫名N   備份某幾個(gè)庫的所有數(shù)據(jù)

#mkdir  /databak
#mysqldump -uroot -p123qqq userdb >

/databak/userdb.sql

#mysqldump -uroot -p123qqq teadb >

/databak/teadb.sql

完全恢復(fù)
#mysql  -hlocalhost  -uroot  -p123qqq    數(shù)據(jù)庫名  <  目錄

名/名.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

只使用完全備份策略備份數(shù)據(jù)的缺點(diǎn)：
a 使用完全備份文件恢復(fù)數(shù)據(jù)時(shí)只能把數(shù)據(jù)恢復(fù)到備份時(shí)的狀態(tài)

，完全備份新產(chǎn)生的數(shù)據(jù)無法恢復(fù)。

b 備份和恢復(fù)數(shù)據(jù)時(shí)都會(huì)對表加寫鎖。
+++++++++++++++++++++++++++++++++++二

、增量備份（啟用mysql服務(wù)binlog日志做時(shí)時(shí)增量備份、安裝

第3方軟件提供增量備份命令做備份）

2.1啟用mysql服務(wù)binlog日志做時(shí)時(shí)增量備份
binlog日志  又叫二進(jìn)制日志  ，是mysql數(shù)據(jù)服務(wù)日志文件的

一種，記錄客戶端連接數(shù)據(jù)庫服務(wù)后，執(zhí)行的除查詢之外的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/主機(jī)名-bin.000001      500M+
ls /var/lib/mysql/localhost-bin.index     索引文件

查看binlog日志文件內(nèi)容
#mysqlbinlog  /var/lib/mysql/localhost-bin.000001

binlog日志文件記錄sql命令的方式？
時(shí)間點(diǎn)
--start-datetime="yyyy-mm-dd  hh:mm:ss"
--stop-datetime="yyyy-mm-dd  hh:mm:ss"
pos點(diǎn)
--start-position=數(shù)字    
--stop-position=數(shù)字

執(zhí)行binlog日志里的sql命令恢復(fù)數(shù)據(jù)
#mysqlbinlog  [選項(xiàng)]  日志文件名  |  mysql  -uroot  -

p123qqq

#mysqlbinlog   --start-position=300   --stop-

position=1006   /var/lib/mysql/localhost-bin.000001  |

mysql  -uroot  -p123qqq

手動(dòng)生成新的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日志文件存儲(chǔ)的目錄和文件名

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提供增量備份命令做備份
一款強(qiáng)大的在線熱備份工具
備份過程中不鎖庫表，適合生產(chǎn)環(huán)境
由專業(yè)組織Percona提供（改進(jìn)MySQL分支）

主要含兩個(gè)組件
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   <選項(xiàng)>
1 innobackupex完全備份與恢復(fù)
#mkdir  /pljdir

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

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

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

databases="teadb"    --apply-log    /pljdir

完全恢復(fù)

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

數(shù)據(jù)庫 /var/lib/mysql/
事務(wù)日志文件
lsn  日志序列號
ib_logfile0
ib_logfile1
ibdata1

重新初始化數(shù)據(jù)庫目錄下的初始數(shù)據(jù)
#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

增量恢復(fù)步驟
1 rm  -rf  /var/lib/mysql/
2 恢復(fù)日志信息
3 恢復(fù)數(shù)據(jù)
4 重啟數(shù)據(jù)庫服務(wù)
5 登錄查看

增量恢復(fù)步驟
1 rm  -rf  /var/lib/mysql/
2 mkdir  /var/lib/mysql

2 恢復(fù)日志信息
#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 恢復(fù)數(shù)據(jù)
#innobackupex --user root --password 123456 --

databases="db106.t1" --copy-back   /onedir

4 重啟數(shù)據(jù)庫服務(wù)
#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 使用完全備份文件恢復(fù)某個(gè)表的記錄。
db106.a/b/t1
完全備份
#innobackupex  --user  root  --password  123456   --databases="db106"    /db106all   --no-timestamp

#ls /db106all

#mysql  -uroot  -p123456
#drop table   db106.a;

恢復(fù)某個(gè)表的記錄
#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表相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。