1 | Telnet/SSH into the server where your vBulletin is currently installed. We will call this machine1. |
2 | Type:mysqldump --opt -Q -uUSERNAME -p DATABASENAME > /PATH/TO/DUMP.SQLIn this line, you should change the following:
|
3 | Once 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
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
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.
mysqldump --opt -Q dbname > filename.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
It includes:
--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset.