Backup all MySQL Databases?

keat63

Well-Known Member
Nov 20, 2014
1,486
124
93
cPanel Access Level
Root Administrator
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

Well-Known Member
Jul 18, 2013
1,099
452
113
UK
cPanel Access Level
Root Administrator
Backup all MySQL Databases to individual files
Code:
for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done
Backup everything to one file
Code:
mysqldump --all-databases > all_databases.sql
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,569
221
343
Chesapeake, VA
cPanel Access Level
DataCenter Provider
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.
 
  • Like
Reactions: rpvw

keat63

Well-Known Member
Nov 20, 2014
1,486
124
93
cPanel Access Level
Root Administrator
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.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,917
2,248
363
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

Well-Known Member
Nov 20, 2014
1,486
124
93
cPanel Access Level
Root Administrator
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

Member
Oct 18, 2016
5
0
1
Bistrita
cPanel Access Level
Root Administrator
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

Well-Known Member
Nov 20, 2014
1,486
124
93
cPanel Access Level
Root Administrator
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

Administrator
Staff member
Apr 11, 2011
47,917
2,248
363
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):

Code:
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.