Backup / restore without MySQLDumper

[CarolC1]

The command line I am using is:

Code: Select all

mysqldump -u [username] -p --opt [databasename] > [backupfilename.sql]

What is the --opt ?

[invenio]

The command line I am using is:

Code: Select all

mysqldump -u [username] -p --opt [databasename] > [backupfilename.sql]

What is the --opt ?

https://dev.mysql.com/doc/refman/8.4/en ... qldump_opt

[CarolC1]

Thank you.

Thought I had a good idea. Use mysqldump to back up a few tables at a time or one table at a time. I have NO problem doing it that way if it works.

So I tested it on the posts table. It says the .sql file is 93,736 KB. That's a little less than half the size of the total MySQLDumper dump, so that sounded more complete.

But when I opened it in my text editor, the INSERTs are one per line and go from 77 to 169. So again not even 100 posts. What is going on?

Somewhere here I saw a recommendation to check the backup in WordPad. I tried that. It doesn't have numbers for the lines.

I've been doing all this on the test board, but I'm realizing there appears to be no way to back up the live board right now, if I wanted to. The host is supposed to have backups, but if they're backing up with the same software I'm trying to get to work, how can I assume their backups are any good either?

Something is wrong with this picture. That can't be right.

[invenio]

What text editor are you using? Some can't open very large files correctly. I recommend notepad÷÷.

[Kailey]

What about using this and then FTP the file to your local machine (or wherever you want to store it).

Restores would require something different.

[CarolC1]

What text editor are you using? Some can't open very large files correctly. I recommend notepad÷÷.

I use a Jan 2010 version of EditPlus3. I just tried Notepad++ 8.7. It has an elegant display that is easy to read, but unfortunately it seems to confirm the incomplete backup. Thanks for the idea, it was worth a try. It's weird how it jumps post numbers.

notepad.PNG

EP3.PNG

What about using this and then FTP the file to your local machine (or wherever you want to store it).

Restores would require something different.

I'm using Filezilla SFTP to download the dump to my local machine. But the current issue is, the backups I've made have been incomplete. I know it's hard to follow, my posts are so long.

The board has roughly 100,000 posts and my last good backup was done with MySQLDumper, which worked on our old hosting but gives a white screen on the new hosting. So I am seeking an alternative that works. No luck so far.

Backup from the ACP only contained 592 posts, images here
viewtopic.php?p=16034470#p16034470

Backup by SSH Teriminal on the new host is less than 100 posts, images here
viewtopic.php?p=16034800#p16034800

I'm tempted to ask them to make one for me so I can see if theirs is any better, this is ridiculous. They can see for themselves on the testboard and live board how many posts there should be in the backup.

Any other ideas welcome. In spite of my joined date on this board, I am no computer expert.

Right now as our board gains posts, I can't back it up properly. This was all kind of an intellectual exercise until that hit me yesterday. We lost 2 weeks of posts when we lost our last hosting. Thank Heaven I had a 2-week old backup. Right now if we somehow lost hosting (unlikely, but still...) we'd be set back to the 8-16 backup again because I can't make a complete current backup.

[invenio]

Thinking outside the box, but do they offer a backup service? My host does and you can download the entire hosting site, the files, or the database together or individually from their backup interface.

[thecoalman]

That's probably using extended inserts with multiple rows on one line, scroll to the right. Import it into test DB or on test machine to confirm.

mysql -u<username> -p <db_name> < yoursqlfile.sql

No brackets around username and db_name.

[CarolC1]

That's probably using extended inserts with multiple rows on one line, scroll to the right. Import it into test DB or on test machine to confirm.

@thecoalman Thank You! There are not enough thanks in the world.

I did what you said and...

It worked!

Thank you so much, because I didn't know to do that, even if it seems obvious now.

I had already scrolled to the right previously, and it went on and on, but that didn't mean anything to me. I'd heard of 'extended inserts' before, but never knew what that meant till now. I need to read about them.

I created the new empty db using their UI, set up the SSH user credentials for it, enabled SSH for it in their UI, managed to log into Shell In A Box again with the new user credentials, and imported the mysqldump of the posts table. This is what I see in their Database Manager (no PhpMyAdmin).

98K.PNG

Then. I dropped that table and imported the ACP dump and checked the posts table, and it, too, seems to be complete.

ACP_backup.PNG

I need to take a break and then repeat all of this again, to lock it in and try to think if there is anything else I need to check. I need to check more carefully. If everything still appears fine, then I'll run a backup of the live board to have(!) and try importing that into my check-the-backup database and be sure it's all there. It should be, but I just want to be sure. I knew I could trust MSD all these years, and I have to get used to this.

Thank you so very much. And thanks to EVERYONE who posted in this topic. You're the best!

@warmweer, you mentioned Adminer earlier. That seems to be what this host is using when you click their "Database Manager" which these last 2 screenshots are from. What a coincidence.