The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

upgrade MySQL v4 to v5

Discussion in 'General Discussion' started by sehh, Apr 3, 2008.

  1. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    What is the correct and proper way to upgrade from MySQL v4.1.22 to the latest v5 please?
     
  2. WebScHoLaR

    WebScHoLaR Well-Known Member

    Joined:
    Dec 14, 2005
    Messages:
    511
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Planet Earth
    -Take the backup of all the databases
    -Set MySQL version to 5 in WHM > Tweak Settings
    -/scripts/mysqlup --force
     
  3. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    sounds easy enough...

    now the next question, how do i go back to the previous version if needed? Do i just follow the same steps? (choose older version from WHM, and run mysqlup?)
     
  4. WebScHoLaR

    WebScHoLaR Well-Known Member

    Joined:
    Dec 14, 2005
    Messages:
    511
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Planet Earth
    I believe that once you will upgrade to MySQL 5, WHM will not let you choose MySQL 4 :) from Tweak Settings as you will not find that option there. There is a work around, you can specify the mysql version in /var/cpanel/cpanel.config in parameter mysql-version set it to 4.1 as

    mysql-version=4.1

    save the file and then do /scripts/mysqlup --force. Please note that there is a possibility of data loss when you downgrade so in that case you will need to restore the backup that you should make before upgrade.
     
  5. sehh

    sehh Well-Known Member

    Joined:
    Feb 11, 2006
    Messages:
    579
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Europe
    hmmm i see...

    thank you for taking the time to help, i'll do the upgrade as soon as my full backup completes.
     
  6. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    MySQL data is not readily downgradable back to 4 from 5. Therefore it is strongly encouraged that you make a backup when the software is still running MySQL 4 before migrating to MySQL 5 to ensure that if you need to go back to 4, you can restore the data appropriately.
     
  7. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Don't forget to recompile Apache and PHP via EasyApache as well (WHM -> Software -> Apache Update or /scripts/easyapache). Otherwise, PHP scripts and any Apache functionality that requires MySQL may not function properly.

    On rare occasion, Perl scripts may have difficulty interacting with MySQL after an update. If that becomes the case, run this command:

    /scripts/perlinstaller --force Bundle::DBD::mysql
     
    #7 cPanelDavidG, Apr 3, 2008
    Last edited: Apr 3, 2008
  8. celliott

    celliott Well-Known Member

    Joined:
    Jan 2, 2006
    Messages:
    460
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    United Kingdom
    Hi,

    Quick question,

    Would taking a backup of /var/lib/mysql be adequete, should we need to downgrade again?

    Thanks
     
  9. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,461
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    It depends upon the table types being used by the databases. For InnoDB you should perform a mysqldump with either tables locked for writes or the service shutdown. The InnoDB format can change between major versions causing downgrades (e.g. 5.0 -> 4.1) to fail.
     
  10. JeDDaHosT

    JeDDaHosT Member

    Joined:
    Apr 13, 2005
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    How do i make the full backup ?

    do i just copy the dir /var/lib/mysql ?
     
  11. forum21

    forum21 Registered

    Joined:
    Nov 20, 2006
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    hello,


    Ya... or simply make a tar.gz of the dir /var/lib/mysql


    Regards,
     
  12. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    I personally recommend a full database dump just in case. Just backing up the files can work in some circumstances, but a database dump is more portable in the event restoration is required.
     
  13. hekri

    hekri Well-Known Member

    Joined:
    Oct 14, 2003
    Messages:
    149
    Likes Received:
    2
    Trophy Points:
    18


    I have that problem, downgrade mysql to 4.1 and now have prroblems with roundcube database (empty base I cant create tables, because it says that table exist :(

    HTML:
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    Cannot determine thread, fp=0x4de448, backtrace may not be correct.
    Stack range sanity check OK, backtrace follows:
    0x81330e3
    0x823bedb
    0x81bc58f
    0x81c15a8
    0x814a545
    0x814a6ef
    0x814aae2
    Stack trace seems successful - bottom reached
    Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
    stack trace is much more helpful in diagnosing the problem, so please do
    resolve it
    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort...
    thd->query at 0x942f750 = DROP DATABASE `roundcube`
    thd->thread_id=46
    The manual page at http://www.mysql.com/doc/en/Crashing.html contains
    information that should help you find out what is causing the crash.
    
    Number of processes running now: 0
    081014 12:12:34  mysqld restarted
    /usr/sbin/mysqld, Version: 4.1.22-standard-log. started with:
    Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
    Time                 Id Command    Argument
    081014 12:12:34  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer...
    081014 12:12:34  InnoDB: Starting log scan based on checkpoint at
    InnoDB: log sequence number 0 1902357.
    InnoDB: Doing recovery: scanned up to log sequence number 0 1902357
    081014 12:12:34  InnoDB: Flushing modified pages from the buffer pool...
    081014 12:12:34  InnoDB: Started; log sequence number 0 1902357
    /usr/sbin/mysqld: gotowe do po▒?czenia  MySQL Community Edition - Standard (GPL)
    081014 12:13:41  InnoDB: Error: table `roundcube/cache` already exists in InnoDB internal
    InnoDB: data dictionary. Have you deleted the .frm file
    InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
    InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
    InnoDB: See the Restrictions section of the InnoDB manual.
    InnoDB: You can drop the orphaned table inside InnoDB by
    InnoDB: creating an InnoDB table with the same name in another
    InnoDB: database and copying the .frm file to the current database.
    InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
    InnoDB: succeed.
    InnoDB: You can look for further help from
    InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
    
    081014 12:12:12  InnoDB: Error: table `roundcube/users` does not exist in the InnoDB internal
    InnoDB: data dictionary though MySQL is trying to drop it.
    InnoDB: Have you copied the .frm file of the table to the
    InnoDB: MySQL database directory from another database?
    InnoDB: You can look for further help from
    InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
    081014 12:12:34  InnoDB: table roundcube/cache is in the new compact format
    InnoDB: of MySQL 5.0.3 or later
    081014 12:12:34InnoDB: Assertion failure in thread 5114768 in file row0mysql.c line 2821
    InnoDB: Failing assertion: table
    InnoDB: We intentionally generate a memory trap.
    InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
    InnoDB: If you get repeated assertion failures or crashes, even
    InnoDB: immediately after the mysqld startup, there may be
    InnoDB: corruption in the InnoDB tablespace. Please refer to
    InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
    InnoDB: about forcing recovery.
    
     
  14. Kurieuo

    Kurieuo Well-Known Member

    Joined:
    Dec 13, 2002
    Messages:
    98
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Australia
    Just a word of warning - do it late at night when few users would being using their websites.

    I did it during the day and Apache did not work properly, and I had to wait until a second recompile was finished for it to come back to life (the first recompile failed!).
     
Loading...

Share This Page