MYSQL db question. To close or not to close ?

mike2010 · 04-12-2015, 04:33 PM

hello.

I need to backup about 40 sites that have mysql db's..on an every night basis.

What I used to do (years ago) cron wise was something along the lines of :

mysqldump -uadmin -p$(cat /etc/psa/psa.my/.psa.myshadow) MyActualDB > /var/www2/fartupback/myDB.sql

And I did that for every domain. This way it would keep a copy of the closed mysql DB's ...into sql file.

BUt is that really necessary? Could I just back them all up at once, with 1 command...since their in the same location ?

var/www2/fartupback/myDB1.sql
var/www2/fartupback/myDB2.sql

And can I do it without the closed off .sql? In other words, can I just drop them all at once in a different directory location...As an open mysql table ? And if done so that way. Is the chance significantly increased that I could have a corrupt table , if/ when I ever need to restore it like that ?

sundialsvcs · 04-12-2015, 07:56 PM

The tables won't be corrupted, in any case, but the odds are high that mysqldump won't be able to run ... and also that, while it's running, no one else will be able to write anything.

Also: Never treat a database as "a collection of files," even if that is physically true.

They're a database, and they must always be manipulated only by the SQL server and by its duly-appointed utilities. (Also, don't assume that the host operating system will actually prevent you from trying . . . so, "don't try.")

mike2010 · 04-12-2015, 10:14 PM

Quote:

Originally Posted by sundialsvcs

The tables won't be corrupted, in any case, but the odds are high that mysqldump won't be able to run ... and also that, while it's running, no one else will be able to write anything.

Also: Never treat a database as "a collection of files," even if that is physically true.

They're a database, and they must always be manipulated only by the SQL server and by its duly-appointed utilities. (Also, don't assume that the host operating system will actually prevent you from trying . . . so, "don't try.")

thanks.

so u recommend closing them and.SQL file'ing them first?

I brought that issue up...since I had a recent db failure with plesk CP. And tech support mentioned to back things up that way -

http://kb.odin.com/en/6586

Code:

I. Force InnoDB Recovery
Stop mysqld and back up all files located in the /var/lib/mysql/ directory:
# /etc/init.d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/

thoughts ?

TenTenths · 04-13-2015, 02:11 AM

As has been said DO NOT try and do things at O/S level with the running database files, there will be inconsistencies.

While you CAN dump all databases to a .sql file in one go it's much better practise to do it for each individual database as it makes restoration etc. easier to deal with if it's a single file per database rather than having to process the whole dump for a single database or table.

If you're at the stage where you've 40+ sites then have you considered doing MySQL replication? With replication you'll have an off-site "hot" copy of the database, and as it's on a replication client you can stop the replication, shut down the database, lock tables for backups etc. etc. and then restart replication and it'll all catch up.

This is my basic backup script http://centos.tips/mysql-backup/ which has options to run on a replication client or on a main server doing locking. It can also compress and optionally copy the files to another backup server.

mike2010 · 04-13-2015, 02:46 AM

nice script ^

But ok, i'll do everything the .sql way.

Quote:

mysqldump -uadmin -p$(cat /etc/psa/psa.my/.psa.myshadow) MyActualDB > /var/www2/fartupback/myDB.sql

Since I just have to make the cron for each site once, and it's good forever, and backs up everynight. then my automatic backup (disk-to-disk hardwired) runs everynight around 3am to include the /var/www2/fartupback/* location. (all the .sql files)

thanks bud.

TenTenths · 04-13-2015, 02:55 AM

You don't need to cron separately for each site, my script will allow you to specify in DBLIST so you can have "MyActualDB MyActualDB2 MySite MySite2" and it'll go through the list and do a separate dump for each site.

jlinkels · 04-13-2015, 04:39 AM

Quote:

Originally Posted by mike2010

Code:

I. Force InnoDB Recovery
Stop mysqld and back up all files located in the /var/lib/mysql/ directory:
# /etc/init.d/mysqld stop
# mkdir /root/mysql_backup
# cp -r /var/lib/mysql/* /root/mysql_backup/

thoughts ?

Yes, this is absolutely the wrong way. First your database is not accessible during this process. Secondly, mysqldump is provided to make backups of a database while it is running. Third the sum of the files is not necessarily a valid database.

Quote:

Originally Posted by sundialsvcs

The tables won't be corrupted, in any case, but the odds are high that mysqldump won't be able to run ... and also that, while it's running, no one else will be able to write anything.

Do you mean that during mysqldump no one is able to write in the database? AFAIK mysqldump is transparant to the user's access because mysqldump is running from within the database. Any write action which could interfere are simply deferred until the dump is completed. Therefor mysqldump should be preferred (and in fact the only) way to back up a database.

jlinkels

Habitual · 04-13-2015, 08:59 AM

mysqlhotcopy never gets any "love".

TenTenths · 04-13-2015, 09:20 AM

Quote:

Originally Posted by Habitual

mysqlhotcopy never gets any "love".

Because the world and its dog use INNODB tables for anything serious.

mike2010 · 04-13-2015, 12:46 PM

how about this route

Quote:

mysqldump -u username -ppassword all-databases > dump.sql

(

hiding...incase he gets yelled at again..)

and does the above dump them with their own sql names? (news.sql , bikes.sql , etc) Or does it just throw everything in 1 big sql file .

mike2010 · 04-13-2015, 12:53 PM

and should apache (httpd) be turned off, when doing such?

please respond quick (if possible)

I appreciate it much.

TenTenths · 04-13-2015, 01:40 PM

Quote:

Originally Posted by mike2010

and does the above dump them with their own sql names? (news.sql , bikes.sql , etc) Or does it just throw everything in 1 big sql file .

That dumps them in to one big sql file. Read the second paragraph of post #4 for my thoughts on this.

jlinkels · 04-13-2015, 05:41 PM

Quote:

Originally Posted by mike2010

and should apache (httpd) be turned off, when doing such?

No why?

Maybe you should sit and lean back for a while and understand how you interact with MySQL. What mysqldump does. How mysqldump interacts with other database processes. Hoe MySQL handles various concurrent jobs.

Again, mysqldump is a mysql process which lives together perfectly with live transactions on a live database.

And frankly I don't see the problem in backing up 40 databases in separate files. With a bash script you can perfectly well quiery mysql for all names of all databases and put those in a list. Then, using this list, you can exececute mysqldump on each and everyone of them.

jlinkels

mike2010 · 04-13-2015, 10:26 PM

I was checking out the script TenTenths recommended just now. It seems decent, but there's a much shorter one here that was also recommended to me -

http://dev.mensfeld.pl/2013/04/backu...eparate-files/

If someone could help me get this right with this admin shadow'd password thing...i'd be really happy.

this part - -uadmin -p$(cat /etc/psa/psa.my/.psa.myshadow)

if u don't feel like clicking link..code is below

Code:

#! /bin/bash

TIMESTAMP=$(date +"F")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump
 
mkdir -p "$BACKUP_DIR/mysql"
 
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done

mike2010 · 04-14-2015, 11:30 PM

looks like nobody cares..