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 --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

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.

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

To backup and restore database users and grants, see here