Auto SQL Backup for Linux

[xMiland]

Anyone knows how to configure auto backups for SQL on Linux?

Or are there any guides?

Advanced thanks for those who'll reply.

[gfxmrmark]

here

http://www.noupe.com/how-tos/10-ways-to-automatically-manually-backup-mysql-database.html

[Olrox]

You should add a cronjob (pre installed in all GNU/Linux distributions) to point a bash script that will read the databases, execute the comand line, and save the backup.

Personally I had one, that stored the backups of 4 databases I had, into chronological order, and added date with hour,minutes and seconds of the file generated.

In the cronjob you can add, the time you want this script executed. I always configured to do this, at 2:00 am or any hour of low population to not overcharge my server.

Here --> http://edwardawebb.com/web-development/simple-shell-script-backup-multiple-mysql-databases a guide. also it includes the script I told you.

[Brian]

In case you don't have enough examples

Here is another version: backup_ragnarok.sh

- stores SQL backups in the same directory the script is located (or you can specify a directory)

- creates sub-folders named YYYY-MM (ex: 2011-12/)

- SQL backup filenames are DBNAME_DATE.sql (ex: ragnarok_2011-12-06_0809.sql)

Example cron to create a backup every night at 1:07 am

7 1 * * * /home/rathena/backups-sql/backup_ragnarok.sh

Edited March 24, 2012 by Brian
updated link

[xMiland]

Thanks for the replies !

@Brian - I'll try it. But will it cause any disconnections to my server? I'm using Systeminplace's control center and everytime I use it's backup tool. It turns off my mapserver.

[Brian]

If your SQL databases are very big, the backup could take a long time.

During this time, the tables are locked, so the server can't run any queries and its MySQL connection times-out.

Do you have log tables in your SQL database (atcommandlog, loginlog, picklog, etc.) ?

If you move those to a separate database, that would make backups smaller and faster.

[xMiland]

If your SQL databases are very big, the backup could take a long time.

During this time, the tables are locked, so the server can't run any queries and its MySQL connection times-out.

Do you have log tables in your SQL database (atcommandlog, loginlog, picklog, etc.) ?

If you move those to a separate database, that would make backups smaller and faster.

Yeap I currently have the logs at my SQL database. If I move them to a different database, will my future logs still be stored on the server logs with the different database?

[Brian]

If you move your log tables to a 2nd database (like "logs"), they will still be accessible,

but the advantage is when you backup your "ragnarok" database, it won't take up extra space because you are not backing up the log tables.

[xMiland]

If you move your log tables to a 2nd database (like "logs"), they will still be accessible,

but the advantage is when you backup your "ragnarok" database, it won't take up extra space because you are not backing up the log tables.

Okay thanks a lot. Please leave this topic opened as soon as I've done setting it up, I might have some problems soon. Thanks.

*EDIT* Already done. It worked fine. Thanks a lot BrianL and for those who replied above. ^__^

Edited December 7, 2011 by xMiland

[Whitecash]

In case you don't have enough examples

Here is another version: backup_ragnarok.sh

- stores SQL backups in the same directory the script is located (or you can specify a directory)

- creates sub-folders named YYYY-MM (ex: 2011-12/)

- SQL backup filenames are DBNAME_DATE.sql (ex: ragnarok_2011-12-06_0809.sql)

Example cron to create a backup every night at 1:07 am

7 1 * * * /home/rathena/backups-sql/backup_ragnarok.sh

hi Brian , i still cant download ur backup_ragnarok.sh , can you please re-upload it? =)

[Brian]

Fixed the link, sorry about that. backup_ragnarok.sh

[Whitecash]

@Brian, Thanks you =)

small qeustion about : mysqldump : Got error: 1130: Host 'xx.xxx.xxx.xxxip' is not allowed to connect to this MySQL server when trying to connect.

[Brian]

Check the permissions on your MySQL user.

If it's the same MySQL User that rAthena uses to connect, it should have all the permissions it needs (plus LOCK TABLE).

[MaximumBlaze]

Hello Brian,

I was wondering instead of moving the the atcommandlog loginlog and picklog is there any sql command that can clean them?

so that i will clean them everyday.

and as for the moving/separate the 3 logs i don't quite get it on how it's done. Sorry new on this.

A step by step guide will be appriciated.

looking forwards to a reply

Thanks in advance

Edited April 8, 2012 by MaximumBlaze

[Brian]

I was wondering instead of moving the the atcommandlog loginlog and picklog is there any sql command that can clean them?

These SQL queries will empty the 2 tables:

TRUNCATE TABLE atcommandlog;
TRUNCATE TABLE picklog;

and as for the moving/separate the 3 logs i don't quite get it on how it's done.

Basically, instead of just 1 database you create 2: one for the main "ragnarok" tables, the other for the "log" tables.

1. CREATE DATABASE ragnarok;
   CREATE DATABASE logs;
   
2. import main.sql into the ragnarok database
   
3. import logs.sql into the logs database
   

If you already have an existing 1-database setup, here's how to move the logs to a separate database:

1. CREATE DATABASE logs;
   
2. import logs.sql into the logs database
   
3. now, you can delete all the log tables from your "ragnarok" database
   
4. or, if you want to save them, move the data to the logs database:
   

   INSERT INTO logs.atcommandlog SELECT * FROM ragnarok.atcommandlog;	
   INSERT INTO logs.branchlog SELECT * FROM ragnarok.branchlog;	
   INSERT INTO logs.chatlog SELECT * FROM ragnarok.chatlog;	
   INSERT INTO logs.loginlog SELECT * FROM ragnarok.loginlog;	
   INSERT INTO logs.mvplog SELECT * FROM ragnarok.mvplog;	
   INSERT INTO logs.npclog SELECT * FROM ragnarok.npclog;	
   INSERT INTO logs.picklog SELECT * FROM ragnarok.picklog;	
   INSERT INTO logs.zenylog SELECT * FROM ragnarok.zenylog;

   
   (then delete the 8 log tables from your ragnarok db)
   

[ngek202]

is this separated in spaces or tab?

7 1 * * *

[Brian]

Spaces (in that example).

But actually in cron, any whitespace will work (spaces OR tabs).

[ngek202]

>.< oh yeah my bad you can't tab on terminal haha.

I'm having command not found:

35 17 * * * /home/Desktop/sqlfiles/backup_ragnarok.sh
-bash: 35: command not found

[Brian]

Example cron to create a backup every night at 1:07 am

7 1 * * * /home/rathena/backups-sql/backup_ragnarok.sh

ohhh, that is the text you type IN the cron file.

1. To add/edit a cron job, this is the command you type:

crontab -e

that will open a text editor listing your cron jobs (if you have any),

2. then you type

7 1 * * * /home/rathena/backups-sql/backup_ragnarok.sh

3. then save the file, and close.

[ngek202]

i'm doing it on putty i've manage to typin in the text but how do you save it?

I got it Brian Thanks a lot

Edited April 8, 2012 by ngek202

[McNeri]

how to move the data to the logs database?

[Asura]

how to move the data to the logs database?

Hi GM Neri,

What data are you referring to? Can you be a bit more specific as to what you are trying to do?