MySQL Data backup

1. Physical Backup: Copy database files directly for large database environments (such as data files, control files, archive log files, etc.). 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. -----> Insert Record Operations

# Syntax: # mysqldump-h Server-u user name-p password database name > backup file. SQL When you are logged on locally, this can be done without writing, and when other users log in, they have to write the-H server #  Example:1-uroot-p123456 day47 >Class Employee Department > e:\\test-c-e.sql 2-uroot-p123456--databases test DB2 Zuoye > e:\\dbzzz.sql3-uroot-p123456- -all-databases > E://all.sql

using mysqldump for logical backups

  >mysql-uroot-p123456 Test < before logging in  Day47.sqldrop database day47;drop database test;create database day47;create database Test;mysql -uroot-p123456 day47 < Day47.sqlmysql -uroot-p123456 Test & Lt Day47.sql #    method 2.mysql Log in to restore MySQL  > use db1;mysql  > SET sql_log_bin=0; #   Set the log, if the write log is equal to 1, do not write is equal to 0. Generally do not set  mysql> source E:\\day47.sql;  3. Note: If the backup/ if   exists school;create database School;use School;  

Restore Backup

When we are not logged in to the user, review the recovery status, you can use the statement, do not login

" Use test;show tables; "   # Note It's double quotes . " show databases; " # note the semicolon inside the double quotation mark

I. Export of tables Select* fromStu into outfile'E:\\userinfo.txt'Fields Terminatedby','Lines terminated by'\ n';#Separate fields by commas, separated by ' \ n ' between rows1290-the MySQL Server isRunning with the--secure-file-priv Option So it cannot the execute this statement to write an error. The new version does not support you to directly specify the path write, in order to ensure the security of the data. If a hacker gets access to SQL, take your data and take it. Your data is not safe. So how to solve this mistake? Show variables like'%secure%'; Workaround: Change the configuration file [Mysqld]secure_file_priv='e:\\'

Export

Second, the table import use Bak;show tables;create table user (ID int primary key auto_increment,name char ( C3>e:\\userinfo.txt','\ n'; # Import Table

Import

be sure to migrate between the same versions # mysqldump-h source ip-uroot-p123--databases db1 | mysql-h target ip-uroot-p456

MySQL Data backup