scripting / Scripting-News

I'm starting to use GitHub for work on my blog. Why not? It's got good communication and collaboration tools. Why not hook it up to a blog?
121 stars 10 forks source link

MySQL questions #73

Open scripting opened 6 years ago

scripting commented 6 years ago

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! :boom:

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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

kwebble commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

@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 commented 6 years ago

@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 commented 6 years ago

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