How do i backup and restore a mysql database to another server?
|
This article shows you several practical examples on how to perform various backup operations of MySQL/MariaDB databases using the mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux. Show
mysqldump is a command-line client program, it is used to dump local or remote MySQL databases or collections of databases for backup into a single flat file. We assume that you already have MySQL installed on the Linux system with administrative privileges and we believe that you already have a small amount of knowledge of MySQL. If you don’t have MySQL installed or don’t have any exposure to MySQL then read our articles below.
How to Backup MySQL Database in Linux?To take a backup of MySQL databases or databases, the database must exist in the database server and you must have access to it. The format of the command would be. # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] The parameters of the said command are as follows.
How to Backup a Single MySQL Database?To take a backup of a single database, use the command as follows. The command will dump the database [rsyslog] structure with data onto a single dump file called rsyslog.sql. # mysqldump -u root -ptecmint rsyslog > rsyslog.sql How to Backup Multiple MySQL Databases?If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data into a single file called rsyslog_syslog.sql. # mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql How to Backup All MySQL Databases?If you want to take a backup of all databases, then use the following command with the option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql. # mysqldump -u root -ptecmint --all-databases > all-databases.sql How to Backup MySQL Database Structure Only?If you only want the backup of the database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a file rsyslog_structure.sql. # mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql How to Backup MySQL Database Data Only?To backup database data without structure, use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql. # mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql How to Backup a Single Table of Database?With the below command you can take a backup of a single table or specific tables of your database. For example, the following command only takes a backup of the wp_posts table from the database wordpress. # mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql How to Backup Multiple Tables of Database?If you want to take a backup of multiple or certain tables from the database, then separate each table with space. # mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql How to Backup Remote MySQL DatabaseThe below command takes the backup of the remote server [172.16.25.126] database [gallery] into a local server. # mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql How to Restore MySQL Database?In the above tutorial, we have seen how to take the backup of databases, tables, structures, and data only, now we will see how to restore them using the following format. # # mysql -u [username] –p[password] [database_name] < [dump_file.sql] How to Restore Single MySQL DatabaseTo restore a database, 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. # mysql -u root -ptecmint rsyslog < rsyslog.sql If you want to restore a database that already exists on the targeted machine, then you will need to use the mysqlimport command. # mysqlimport -u root -ptecmint rsyslog < rsyslog.sql In the same way, you can also restore database tables, structures, and data. If you liked this article, then do share it with your friends. If You Appreciate What We Do Here On TecMint, You Should Consider:TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all. If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation. We are thankful for your never ending support. How do I restore a MySQL database from one server to another?How to Restore a MySQL Database with Command Line or Restore Tools. Use the mysql command to restore a database from the command line. ... . Restore all databases in MySQL from the dump file using mysqldump.. Use mysqldump to restore one table from the dump file.. Restore a MySQL database from the .sql file using Workbench.. How do I backup and restore MySQL database?You can backup and restore a MySQL Database using mysqldump via command line or PHPMyAdmin.. Restore a MySQL Database. ... . Restore a Single MySQL Database From a Full MySQL Dump File. ... . Export and Import a MySQL Database in One Command.. Can I copy MySQL data directory to another server?You have to shut down mysql server (which is not good, if it's a production server) You have to make sure the permission of data (mysql) directory is same as the previous one. You will have to monitor the mysql_error log while starting the second server.
What is the best way to backup MySQL database?How to Backup MySQL Database. Open phpMyAdmin from your web hosting control panel like cPanel.. Select the database from the sidebar navigation panel of phpMyAdmin.. Click the Export link from the top navigation bar.. Choose Custom option in the Export page.. Select the tables that you want to backup.. |
