Mysql backup and recovery commands on windows/linux servers

Mysql backup and recovery commands on windows/linux servers: mysql backup and recovery on windows Servers we often use the mysqldump command to complete these operations, next I will give a detailed summary of the usage of the mysqldump Backup Recovery command. Set mysql to c: disk. the mysql database username is root, the password is 123456, the database name is database_name, And the backup database is stored under the d: disk root directory, the backup database name is backup20070713. SQL (20070713. SQL is the backup date) backup database: mysqldump adopts the SQL-level backup mechanism, which leads data tables into SQL script files and is suitable for upgrading between different MySQL versions, this is also the most common backup method. The Code is as follows: www.2cto.com mysqldump-uroot-p123456 database_name> d:/backup20070713. SQL above windows/linux I usually use the following SQL to back up the MyISAM table: the Code is as follows:/usr/local/mysql/bin/mysqldump-uyejr-pyejr -- default-character-set = utf8 -- opt -- extended-insert = false -- triggers-R -- hex-blob -x db_name> db_name. SQL: the Code is as follows:/usr/local/mysql/bin/mysqldump-uyejr-pyejr -- default-character-set = utf8 -- opt -- extended -Insert = false -- triggers-R -- hex-blob -- single-transaction db_name> db_name. SQL. To implement online backup, you can also use the -- master-data parameter as follows: the Code is as follows:/usr/local/mysql/bin/mysqldump-uyejr-pyejr -- default-character-set = utf8 -- opt -- master-data = 1 -- single-transaction -- flush-logs db_name> db_name. SQL: delete the original database, create a database, and import the backup database. The Code is as follows: mysqladmin-uroot-p123456 drop database_namemysqladmin-uroot-p123456 create database_namemysql-uroot-p123456 database_name the file backed up with mysqldump is an SQL script that can be, there are two ways to import data. Directly use the mysql client www.2cto.com. For example, the Code is as follows:/usr/local/mysql/bin/mysql-uyejr-pyejr db_name <db_name. SQL using the SOURCE syntax is actually not the standard SQL syntax, it is a function provided by the mysql client. For example, the Code is as follows: SOURCE/tmp/db_name. SQL; here, you must specify the absolute path of the file and be the mysqld running user (such as nobody) objects with the read permission. Note: before importing a backup database, if database_name does not exist, you need to create it. It must be the same as the database name in backup20070713. SQL to import the database. Mysqldump (data export tool) mysqldump options db_name [table_name] // back up a single database mysqldump option -- database database-name1 [databases-name2]... // backup one or more mysqldump options for the specified database -- all-database // backup all database link options:-u: Specify username-p: Specify password-h: specify the Server ip address or domain name-P (uppercase): Specify port eg: /usr/bin/mysqldump-u root-h 202.194.132.237-P 3306-p BBS user>/home/wuxiaoxiao/user.txt output content option: -- add-drop-database: before each database creation statement, add the drop database statement -- add-drop-table: before each table creation statement, add the drop table statement-n: does not contain the database creation statement-t: create statement-d: does not contain data output format option: www.2cto.com -- compact: make the output result concise-c -- compact-insert: make the insert statement in the output file contain the field name-T: back up the data in the database table as two files: Data text and table creation SQL -- fields-terminated-by = name (domain delimiter) -- fields-enclosed-by = name (domain quote) -- fields-optionally-enclosed-by = name (optional domain quote) -- fields-escaped-by = name (Transfer Character) eg:/usr/bin/mysqldump-u root-h 202.194.132.237-P 3306-p BBS user-T. /bak character set options: -- default-character-set = name: set the exported client character set. For example: mysql-u root-p -- compact -- default-character-set = utf8 BBS user> test.txt