Backups

all databases

mysqldump -u [user] -p[password] –all-databases > [filename].sql

 

specific databases

mysqldump -u [user] -p[password] [database_name] > [filename].sql

 

specific database but omitting specific tables

mysqldump -u [user] -p[password] [database_name] –ignore-table=db1.tbl1 –ignore-table=db2.tbl1 > [filename].sql

 

restore

mysql -u [user] -p[password] [database_name] < [filename].sql

 

restore from csv

mysql -u username -ppassword –local-infile [YOUR_DATABASE] -e “LOAD DATA LOCAL INFILE ‘[YOUR_FILE_PATH_CSVname.csv]’ INTO TABLE [table_name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n'”

 

to ignore the first line in case you have table headers.

mysql -u username -ppassword –local-infile [YOUR_DATABASE] -e “LOAD DATA LOCAL INFILE ‘[YOUR_FILE_PATH_CSVname.csv]’ INTO TABLE [table_name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 ROWS””

 

backup specific table from a database

mysqldump -u [user] -p[password] [database_name] [table] > [filename].sql

 

backup specific table with a different name (works cool to do migrations of large databases)

mysqldump -u [user] -p[password] [database_name] [table] | sed ‘s/table/new_table_name/g’ > [filename].sql

 

copy database with a different name:

first, create the new database (leave it open)

then:

mysqldump -u <user name> –password=<pwd> <original db> | mysql -u <user name> -p <new db>