Jump to content
  • 0

Auto SQL Backup for Linux


Question

21 answers to this question

Recommended Posts

Posted

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.

Posted (edited)

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 by Brian
updated link
  • Upvote 1
Posted

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.

Posted

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.

Posted

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?

Posted

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.

Posted (edited)

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 by xMiland
Posted

In case you don't have enough examples :P

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? =)

Posted

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).

Posted (edited)

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 by MaximumBlaze
Posted

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)

Posted

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...