Database operations--Backup of databases

One using mysqldump for logical backup

# Syntax: # mysqldump-h Server-u user name-p password database name > backup file. sql # Example: # Single Library backup mysqldump-uroot-p123 db1 >-uroot-p123 db1 table1 table2 > db1-table1-table2.sql #后面跟着的 is an address, such as C:\\user# multi-Library backup mysqldump-uroot-p123--databases db1 db2 mysql db3 > db1_db2_ Mysql_db3.sql# back up all libraries mysqldump-uroot-p123--all-databases > All.sql

Two recovery logical backups.

# method One: [[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 if exists school;create database School;use School;

Import and export of three tables

SELECT ... into OUTFILE export text file example: MySQL> SELECT *From School.student1into OUTFILE'Student1.txt'Fields TERMINATED by','//defining field separators optionally enclosed by'"'//defines what symbols to use in a string lines TERMINATED by'\ n'; //example of defining a line break MySQL command to export a text file:#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.htmlLOAD DATA INFILE importing text files MySQL>DELETE from Student1;mysql> LOAD DATA INFILE'/tmp/student1.txt'Into TABLE school.student1fields TERMINATED by','optionally enclosed by'"'LINES TERMINATED by'\ n';

#could be an error .Mysql> SELECT * fromDb1.emp into outfile'C:\\db1.emp.txt'Fields terminated by','Lines terminated by'\ r \ n'; ERROR1238 (HY000): Variable'Secure_file_priv'  isA read only variable#database is the most critical is the data, once the database permissions leaked, then through the above statement can easily export data to a file and then download take away, so MySQL limit, can only export files to the specified directoryin the configuration file [Mysqld]secure_file_priv='c:\\' #data can only be exported to c:\\Restart MySQL re-execute the above statement error: Variable'Secure_file_priv'  isA Read Only

Database operations--Backup of databases