Topics

Railway•2y ago

How can I create daily backups of my Mysql database?

Hi everyone! I want to create daily backups of my Mysql database, I'm not sure if I should create an app with a cronjob or something like that. Can anyone help me with this? There's no option in the project settings. Thanks. ProjectID f4ffaf00-bf8e-4fc7-8e95-9d64d9995b8d

Solution:

you could easily modify the template linked in this tutorial to do a mysql backup instead https://blog.railway.app/p/automated-postgresql-backups...

Jump to solution

84 Replies

Percy•2y ago

Project ID: f4ffaf00-bf8e-4fc7-8e95-9d64d9995b8d

Solution

Brody•2y ago

you could easily modify the template linked in this tutorial to do a mysql backup instead https://blog.railway.app/p/automated-postgresql-backups

danielthamesOP•2y ago

Hey I want to work on this now! @Brody I saw that the example contains a dockerfile with the postgree config inside is that correct? the postgree db runs inside the docker? Do I need a docker file?

danielthamesOP•2y ago

this is my repo... independent mysql db I plan to create the project and then add it to this project

Brody•2y ago

no what you are referring to would just be the postgres client library and the dump utility

danielthamesOP•2y ago

I have my node cron app ready to get the mysql db and then upload it to cloudinary - this is based in the example with postgree here you can see the screenshot to dump the file

danielthamesOP•2y ago

Do you see something wrong? Should it work with the database shown above?

Brody•2y ago

looks like it could work to me, but it's not like I've ever done this type of thing before

danielthamesOP•2y ago

ok what I'm going to have is something like this

danielthamesOP•2y ago

I will push the project and see what happens

danielthamesOP•2y ago

I'm going to use a test project just in case

Brody•2y ago

sounds good

danielthamesOP•2y ago

It doesn't work

danielthamesOP•2y ago

basically the mysql db doesn't exists for my cron job

Brody•2y ago

are you installing mysqldump into the container?

danielthamesOP•2y ago

it's not a container Should I work with docker? I need to migrate my db from standalone railway mysql to a docker container with mysql + cronApp ?

Brody•2y ago

railway uses docker, so you can just install mysqldump the same way that the postgres backup cron job app installs pg_dump

danielthamesOP•2y ago

there's not a pg_dump; but I got you I'm going to run -> npm i mysqldump

Brody•2y ago

pg_dump is installed with postgresql-client in the templates dockerfile

danielthamesOP•2y ago

ok I see that it doesn't work only installing it; but I see what I can do with this package I will make some modifications and try it

Brody•2y ago

you don't need to change your code lol just install mysqldump in the container whether you do that with a dockerfile or a railway.json file is up to you

danielthamesOP•2y ago

ok, I'm going to create this exact same file in my cronApp; changing pg_dump to mysqldump

danielthamesOP•2y ago

https://github.com/danielthames360/cronJob by the way, this is my repo

GitHub

GitHub - danielthames360/cronJob

Contribute to danielthames360/cronJob development by creating an account on GitHub.

Brody•2y ago

mysqldump doesn't appear to be an alpine package

danielthamesOP•2y ago

yup!

danielthamesOP•2y ago

I'm almost there

danielthamesOP•2y ago

29c518cd-5f54-47a7-841e-9a5f4c8b2c3d project test ID

danielthamesOP•2y ago

final docker file just change the postgre client to mysql-client

Brody•2y ago

are you checking if these are actually available alpine packages?

danielthamesOP•2y ago

it's trying to make the bkp; I'm getting an error now

danielthamesOP•2y ago

https://tenor.com/view/dug-cone-shame-sad-up-gif-21986026

Tenor

danielthamesOP•2y ago

Brody•2y ago

let's not resort to chat gpt lol

danielthamesOP•2y ago

lol

Brody•2y ago

add mysql in line 22

danielthamesOP•2y ago

RUN apk add --update --no-cache mysql mysql-client nodejs npm same error after deploy it https://chrisshennan.com/blog/fixing-authentication-plugin-cachingsha2password-cannot-be-loaded-errors I'm not sure Hey man! we can continue tomorrow if you want; I don't want to waste your Friday time cryingman

cryingman

I'll delete this message...

Brody•2y ago

nah it's good my Friday night has already come to an end and tbh I don't actually know why the original template uses a dockerfile, everything it does can easily be configured through a railway.json file and built with nixpacks once I'm back on my computer in a bit I can try to slap together a railway.json file for you

danielthamesOP•2y ago

I appreciate your time! and help If you know anything I can do to make it work would be fantastic. I am not in any hurry. So, I can wait pattienly

Brody•2y ago

in the mean time remove mysqldump from your package.json

danielthamesOP•2y ago

Done! I've also moved the dev dependencies to their correct place

Brody•2y ago

delete your Dockerfile and give this railway.json file a try

{
  "$schema": "https://railway.app/railway.schema.json",
  "build": {
    "builder": "NIXPACKS",
    "nixpacksPlan": {
      "phases": {
        "setup": {
          "nixPkgs": ["...", "mysql80"]
        }
      }
    }
  },
  "deploy": {
    "numReplicas": 1,
    "restartPolicyType": "ON_FAILURE",
    "restartPolicyMaxRetries": 10
  }
}

{
  "$schema": "https://railway.app/railway.schema.json",
  "build": {
    "builder": "NIXPACKS",
    "nixpacksPlan": {
      "phases": {
        "setup": {
          "nixPkgs": ["...", "mysql80"]
        }
      }
    }
  },
  "deploy": {
    "numReplicas": 1,
    "restartPolicyType": "ON_FAILURE",
    "restartPolicyMaxRetries": 10
  }
}

danielthamesOP•2y ago

weird error

Brody•2y ago

one moment change your start script to node dist/index.js

danielthamesOP•2y ago

https://tenor.com/view/ah-shit-here-we-go-again-ah-shit-cj-gta-gta-san-andreas-gif-13933485

Tenor

Brody•2y ago

the postgre backup template used node 18, try using node 18 set engines.node = 18 in your package.json

danielthamesOP•2y ago

I think I'm almost there

Brody•2y ago

lol what solved those es5 errors

danielthamesOP•2y ago

it was... Firstly, I was working with javascript only. Then, I migrated the project to typescript and I don't needed the type: module on package.json anymore

Brody•2y ago

removed the module type, makes sense since node 16 probably doesnt support the module type

danielthamesOP•2y ago

https://tenor.com/view/the-pursuit-of-happiness-will-smith-success-joy-happiness-gif-3517714

Tenor

danielthamesOP•2y ago

Brody•2y ago

well is the file in cloudinary valid?

danielthamesOP•2y ago

danielthamesOP•2y ago

I'm gonna give it a try in my local db

Brody•2y ago

yeah was just about to say to do a test restore

danielthamesOP•2y ago

oh; these are the lines maybe I need to add/update some lines of my mysqldump instruction in order to have a fully backup with schema creation, data and so on.

danielthamesOP•2y ago

by the way, this was an empty db only for test purpose .

Brody•2y ago

sounds plausible, ive never used mysqldump

danielthamesOP•2y ago

lol the first instrucctions says -> --no-create-db --no-create-info --skip-comments mildpanic

mildpanic

danielthamesOP•2y ago

after

Brody•2y ago

yeah that sounds like stuff you'd want

danielthamesOP•2y ago

awesome awesome awesome!!

Brody•2y ago

you should totally make this into a template, if that's something you'd want to do

danielthamesOP•2y ago

I will update the code to work with a specific folder or some specific things of my liking (cloudinary stuff too). Tomorrow morning I will test this project with the real production application, and make the cron work based on a configuration. With that I think I'll be all set and the project will be fully configurable. I'll attach some screenshots here

Brody•2y ago

awesome

danielthamesOP•2y ago

https://tenor.com/view/michael-scott-dwight-schrute-the-office-raise-the-roof-gif-21488302

Tenor

danielthamesOP•2y ago

thank you very much Brody! have a good rest

Brody•2y ago

haha no problem, happy to help!

danielthamesOP•2y ago

definitely! I'm not sure how to do that but just let me know what I can do tomorrow, I'll clean the code and do what I described above.

Brody•2y ago

https://railway.app/account/templates New Template add your public repo, and configure all the variables and such, if you need any help with that tomorrow feel free to ask me

danielthamesOP•2y ago

hey Brody! I have the app ready to create the template https://github.com/danielthames360/mysqlCloudinaryBackups Would you suggest something?

GitHub

GitHub - danielthames360/mysqlCloudinaryBackups: Node.js applicatio...

Node.js application that performs scheduled backups of your MySQL database and uploads them to Cloudinary for secure storage. - GitHub - danielthames360/mysqlCloudinaryBackups: Node.js application ...

danielthamesOP•2y ago

Should I have to add the variables I use?

Brody•2y ago

just add all the variable names you have in your .env.example file, with no default value like how this does it https://railway.app/new/template/I4zGrH

danielthamesOP•2y ago

https://railway.app/template/mT6Hiw?referralCode=rRjTM0

Railway

Deploy mysqlCloudinaryBackups on Railway

Railway is an infrastructure platform where you can provision infrastructure, develop with that infrastructure locally, and then deploy to the cloud.

Brody•2y ago

danielthamesOP•2y ago

https://tenor.com/view/kill-myself-seatbelt-gif-13298646

Tenor

Brody•2y ago

now you just have to write info text for it and publish it

danielthamesOP•2y ago

where? how?

Brody•2y ago

ohhh publishing templates is a beta feature so just type /beta here so are you gonna upgrade to the dev plan too? otherwise you won't be able to run the backup 24/7

danielthamesOP•2y ago

yeap! my client has their credit card in his account we have the production project there I don't need a plan for my personal account yet.

danielthamesOP•2y ago

Brody•2y ago

sounds good

danielthamesOP•2y ago

Ok done! thanks for the patience Brody

Brody•2y ago

no problem, im happy i could help!

Hang out with other likeminded developers & talk about all things https://railway.app on the Railway Community Server.

25KMembers

View on Discord

Want results from more Discord servers?

Add your server