2) Backing-Up the Current Database 
1Telnet/SSH into the server where your vBulletin is currently installed. We will call this machine1.
2Type:
mysqldump --opt -Q -uUSERNAME -p DATABASENAME > /PATH/TO/DUMP.SQL
In this line, you should change the following:
  • USERNAME – this is the username you use to access MySQL. It is specified in your config.php file.
  • DATABASENAME – the name of the database which your vBulletin is installed into. It is specified in your config.php file.
  • /PATH/TO/DUMP.SQL – this is the path to the file that will be outputted. If you are unsure what to put here, simply use vb_backup.sql or something similar.
Once you press enter, you will be prompted for the password you use to access MySQL. This is also specified in your config.php file.
3Once it has returned to the prompt, verify that DUMP.SQL exists in the directory you specified. If you did not specify a full directory, the file will be in the directory you are currently in.
Note:
When moving servers you need to check the MySQL versions on both the old and new servers. It is always best if the new server is running the same or a newer version of MySQL.
If the new server is running an older version of MySQL it would be best to seek out a different host. If this is not possible, you will need to make a compatible dump of the database.
This only applies between major version number, ie MySQL 5.x to MySQL 4.x.

Add the following option to the mysqldump command:

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode.

Please note that there may still be issues even using the --compatible option

MySQL Manual
User Contributed Notes: 2) Backing-Up the Current Database Add a Comment
Rob (daemon) 20th Apr 2005, 11:03am
It may help if you encase the table names in backticks (like you can do in phpMyAdmin). I have found that it helps to encase the table/field names in backticks when you are restoring the database later on in the server moving process.

To do this, run the following command command:

mysqldump --opt --quote-names -uUSERNAME -p DATABASENAME > /PATH/TO/DUMP.SQL

Note the --quote-names after the --opt option.
Kier Darby 20th Apr 2005, 11:03am
Referring to the note above, you can also use '-Q' in place of '--quote-names', leaving you with

mysqldump --opt -Q dbname > filename.sql
belindaj 20th Apr 2005, 11:11am
Removing the --OPT parameter may work if you are sure that the username and the database name in the rest of the statement are in fact correct and you are getting access errors about LOCKED TABLES. Once I took out the --OPT, the rest of the procedure went perfectly.
SaN-DeeP 06th Oct 2008, 06:09pm
mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -p****** nameof_db > db-sqldump.sql

The above command has helped us backup large databases with more then 4 GB size without any problems/errors.

Explanation of above variables:
q, --quick
Don't buffer query, dump directly to stdout.

--add-drop-table
Add a 'drop table' before each create.

--add-locks
Add locks around insert statements.

--e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.

--l, --lock-tables
Lock all tables for read.

--opt [looks like this is me being redundance]
Same as --add-drop-table --add-locks --all --quick --extended-insert --lock-tables --disable-keys
Rental Car Cowboy 13th Jul 2010, 03:57am
--opt is on my default, by the way.

It includes:
--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset.
Add a Comment