xianx/Basic MySQL Backup.md

Latest 'X' Records from a Single Table

mysqldump -u username -p --where="(%OPTION%)" Database_Name Table_Name  > /path/to/mysql_DUMP_File.sql

where %OPTION%

--where="TRUE ORDER BY id DESC LIMIT 1000"
--where="TRUE LIMIT 500"
--where="mailbox_id=45"
--where="mailbox_id=45 AND TRUE ORDER BY id DESC LIMIT 300"

Latest 'X' Records from a All Database

mysqldump -u User_Name -p --where="true limit 1000" --all-databases > /path/to/mysql_DUMP_File.sql

--opt => Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

All Database Backup and Restore

Backingup all database

mysqldump --user=User_Name --password=Password --all-databases > /path/to/mysql_DUMP_File.sql

Single Table Backup and Restore

Backingup a single table from a database

mysqldump -u username -p Database_Name Table_Name > /path/to/mysql_DUMP_File.sql

mysqldump --user=User_Name --password=Password Database_Name Table_Name > /path/to/mysql_DUMP_File.sql

Restoring the single table into another database

mysql -u username -p Database_Name < /path/to/mysql_DUMP_File_Name.sql

mysql --user=User_Name --password=Password Database_Name > /path/to/mysql_DUMP_File.sql

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

mysqldump -u dave -ppassword -h localhost --ignore-table=my_db_name.my_table_name my_db_name