backing up the database
1. Use the mysqldump command to back up
- Back up a database: Mysqldump-u user name-p password database name [table name 1, table Name 2 ...] > backup file path and name. sql
If there is no table name, back up the entire database eg:mysql-u root-p test Studentinfo >d:\test.sql
- Back up multiple databases: mysqldump-u user name-p--databases database 1, database 2 ... > backup file path and name. sql
- Back up all databases: Mysqldump-u username-P--all-databases > backup file path and name. sql
Note: The backup time is not necessarily the. sql file name extension, which can be specified arbitrarily
2. Copy the entire database directory directly
- Copy the MySQL database file directly, it is best to stop the service first, ensure the data is consistent
- The table for the InnoDB storage engine does not apply
- Preferably the same version of the MySQL database when restoring
3. Quick Backup using the Mysqlhotcopy tool
- You can use this tool if you cannot stop the MySQL service while backing up. Faster than the mysqldump command.
- This tool is a Perl script that is used primarily under Linux
- Fast backup with lock TABLES, FLUSH TABLES, and CP
- Principle: First add a read operation lock to the backed up database, write the data in memory to the hard disk database with flush tables, and then copy the backed up database file to the target directory
Restoring a database
1. Restore using MySQL command
- Mysql-u root-p[database name] < Backup.sql
2. Copy directly to the database directory
- Copy the directory to the corresponding folder as in the previous copy directory backup
- Windows directory: C:/mysql/data c:/document and Settings/all users/application data/mysql/mysql server5.1/data default paths
- Linux under:/var/lib/mysql//usr/local/mysql/data usr/local/mysql/var, etc. default directory
- A database backed up with Mysqlhotcopy is also restored in this manner, and after being copied to the database directory, the users and groups of the database become mysql:chwon-r mysql.mysql DataDir
Database Migration
1. Database Migration classifications:
- MySQL migrating between databases of the same version
- Migrating to other MySQL versions of the database
- Migrating to other types of databases
migrating between the same versions of MySQL:MYSQLDUMP-H host name-U root-password=password1-all-databases | Mysql-h Host2-u Root-password=password2
Use Select ... into outfile exporting text files
- SELECT [column name] from table name [where condition] into OUTFILE ' target file path ' [OPTION];
export text files with mysqldump
- Mysqldump-u root-ppassword-t Directory dbname table [OPTION];
exporting a text file with the MySQL command
- Mysql-u root-ppassword-e "SELECT statement" dbname > D:/backup.txt
- To export an XML file:
-
- Mysql-u root-ppassword-xml-x-E "SELECT statement" dbname > D:/backup.xml
- To export an HTML file:
-
- Mysql-u Root-ppassword--html-h-E "SELECT statement" dbname > d:/backup.html
