MySQL Export command

Database backup

/data/mysql/bin/mysqldump-hlocalhost-u ' root '-P ' do ' my_db--single-transaction-q | gzip > my_db.sql.gz

Find '/home/yourname/backup/mysql '-name ' 123.txt '-ctime +5 | Xargs RM-RF;

Mysqldump-u "root"-P "pass"--single-transaction--master-data=2--flush-logs--databases mydb1 mydb2>mydb.sql

Structure Export

/usr/local/mysql/bin/mysqldump-uroot–p ' Passport ' youdb--single-transaction--master-data=2-d--flush-logs> Youdb_schema.sql

Data export

/usr/local/mysql/bin/mysqldump-uroot-p ' ka '--single-transaction--master-data=2-t--flush-logs my>my_data.sql

Mysqldump-uroot-p mydb--single-transaction--master-data=2-t--flush-logs--where= "pid=100776562 limit 10000" table1& Gt;table1_100776562_data.sql
Mysqldump-u root-p database name--no-create-db=true--no-create-info=true--add-drop-table=false--where= "id >1000" indicates > Export file name. sql;

commands to back up the MySQL database

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

backing up the MySQL database to a format with a delete table

Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.

Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

compress the MySQL database directly backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

back up a MySQL database (some) table

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

back up multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

backing up the database structure only

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql

commands to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

restore a compacted MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

to transfer a database to a new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

/usr/local/mysql/bin/mysqldump-uroot-p mydb-t--flush-logs--where= "created_at< ' 2013-09-01 '" Search_log | gzip > search_log_lt_2013-09-01.sql.gz

Export to File

SELECT field1,field2 from mytable to OUTFILE '/backup/outfile.txt ';

MySQL Export command