How to Backup MySQL Database?
[root@server ~]# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
How to Backup a Single MySQL Database?
[root@server ~]# mysqldump -u root -pcentos123 wordpress > wordpress.sql
How to Backup Multiple MySQL Databases?
[root@server ~]# mysqldump -u root -pcentos123 –databases joomla wordpress > joomla_wordpress.sql
How to Backup All MySQL Databases?
[root@server ~]# mysqldump -u root -pcentos123 –all-databases > all-databases.sql
How to Backup MySQL Database Structure Only?
If you only want the backup of database structure without data,
then use the option –no-data in the command.
The below command exports database [wordpress] Structure into a file wordpess_structure.sql.
[root@server ~]# mysqldump -u root -pcentos123 -–no-data wordpress > wordpress_structure.sql
How to Backup MySQL Database Data Only?
To backup database Data only without structure,
then use the option –no-create-info with the command.
This command takes the database [wordpress] Data into a file wordpress_data.sql.
[root@server ~]# mysqldump -u root -pcentos123 –no-create-db –no-create-info wordpress > wordpress_data.sql
How to Backup Single Table of Database?
With the below command you can take backup of single table or certain tables of your database.
For example, the following command only take backup of wp_posts table from the database wordpress.
[root@myserver ~]# mysqldump -u root -pcentos123 wordpress wp_posts > wordpress_posts.sql
How to Backup Multiple Tables of Database?
If you want to take backup of multiple or certain tables from the database, then separate each table with space.
[root@myserver ~]# mysqldump -u root -pcentos123 wordpress wp_posts wp_comments > wordpress_posts_comments.sql
How to Backup Remote MySQL Database
The below command takes the backup of remote server [10.10.12.143] database [wordpress] into a local server.
[root@myserver ~]# mysqldump -h 10.10.12.143 -u root -pcentos123 wordpress > wordpress.sql
How to Restore MySQL Database?
[root@myserver ~] # mysql -u [username] –p[password] [database_name] < [dump_file.sql]
How to Restore Single MySQL Database
To restore a datab6ase, you must create an empty database on the target machine and
restore the database using msyql command.
For example the following command will restore the rsyslog.sql file to the rsyslog database.
[root@server ~]# mysql -u root -pcentos123 wordpress < wordpress.sql
If you want to restore a database that already exist on targeted machine,
then you will need to use the mysqlimport command.
[root@server ~]# mysqlimport -u root -pcentos123 wordpress < wordpress.sql
linuxodisha 