There's the Module method and the Manual method... but you should really learn how to do it manually MySQL command line, then PHPadmin... then the module if you're serious about backing up your data...
First download (via FTP or SFTP) all of the folders and files from your drupal installation.
If you're like me then setup a subdomain on your new hosting provider. Then upload all of the drupal folders and files (the PHP infrastructure that may contain custom themes, settings, etc)
Now comes the tricky bit. Using PHPmyAdmin you can create a text dump of your database:
- Open phpMyAdmin. (0.5 You may have to double click on the name of your Drupal Database.)
- Click Export from the Menu (that's how we're "backing it up", by exporting it to a file).
Click and highlight every table in the database listed on the left, not just one table. EXCEPT... you don't need to backup the information_schema table. AND this only applies if you've drilled down inside of your Drupal database (step .5)
Ensure that: output format = SQL, "Structure" and "Add AUTO_INCREMENT" and "Enclose table and field name with backquotes" are checked. Also check "DATA" with "hexadecimal for binary field". Export type set to "INSERT".
- IMPORTANT! "Save as file", do not change the file name, use compression if you want.
- Click "GO" to download the backup file.
Note that this may cause your browser to "freeze" while it does some of the work. Also note that if you don't click "save as file" you'll see the "backup" as a text export in a screen that may very well freeze your browser for a very long time (depending on your computer speed and table sizes!)
Why did we do all of that crazy stuff above? Well basically it will export your database as a set of "create table" and "insert data" statements (cool eh!) BUT auto-increment ensures that it doesn't get accidentally give the same unique record id to two different things (a big No No in databases!) ...
OR YOU COULD, IF YOU HAD ACCESS TO THE COMMAND LINE: mysqldump -u username -ppassword database_name > dump.sql
SO FAR WE'VE GOT our PHP infrastructure and themes backed up and restored. We've got our MySQL database as one huge text file (zipped/compressed).
NEXT we must create a MySQL user on our new hosting/server that is authorized to import databases...
CREATE USER 'username' @ 'localhost' IDENTIFIED BY 'password'
GRANT ALL ON . TO 'username' @ 'localhost';
If life was simple you could just
- Open phpMyAdmin.
- Click Import
BUT IT AIN'T ALWAYS THAT EASY...
Ironically, as I mention below in "story" form, migrating between web hosts may require putting MySQL on your home computer to an extra import export...
straight from the linux command line!
$ mysql -u root -p -h localhost < backup-filename-data.sql
this is how you download/install mysql on Debian/Ubuntu, could be RedHat yum...
apt-get install mysql-server
YOU MUST CONFIGURE DRUPAL settings.php TO USE THE NEW USERNAME AND NEW DATABASE
/drupal-root/sites/default/settings.php with new username and password details
around line 92...? $db_url = 'mysql://kbdrupaluser:PASSWORD@10.6.166.225/kbdrupaluser';
line 33 var $user = 'databasename_username'; line 48 var $password = 'enter new password here';
apparently don't forget that username & password characters need to be hex escaped? or is that about sql?
- : = %3a / = %2f @ = %40 * + = %2b
( = %28 ) = %29 * ? = %3f = = %3d & = %26
Start up your browser and say hello to the same old new website! You'll probably want to flush the caches and run cron... I found a weird error about themes.inc and eventually realized some experiments on custom themes needed to be deleted from the Drupal File folders and then everything was fine.
ALL OF THIS STUFF BELOW IS THE WRITTEN AS IT HAPPENED JUNK (NOT VERY CLEAR...) IT ALSO SHOWS MUCH OF MY IGNORANCE ON THE SUBJECT...
!prerequisite! to migrate to a "new" hosting you'll need to recreate a default Drupal installation (and databases), then overwrite with the new data...
The drupal module "backup and restore (migrate)" is the ideal way to stay in the Drupal mindset. What I mean is, yes, you can download the framework by FTP but rather than getting involved in MySQL backups, use a Drupal Module (which is why we like Drupal!)
Download the module from Drupal.org Install the module as per usual, by un tar / gzipping (izarc portable for windows users) the folder then uploading it to the folder /drupal-root/sites/all/modules (which you may to have create)
unfortunately it didn't work with my GoDaddy hosting because it was running PHP4 I believe my new host, Rochen (very highly recommended by Joomla users), will support PHP5
I've sent the request to update to PHP5 but in the meantime I'll try the MySQL backup method too... http://www.ardamis.com/2009/07/25/upgrading-godaddy-hosting-to-php-5-x-from-php-4-3-11/
!prerequisite! - create a user that has full database permissions on the new database (ie create a user on the new MySQL system that has the same name and password as the old system)
BUT be aware that some phpadmin/hosting services (Rochen) only allow a username of 7 letters max!) ... AND other hosts (Godaddy) only allow one MySQL user per database (which I picked with12 letters)...
otherwise, the mystery error: "Database Error: Unable to connect to the database:Could not connect to MySQL"
Best Workaround: use the builtin phpADMIN SQL query section to run:
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
1227 - Access denied; you need the CREATE USER privilege for this operation
a php file with the following line (to change the password?) SET PASSWORD FOR 'mambo'@'localhost' = OLD_PASSWORD('mambo');
Workaround, export the database, then create a new database with a different root user and then import it back to the original host but the new databaseagain...
user / pass / db kittyand_bear / ... / kittyand_drupal
MY AGONY IN MIGRATING A DATABASE
As I've described, Godaddy creates a database with a single user in a very limited form (can't change the MYSQL username, can't add more users).
To "import" using PHPadmin you need to have the same user defined on the target hosting MySQL framework.
BUT Rochen only allows 7 characters (and appends this to your account name) so basically they're guaranteeing users won't create the same Database Names (fair enough) - BUT you can't create any more MYSQL users yourself...
So I've exported and downloaded the MySQL database (one with information_schema, one without... though I don't think I need information_schema as it's MySQL's own way of keeping track of what users/permissions are allowed on the rest of the databases).
apt-get install mysql-server (this seems to also install mysql client) apt-get install mysql-navigator apt-get install mysql-administrator
The mysql-navigator or mysql-administrator GUI front end made things a little easier... login using the root user (with password defined during mysql-server installation) and "localhost"
BUT the command line is the most sure way...
If you do not know the database name or database name is included in sql dump you can try out something as follows: $ mysql -u username -p -h 18.104.22.168 < data.sql
$ mysql -u root -p -h localhost < data.sql THIS WORKS!
Unfortunately I was then unable to use MYSQL navigator to create a new user (some sort of "column doesn't match error) BUT I could use MYSQL administrator to create the user that already exists on Rochen.
I then used MySQL navigator to export .sql file (first had to "touch filename.sql" because the "navigator" uses an "open" dialog box... weird) ... ah... but that crashed... back to the command line...
//tips: mysqldump -u username -ppassword database_name > dump.sql --no-data switch to export only the tables’ structures.
Adding a new database to Rochen and then "adding" the user to the database with full privileges:
User kittyand_kittyan was added to the database kittyand_kbdrupaluser.
Notice that there's a naming scheme accountname_mysqluser and accountname_mysqldatabasename
Added the database kittyand_drupal Added user bear with the password
Manage User Privileges
User: kittyand_user Database: kittyand_drupal ALL PRIVILEGES
User kittyand_bear was added to the database kittyand_drupal.
Start PHPAdmin then click on the NAME of the database to see it's settings...
BUT the default MySQL from Rochen is a swedish?
MySQL charset: UTF-8 Unicode (utf8)
Modified it to utf8_unicode_ci
kittyand_drupal DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
GRANT USAGE ON . TO 'kittyand'@'localhost' IDENTIFIED BY PASSWORD 'EDF1BF0272C14D7E30E996877BF7015F2D33C5A2'
GRANT ALL PRIVILEGES ON
kittyand\_drupal. TO 'kittyand'@'localhost'
GRANT ALL PRIVILEGES ON ''databasename''.* TO "''username''"@"''hostname''" IDENTIFIED BY "''password''";
GRANT ALL PRIVILEGES ON ''kittyand_drupal''.* TO "''kittyand_bear''"@"''localhost''" IDENTIFIED BY
GRANT ALL PRIVILEGES ON
kittyand\_drupal . * TO 'kittyand_bear'@'localhost'