How to create and restore Full MySQL backup of all accounts

If you have many websites / accounts on your server, and you want to make backup of all mySQL via a single command, here is how you can do. Creating regular full mySQL backup of all accounts is a very good practice. Creating full MySQL backup is also recommended before performing any mysql upgrade.

How to create full MySQL backup

Login as root via SSH and execute following command:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz

This will create a SQL-based backup of everything in MySQL and save it into your /home directory. The name of your backup file will be alldatabases.sql.gz

You may change the backup file name and location where it will be saved.

How to restore full MySQL backup

To restore a full MySQL backup file, you will need root MySQL password. Run following command to get root mySQL password

tail /root/.my.cnf

The above command will show the root MySQL password. When you have password, run following command:

gunzip < /home/alldatabases.sql.gz | mysql -u root -p

If your saved full mysql backup is at different location, change the path in above command. If the name of your full mysql backup file is different, please change in above command.

You will be asked to enter password. So, enter the password that you have got above from tail /root/.my.cnf command.

Done!! It will take sometime to restore all database depending on size of database file.


Our Top Rated Host

Related Posts