+91 9717203377

Home » Web » Backup and restore mysql database in linux

Backup and restore mysql database in linux

Database management from command line can be very fast rather than visual tools like PHPMyadmin etc.
Some very useful commands are database backup of mysql and import the database into mysql.
mysqldump is a command of mysql which can be use from shell command into linux. It is used to dump local or remote MySQL database mysql tables or collection of databases as a backup into a single sql file.
Before run these commands make sure that MySQL installed on Linux server with administrative privileges.
Backup a single Database

# mysqldump -u username –p database_name > /path/new_dump.sql

Here username is database username, path is location where file need to be save. After press enter it will ask for password.
Backup multiple Databases

# mysqldump -u username -p --databases db1 db2 > new_dump.sql

Backup Database Structure Only

# mysqldump -u username -p -–no-data database_name > new_structure_dump.sql

Backup All Databases

# mysqldump -u username -p --all-databases > new_dump.sql

Backup Some Tables From Database

# mysqldump -u username -p database_name table1 table2 table3 > new_dump.sql

Backup Remote Database

# mysqldump -h SERVER_IP_ADDRESS -u username -p database_name > new_dump.sql

Backup Online From one Database to Other Database

mysqldump -h REMOTE_SERVER -u username1 -p database1| mysql --host=localhost --user=username2 --password local_database

Above Commands are for backup the databases. Sometimes we need to restore the database. First we need to upload the sql file on server if it is on local then use following command.

# mysql -u username -p database_name < databasefile.sql