Backup all MySQL Databases?

[keat63]

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.

 

[rpvw]

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 file

mysqldump --all-databases > all_databases.sql

 

[GOT]

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.

 

[keat63]

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.

 

[GOT]

The key here is that when you copy or download the files whichever it is mysql has to not be running

 

[keat63]

And no doubt I'll have to shutdown apache to stop new data being added by web site users.

 

[GOT]

No you just need to have the mysql service off.

 

[keat63]

doh... of course.

 

[cPanelMichael]

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.

 

[keat63]

Thats reassuring and good to know.

 

[keat63]

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 ?

 

[Tiberiu]

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.

 

[keat63]

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.

 

[cPanelMichael]

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.

 

[fmosse]

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 file

mysqldump --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,
Francisco

 

[hmaddy]

mysqldump --all-databases > all_databases.sql