MySQL questions

[scripting]

Background

I need a MySQL mentor or two. While I was working on BingeWorthy and early versions of feedBase, I had two really good mentors on MySQL, JY Stervinou and Scott Hanson. I don't want to lean just on them, so I'm putting out a call for a few people with MySQL experience (more than me, minimal) so I can ask questions, and get answers based on experience. Saves a lot of time, and it will make feedBase more robust. With that in mind I have a basic question today, posted here. Thanks in advance! 💥

Today's questions

I have one really basic one.

1. What's the best way to back up a MySQL database so that I can transfer the contents to my local machine, so I can test new features, without bringing the server down. I have a couple of features I want to add, but I'm going to need to start fresh a few times while I debug it. The only way to do it is to operate a copy locally with a good base of data to work with.

Thanks in advance for the help! ;-)

[scripting]

I figured out how to install the mysql command line interface.

https://stackoverflow.com/questions/30990488/how-do-i-install-command-line-mysql-client-on-mac

Now I have to figure out what the password is. ;-)

[drewkime]

I'm guessing PHP is not an option? There's a GUI package PHPMyAdmin that has an export function that I've always used. I prefer command line for most interaction but things like this, that I don't do very often, are easier to discover in the GUI.

[andrewshell]

Dave, you probably want mysqldump. Here’s a thread about it.

https://stackoverflow.com/questions/11407349/mysql-how-to-export-and-import-a-sql-file-from-command-line

[jan-vandenberg]

/usr/binmysqldump -u root -pSECRETPASSWORD --all-databases | gzip > database_date +%Y%m%d.sql.gz

[kwebble]

Tools like MySQL Workbench, Sequel Pro and HeidiSQL are free tools to manage your databases. They can export and import the data.

But they can do a lot more, like give you direct access to the data and database structure, for viewing and editing.

[facej]

I have used the "automysqlbackup" tool - https://sourceforge.net/projects/automysqlbackup/ - for close to 10 years. Production database(s), experimental databases, "user" databases at a small ISP.
In the end it uses 'mysqldump', but does lots of useful things like keep daily, weekly, monthly things etc.

Like any "real" backup I have rebuilt whole database systems from backups both as an exercise and as a recovery from my own shitty software.

If you want something you can just run via crontab/daily this works fine. Runs nicely on my Mac(s) with mariadb and mysql databases as well as the CentOS system I work with for a "production" gig.

Simplest case is use MySQL Workbench and do a data export - choose

dump Stored Procedures and Functions
Export to self-contained file
Create Dump in a single transaction
Include "Create Schema"

You can easily compress the resulting dump file with your favorite Mac compressor (I just use zip)

[facej]

the dump sequence looks like this

12:36:28 Dumping musicdb (all tables)
Running: /Users/musicdb/Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump --defaults-file="/var/folders/tg/1tzqw7x54rbflc3phy_xvkxm0000gs/T/tmp2sssOP/extraparams.cnf"  --set-gtid-purged=OFF --user=musicdb --host=10.0.0.50 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --skip-triggers "musicdb"
12:36:30 Export of /Users/musicdb/dumps/Dump20180402.sql has finished

the mysqldump command is what is of interest here.

[scripting]

Thanks for the mentorship! ;-)

I will try these ideas out, but I had another approach I'm considering and wanted to know what you all thought.

What if I do it via JavaScript, and loop over the feeds and users and just export all the info I have for each in JSON format in a file in the file system.

I kind of like that approach because it yields something that I can use anywhere, in any kind of app, not just an SQL database.

So there would be code in feedbase.js that does the backing up.

[facej]

MySQL Workbench - Table Data Export - export as JSON -
https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html

You could also build an SQL script that does a SELECT ... INTO OUTFILE ... with things formatted for JSON. Can the feedbase.js code invoke a shell script?

Also - might the MySQL JSON features aid you in your quest?

https://dev.mysql.com/doc/refman/5.7/en/json.html

[jystervinou]

@scripting doing a custom json format export and import code, why not? but i'd do that after fixing the encoding issues first. And for that, you might want to do SQL dumps/restores until fixed. (so you don't fight two problems instead of one :-) )

@facej just be careful that with a SELECT... INTO OUTFILE, the dump is actually performed by the MySQL daemon (not the client running the command), so you need to run the command from the MySQL host, and the daemon needs write access to the file dump location.

[scripting]

@jystervinou -- I want to do the backup before I do any mass updates to the database. Just to be sure. I already have OPML backups of everything, btw -- one of the advantages of publishing everything.

Right now, all I have is the server, and it's running at Digital Ocean, and there's no GUI, so only command line access.

I think I have enough info to pick this up at the beginning of my next session.

[scripting]

So I've got the backup fully working. It's a separate app, every 20 minutes it writes out the data in the feeds table and the subscriptions table.

Here's the code.

https://github.com/scripting/feedBase/blob/master/backup/feedbasebackup.js

Dave