数据备份

 

数据备份

 

一 IDE工具介绍

　　生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具。

掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表

#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键

二 MySQL数据备份

1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
3. 导出表： 将表导入到文本文件中。 

1.使用mysqldump实现逻辑备份

　　备份库与恢复库

 

语法：
mysqldump -h服务器 -u用户名 -p密码 数据库名 > 路径名\\备份文件.sql

备份库
mysqldump -uroot -p123 --databases day045 > c:\\bak\\day045_bak_2017_10_30.sql
把day045库备份到c:\\bak并命名为day045_bak_2017_10_30
恢复库
mysql -uroot -p123 < c:\\bak\\day045_bak_2017_10_30.sql

备份多个库
mysqldump -uroot -p123 --databases day43 day44 day45 > c:\\bak\\day43_day45_day44_bak_2017_10_30.sql
恢复库
mysql -uroot -p123 <  c:\\bak\\day43_day45_day44_bak_2017_10_30.sql

备份所有的库
mysqldump -uroot -p123  --all-databases > c:\\bak\\all.sql
恢复库
mysql -uroot -p123 <  c:\\bak\\all.sql

　　备份库与恢复库的第二种方法：

登陆入mysql中输入：
use db1;
SET SQL_LOG_BIN=0;
source 地址 文件名.sql

#注：如果备份/恢复单个库时，可以修改sql文件
DROP database if exists school;
create database school;
use school; 

　　备份表与恢复表

表的导出
SELECT * FROM day045.userinfo
INTO OUTFILE 'c:\\bak\\userinfo.txt'
FIELDS TERMINATED BY ','            //定义字段分隔符
OPTIONALLY ENCLOSED BY '”'       //定义字符串使用什么符号括起来
LINES TERMINATED BY '\n' ;         //定义换行符
输出：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

表的导入
LOAD DATA INFILE 'c:\\bak\\userinfo.txt'
INTO TABLE day045.userinfo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n';

　　--secure-file-priv是怎么回事？

数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录

我们做如下设置：
在配置文件中
[mysqld]
secure_file_priv='c:\\bak\\'     #只能将数据导出到c:\\bak\\下

重启mysql
重新执行上述语句

2.数据库迁移

务必保证在相同版本之间迁移
mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

三 pymysql模块

1.链接、执行sql、关闭（游标）

+--------+----------+
| user   | password |
+--------+----------+
| wang   | 456      |
| yh     | 123      |
| evial  | 123      |
| evial1 | 123      |
| evial2 | 123      |
| evial3 | 123      |
+--------+----------+

 

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')

#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行sql语句
sql='select * from userinfo where user="%s" and password="%s"' %(user,pwd)     #注意%s需要加引号
print(sql)
res=cursor.execute(sql)     #执行sql语句，返回sql查询成功的记录数目
print(res)

conn.commit()　　#可以不写
cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

2.execute()之sql注入

　　注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

　　根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了

#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

 

# 原来是我们对sql进行字符串拼接
sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
print(sql)
res=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s"     #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd])     #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来

3.增、删、改：conn.commit()

import pymysql
#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')
#游标
cursor=conn.cursor()

#执行sql语句
#part1
sql='insert into userinfo(user,password) values("yeah","123456");'
res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
print(res)

#part2
# sql='insert into userinfo(user,password) values(%s,%s);'
# res=cursor.execute(sql,("what","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)

#part3
# sql='insert into userinfo(user,password) values(%s,%s);'
# res=cursor.executemany(sql,[("nani","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
# print(res)

conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

4.查：fetchone，fetchmany，fetchall

import pymysql

conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')

cur=conn.cursor(cursor=pymysql.cursors.DictCursor)

res=cur.execute('select * from userinfo;')
print(cur.fetchone())
print(cur.fetchone())
print(cur.fetchmany(3))
print(cur.fetchall())

conn.commit()
cur.close()
conn.close()

输出：
{'user': 'wang', 'password': '456'}

{'user': 'yh', 'password': '123'}

[{'user': 'evial', 'password': '123'}, {'user': 'evial1', 'password': '123'}, {'user': 'evial2', 'password': '123'}]

[{'user': 'evial3', 'password': '123'}, {'user': 'nani', 'password': '123456'}, {'user': 'lhf', 'password': '12356'}, {'user': 'eee', 'password': '156'}, {'user': 'what', 'password': '123456'}, {'user': 'yeah', 'password': '123456'}]

　　绝对位置移动与相对位置移动

import pymysql

conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')
cur=conn.cursor(cursor=pymysql.cursors.DictCursor)

res=cur.execute('select * from userinfo;')
print(res)

cur.scroll(6,mode='absolute') # 绝对位置移动
print(cur.fetchone())
cur.scroll(2,mode='absolute') # 绝对位置移动
print(cur.fetchone())

# print(cur.fetchone()) #1
# cur.scroll(1,mode='relative') # 相对当前位置移动
# print(cur.fetchone()) #3
# cur.scroll(1,mode='relative') # 相对当前位置移动
# print(cur.fetchone()) #5

conn.commit()
cur.close()
conn.close()

 

5.获取插入的最后一条数据的自增ID

import pymysql
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')
cur=conn.cursor()

sql='insert into userinfo(user,password) values("xxx","123");'
rows=cur.execute(sql)
print(cur.lastrowid) #在插入语句后查看

conn.commit()
cursor.close()
conn.close()

 

 

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()

#链接
conn=pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123',
    database='day045',
    charset='utf8')

#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行sql语句
sql='select * from userinfo where user="%s" and password="%s"' %(user,pwd)     #注意%s需要加引号
print(sql)
res=cursor.execute(sql)     #执行sql语句，返回sql查询成功的记录数目
print(res)

conn.commit()
cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')