Backup and restore MySQL database

To backup a MySQL database

See this link here

shell> mysqldump [arguments] > file_name
shell> mysqldump --all-databases > dump.sql

shell> mysqldump --triggers --routines --all-databases > dump.sql  
shell> mysqldump --databases db1 db2 db3 > dump.sql

shell> mysqldump --databases dbname > dump.sql

OR omit the --databases

shell> mysqldump test > dump.sql

To dump only specific tables from a database use;

shell> mysqldump dbmane table1 table3 table7 > dump.sql

To dump only the structure from a database use;

shell> mysqldump --no-data dbmane > dump.sql 

shell> mysqldump --d dbmane > dump.sql  

Note: -d includes the triggers. 

To dump only the routines from a all databases use;

mysqldump -u username -p -n -t -d -R --all-databases > routines.sql
 
To add the host 192.168.254.198 and the user root, do;

shell> mysqldump --host=192.168.254.198 --port=3307 -u root -p dbmane > dump.sql

See also my post here for details on how to simply copy the table structure.

Real examples with my_db:

The following will backup the structure, triggers, functions and procedures; 
mysqldump --host=[...] -udev -p --no-data --triggers --routines my_db > my_db_structure.sql

 

The following will backup the entire database, as above but including the data;

mysqldump --host=[...] -udev -p --triggers --routines my_db > my_db_all.sql

To restore a MySQL database

See this link here

shell> mysql -u root -p < dump.sql

Within mysql (first login, see below);

mysql> source dump.sql

To login to mysql, do;
mysql -u username -p

If the dump file does not create the database, then this must first be created;

shell> mysqladmin create dbname

Within mysql (this works for me);

mysql> CREATE DATABASE IF NOT EXISTS dbname; mysql> USE dbname; mysql> source dump.sql

Real example with my_db:

Firstly, ensure the database has been created by logging in and doing:

 

CREATE DATABASE mydb;

 

then;

 

mysql --host=[...] -udev -p my_db < my_db_all.sql

 

 

To backup and restore database users and grants, see here