By Julien PHAM on

Hi,
So my question is in two parts.

First, I have a running drupal site on a mySQL 5 server. I need to change server to a dedicated one, but with Linux Debian 3.1 sarge stable and so with a mySQL 4.1.

So, how can I backup my database from the old server and restore it on the new one? Export works fine, and so I have a huge .sql file. But I'm unable to import it in the new mySQL, as the file is too big so the query fails. Are there convenient tools to do such things?

Second, once the new server will be up and running, are there tools to make periodic backup of the database?

Thanks

⋅ Categories: Drupal 4.7.x

Comments

wellsy’s picture

wellsy commented

http://drupal.org/node/43024

and

http://drupal.org/node/29661

They may help you.

wellsy

orchidsonline.com.au

Julien PHAM’s picture

Julien PHAM commented

Thanks.
But I still have issues, and I don't know why. My original database uses UTF8 encoding, and have accentuated characters like "é" for instance. So I backup my database using phpmyadmin, so on windows I have a .sql file, I look at it and it seems correct. Then I upload it on the server, I launch bigdump, and after the restore when I look at my tables I have strange characters instead of the accentuated ones...

Any idea?

If with my ssh on my linux distro I do a vim backup.sql, I see strange characters as well...

wellsy’s picture

wellsy commented

on utf8 encoding and mysql here on drupal.org

but look here http://drupal.org/node/8408

wellsy

orchidsonline.com.au

Julien PHAM’s picture

Julien PHAM commented

Thanks, but what I don't understand is that my database is utf8, both the old one and the new one, that is when I put my file on my linux server that I have issues. If I restore my database with phpmyadmin I have no problem, except that I cannot have too long queries.

Edit : ok I found the problem... I have to save the sql file as ANSI and not UTF-8 to store it on my linux server...

mango’s picture

mango commented

Although this comment does not relate to your original question, it might apply to your new situation where you will be working with mySQL 4.1.

As I have found out recently, mySQL 4.1 has a problem with exporting sql dumps in UTF8 through PhpMyAdmin. When you create an sql dump through PhpMyAdmin, special characters, like é and ü, turn into rubbish. This is a mySQL issue and not a Drupal issue. After a a lot of time bugging my webhost's customer support at Siteground, they have supplied an alternative command line solution that seems to work. The command line command for creating an SQL dump from mySQL 4.1 is:

mysqldump -Q --add-drop-table --default-character-set=utf8 --compatible=mysql40 --set-charset=utf8 DATABASENAME > BACKUP.SQL

where DATABASENAME is the name of your database and BACKUP.SQL is the name of the sql dump file.

The upside is that it seems to work. You can run it as a cron job so that it automatically creates an sql backup of your website. Then you only need to download it to your own computer.

The downside is that it degrades your database character set to be compatible with mySQL 4.0. When you import this sql dump into mySQL 4.1, the character set of the tables turns into latin1_swedish_ci. On Drupal.org there are warnings against using anyting other than UTF8, but so far it seems to work for a simple website.

Julien PHAM’s picture

Julien PHAM commented

My problem is not exporting from a mySQL 4.1 database, my original drupal database is mySQL 5, and I try to import it into a mySQL 4.1. For now I have no success doing this, when I access my website I go on a blank page and nothing more, don't understand why... I succeeded in importing my database in mySQL 4.1, or I think so, by using UTF8 as source and destination database, and by changing the character encoding to ANSI instead of UTF-8 in windows before exporting it to my linux OS where my database is in...

I then watched my database and the special characters looked ok.

Then I copied all the files from my old server to my new one, and nothing happens, just a blank page when I go to my website...

I then tried to erase all and to install a fresh drupal version, with a new database, and it worked fine...

So don't understand what happens...

kalulwe’s picture

kalulwe commented

you needed to allow overriding .htaccess in the vhost config to AllowwOverride All

kalulwe’s picture

kalulwe commented

you need to allow overriding .htaccess in the vhost config

mango’s picture

mango commented

My webhost Siteground has upgraded both MySQL and PhpMyAdmin to newer versions. As a consequence, the export function in PhpMyAdmin now works as it should and there is no need to hack the mysqldump command by ticking the backwards compatibility for mysql40 in PhpMyAdmin. In the example above, this option is shown by the addition of "--compatible=mysql40" to the mysqldump command. In the new environment, it is better to leave this option out of the command. By leaving it out, the database is exported as UTF8 and after restoring the database, the encoding will remain UTF8.

The webhost is running the following versions:
-- MySQL Server version: 4.1.19
-- PHP Version: 4.4.2

lisa’s picture

lisa commented

But what if your problem is like Jullian Pham's who said in the above comment http://drupal.org/node/63795#comment-121696

My problem is not exporting from a mySQL 4.1 database, my original drupal database is mySQL 5, and I try to import it into a mySQL 4.1.

Then you have to include --compatible=mysql40 in the mysqldump command don't you? But as you mentioned including --compatible=mysql40 causes the character set of the tables to turn into latin1_swedish_ci when they are imported into MySQL4.1. This mangles a multi-lingual site.

Anybody know how to successfully move non-English data from MySQL 5 to 4.1?

wellsy’s picture

wellsy commented

a few more details for those who may read this thread later if you have solved the problem.

This discussion may then help those with the same problems.

wellsy

orchidsonline.com.au

bomarmonk’s picture

bomarmonk commented

Bigdump looks good. I get tired of trying to slit my database into pieces so that it can be uploaded.

Julien PHAM’s picture

Julien PHAM commented

My problem was not solved, I just created my articles again with cut and paste...
btw if you have an access to your server, no need of bigdump, you can just use "mysql" command...

abqaria’s picture

abqaria commented

Dear All
my host siteground runs mysql 4.1
on my local mashine i run 5
i managed to export from my local mashine with checking the 4 compatability but u will get errors in fields that have autoincrement
u will need to do some changes manually

my problem now is how to import the online database which is 4.1 to my local mashine which is 5, i failed.. any solutions ? should i make my local mashine 4.1??

also abou the characters , i got also this swedish/latin thing not unicode, how can i change it..

i tried today to post something in arabic, in the preview it looked okay but after submitting the post i got rubish, i think i should change the character setting in the db to unicode...but how

any help

abqaria’s picture

abqaria commented

to import from mysql 4 to 5
just in phpadmin , do not check any compatability
drop all the tables
and import data and structure

about the utf and latin i did the following

i exported as text, replaced latin1 with utf8 (find and replace in wordpad)
then created a new database (to keep the old one as backup)
imported the modified text file
made a new connection
tried it
all fine
kept the new database
http://www.i-bloggers.com

http://www.i-bloggers.com

albertc’s picture

albertc commented

Hi

After a failed upgrade to 4.7 via Fantastico, I'm trying to restore my previous 4.5 site in order to run a manual upgrade. I already have my HTML and PHP in place and have dropped all tables in the database via phpMyAdmin.

However, when I try to import the backup SQL dump, I'm getting the following error:

Error
consulta SQL: 

SELECT * 
FROM 
WHERE CONCAT_WS(  "-", 1318,  'què', 0, 1, 1, 1144603001 ) =  "què"
ORDER BY 1318 ,  'què', 0, 1, 1, 1144603001

MySQL ha dicho: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE CONCAT_WS("-", 1318, 'què', 0, 1, 1, 1144603001)
                     ' at line 3

Does it look like a character set issue to you? If so, how can I solve it? I have no idea of which character set was used when making the dump. Here's what the file header says:

-- MySQL dump 9.11
--
-- Host: localhost    Database: XXXXXXXX
-- ------------------------------------------------------
-- Server version	4.0.26-standard

Can somebody help? Thanks in advance!
--
Albert

ronen-1’s picture

ronen-1 commented

Hi there,

did you try to do the job with 'mysqldumper'?

'mysqldumper' is a small script that you can install on your server to export, backup, or import dumps from your mysql-database, no matter if 4.1 or 5.
I used mysqldumper to workaround the upload limitation in phpMyAdmin, that was set to only 8 MBs in my case.
'mysqldumper' also allows for ftp uploads of your *.sql files.
And, it did also bypass the RAM memory limitation of my server which was then 32MBs when exporte my db from within mysqldumper

http://www.mysqldumper.de/

Hope this helps!
R