Backup all MySQL Databases?
I'm about to update MYSQL at the weekend.
Is there a way I can perform a backup of just the databases.
I can't do this as an export through PHP MyAdmin, as I suffer timeouts or memory space issues.
-
Backup all MySQL Databases to individual files for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done
Backup everything to one filemysqldump --all-databases > all_databases.sql0 -
The previous reply is a good way to go A alternative if you think you may need to roll back the mysql version is to make a copy of the mysql data files To do this properly you have to take several steps. Go into service manager and uncheck monitor for mysql service stop mysql cp -rav /var/lib/mysql /var/lib/mysql.bak service mysql start Then you can perform the upgrade. Rolling it back is a whole different process but this would allow for a speedy roll back if it was required. 0 -
Is it really this simple or am I being nieve. If I FTP off the server var/lib/mysql Perform the upgrade. If anything goes wrong, roll back to MYSQL 5.6 and ftp the folder back up. ? I just did a dummy run on the ftp and it only took 5 minutes. 0 -
The key here is that when you copy or download the files whichever it is mysql has to not be running 0 -
And no doubt I'll have to shutdown apache to stop new data being added by web site users. 0 -
No you just need to have the mysql service off. 0 -
doh... of course. 0 -
If anything goes wrong, roll back to MYSQL 5.6 and ftp the folder back up. ?
Hello @keat63, One aspect of the upgrade to keep in-mind is that downgrading back to MySQL version 5.6 isn't a supported action if the MySQL upgrade succeeds (if the upgrade fails then MySQL 5.6 will remain installed). Thank you.0 -
Thats reassuring and good to know. 0 -
The update from MySQL 5.6 to 5.7 appears to have gone without a hitch (finger crossed), and went a lot quicker than I expected. Thanks for the info. One thing I picked up on though. After the update, I tried to stop the MySql service to update my.cnf (to disable strict mode), but the command 'service mysql stop' comes back with an error saying unrecognised service. Any ideas ? 0 -
Dont stop Cpanel services by running service xxx stop, instead you should run /scripts/restartsrv_service --stop. Dont forget do disable mysql from tailwatch from WHM > service manager otherwise it will restart your service if it detects its down. 0 -
Prior to the update, I disabled tailwatch, then via terminal I ran 'service mysql stop' This echo'd back that the service had stopped. I took my backups, then retstarted mysql before running the update process. The update appears to have gone without a hitch. Next I modified 'my.cnf' to disable strict mode, but this time, I was unable to stop the mysql service using 'service mysql stop' Instead I restarted my sql via the whm gui. Just wondered why it would stop when at 5.6 but not when at 5.7. 0 -
Hello @keat63, MySQL version 5.7 comes with native support for systemd: MySQL :: MySQL 5.7 Reference Manual :: 2.5.10 Managing MySQL Server with systemd You should always restart the service using /scripts/restartsrv_mysql, however if for some reason it doesn't work and you need to restart the service directly, use the following command (on CentOS 7): systemctl restart mysqld
The service command will automatically redirect to the corresponding systemctl command, but as of MySQL 5.7 you need to enter "mysqld" as the service name instead of "mysql". Thank you.0 -
Backup all MySQL Databases to individual files
for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done
Backup everything to one filemysqldump --all-databases > all_databases.sql
Hi! If I proceed with this for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done where will .GZ files be saved? Thanks, Francisco0 -
mysqldump --all-databases > all_databases.sql 0
Please sign in to leave a comment.
Comments
15 comments