Python進行MySQL數(shù)據(jù)備份與增刪改查操作實戰(zhàn)指南
一、IDE工具介紹
生產(chǎn)環(huán)境還是推薦使用mysql命令行，但為了方便我們測試，可以使用Navicat之類的IDE工具。
二、MySQL 數(shù)據(jù)備份
1. 備份類型
物理備份
直接復制數(shù)據(jù)庫文件，這種方式適用于大型數(shù)據(jù)庫環(huán)境。不過，它存在一定局限性，不能恢復到異構(gòu)系統(tǒng)中，例如不能從 Linux 環(huán)境恢復到 Windows 環(huán)境。
邏輯備份
備份的是建表、建庫、插入等操作所執(zhí)行的 SQL 語句，比較適合中小型數(shù)據(jù)庫。但相對物理備份，其效率較低。
導出表
將表導入到文本文件中，方便數(shù)據(jù)的存儲和轉(zhuǎn)移。
2. 使用 mysqldump 實現(xiàn)邏輯備份
語法
# mysqldump -h 服務器 -u用戶名 -p密碼 數(shù)據(jù)庫名 > 備份文件.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
3. 恢復邏輯備份
方法一
[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;
4. 備份/恢復案例
數(shù)據(jù)庫備份/恢復實驗一：數(shù)據(jù)庫損壞
備份步驟
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截斷并產(chǎn)生新的 binlog
3. 插入數(shù)據(jù) //模擬服務器正常運行
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 文件
數(shù)據(jù)庫備份/恢復實驗二：如果有誤刪除
備份步驟
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截斷并產(chǎn)生新的 binlog
3. 插入數(shù)據(jù) //模擬服務器正常運行
4. drop table db1.t1 //模擬誤刪除
5. 插入數(shù)據(jù) //模擬服務器正常運行
恢復步驟
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 文件
注意事項
- 完全恢復到一個干凈的環(huán)境（例如新的數(shù)據(jù)庫或刪除原有的數(shù)據(jù)庫）。
- 恢復期間所有 SQL 語句不應該記錄到 binlog 中。
5. 實現(xiàn)自動化備份
備份計劃
- 時間：2:00
- 備份數(shù)據(jù)庫：可根據(jù)需求指定
- 備份文件位置：可自定義
備份腳本
[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
6. 表的導出和導入
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 '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
這是因為 MySQL 為了安全考慮，對文件導出位置作了限制。需要在配置文件中設置：
[mysqld]
secure_file_priv='C:\\' #只能將數(shù)據(jù)導出到 C:\\ 下
重啟 MySQL 后重新執(zhí)行導出語句。
7. 數(shù)據(jù)庫遷移
務必保證在相同版本之間遷移，示例命令如下：
# mysqldump -h 源 IP -uroot -p123 --databases db1 | mysql -h 目標 IP -uroot -p456
三、pymysql 模塊
1. 安裝
pip3 install pymysql
2. 鏈接、執(zhí)行 sql、關(guān)閉（游標）
import pymysql
user=input('用戶名: ').strip()
pwd=input('密碼: ').strip()
# 鏈接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao',charset='utf8')
# 游標
cursor=conn.cursor() # 執(zhí)行完畢返回的結(jié)果集默認以元組顯示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執(zhí)行 sql 語句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) # 注意 %s 需要加引號
print(sql)
res=cursor.execute(sql) # 執(zhí)行 sql 語句，返回 sql 查詢成功的記錄數(shù)目
print(res)
cursor.close()
conn.close()
if res:
print('登錄成功')
else:
print('登錄失敗')
3. execute() 之 sql 注入
最后那一個空格，在一條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 的規(guī)矩來。
4. 增、刪、改：conn.commit()
import pymysql
# 鏈接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao')
# 游標
cursor=conn.cursor()
# 執(zhí)行 sql 語句
# part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) # 執(zhí)行 sql 語句，返回 sql 影響成功的行數(shù)
# print(res)
# part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) # 執(zhí)行 sql 語句，返回 sql 影響成功的行數(shù)
# print(res)
# part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) # 執(zhí)行 sql 語句，返回 sql 影響成功的行數(shù)
print(res)
conn.commit() # 提交后才發(fā)現(xiàn)表中插入記錄成功
cursor.close()
conn.close()
5. 查：fetchone，fetchmany，fetchall
import pymysql
# 鏈接
conn=pymysql.connect(host='localhost',user='root',password='123',database='xiao')
# 游標
cursor=conn.cursor()
# 執(zhí)行 sql 語句
sql='select * from userinfo;'
rows=cursor.execute(sql) # 執(zhí)行 sql 語句，返回 sql 影響成功的行數(shù) rows，將結(jié)果放入一個集合，等待被查詢
# 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() # 提交后才發(fā)現(xiàn)表中插入記錄成功
cursor.close()
conn.close()
6. 獲取插入的最后一條數(shù)據(jù)的自增 ID
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()
到此這篇關(guān)于Python進行MySQL數(shù)據(jù)備份與增刪改查操作實戰(zhàn)指南的文章就介紹到這了,更多相關(guān)Python MySQL操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- Python+PyQt5實現(xiàn)MySQL數(shù)據(jù)庫備份神器
- python定時按日期備份MySQL數(shù)據(jù)并壓縮
- Python實現(xiàn)定時備份mysql數(shù)據(jù)庫并把備份數(shù)據(jù)庫郵件發(fā)送
- Python實現(xiàn)備份MySQL數(shù)據(jù)庫的方法示例
- python備份文件以及mysql數(shù)據(jù)庫的腳本代碼
- Python使用PyMySql增刪改查Mysql數(shù)據(jù)庫的實現(xiàn)
- pymysql實現(xiàn)增刪改查的操作指南（python）
- python對 MySQL 數(shù)據(jù)庫進行增刪改查的腳本
相關(guān)文章
python3 使用Opencv打開USB攝像頭,配置1080P分辨率的操作
今天小編就為大家分享一篇python3 使用Opencv打開USB攝像頭,配置1080P分辨率的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-12-12
Python爬蟲教程之利用正則表達式匹配網(wǎng)頁內(nèi)容
這篇文章主要給大家介紹了關(guān)于Python爬蟲教程之利用正則表達式匹配網(wǎng)頁內(nèi)容的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
Python?存根文件(.pyi)簡介與實戰(zhàn)案例及類型提示的高級指南
存根文件(.pyi) 是Python用于定義接口類型但不包含具體實現(xiàn)的特殊文件,它提供了一種獨立于實現(xiàn)的類型定義方式,這篇文章給大家介紹Python存根文件(.pyi)簡介與實戰(zhàn)案例及類型提示的高級指南,感興趣的朋友一起看看吧2025-08-08
Python實現(xiàn)批量將word轉(zhuǎn)html并將html內(nèi)容發(fā)布至網(wǎng)站的方法
這篇文章主要介紹了Python實現(xiàn)批量將word轉(zhuǎn)html并將html內(nèi)容發(fā)布至網(wǎng)站的方法,涉及Python調(diào)用第三方接口進行文件轉(zhuǎn)換及操作數(shù)據(jù)庫等相關(guān)技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-07-07
這篇文章主要給大家介紹了關(guān)于利用python批量下載網(wǎng)站馬拉松照片的完整步驟，文中通過示例代碼介紹的非常詳細，對大家的學習或工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
PyTorch搭建LSTM實現(xiàn)多變量時序負荷預測
這篇文章主要為大家介紹了PyTorch搭建LSTM實現(xiàn)多變量時間序列預測及負荷預測，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-05-05
淺談Python中的zip()與*zip()函數(shù)詳解
這篇文章主要介紹了淺談Python中的zip()與*zip()函數(shù)詳解，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-02-02
在本篇文章里小編給大家分享了關(guān)于Python列表的切片的知識點實例，需要的朋友們可以參考下。2019-08-08
map與reduce是兩個十分常用的Python內(nèi)置函數(shù)，它們與Hadoop中的MapReduce在某些方面有一定的相似之處。2018-02-02
這篇文章主要介紹了windows下python安裝pip方法詳解,需要的朋友可以參考下2020-02-02