Page 1 of 1
backing up
Posted: Thu Nov 10, 2005 10:48 am
by hessodreamy
What I need to do is schedule a regular and automatic backup of my database tables. Preferably I'd like to have each table in it's own file so I can reload it quickly if i have to.
I was planning to set up a cron job to run a php script to call mysqldump and put each of the tables in appropriate files. Does this sound like the way forward?
As far as I know I can't put each table in an appropriate file via a cron job.
Given that I need the process to be automatic and flexible, is php the way to go?
Re: backing up
Posted: Thu Nov 10, 2005 11:22 am
by onion2k
hessodreamy wrote:As far as I know I can't put each table in an appropriate file via a cron job.
If it can be done from a command line then it can be done from a cron job.
Posted: Thu Nov 10, 2005 12:24 pm
by hessodreamy
Yes but it can't be done dynamically from the command line can it?
I don't want to hard code all the table names to backup. Just show tables. And for each table dump it into a file.
This can't be done on command line, can it?
Posted: Tue Nov 15, 2005 10:11 am
by BDKR
hessodreamy wrote:Yes but it can't be done dynamically from the command line can it?
I don't want to hard code all the table names to backup. Just show tables. And for each table dump it into a file.
This can't be done on command line, can it?
Yes it can. If you can get a list of tables, then you are all the way there. I've done very similar things. Here's an algorithm that could work.
1) Connect to DB
2) Change to your DB (the db can be passed to the script as a command line arg)
3) Query the db with "SHOW TABLES"
4) Save the result in an array
5) Iterate over the array. At each element of the array (your table names), issue a command to mysql dump to dump
that table to a particular file.
You can also get a list of the tables using mysqlshow with "mysqlshow your_database". However, I think it would be easier to just
connect to the db and grab the data.
Cheers,
BDKR
Posted: Tue Nov 15, 2005 10:13 am
by BDKR
I might add that 4) and 5) can be done in one step. Instead of saving the result to a seperate array, you could also issue the commands to mysqldump as you pull the results back from the query.
Cheers
Posted: Tue Nov 15, 2005 10:14 am
by hessodreamy
Interesting... I didn't realise the command line could do that.
er...How DO you do it?

Posted: Tue Nov 15, 2005 10:25 am
by timvw
I believe the -T / --tab option can do that for you (If you run mysqldump on the same machine where your mysqld is running)
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
Posted: Tue Nov 15, 2005 11:54 am
by BDKR
timvw wrote:
...(If you run mysqldump on the same machine where your mysqld is running)
It doesn't have to be on the same machine. You can use the -h (host) switch. You'll most likely have to give a username and password to, but that's no big deal.
Cheers
Posted: Tue Nov 15, 2005 11:59 am
by BDKR
hessodreamy wrote:Interesting... I didn't realise the command line could do that.
er...How DO you do it?

I would use "msyqldump database table > table.sql". You may need to pass the password and username. In that case it could look like
"msyqldump -u username -ppassword database table > table.sql"
Notice that the password has no space between it and the switch. That's correct.
RTFM my good man. LOL
Cheers
Posted: Tue Nov 15, 2005 12:04 pm
by Burrito
The approach I took was sort of a combination of the two (command line and php).
I wrote a php page to use mysqldump (based on show tables; query from the db). I then used a cron to call php.exe from the CLI to dynamically name and create my backups (name is datestamped+table.sql). Then I tar it up and finally gz it so I have a small little bundle of joy at the end of every day.
Posted: Tue Nov 15, 2005 2:23 pm
by timvw
BDKR wrote:timvw wrote:
...(If you run mysqldump on the same machine where your mysqld is running)
It doesn't have to be on the same machine. You can use the -h (host) switch. You'll most likely have to give a username and password to, but that's no big deal.
I know one can use the -h switch, i use it virtually every day
I was talking about the -T switch, as the manual states:
Note: This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.
Posted: Tue Nov 15, 2005 2:26 pm
by timvw
Btw, i have the following problem if i try to use that switch:
As a regular user only the first %table%_sql.dmp is generated.
As root all %table%_sql.dmp files are generated though.
Code: Select all
timvw@madoka:~$ mysqldump -u timvw -p timvw -T /home/users/timvw
Enter password:
mysqldump: Got error: 1045: Access denied for user: 'timvw@%.isw.student.khleuven.be' (Using password: YES) when executing 'SELECT INTO OUTFILE'
Posted: Tue Nov 15, 2005 4:11 pm
by BDKR
timvw wrote:BDKR wrote:timvw wrote:
...(If you run mysqldump on the same machine where your mysqld is running)
It doesn't have to be on the same machine. You can use the -h (host) switch. You'll most likely have to give a username and password to, but that's no big deal.
I know one can use the -h switch, i use it virtually every day
I was talking about the -T switch, as the manual states:
Note: This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.
My bad. I mis-read your post.
