MySQL Database Restore Commands

To perform various backup operations of MySQL databases using mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.

How to Restore MySQL Database?

To take the backup of databases, now we will see how to restore them using following format.

[root@localhost]# mysql -u [username] –p[password] [database_name] <[dump_file.sql]

How to Restore Single MySQL Database?

To restore a database, you must create an empty database on the target machine and restore the database using msyql command.

[root@localhost]# mysql -u root -p pass123 monitor < monitor.sql

If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.

[root@localhost]# mysqlimport -u root -p pass123 monitor < monitor.sql

 

MySQL Database Backup

How to perform various backup operations of MySQL databases using mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.

How to Backup MySQL Database?

To take a backup of MySQL database, the following format of the command can do.

[root@localhost]# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]

How to Backup a Single MySQL Database?

To take a backup of single database, use the command as follows.

[root@localhost]# mysqldump -u root -p pass123 monitor > monitor.sql

How to Backup Multiple MySQL Databases?

If you want to take backup of multiple databases, run the following command.

[root@localhost]# mysqldump -u root -p pass123 –databases zabbix monitor > zabbix_monitor.sql

How to Backup All MySQL Databases?

If you want to take backup of all databases, then use the following command with option –all-database.

[root@localhost]# mysqldump -u root -p –all-databases > all-databases.sql

 

Change a Password for MySQL on Linux

The follwing steps will you through how to reset the root password for MySQL.

Step-1: Stop the MySQL process

[root@localhost]# /etc/init.d/mysqld stop

Step-2: Restart it with the –skip-grant-tables option

[root@localhost]# mysqld_safe –skip-grant-tables &

Step-3: Connect to MySQL using the root user.

[root@localhost]# mysql -u root -p

Once logged in, you should see the following prompt:

mysql>

Change MySQL which database to use:
mysql> use mysql;

Enter the new password for the root user as follows:
mysql> UPDATE user SET password=PASSWORD(“YOUR NEW PASSWORD HERE”) WHERE user=’root’;

Flush the privileges:
mysql> flush privileges;

Exit MySQL:
mysql> quit

Step-4: Now restart MySQL and test your new login.

[root@localhost]# service mysqld restart

[root@localhost]# mysql -u root -p