Bashscripts.org

Here is a backup script for the mysql databases on your system. It is what I use on my own computers. This script keeps 8 days worth of backups




Geeshock notes :


On the 7th day, I suppose you could have a cron job do do a backup to cdr of all the databases you have saved in /var/backups/mysql or like geeshock does..... rsync them to another server for safe keeping. There are countless ways to use this script and variations on how you want to use the backups to save your data....

It's ONE NICE script

Originally posted here:
http://usalug.org/phpBB2/viewtopic.php?t=1657


the --opt part of the command locks the tables, preventing corruption.
FWIW: This has worked on my home machine with SEVERAL databases running at once. No issues.

From the man mysqldump page

Wide
New member

Registered: Dec 9th, 2004
Posts: 4

New to scripting

I am trying to get this script to work & getting an error.

ERROR 1045 access denied for user(using password: NO)

I made it chmod +x filename

I know I'm missing something simple?




EDIT, I'm using mysql 3.2

Last edited by Wide (Dec 12th, 2004 3:06 pm)


Dec 12th, 2004 3:05 pm
E-mail Report | Delete | Edit | Quote
jbsnake
Moderator

From: Georgia, U.S.A.
Registered: Nov 11th, 2004
Posts: 102

you have to have user access on the database schema and table in order to access it using mysql.
if you havn't done anything to the tables in mysql yet...then do this as root:

Code:

mysqladmin -u root password <newpassword>


that creates your root access to mysql using a password you specify
now to add your user account to the user database

Code:

mysql -uroot -p<newpassword> -e"insert into user (host, user)values ('<hostname>', '<username>')" mysql



Note: The <newpassword> should match what you set above for root user and
<hostname> should match the hostname on your system (or be localhost) and
<username> should be the user account on your system that you want to connect with.

since this is probably going to be your main username that you want to connect with on a regular basis...i suggest giving this user account the almost same access as your root account:

Code:

mysql -uroot -p<newpassword> -e"insert into db (host, db, user, select_priv, insert_priv, update_priv, delete_priv)values ('<hostname>', '<databasename>', '<username>', 'Y', 'Y', 'Y', 'Y' )" mysql


if you don't have a database already created to give access to from the above code...

Code:

mysql -uroot -p<newpassword> -e"create database <databasename>"


mysql is set up slightly different than other database programs...in mysql, top level objects are called "databases" whereas in oracle they are called schemas. The next level in mysql are called tables (the same as oracle). To create a table in a mysql database, (the database should have been made first from the above code) you would do this:

Code:

mysql -uroot -p<newpassword> -e"create table <tablename> (
ID int(2),
Name char(20),
);" <databasename>


the above creates a table with 2 attributes. The first one being an ID number with the greatest length of two digits. The second one being an alphanumeric name with the greatest length of twenty characters.
Ofcourse this is kind of a waste...but you can take this and expand on it
Let's say you created a database called "Info" and a table in that database called "People". You would add items to that table with the following code:

Code:

mysql -e"insert into Info.People (ID, Name)values ('1', 'Joe Bob')"


So now if you did the code:

Code:

mysql -e"select * from Info.People"


It would display under the ID column the number 1 and under the Name column the name Joe Bob.

Hope this little quick helped you out some
Want more info on this...let me know

_______________________________________
Arch Linux 0.7 Kernel 2.6.11.7-ARCH

Dec 13th, 2004 12:29 pm



Wide
New member
Registered: Dec 9th, 2004
Posts: 4


Thank you again JBSnake!!

I'll be on this soon as I figure out getting rid of spaces

Dec 13th, 2004 4:01 pm



Wide
New member

Registered: Dec 9th, 2004
Posts: 4
Thank you JBSnake!!!!


I managed to get it to work.

I think I learned more figuring it out then the script itself.

Now on to the spacees script

Dec 15th, 2004 5:57 pm

glad to see you caried this one over

I tried to run this script and gave me the following error:


The script goes as follow:



I've tried breaking down the script to see if there was anything wrong, first I tried the following:



I tried inserting the pipe tail and so far so good, it reply me just the 'mydatabase' which is what was expected. After that I insert the for and got the follwoing error:


I wonder if its because of the single quotes instead of the stress sign. I later added the do ... part but still gave me the same error.

ok I found a couple of bugs including the for i instead of for $i, also there were a couple of bugs on mysqldump didn't authenticate again to the db. Finally there I am having an error with gzip:

I belelieve you need to tell gzip to write to stdout with the -c option before redirecting, otherwise it wants a list of files to compress in place (i.e. file.foo => file.foo.gz).

Well I put it like the sample posted at the top, gzip seemed to work without the 'date' command. Otherwise it will throw out errors. This seems pretty odd since date by itself return no errors.



while

Correct

Then if that's the case, set a variable to the value of the `date` command and then simply use the variable in the file name: