Migrating mySQL
Migrating mySQL from one Database to Another (even across Hosts).Overview
The process boils down do outputting the sql commands from your old host, then importing them into to your new host. This can be done in one step (See the One Step Solution), or several steps.Before you begin
Before you begin you need to:- Create a new database on the destination server (From cpanel/netadmin)
- Create a user for that database
- grant All permissions for that user to that database.
Details
One Step Solution
This remote access to the "other" database.mysqldump dumps the old database. -h Specifies the hostname -u the username -p the password and finally the Database name the output is sent to stout
mysql loads the new database (Which must already be created). The Parameters (for our use) are the same as mysqldump.
mysqldump -holdhost -uoldusername -poldpassword olddbname | mysql -hnewhost -unewusername -pnewpassword newdbname
Note: If you're downgrading from mysql 4.1 to 4.0 (the version on MysticServer) you'll need to use the --skip-opt switch when creating the dump, e.g
mysqldump --skip-opt -holdhost -uoldusername -poldpassword olddbname | mysql -hnewhost -unewusername -pnewpassword newdbname
or PHP (No SSH access)
Put the following PHP Script in to a file and call it "mysqlimport.php":
<?PHP
// Source server settings
$from_user = "Put User Here";
$from_password = "Put password here";
$from_database = "Put Database name here";
$from_server = "Put Source Server here";
$from_41 = False;
// Destination Server Settings
$to_user = "Put User here";
$to_password = "Put Password here";
$to_database = "Put Database name Here";
$to_server = "Put Source Server here";
// $to_server = "localhost";
// Don't Edit below here!!!! (Unless you know what your doing).
if ($from_41) {
$theOpt = "--skip-opt ";
} else {
$theOpt = "";
}
$Results = system( "mysqldump $theOpt -h$from_server -u$from_user -p$from_password $from_database | mysql -u$to_user -p$to_password -h$to_server $to_database", $ResultErr );
echo "Results from MySQL import of $DatabaseName with User $User with file $TheFile:\r\n $Results\r\n $ResultErr";
?>
// Source server settings
$from_user = "Put User Here";
$from_password = "Put password here";
$from_database = "Put Database name here";
$from_server = "Put Source Server here";
$from_41 = False;
// Destination Server Settings
$to_user = "Put User here";
$to_password = "Put Password here";
$to_database = "Put Database name Here";
$to_server = "Put Source Server here";
// $to_server = "localhost";
// Don't Edit below here!!!! (Unless you know what your doing).
if ($from_41) {
$theOpt = "--skip-opt ";
} else {
$theOpt = "";
}
$Results = system( "mysqldump $theOpt -h$from_server -u$from_user -p$from_password $from_database | mysql -u$to_user -p$to_password -h$to_server $to_database", $ResultErr );
echo "Results from MySQL import of $DatabaseName with User $User with file $TheFile:\r\n $Results\r\n $ResultErr";
?>
Three Step Solution
This solution requires Shell (Telnet SSH) access to both hosts (but no remote access).mysqldump dumps the old database. -h Specifies the hostname -u the username -p the password and finally the Database name the output is sent to stout
mysql loads the new database (Which must already be created). The Parameters (for our use) are the same as mysqldump.
- Dump the old database
- SSH:
- Telnet to your old host
- mysqldump -hlocalhost -uoldusername -poldpassword olddbname > outfile.sql
Note: If you're downgrading from mysql 4.1 to 4.0 (the version on MysticServer) you'll need to use the --skip-opt switch when creating the dump, e.g
- mysqldump --skip-opt -holdhost -uoldusername -poldpassword olddbname > outfile.sql
- or myPhpAdmin (No Shell required) dump of the database, and structures. With these options:
- Structure
- Add DROP TABLE
- Add AUTO_INCREMENT
- Enclose table and field names with back quotes
- Data
- Complete inserts
- Extended inserts
- Use hexadecimal for binary fields
- Structure
- SSH:
- transfer the file to the new host
- Load the Old Database
- SSH
- telnet to your new host
- mysql -hlocalhost -unewusername -pnewpassword newdbname < outfile.sql
- or PHP (No SSH access)
- Put the following PHP Script in to a file and call it "mysqlimport.php":
<?PHP
$TheFile = "Complete Path File to file -- must be /home/User/rest of the path";
$User = "Put User here";
$Password = "Put Password here";
$DatabaseName = "Put Database name Here";
$Results = system( "mysql -u$User -p$Password -hlocalhost $DatabaseName < $TheFile", $ResultErr );
echo "Results from MySQL import of $DatabaseName with User $User with file $TheFile:\r\n $Results\r\n $ResultErr";
?>
- Put the following PHP Script in to a file and call it "mysqlimport.php":
- SSH
Common Errors
If your SQL has a 'CREATE DATABASE' command it will fail. You need to create the databases before hand and remove any 'CREATE DATABASE' commands. This holds true for DROP Database, and USE commands as well. If these commands appear in your SQL they will usualy be found near the top of the file (after the initial comments).Afterwards
After you have the data transferred, you will need to edit your php scripts so they reflect the new database names, user names, passwords and possibly server names (if your old host didn't use localhost). Usually this is done in a configuration file config.php, config.inc.php, etc. If you can't find the right file, check the software vendors site.Search:
Back:
MysticServer
| Total Hits: | 5807 |
mysqldump --skip-opt ...
Else you'll have lots of trouble bringing it into mysql 4.0
Seems to have a incorrect $ in front of the if