Beginner's worries

[ajmch]

About backing up and restoring. I'm thinking here of backing up DB (downloading) from a server and then restoring (uploading) the DB of another server with the BU files.

The help file says, about restoring,

Select backup script file to restore and target database. If backup script file contains CREATE DATABASE statement, backup data will be restored to the created database instead of the selected database.

This sounds scary. How am I to know whether the restore will happen in the correct, present DB?

At another place, it says

Check that the encoding for the restored file is selected correctly.

How do I know that? (No, I didn't created those DB, it's just some sort of heritage)

I actually tried a BU and restore (<as in script>) and I had the following error, at the end of the restoring process, for both my tests (with two different DB):

Duplicate entry '\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xBF\xBD\xEF' for key 'PRIMARY'

What have I done wrong or not good enough?

[ajmch]

I checked "Detect MySQL character set" in the connection properties, created two new backup files and was able to restore them without error, this time.

Another question:
Am I missing something or is there no way to schedule automatic backups (dbForge Studio for MySQL 6.2.280)?

[ajmch]

Making another backup, of another DB, I ran into an error

Cannot load from MySQL.proc. The table is probably corrupted.

I tried to check the DB using dbForge but I found nothing wrong (just warnings about users online or nor correctly disconnected).

I read online that it may be caused by some previous upgrade of MySQL Server, which would require an upgrade of the DB too, and that I should run

/usr/bin/mysql_upgrade -uroot -p --force

in order to fix it. Sounds good but is it a good advice? Will dbForge do the trick too with its repair mode?

[alexa]

You can automate the process using the command line interface of dbForge and, for example, Windows Task Scheduler. Please refer to the following articles on our website for details:

http://blogs.devart.com/dbforge/how-to- ... ndows.html
http://www.devart.com/dbforge/mysql/stu ... ackup.html

[ajmch]

Thank you Alexa.

Apparently, dbForge cannot repair the MySQL.proc problem. I guess it needs root access?

As for the upgrade script, I tried

mysql_upgrade

and I got

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

I tried to see which version I was running, typing

mysql

in SSH Explorer, I got

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I'll have a coffee and a cigarette...

[ajmch]

Tried

mysql -u root -p -hlocalhost

and got

Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

with the root password I used to log in with ssh and with the sql admin password. This is a Centos 6 machine.

[alexa]

Please note that mysql_upgrade needs to be run as user root and only root.

To obtain MySQL version, please type the following:

Code: Select all

mysql -V

The example of running mysql_upgrade:

Code: Select all

mysql_upgrade -u root -p

[ajmch]

Thanks! Here the result:

Code: Select all

mysql -V
mysql  Ver 14.14 Distrib 5.5.40, for Linux (x86_64) using readline 5.1
[root@ms169 ~]# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

[ajmch]

If I chose "Ignore all errors" for the backup, will I get a serviceable file, that I can use to restore the DB on the other server, just with the same "errors"? Or will I get an corrupted file, with missing parts? What are the chances?

[alexa]

It depends what kind of errors will popup. Most probably the backup will be not valid.

[ajmch]

Well, that is conforting.

I tried to restore the DB of the other server with such a backup and it went down as a complete success. Is that just luck?

Let me rephrase it from scratch. I made a backup from a DB that is online, staging (a WordPress blog), and it gave me (2) errors (Cannot load from MySQL.proc. The table is probably corrupted.) I ignored them. Then I restored that backup file to a server with the same domain, but which is not active (the nameservers point to the first server). No problem. Then, in order to see the result, as the domain is on a shared IP, I gently asked Plesk to preview it. And everything looked fine and up to date (the last comment was the one I expected).

Does that mean that this site would work in the real world?

[alexa]

Unfortunately, we do not guarantee that it will work correctly in this case.

[ajmch]

But, what can I do? What would a pro do? Buy MySQL Enterprise Edition? Hire some specialists? Or where can I read solid information about this stuff? Without starting a new education.

Sorry if I sound so awkward. Our IT pro just bailed on us and I now have two servers on my hands, in the middle of a migration, with a couple sites on them that draw hackers like crazy (political discussion blogs and forums). And I do want to do it good. I'm just not really able to, not as it is.

[alexa]

We can also recommend you to compare the original DB with the restored one with our Schema and Data Comparison features and, in case some corrections are needed, perform schema or data synchronization.

For help, please refer to the product's Help documentation and the following demo videos and tutorials on our website:

http://www.devart.com/dbforge/mysql/sch ... -more.html
http://www.devart.com/dbforge/mysql/dat ... -more.html

[ajmch]

Thank you alexa!

You should check the first video, "dbForge Data Compare Demo" -- there's nothing more on that YouTube link.

We have come to nothing neither with those tables and eventually our only solution has been to copy the original files over, by hand, where phpMyAdmin would have put them. Any other mean corrupted the data.

It was a mix of several languages in a WordPress blog, among them Arabic, and there was no way to export/import them without messing with the Arabic part.

Maybe you should/could add that possibility in dbForge? I for one, were a buyer.

All best!