编程部落

Python进行MySQL数据备份与增删改查操作实战指南

作者：

风君子

在

文章目录
  • 生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用Navicat之类的IDE工具。
    • 目录
    • 一、IDE工具介绍
    • 二、MySQL 数据备份
      • 1. 备份类型
      • 2. 使用 mysqldump 实现逻辑备份
      • 3. 恢复逻辑备份
      • 4. 备份/恢复案例
      • 数据库备份/恢复实验二：如果有误删除
      • 5. 实现自动化备份
      • 6. 表的导出和导入
      • 7. 数据库迁移
    • 三、pymysql 模块
      • 1. 安装
      • 2. 链接、执行 sql、关闭（游标）
      • 3. execute() 之 sql 注入
      • 4. 增、删、改：conn.commit()
      • 5. 查：fetchone，fetchmany，fetchall
      • 6. 获取插入的最后一条数据的自增 ID

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

    物理备份

    直接复制数据库文件，这种方式适用于大型数据库环境。不过，它存在一定局限性，不能恢复到异构系统中，例如不能从 Linux 环境恢复到 Windows 环境。

    逻辑备份

    备份的是建表、建库、插入等操作所执行的 SQL 语句，比较适合中小型数据库。但相对物理备份，其效率较低。

    导出表

    将表导入到文本文件中，方便数据的存储和转移。

    语法

    # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

    示例

    单库备份

    mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

    多库备份

    mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

    备份所有库

    mysqldump -uroot -p123 --all-databases > all.sql

    方法一

    [root@egon backup]# mysql -uroot -p123 < /backup/all.sql

    方法二

    mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/db1.sql

    如果备份/恢复单个库时，可以修改 sql 文件，示例如下：

    DROP database if exists school;
create database school;
use school;

    数据库备份/恢复实验一：数据库损坏

    备份步骤

    1. # mysqldump -uroot -p123 –all-databases > /backup/`date +%F`_all.sql

    2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的 binlog

    3. 插入数据 //模拟服务器正常运行

    4. mysql> set sql_log_bin=0; //模拟服务器损坏

    mysql> drop database db;

    恢复步骤

    1. # mysqlbinlog 最后一个 binlog > /backup/last_bin.log

    2. mysql> set sql_log_bin=0; 

    mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 
    mysql> source /backup/last_bin.log //恢复最后个 binlog 文件

    备份步骤

    1. mysqldump -uroot -p123 –all-databases > /backup/`date +%F`_all.sql

    2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的 binlog

    3. 插入数据 //模拟服务器正常运行

    4. drop table db1.t1 //模拟误删除

    5. 插入数据 //模拟服务器正常运行

    恢复步骤

    1. # mysqlbinlog 最后一个 binlog –stop-position=260 > /tmp/1.sql 

    # mysqlbinlog 最后一个 binlog –start-position=900 > /tmp/2.sql 

    2. mysql> set sql_log_bin=0; 

    mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
    mysql> source /tmp/1.log //恢复最后个 binlog 文件
    mysql> source /tmp/2.log //恢复最后个 binlog 文件

    注意事项

    • 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）。
    • 恢复期间所有 SQL 语句不应该记录到 binlog 中。

    备份计划

    • 时间：2:00
    • 备份数据库：可根据需求指定
    • 备份文件位置：可自定义

    备份脚本

    [root@xiao ~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} ;

    手动测试

    [root@xiao ~]# chmod a+x /mysql_back.sql 
[root@xiao ~]# chattr +i /mysql_back.sql
[root@xiao ~]# /mysql_back.sql

    配置 cron

    [root@xiao ~]# crontab -l
2 * * * /mysql_back.sql

    SELECT… INTO OUTFILE 导出文本文件

    mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY 'n' ; //定义换行符

    mysql 命令导出文本文件

    # mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

    LOAD DATA INFILE 导入文本文件

    mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY 'n';

    在导出文件时可能会报错，例如：

    mysql> select * from db1.emp into outfile 'C:\db1.emp.txt' fields terminated by ',' lines terminated by 'rn';
    ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

    这是因为 MySQL 为了安全考虑，对文件导出位置作了限制。需要在配置文件中设置：

    [mysqld]
    secure_file_priv='C:\' #只能将数据导出到 C:\ 下

    重启 MySQL 后重新执行导出语句。

    务必保证在相同版本之间迁移，示例命令如下：

    # mysqldump -h 源 IP -uroot -p123 --databases db1 | mysql -h 目标 IP -uroot -p456


    pip3 install pymysql


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

# 链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao',charset='utf8')
# 游标
cursor=conn.cursor() # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

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

cursor.close()
conn.close()

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

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

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

#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 的规矩来。


    import pymysql
# 链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao')
# 游标
cursor=conn.cursor()

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

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

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

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


    import pymysql
# 链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao')
# 游标
cursor=conn.cursor()

# 执行 sql 语句
sql='select * from userinfo;'
rows=cursor.execute(sql) # 执行 sql 语句，返回 sql 影响成功的行数 rows，将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)
res5=cursor.fetchall()
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)
print('%s rows in set (0.00 sec)' %rows)

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


    import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao')
cursor=conn.cursor()

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

conn.commit()

cursor.close()
conn.close()

    到此这篇关于Python进行MySQL数据备份与增删改查操作实战指南的文章就介绍到这了,更多相关Python MySQL操作内容请搜索风君子博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持风君子博客！

    您可能感兴趣的文章:

    • Python+PyQt5实现MySQL数据库备份神器
    • python定时按日期备份MySQL数据并压缩
    • Python实现定时备份mysql数据库并把备份数据库邮件发送
    • Python实现备份MySQL数据库的方法示例
    • python备份文件以及mysql数据库的脚本代码
    • Python使用PyMySql增删改查Mysql数据库的实现
    • pymysql实现增删改查的操作指南（python）
    • python对 MySQL 数据库进行增删改查的脚本

    更多文章

    站内搜索

    标签云

    iphone 三星 信用卡 信贷 华为 小米 微软 快科技 手机 投资理财 支付宝 特斯拉 理财知识 电脑 科技 程序 股票 芯片 苹果 贷款 路由器 银行 银行卡 额度

    热门文章

    友情链接