UBB.Developers Forums Community Development Programming MySQL and Server Configuration What to use to backup MySQL Database?

UBB.threads version 6.3.1
-------------------------


I'm switching to a dedicated server and I've never backed up my UBB.threads database other than by the Admin Panel which backs up the tables.

I do have phpMyAdmin, but I don't know how to backup with it as I've not had time to read up much on it. I've had a friend attempt to backup with it but says it keeps timing out.

Any advice I can get on backing up my Infopop database and importing it on my dedicated server. The old server operates with Windows NT and the new dedicated server operates with Linux.

Any help on this is greatly appreciated.

Run mysqldump from the Linux shell.

Example:

# backup
$ mysqldump --opt -u USERNAME -p DBNAME > 20030907_DBNAME.sql

# restore
$ mysql -u USERNAME -p DBNAME < 20030907_DBNAME.sql

Dave thanks...but I'm trying to make a backup from the old server which is on a Windows NT OS.

I'm trying to also do the backup from the server (Windows NT) using phpMyBackup but I keep getting:


CGI Timeout
The specified CGI application exceeded the allowed time for processing. The server has deleted the process


Seems consistently happening at the 2 minute mark into the backing up process.

If you're using a windows server, you can just backup the actual data files from the mysql folder using the windows backup utility

So the actual files are the same regardless of whether the server is Windows or *nix?

That's too easy. If you're going to use MySQL, you should use commands that make you learn a bunch of complicated options.

I don't have root access to the server.

Update on what I've done thus far....

I've been using phpMyAdmin to do the dumps. I've dumped all the tables in the Infopop database except one; w3t_posts

I keep getting a time out on that table. I figured I'd break down the dumps so it wouldn't take as long doing them and limiting the possibility of time outs. Obviously, w3t_posts is the largest of all the tables (4.7mb).

I successfully conducted an sql command in restoring the other tables onto my Linux server....but this last table (w3t_posts) is kicking my butt from dumping from the Windows server!

Any way to get this table without a time out or is that something that the web provider will have to do at this point?

Thanks guys...

If the database is too big to dump with phpMyAdmin and you don't have command line.... if you click on an individual table and use the export tab (if you have the latest version) there is the ability to dump just rows x through y so you could chunk it up.

I've had to do that in a pinch.

Mmmm, well after contacting my webhost provider on the Win NT server that I'm trying to get the database off of....he made backups of the files...but they are in this form:

w3t_posts.frm
w3t_posts.MYD
w3t_posts.MYI

How the hell can I get these files restored on the server?

Just copy them to the new server's MySQL data directory. On my old Linux server, I think that was /var/lib/mysql. There was a subdirectory under that directory for each database. So if your UBB.threads database were named, for example, "ubbt", then you would copy those files to /var/lib/mysql/ubbt. Before doing that, you could create a temporary database on the new server to see exactly where it stores the files.

Yikes.... this seems like it's more of a hassle to do this way....I'll first see if my webhost will just save the files as .sql and then put them up somewhere that I can ftp them from.

Okay, now here's what we got going on now....

My webhost has backed up the entire Infopop database and saved it as Infopop_backup.sql

Now, I went into phpMyAdmin to run the sql command trying to restore the the database....but I get this in return:

No SQL query!

I'm following the instructions in the phpMyAdmin Documentation as well on doing this:

[6.5] How can I restore (upload) my database or table using a dump? How can I run a ".sql" file.

Click on a database name in the left frame, the properties will be local displayed. Then in the "Run SQL query" section, type in your dump filename, or use the Browse button. Then click Go.

Well, copying the .frm/.MYD/.MYI files is very easy. Just put the files in the right place, and poof, there's the database.

But I would create a test database first just to make sure you know where the files are supposed to be, and which group and user owns them. The test database could consist of a single table with one row in it, and can be created easily with phpMyAdmin.

If you really have to use phpmyadmin, make sure you use the latest version, as it has improved support for dumping large databases.

If you're gonna use the
w3t_posts.frm
w3t_posts.MYD
w3t_posts.MYI

just do as dave suggests and have your hosts restore them to the directory saved from.