下面講講關(guān)于如何管理MySQL表,文字的奧妙在于貼近主題相關(guān)。所以,閑話就不談了,我們直接看下文吧,相信看完如何管理MySQL表這篇文章你一定會有所受益。
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比全州網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式全州網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們,業(yè)務(wù)覆蓋全州地區(qū)。費用合理售后完善,10余年實體公司更值得信賴。
MySQL
數(shù)據(jù)導(dǎo)入
把系統(tǒng)文件的內(nèi)容存儲到數(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”;
查看默認使用目錄及目錄是否存在
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ù)信息拷貝進默認使用目錄
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 修改配置文件,默認使用目錄
[mysqld]
secure_file_priv="/myfile"
數(shù)據(jù)導(dǎo)出:把表記錄存儲到系統(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;
邏輯匹配:有多個條件
邏輯與 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(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)計函數(shù)
-avg():集合的平均值
-sum():對集合中的各參數(shù)求和
-min():集合中的最小值
-max():集合中的最大值
-count():記錄的個數(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;(升序/降序,默認升序)
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; 迪卡爾集會出現(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管理密碼(忘記密碼時,管理員有權(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 .
當庫名.表名 為.時,匹配所有庫所有表
授權(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
新建虛擬機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; 查看當前登陸的數(shù)據(jù)云服務(wù)器
mysql> select user(); 查看當前正在訪問的終端
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庫進行增刪改。
grant select on . to admin@"localhost" identified by "123456";
本機用戶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ù)丟失或誤刪除時,使用備份文件恢復(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
邏輯備份?備份時根據(jù)已有的庫表及記錄生成對應(yīng)的sql命令,把
sql保存到指定的備份文件里
3數(shù)據(jù)備份策略?
完全備份 備份所有數(shù)據(jù)(一臺云服務(wù)器 一個庫 一張表)
差異備份 備份自完全備份后所有新產(chǎn)生
增量備份 備份自上一次備份后所有新產(chǎn)生
完全備份+差異備份
完全備份+增量備份
4在生成環(huán)境下如何實現(xiàn)數(shù)據(jù)備份
周期性計劃任務(wù) 執(zhí)行 備份腳本
00 18 1 sh /shell/allbak.sh
5 數(shù)據(jù)備份時要考慮因素?
備份方式 邏輯備份
備份策略 ?完全 差異 增量
數(shù)據(jù)備份頻率? 1 小時 1天 1周
數(shù)據(jù)備份的時間? 數(shù)據(jù)訪問量小的時候執(zhí)行備份
存儲空間可擴展? LV
備份文件命名要有標識性? 使用日期做備份文件名
完全備份
#mysqldump -hlocalhost -uroot -p123qqq 數(shù)據(jù)庫名
目錄名/名.sql
數(shù)據(jù)庫名的表示方式?
--all-databases 備份一臺服務(wù)上的所有數(shù)據(jù)
數(shù)據(jù)庫名 備份一個庫里的所有表
數(shù)據(jù)庫名 表名 備份一張表里的所有數(shù)據(jù)
-B 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 數(shù)據(jù)庫名N 備份某幾個庫的所有數(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
只使用完全備份策略備份數(shù)據(jù)的缺點:
a 使用完全備份文件恢復(fù)數(shù)據(jù)時只能把數(shù)據(jù)恢復(fù)到備份時的狀態(tài)
,完全備份新產(chǎn)生的數(shù)據(jù)無法恢復(fù)。
b 備份和恢復(fù)數(shù)據(jù)時都會對表加寫鎖。
+++++++++++++++++++++++++++++++++++二
、增量備份(啟用mysql服務(wù)binlog日志做時時增量備份、安裝
第3方軟件提供增量備份命令做備份)
2.1啟用mysql服務(wù)binlog日志做時時增量備份
binlog日志 又叫二進制日志 ,是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/主機名-bin.000001 500M+
ls /var/lib/mysql/localhost-bin.index 索引文件
查看binlog日志文件內(nèi)容
#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=數(shù)字
--stop-position=數(shù)字
執(zhí)行binlog日志里的sql命令恢復(fù)數(shù)據(jù)
#mysqlbinlog [選項] 日志文件名 | mysql -uroot -
p123qqq
#mysqlbinlog --start-position=300 --stop-
position=1006 /var/lib/mysql/localhost-bin.000001 |
mysql -uroot -p123qqq
手動生成新的binlog日志?
mysql> flush logs;
#mysqldump -uroot -p123qqq --flush-logs teadb t7 >
/databak/t7.sql
刪除已有的binlog日志文件
mysql> reset master;
mysql> purge master logs to "binlog文件名";
#rm -rf binlog日志文件
自定義binlog日志文件存儲的目錄和文件名
#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提供增量備份命令做備份
一款強大的在線熱備份工具
備份過程中不鎖庫表,適合生產(chǎn)環(huán)境
由專業(yè)組織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
1.el7.x86_64.rpm
#man innobackupex
#man xtrabackup
#innobackupex <選項>
1 innobackupex完全備份與恢復(fù)
#mkdir /pljdir
databases="teadb" /pljdir --no-timestamp
databases="teadb" --apply-log /pljdir
完全恢復(fù)
#rm -rf /var/lib/mysql
#mkdir /var/lib/mysql
databases="teadb" --copy-back /pljdir
#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ù)某個表的記錄。
db106.a/b/t1
完全備份
#innobackupex --user root --password 123456 --databases="db106" /db106all --no-timestamp
#ls /db106all
#mysql -uroot -p123456
#drop table db106.a;
恢復(fù)某個表的記錄
#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è)資訊板塊。