Announcement

Collapse
No announcement yet.

mysqldump problem

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysqldump problem

    It's quitting in the attachments table:

    [[email protected] backups]# mysqldump -p rushvb -v > rushvb11-02-02.sql
    Enter password:
    # Connecting to localhost...
    # Retrieving table structure for table access...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table adminlog...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table adminutil...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table afterburner_sessions_day...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table afterburner_stat...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table announcement...
    # Sending SELECT query...
    # Retrieving rows...
    # Retrieving table structure for table attachment...
    # Sending SELECT query...
    Terminated


    any ideas?

  • #2
    how large is attachment table ?

    what are you server specs/hardware ?

    version of php/mysql used ?

    dedicated server or shared hosting ?
    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
    :: Nginx SPDY SSL - World Flags Demo [video results]
    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

    Comment


    • #3
      Celeron 1.3
      Redhat 7.1
      512MB RAM

      MySQL 3.23.36
      PHP 4.0.6

      20,716 rows in the attachments table. It's huge. I'd say average attachment size is ~70k. The database overall is ~1.5GB. [Actually, side note... How am I supposed to back it up once the file size goes > 2GB? Perhaps I can pipe it to rar and make separate files?]

      I was thinking that maybe it was timing out, but then I would hope that it would say something rather than just terminating.

      I tried mysqldump -p -v --debug=rushvbdbg.log rushvb > rushvb11-03-02.sql, but it didn't seem to write a debug log.

      Comment


      • #4
        vB's attachment upload function was never meant to be a file storage facility, your attachment table is simple too large to be held in a database... especially on your underpowered server hardware

        you will probably have to purne the attachment table or get a hack from vbulletin.org to move the files out of the database into a files.. vB 3 will have this function

        other option is to physically back up the data file for that table by shutting down mysql on the server and physically copying it

        post your current /etc/my.cnf contents not sure if i can tweak your mysql settings to help but it's worth the try
        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
        :: Nginx SPDY SSL - World Flags Demo [video results]
        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

        Comment


        • #5
          Looks like we'll have to do a file backup... You don't think we could possibly increase a timeout somehow to get mysqldump going?

          Here's the my.cnf file
          [mysqld]
          datadir=/var/lib/mysql
          socket=/var/lib/mysql/mysql.sock
          set-variable = max_connections=255

          [mysql.server]
          user=mysql
          basedir=/var/lib

          [safe_mysqld]
          err-log=/var/log/mysqld.log
          pid-file=/var/run/mysqld/mysqld.pid


          Thanks

          Comment


          • #6
            Originally posted by SureShot
            Looks like we'll have to do a file backup... You don't think we could possibly increase a timeout somehow to get mysqldump going?

            Here's the my.cnf file
            [mysqld]
            datadir=/var/lib/mysql
            socket=/var/lib/mysql/mysql.sock
            set-variable = max_connections=255

            [mysql.server]
            user=mysql
            basedir=/var/lib

            [safe_mysqld]
            err-log=/var/log/mysqld.log
            pid-file=/var/run/mysqld/mysqld.pid


            Thanks
            i'd recommend you upgrade mysql to 3.23.53a as <3.23.38 don't have some mysql database tools which would be useful for such a large database, i.e. mysqlcheck command etc

            for now change your /etc/my.cnf to the below version and restart mysql see if that helps

            [mysqld]
            datadir=/var/lib/mysql
            socket=/var/lib/mysql/mysql.sock
            set-variable = max_connections=350
            set-variable = key_buffer=16M
            set-variable = myisam_sort_buffer_size=64M
            set-variable = join_buffer=1M
            set-variable = record_buffer=1M
            set-variable = sort_buffer=2M
            set-variable = table_cache=1024
            set-variable = thread_cache_size=256
            set-variable = wait_timeout=14400
            set-variable = connect_timeout=10
            set-variable = max_allowed_packet=16M
            set-variable = max_connect_errors=10

            [mysql.server]
            user=mysql
            basedir=/var/lib

            [safe_mysqld]
            err-log=/var/log/mysqld.log
            pid-file=/var/run/mysqld/mysqld.pid
            open_files_limit=8192

            [mysqldump]
            quick
            set-variable = max_allowed_packet=16M

            [myisamchk]
            set-variable = key_buffer=64M
            set-variable = sort_buffer=64M
            set-variable = read_buffer=16M
            set-variable = write_buffer=16M
            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
            :: Nginx SPDY SSL - World Flags Demo [video results]
            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

            Comment

            Related Topics

            Collapse

            Working...
            X