MySQL Database learning "11th" IDE Tool introduction and data backup

I. Introduction of IDE Tools

The production environment is also recommended for MySQL command line, but for our testing purposes, you can use the IDE tools Navicat

Second, MySQL data backup

#1. Physical backup: Direct copy of database files for large database environments (

such as data files, control files, archive log files, etc.).


#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.

You can also use IDE tools to make backups, and click Dump SQL ... then we'll focus on the logical backup of the mysqldump implementation.

1. Using Mysqldump for logical backups

#语法: # mysqldump-h Server-u user name----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

#示例:

1. Single-Library backup
mysqldump-uroot-p123456 day47 > E:\\day47.sql
MYSQLDUMP-UROOT-P123456 Test Class Employee Department > E:\\test-c-e.sql
2. Multi-Library Backup
mysqldump-uroot-p123456--databases Test DB2 Zuoye > E:\\dbzzz.sql
3. Back Up all libraries
mysqldump-uroot-p123456--all-databases > E://all.sql

2. Restoring a logical backup

Method 1. Recover c:\users\administrator>mysql-uroot-p123456 Test < Day47.sqldrop database Day47;drop before logging in Test;create database day47;create Database test;mysql-uroot-p123456 day47 < day47.sqlmysql-uroot-p123456 test < Day47.sql  #也可以在自己新建的空数据库里添加你备份的库方法2. mysql log in to recover mysql> use db1;mysql> SET sql_log_bin=0;  #设置日志, if 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 you back up/restore a single library, you can modify the SQL file drop database if exists school;create database school; Use school;

Before the recovery is not logged in, if you want to see the recovery is not every time you have to log in to see Ah, then we can do so, we do not have to login directly to view.

No more logging in to execute SQL statements.
Mysql-uroot-e "use test;show tables;"   #注意是双引号mysql-uroot-e "show databases;" #注意分号在双引号里面

Precautions: 1. Fully 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

3. Export and import of tables

I. Export of tables use Day47select * from Userinfo;select * from userinfo to outfile ' e:\\userinfo.txt ' fields  terminatedby ', ' l Ines terminated by ' \ n ';  #将字段之间按逗号分开, the line between the use of ' \ n ' separate this write 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:\\ '

Lines terminated by ' \ n '; #导入表

4. Database Migration

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

MySQL Database learning "11th" IDE Tool introduction and data backup