MySQL Command

Basic MySQL Backup.md

** Backup a single database with verbose output**

mysqldump -u USERNAME -pPASSWORD -v DATABASENAME >  DATABASENAME.sql

** Restore single database with verbose output**

mysql -u USERNAME -pPASSWORD -v DATABASENAME <  DATABASENAME.sql

Limiting the number of records.md

Limiting the number of records from mysqldump

mysqldump --opt --where="1 limit 1000000" database

that would give you the first million rows from every table.

---

As the default order is ASC which is rarely what you want in this situation, you need to have a proper database design to make DESC work out of the box. If all your tables have ONE primary key column with the same name (natural or surrogate) you can easily dump the n latest records using:

mysqldump --opt --where="1 ORDER BY id DESC limit 1000000" --all-databases > dump.sql This is a perfect reason to why you should always name your PK's id and avoid composite PK's, even in association tables (use surrogate keys instead).

---

f you want to get n records from a specific table you can do something like this:

mysqldump --opt --where="1 limit 1000000" database table > dump.sql This will dump the first 1000000 rows from the table named table into the file dump.sql.

---

Source : https://techtun.es/hzhzLZ

MySQL Backup of Latest X Records.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

MySQL Backup Restore.md

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 -v Database_Name < /path/to/mysql_DUMP_File_Name.sql

Restoring into another Remote Host database

mysql --host=Host_Name --user=User_Name --password=Password --verbose Database_Name < /path/to/mysql_DUMP_File.sql

MySQL Backup with Ignore Table.md

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