Announcement

Collapse
No announcement yet.

MySQL Dump

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

  • MySQL Dump

    If you want to upgrade your forums to vBulletin 3 Beta 3, you should first close your board via the vBulletin 2 admin control panel, and then make a complete back-up your database using the following command from the system command line:

    mysqldump --opt -uusername -p databasename > databasename.sql

    (Where username is your MySQL username and databasename is the name of your database)

    How exactly do I do this?


    I want to make sure I do not make a mistake before upgrading.
    I have went to the admin cp, Import Maintenace, Database Backup, and "Go" on "atabase tables to include in backup."

    I have only made that backup. Is that enough?

  • #2
    Originally posted by Cal Poly Forum
    How exactly do I do this?


    I want to make sure I do not make a mistake before upgrading.
    I have went to the admin cp, Import Maintenace, Database Backup, and "Go" on "atabase tables to include in backup."

    I have only made that backup. Is that enough?
    It is not recommended that you try and backup through the admincp because of PHP timeout issues creating incomplete backups you have to use ssh/telnet or get your host to back up the database for you if you don't have access.

    Comment


    • #3
      Originally posted by poolking
      It is not recommended that you try and backup through the admincp because of PHP timeout issues creating incomplete backups you have to use ssh/telnet or get your host to back up the database for you if you don't have access.
      what are the commands to do a backup on shh ?

      Comment


      • #4
        See Step 1 here:

        http://www.vbulletin.com/manual/movingservers.html
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment


        • #5
          You must change what's in red to your own site info and directory where you want the database backed up to.

          mysqldump --opt -uusername -p databasename > /home/site120/www.yoursite.com/sql_backup/my_dump_file.sql

          1. You telnet/shell to your site. You can use 'putty' program to conect to your site by way of telnet/shell.

          2. After you telnet to your site, you put the command above. Just change what's in red to fit your site info. The command above tells your server to connect to your database using your username and database name and tells it to dump your database data into a file called my_dump_file.sql or whatever name you want to give it in to a directory you specify. Just make sure it ends with .sql

          3. After you put the command and hit enter, in telnet it will ask for your database password. Put in your database password and wait. This could take a few seconds up to a few minutes depending how big your database is. You'll know when it's done when you see the shell command is back.

          4. Ftp to the directory you specified in the above command and make sure the file is there after the download is complete.

          Hope this helps.

          :cool: That which is obvious, needs no introduction. :cool:

          Comment


          • #6
            also i belive that not everyone relzises that -p stands for where to add your password

            mysqldump --opt -uusername -ppassword databasename > /home/site120/www.yoursite.com/sql_backup/my_dump_file.sql

            Comment


            • #7
              can you do this from your the MySql query hack in the CP? or does it have to be from telnet?

              thanks

              Comment


              • #8
                I'm not familiar with that hack. You should ask this over at vbulletin.org.
                Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                Change CKEditor Colors to Match Style (for 4.1.4 and above)

                Steve Machol Photography


                Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                Comment


                • #9
                  no i belive you should do it from telnet / SSH

                  Comment


                  • #10
                    Originally posted by Faranth
                    also i belive that not everyone relzises that -p stands for where to add your password

                    mysqldump --opt -uusername -ppassword databasename > /home/site120/www.yoursite.com/sql_backup/my_dump_file.sql
                    Two things I would like to point out. First the -uUSERNAME ...

                    It should be -u USERNAME

                    The space has to be there. So the instructions vBulletin gives is misleading.

                    The second commet about the -p ...

                    -p is not for password, it is for Port number to use for TCP/IP connections.

                    Comment


                    • #11
                      Originally posted by Andy
                      -p is not for password, it is for Port number to use for TCP/IP connections.
                      That's not correct. -P is for port and -p is for password:

                      Code:
                      NAME
                      	   mysqldump  -  text-based  client for dumping or backing up mysql databases , tables
                      	   and or data.
                      USAGE
                      	   mysqldump [OPTIONS] database [tables]
                      	   OR	 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
                      	   OR	 mysqldump [OPTIONS] --all-databases [OPTIONS]
                      OPTION SYNOPSIS
                      	   mysqldump  [-A|--all-databases]  [-a|--all]  [-#|--debug=...]	[--character-sets-
                      	   dir=...]	[-?|--help]   [-B|--databases]  [-c|--complete-insert]  [-C|--compress]
                      	   [--default-character-set=...]   [-e|--extended-insert]  [--add-drop-table]  [--add-
                      	   locks]   [--allow-keywords]   [--delayed-insert]   [-F|--flush-logs]   [-f|--force]
                      	   [-h|--host=...]	[-l|--lock-tables]   [-n|--no-create-db]   [-t|--no-create-info]
                      	   [-d|--no-data]	[-O|--set-variablevar=option]	[--opt]	[-p|--password[=...]]
                      	   [-P|--port=...]   [-q|--quick]  [-Q|--quote-names]  [-S|--socket=...]	[--tables]
                      	   [-T|--tab=...]	 [-u|--user=#]	[-v|--verbose]   [-V|--version]   [-w|--where=]
                      	   [--delayed]		   [-e|--extended-insert]		   [--fields-terminated-by=...]
                      	   [--fields-enclosed-by=...]					[--fields-optionally-enclosed-by=...]
                      	   [--fields-escaped-by=...]  [--lines-terminated-by=...]   [-v|--verbose]  [-V|--ver­
                      	   sion] [-O net_buffer_length=#, where # < 16M]
                      Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                      Change CKEditor Colors to Match Style (for 4.1.4 and above)

                      Steve Machol Photography


                      Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                      Comment


                      • #12
                        Thanks Steve, you are correct. Even what I mentioned about the space after -uUSERNAME was incorrect.

                        Using -pPASSWORD works very well.

                        Comment


                        • #13
                          Originally posted by ABLady
                          You must change what's in red to your own site info and directory where you want the database backed up to.

                          mysqldump --opt -uusername -p databasename > /home/site120/www.yoursite.com/sql_backup/my_dump_file.sql

                          1. You telnet/shell to your site. You can use 'putty' program to conect to your site by way of telnet/shell.

                          2. After you telnet to your site, you put the command above. Just change what's in red to fit your site info. The command above tells your server to connect to your database using your username and database name and tells it to dump your database data into a file called my_dump_file.sql or whatever name you want to give it in to a directory you specify. Just make sure it ends with .sql

                          3. After you put the command and hit enter, in telnet it will ask for your database password. Put in your database password and wait. This could take a few seconds up to a few minutes depending how big your database is. You'll know when it's done when you see the shell command is back.

                          4. Ftp to the directory you specified in the above command and make sure the file is there after the download is complete.

                          Hope this helps.
                          1. Is telnet/shell that I can find from my website pannel control or from vb pannel control or it have from my pc?

                          I am really to don't get it? help me about it! many thanks

                          Comment


                          • #14
                            Telnet is built into Windows. Here is how you bring it up.

                            Start>Run>Command>Telnet www.yourdomain.com

                            Not all web hosting services allow using Telnet because it isn't as safe as SSH.

                            If your web hosing company requires SSH you need to use a program called putty.exe. It is a free download and you install it Windows like any other program. Just type putty.exe in a Google search and follow the first link to down load it.

                            Comment


                            • #15
                              To get back to the cron, my hosting provider suggested the following line:

                              mysqldump -u *******_root --password=******** -A --all -l -F -q | gzip > /home/*******/backups/`date -I`.gz

                              I have multiple SQL databases, the same root user has access to all databases. I am getting the following error:

                              mysqldump: Got error: 1227: Access denied. You need the RELOAD privilege for this operation when doing refresh

                              I'm mucho confused, the directory has a 777 perm, and as far as I can tell the --A and --all dump all databases, the -l locks the tables, the -F flushes the logs, and the -q does it in quick mode.

                              Any suggestions? I have PHP scripts that will back the database up, but since I dont the ISP doesn't allow and I dont want to beg for SSH access, I would really prefer an option that can be setup in cpanel in a cron job.

                              Thanks!

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X