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.
[dedicated_hosting]
