Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
I have a questions regarding SQL tables etc.
I have a forum in <location1> and would like to back it up and import it to <location2>, from one server/host to another.
I’ve installed the basic forum software on host2 and I was simply hoping to backup/restore between the two. The only downside is, host1 has a table prefix of phpbb_ for each table, where as the installation for host1 was a one click installation using the host, and it does not have the prefix.
Is there any easy way to resolve this? Or is it likely to get very messy? I have no real experience with SQL/tables.
|
willay
Moderator
Organiser: South East, National Events
Premium Member
Registered: 10th Nov 02
Location: Roydon, Essex
User status: Offline
|
mysql?
If so then use mysqldump
> mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
>
so just do a
> mysqldump --database dbnamehere > my_databases.sql
Tehn when you want to put that data into a different database...
> mysql dbnamehere < my_databases.sql
sorted.
[Edited on 02-04-2009 by willay]
|
Sam
Moderator
Premium Member
Registered: 24th Dec 99
Location: West Midlands
User status: Offline
|
Or phpMyAdmin, export the database from the old host and import it into the new database.
Most hosts give you phpMyAdmin access, more user friendly/easier than hardcore SSH command-line stuff like what William has described.
[Edited on 02-04-2009 by Sam]
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
Yes, sorry guys, its phpMyAdmin.
Does that mean I will have to manually edit each table name and remove the prefix myself?
Its not the end of the world... just going to take some time.
I was also wondering, if I modified the tables, what will that conflict with when writing (i.e the php files?).
There must be something at file/folder level to tell the website which table it should be writing to, or is this likely to be within a config file?
Thanks guys.
[Edited on 02-04-2009 by Bart]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Table prefix is normally in the config so you may be able to add your prefix to the config on the new install and it'll just work.
Failing that you'll have the raw SQL commands if you use mysqldump or export from phpMyAdmin so you can fairly easily use a text editor to find/replace the strings.
It won't conflict so far as the PHP is concerned, in fact it'll never know. It just prepends the table prefix from the config and goes and does queries on that table. If the table is there and the privs are right it'll work.
|
willay
Moderator
Organiser: South East, National Events
Premium Member
Registered: 10th Nov 02
Location: Roydon, Essex
User status: Offline
|
quote:
Originally posted by Samthan hardcore SSH command-line stuff like what William has described.
fags!!!  
|
ENB
Member
Registered: 24th Apr 06
User status: Offline
|
quote:
Originally posted by Sam
Or phpMyAdmin, export the database from the old host and import it into the new database.
Most hosts give you phpMyAdmin access, more user friendly/easier than hardcore SSH command-line stuff like what William has described.
[Edited on 02-04-2009 by Sam]
But by using a bash script you can automate the backup and restore.
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
Ian, I can backup as a .sql file and simply use notepad2 to do a nice find/replace, (ive just opened another SQL file and all the text appears readable), so im ok with that bit.
With regard to the "config", are you referring to a likely config.php file or something within the database itself?
|
PaulW
Member
Registered: 26th Jan 03
Location: Atherton, Greater Manchester
User status: Offline
|
quote:
Originally posted by Sam
than hardcore SSH command-line stuff like what William has described.
Its far better to do backups/imports via SSH anyways, especially on large databases. Although I just have a bash script to do automated backups at set intervals
Plus, with phpMyAdmin, you still have timeout issues or file size upload restrictions depending on how php is configured (aswell as whatever http server program you are using)
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
quote:
Originally posted by Bart
With regard to the "config", are you referring to a likely config.php file or something within the database itself?
Yeah config.php in the forum dir, just get in there and have a look for $table_prefix
Ditto the commends on SSH being less risky.
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
well, I manually edited the .sql backup and removed the pre-fix using find/replace.
But afterwards I did notice that prefix setting in the config. Either way, job done 
thanks guys
|
