Previous Thread
Next Thread
Print Thread
Hop To
#184181 05/12/2007 2:18 PM
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
I do think this is somthing all Threads 7 users should know.
Cron + Backup Database.

Hi ive been rummaging around the tinternet,
and looking for a solution to backup mysql database
and also make it into a cron job for nightly back ups.

Why?
Well coming from classic we didnt have to worry about databases and looking after them, so if there was documentation covering this with Thread 6 then i dont have that documentation.

So now i'm on V7 Threads, and its all new, i'm now getting worried about backups and making sure i have steps in place
if somthing was to go wrong or DIE.

Currently i'm manually downloading them via my server control panel not phpadmin but via Direct Admin.(cp)
But there only about 30mb .tar.gz
But that is sure to grow and grow and then my problems will start.

So i'm asking for help with this as i have read on numerous occasions that once your database gets to xx size or time out
your asking for problems, and i would like to at the least
begin to address this before its too late.

Originally Posted by Gimzo
MySQL Export: mysqldump -uusername -ppassword dbname > dbname.sql
MySQL Import: mysql -h localhost -uusername -ppassword dbname < dbname.sql

I want be able to make a cron job (Script) to automate this in the night.
Links or any Help and advice would be appreciated. smile


Rather than tears later.

Thanks.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jul 2006
Posts: 116
Likes: 4
P
Member
Member
P Offline
Joined: Jul 2006
Posts: 116
Likes: 4
I am using this script:

Code
#!/bin/sh
CDATE=`date '+%d'`

mysqldump -uusername -ppassword database > /home/backup/database.sql
tar czvf /home/backup/database.tar.gz /home/backup/database.sql
cp /home/backup/database.tar.gz /home/backup/databases/database-${CDATE}.tar.gz
This generates 1 month of database backups in the /home/backup directory. I am additionally using rsync to mirror the backup directory on my second server.

Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Thank's smile
I'm going to play more than dumb, which isnt that dfficult
so it can help others who are curious.

The script about does need to be modified to suit your paths.
It needs to be saved in a file ending in .sh
uploaded to a directory to be used.

Is that about right?


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jul 2006
Posts: 116
Likes: 4
P
Member
Member
P Offline
Joined: Jul 2006
Posts: 116
Likes: 4
Quote
The script about does need to be modified to suit your paths.
Yes

Quote
It needs to be saved in a file ending in .sh
uploaded to a directory to be used.
No, the .sh ending is not necessary. I called the script on my installation "zbackup".

You need to copy the script to your /etc/cron.daily directory and chmod it to 755. All scripts in this directory will be automatically executed every day in the morning.

Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Thank you smile

I have the option via my cp (Direct Admin) to select the path
where the script would be located, and the cron can run from
there, well that's what I'm going to try.

What's your thoughts on this one?
I found it on a google search ?
Good or Bad ?

Its big but covers a lot of things too, e-mailing when done?
Or is it just over the top.

Link

Thanks for any help wink

Last edited by Mark S; 05/12/2007 8:27 PM. Reason: Removed the copy and paste as it was Huuuuuge lol

BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 1,344
G
veteran
veteran
G Offline
Joined: Jun 2006
Posts: 1,344
I have a nightly cron job that backups the database and it is done through webmin.

Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Help i have a problem frown

Its either something stupid or something worse?

I'm in at root and i'm running the command just to test
the paths before a start any scripts.

I get this error and a google search shows 5 results frown

mysqldump: Got error: 1103: Incorrect table name 'test.sql' when doing LOCK TABLES

To get that error i'm typing in the following.
User name and passwords are changed.

mysqldump -uXXXX -p1234 my_table test.sql

Ive been trying to sort it for hours now frown

My Table name does have something similar too

my_table

but that's it?

i can connect in via the command line.
and if i run the mysqldump with out a file name
mysqldump -uXXXX -p1234 my_table
it wizzes past my screen in Putty and stops thankfully.

Any ideas Help !?


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Working now (O the fool)

mysqldump -uXXXX -p1234 my_table test.sql

is now

mysqldump -uXXXX -p1234 my_table >test.sql

Added ">"

May help someone, ?


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
I use this (crafted her myself, modified as to not show all of my sites, since it can get extensive):

Code
[root@server code]# cat backup.c 
# Create Backups with Today's Date
tar -cvpzf /home/backups/code/temp/ugn-`date '+%d-%B-%Y'`.tgz /home/ugn/
tar -cvpzf /home/backups/code/temp/mysql-`date '+%d-%B-%Y'`.tgz /var/lib/mysql/
# Change permissions on the above files to local user.
chown -R backups:backups /home/backups/code/temp/
# Upload Data
/home/backups/code/temp
ncftpput -R -b -DD -P 2121 -f /home/backups/remotebackup.conf /data /home/backups/code/temp
# Email user with completion
echo "The monthly backup of critical domains has been completed" | mail -s "Monthly Backup Completed" owned.ugn

Basically:
Backs up webspace.
Backs up Database
Changes owner to the backup user.
Uploads to my home machine.
Deletes the backup on the server.
Emails me that its complete.

Now here my "backup MySQL" backs up the mysql data directory directly; you could easily just program in your MySQL dump paramaters in its place.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
I'm finding all this very helpful, cheers
i'm sure its going to help others too.

I'm sure to post more lol...
Ive had to forget my script as ive been on with this and that
all day now and eyes are going fuzzy.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
The Fuzzy Bunny Compells you...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
ive just learned how damaging this command is when not finished and hitting the return key too soon ?

Example : i hit it when it looked like this by mistake.
chown -R backups:backups /

I stopped it as soon as i seen it, but too late.
I've just changed most of the ownerships on my server frown
I cant log in to my server control panel now.

Looks like a re-install and a bill to match frown

Gutted frown

As i walk the street looking for a cat to kick
Please note no animals were hurt on my walk, it was too dark,
and they were too fast for me, and i really couldnt
be arsed running after one. I want to cry.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Some of these commands, especially if you have root access can be quite dangerous. I've had my fair share of blunders.

Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Its still alive (Just) so I'm backing up what i can wink
Forums are Still on line ha ha..

My customers think there getting new hardware lol...
Hence the downtime, when i get a re-install smile

What is the extra Hardware one may ask?
A FAN smile smile smile


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
haha, never ever do anything to / lol... Especially "rm -rf /".

At least it was just a chown issue, you can fix it as root, should you know the proper owners.

What're you re-installing?


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
The OS it sounds like wink

Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
Lol, you don't need to reformat th eos, just go in and update things that you b0ged :x...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
Ive decided to go for a re-install ;(

Its the only way to be sure about the ownership of the file being correct and preventing future problems too.

I tweaked one of my forums today and got a snotty reply.

/home/domains/mysite.co.uk/public_html/forums/libs/mysql.inc.php on line 210

As i'm still finding that ownerships are still messed up, but give me some credit ??

I fixed it went in and made the mysql permissions back to mysql,
and it seems to be working now.

In a spooky kind of a way ive learned so much in a short time,
and continue to learn smile



BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
and annoy me on msn :x


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
Originally Posted by Gizmo
and annoy me on msn :x


ROFL - Come on you know you enjoy it.

Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
Gives me someone, other than Glider, to bother during the day wink...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jul 2006
Posts: 4,057
Joined: Jul 2006
Posts: 4,057
This topic could kill me or save me again lol..

Server Online and settling down again.

So back to where i was, before i cocked it all up.

Now i'm going to be very careful.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Joined: Jun 2006
Posts: 16,299
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,299
Likes: 116
lol, don't choke wink


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Bots
by Outdoorking - 04/13/2024 5:08 PM
Can you add html to language files?
by Baldeagle - 04/07/2024 2:41 PM
Do I need to rebuild my database?
by Baldeagle - 04/07/2024 2:58 AM
This is not a bug, but a suggestion
by Baldeagle - 04/05/2024 11:25 PM
Is UBB.threads still going?
by Aaron101 - 04/01/2022 8:18 AM
Who's Online Now
1 members (Nightcrawler), 607 guests, and 159 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)