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>