Halaman

Selasa, 11 Mei 2010

Backup and restore all database between MySQL instances

I have two instances of MySQL, one using 5.0.27 and the other 5.5.3. My requirement is to transfer all database to a new one. I try to backup all database and restore using My SQL Administration Tools. All database successfully restored (imported) to the new instance, but got problem with privilege that reports Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted.

Then I look at the backup script but could not found command to create/insert user privilege on mysql database. Probably I missed something, and glad to hear from you. I switch the way, and try following steps:

1. Do backup:
shell5027>mysqldump -uroot -ppass --all-database > C:\temp\sqldataku.sql

Then opened the backup script and found commands to create-insert users privileges on mysql database, something like these:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
DROP TABLE IF EXISTS `db`;
CREATE TABLE `db` ( .... )...;
INSERT INTO `db` VALUES ('%','cms','fercms','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),....);

INSERT INTO `user` VALUES ('localhost','root','*7775476747C6AD73358FA54DAED7828A72014B4E','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('%','fercms','*778F407DE7C65307389FA34AAED7828A72014B4E','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0), ...);

mysql.user table contains user list (with passwords) and their global privileges, mysql.db handles users' database-level privileges.

2. Do restore:
shell553>mysql -uroot -ppass < C:\temp\sqldataku.sql

3. Do flush privilege:
shell553>mysql -uroot -ppass
mysql> flush privileges;
mysql>\q

Test the database using myprogram or tools but come with error message: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

4. Do upgrade, by run this command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\

Looking for 'mysql.exe' as: shell553\mysql.exe
Looking for 'mysqlcheck.exe' as: shell553\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--port=3306"
drupal64.access OK
drupal64.actions OK
fkm.phpbb_acl_groups OK
fkm.phpbb_acl_options OK
Running 'mysql_fix_privilege_tables'...
ERROR 1547 (HY000) at line 167: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 168: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
ERROR 1547 (HY000) at line 181: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (cp850). Please verify if necessary.
WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (cp850_general_ci). Please verify if necessary.
WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.
FATAL ERROR: Upgrade failed

Once again I run the command:
shell553>mysql_upgrade -uroot -ppass --tmpdir=C:\temp\
...
...
Running 'mysql_fix_privilege_tables'...
OK

I did the command again, and reported that "This installation of MySQL is already upgraded to 5.5.3-m3, use --force if you still need to run mysql_upgrade".

If these solve my requirement above (I have to see in future), than I suspect that first try using MySQL Administration Tools should work as well.

13 komentar:

  1. wo keren skali brow.........

    BalasHapus
  2. Thanks for this. it worked very well for me.

    BalasHapus
  3. It's an amazing post designed for all the internet people; they will get benefit from it I am sure.
    Feel free to surf my webpage ... zeolite uses

    BalasHapus
  4. This article is actually a pleasant one it helps new
    net visitors, who are wishing in favor of blogging.
    My site - neucopia review

    BalasHapus
  5. If you desire to increase your experience simply keep visiting this site and be updated with
    the latest news posted here.

    my webpage :: legal ecstasy

    BalasHapus
  6. I drop a leave a response when I appreciate a article
    on a site or if I have something to contribute to
    the conversation. Usually it's triggered by the fire communicated in the article I looked at. And on this article "Backup and restore all database between MySQL instances". I was moved enough to leave a comment ;) I do have 2 questions for you if you tend not to mind. Could it be simply me or does it give the impression like some of the comments appear as if they are coming from brain dead individuals? :-P And, if you are posting at other online social sites, I would like to keep up with anything new you have to post. Would you list the complete urls of your shared sites like your Facebook page, twitter feed, or linkedin profile?

    Feel free to visit my website: fat burner reviews

    BalasHapus
  7. I go to see every day some web sites and information sites to read articles, but this weblog gives quality based writing.


    My weblog :: buy cheap youtube likes

    BalasHapus
  8. I don't even know how I stopped up here, however I thought this submit was once good. I do not know who you are however certainly you are going to a famous blogger in the event you are not already. Cheers!

    Here is my web page :: happy herbal highs

    BalasHapus
  9. Do you mind if I quote a couple of your posts as long as I provide credit and sources back to
    your blog? My blog site is in the exact same niche as yours
    and my visitors would truly benefit from some of
    the information you provide here. Please let me know if this ok with you.
    Thanks!

    Feel free to surf to my webpage: http://www.analteenexam.org/

    BalasHapus
  10. Hello i am kavin, its my first time to commenting anyplace, when i read this post i
    thought i could also make comment due to this good piece of writing.


    my site; free xxx movies

    BalasHapus
  11. you're in point of fact a just right webmaster. The web site loading pace is amazing. It sort of feels that you're doing any unique tгiсk.
    Furthеrmοге, Thе contents аre mаstеrріece.
    уou've performed a magnificent job on this subject!

    Look into my web site ... cheap legal highs

    BalasHapus
  12. Νo matter if some οnе searcheѕ for hіѕ required
    thing, thuѕ he/she dеsires to be availablе that
    іn ԁеtаil, so that thіng
    is mаintainеԁ over here.

    Hаve a lοok at my pagе - cheap party pills

    BalasHapus