Data Backup, Pymysql module

MySQL Data backup

#1. Physical backup: Direct copy of database files for large database environments. However, you cannot restore to a heterogeneous system such as Windows. #2. Logical Backup: Backup is the SQL statement executed by the operation of the table, build, insert, etc., for small and medium sized databases, the efficiency is relatively low. #3. Export table: Imports a table into a text file.

First, using mysqldump to implement logical backup

#语法: # mysqldump-h Server-u user name-p password database name > backup file. sql# Example: #单库备份mysqldump-uroot-p123 db1 > db1.sqlmysqldump-uroot-p123 DB1 table1 table2 > db1-table1-table2.sql# multi-Library backup mysqldump-uroot-p123--databases db1 DB2 mysql db3 > Db1_db2_mysql_ db3.sql# backing up all libraries mysqldump-uroot-p123--all-databases > All.sql

Second, restore the logical backup

#方法一: [[email protected] backup]# mysql-uroot-p123 </backup/all.sql# method Two:mysql> use db1;mysql> SET sql_log_bin= 0;mysql> source/root/db1.sql# Note: If you back up/restore a single library, you can modify the SQL file drop database if exists school;create database School;use School

Third, backup/recovery case

#数据库备份/Recovery Experiment One: Database corruption backup:1. # mysqldump-Uroot-P123--all-databases >/backup/' Date +%f ' _all.sql2. # MySQL-Uroot-P123-E'Flush Logs' //truncate and generate a new Binlog3. Inserting data//Simulate server uptime4. Mysql> SetSql_log_bin=0;//emulation server corrupted MySQL> Drop Databasedb; Recovery:1. # Mysqlbinlog Last Binlog> /Backup/Last_bin.Log2. Mysql> SetSql_log_bin=0; MySQL>Source/Backup/ the- Geneva-13_ All. sql//Restore the most recent full backup MySQL>Source/Backup/Last_bin.Log //Restore last Binlog File # database backup/Recovery Experiment Two: If you delete the backup incorrectly:1. Mysqldump-Uroot-P123--all-databases >/backup/' Date +%f ' _all.sql2. Mysql-Uroot-P123-E'Flush Logs' //truncate and generate a new Binlog3. Inserting data//Simulate server uptime4.Drop TableDb1.t1//Analog Accidental deletion5. Inserting data//To simulate the server's normal operation recovery:1. # Mysqlbinlog Last Binlog--stop-position=260 >/tmp/1.sql# Mysqlbinlog Last Binlog--start-position=900 >/tmp/2.sql2. Mysql> SetSql_log_bin=0; MySQL>Source/Backup/ the- Geneva-13_ All. sql//Restore the most recent full backup MySQL>Source/Tmp/1.Log //restore last binlog file MySQL>Source/Tmp/2.Log //considerations for recovering the last Binlog file:1completely recover to a clean environment (such as a new database or delete an existing database)2. All SQL statements should not be logged to Binlog during recovery

View Code

Enable automated backup

Backup Schedule:1. What time2:xx2. For which database backups3. backup files where the backup script is placed:[[email protected] ~]# VIM/mysql_back.sql#!/Bin/Bashback_dir=/BackupBack_file=' Date+%F ' _ All. SQLUser=Rootpass=123if [!-d/backup]; Thenmkdir-P/Backupfi# back up and truncate the log mysqldump-u${User}-P${pass}--events--all-databases > ${back_dir}/${back_file}Mysql-u${User}-P${pass}-E'Flush Logs'# Keep Only the last week of backup CDs $back _dirfind.-Mtime+7 -execRm-RF {} \; Manual test:[[email protected] ~]# chmod A+X/Mysql_back.sql[[email protected] ~]# chattr+I/Mysql_back.sql[[email protected] ~]#/mysql_back.sql Configuring cron:[[email protected] ~]# crontab-L2 * * * /Mysql_back.sql

View Code

V. Export and import of tables

SELECT... intoOUTFILE Export Text file example: MySQL> SELECT *  fromSchool.student1 intoOUTFILE'Student1.txt'Fields TERMINATED by ',' //defining field separators optionally enclosed by '"' //defines what symbols the string uses to enclose lines TERMINATED by '\ n';//define line breaks MySQL command export text file example: # 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.htmlLOADDATA INFILE Importing text files MySQL> DELETE  fromStudent1;mysql> LOADDATA INFILE'/tmp/student1.txt' into TABLESchool.student1fields TERMINATED by ','optionally enclosed by '"'LINES TERMINATED by '\ n';

Database migration

Be sure to migrate between the same version # Mysqldump-h source ip-uroot-p123--databases DB1 | Mysql-h Target ip-uroot-p456

Pymysql Module

#安装pip3 Install Pymysql

A link, execute SQL, close (cursor)

Import Pymysqluser=input (' username: '). Strip () Pwd=input (' Password: '). Strip () #链接conn =pymysql.connect (host= ' localhost ', user= ' Root ', password= ' 123 ', database= ' Egon ', 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 needs to be quoted in print (SQL) res=cursor.execute (SQL) #执行sql语句, returns the number of records for a successful SQL query print (RES) cursor.close () Conn.close () If res:    print (' login successful ') Else:    print (' Login failed ')

The specific operation:

Import PymysqlUser =Input'Please enter user name'). Strip () PWD=Input'Please enter your password'). Strip () # Connect to database conn=Pymysql.connect (Host= '192.168.11.61', Port= 3306,User = 'Root', password= 'Zhaoyun',Database = 'DB4', CharSet= 'UTF8'# The first one above is the IP address where your database is connected post is the port number of your database user is the login name of your database password is the password for your database account the database refers to the specific databases that you want to connect charset the character encoding # The following need to create a cursor to make your data connection through this cursor to connectcursor =Conn.cursor() SQL="Select *  fromT1whereName= '%s'  andstudent_id= '%s'"%(User, PWD) # This is information to find the data in the T1 table in your databasePrint(SQL) Res= cursor.Execute(SQL) # The number of records that executed the SQL statement and returned a successful SQL querycursor.Close() # Close your cursor conn.Close() # Close your connectionifRes:Print('Landing Success')Else:    Print('Login Failed')

SQL injection of two execute ()

Data Backup, Pymysql module