Mysql help PLEASE!!!

[kansasskydiver 0]

I've gotten myself into quite a pickle and need some help as I have NO idea how to do this.

Just purchased a hosting plan yesterday to move the club's website off of the schools servers and my server to allow us more resources and design options. I hoted a message board (Mercury Board) on my server and now was to move it over to the host server.

So obviously it wasn't as simple as I though and I can't figure out for the life of me how to move an entire db from one server to another?!?!?!?!

Please any help out there

<--- See look, pink dolphins DO exist!

[shropshire 0]

Will your host allow you to use mysqldump?

(.)Y(.)
Chivalry is not dead; it only sleeps for want of work to do. - Jerome K Jerome

[kansasskydiver 0]

I'm not sure, but my server sure as f doesn't

I tried running the mysqldump on my machine and nothing, just ->

I found I could export with myphpadmin, but I can't get the freaking fracking mutter freaking facker to install and run properly.

<--- See look, pink dolphins DO exist!

[davedlg 0]

try the command

mysqldump -p(your password) (the database name) > file.sql

from the command line (not from within mysql)

This is what I do to dump the database and it works every time.

[kansasskydiver 0]

same thing I just get

->

Am I being stupid and doing something wrong?

Example:

mysqldump -12345 ksupc > ksupc.sql

<--- See look, pink dolphins DO exist!

[shropshire 0]

I dont see a -p in there.

(.)Y(.)
Chivalry is not dead; it only sleeps for want of work to do. - Jerome K Jerome

[davedlg 0]

perhaps you dont have the proper permissions to write to the directory you are trying to read or write the file to?? Try chmod to change the permissions of the directory you are writing to. Also, check to see if the file is actually there... IIRC, there is no feedback to tell if it has actually worked.

[kansasskydiver 0]

Quote

I dont see a -p in there.

tried that too, nothing...

<--- See look, pink dolphins DO exist!

[davedlg 0]

Quote

same thing I just get

->

Am I being stupid and doing something wrong?

Example:

mysqldump -12345 ksupc > ksupc.sql

If you are getting "->", you are within Mysql. Type "exit" to get out of mysql and into the unix/linux command line. Then try the mysqldump command.

Edited to add: You may have to hit ";" first...it looks like mysql is expecting a statement end.

[kansasskydiver 0]

I'm writing it from the mysql command line client. It's being run on winxp

adding in the ";" give me an error now

"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 mysql dump -p (password) forum > ksupc.sql ;

<--- See look, pink dolphins DO exist!

[AlexCrowley 0]

Quote

I'm writing it from the mysql command line client. It's being run on winxp

adding in the ";" give me an error now

"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 mysql dump -p (password) forum > ksupc.sql ;

http://dev.mysql.com/doc/mysql/en/select.html

If you cant get mysqldump to work, use select to an outfile.

TV's got them images, TV's got them all, nothing's shocking.

[davedlg 0]

Quote

I'm writing it from the mysql command line client. It's being run on winxp

adding in the ";" give me an error now

"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 mysql dump -p (password) forum > ksupc.sql ;

Okay that makes a lot more sense now. Anytime you write a mysql statement, you have to end it with a ";" otherwise "->" is all you will ever see.

I dont know much about the winxp command line client, but you might try looking to see if there is a msqldump client, perhaps you can run it from a dos command line?

[davedlg 0]

try exiting your mysql client. Go to the DOS command line (START>RUN>"COMMAND") type something like this:

c:\mysql\bin\mysqldump -p(your password) (the database name)  > C:\dump.sql 

[Slurp56 0]

There are a few options....

easiest first:

Mysqlcc or Mysql Administrator should have the options to export a database.
Mysql Administrator:
http://www.mysql.com/products/administrator/

Mysqlcc:
http://www.mysql.com/products/mysqlcc/


can you write any PHP?

I wrote a small bit of code to dump my database to a text file. It makes it in a nice and easy to read text file that you could copy and paste into phpmyadmin or mysqlcc to recreate your database. I'm not sure if it will help you but here it is:

function db_query($method, $data, $tables, $where) {

// Connect to the SQL Server and Database

$link = mysql_connect(SQL_SERVER, SQL_USERNAME, SQL_PASSWORD) or die('Could not connect: ' . mysql_error());

mysql_select_db(SQL_DATABASE) or die('Could not select database');


if($method == 'raw') {

$query = $where;

$results = mysql_query($query, $link) or die('Query failed: ' . mysql_error());

return $results;

mysql_close($link);

}

}



//Create the file and open it for writing

$file = fopen("includes/db_history/db_" . SQL_DATABASE . "-" . date('YmdHis') . ".sql", "w");


//Query the database to create the backup

// Get current date and time

$xNow = getdate();

$o_date = substr($xNow['year']+10000,1) . '-' . substr($xNow['mon']+100,1) . '-' . substr($xNow['mday']+100,1) . ' ' . substr($xNow['hours']+100,1) . ':' . substr($xNow['minutes']+100,1) . ':' . substr($xNow['seconds']+100,1);


// Define backup file header

$sql_backup = '-- --------------------------------------------------------' . "\n";

$sql_backup .= '-- Database Restore point for ISA' . "\n";

$sql_backup .= '-- Date: ' . $o_date . "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= '-- Created By:' . "\n";

$sql_backup .= '-- Tony VanGerpen' . "\n";

$sql_backup .= '-- Auteo, LLC - Auteomoto' . "\n";

$sql_backup .= '-- http://www.auteomoto.com' . "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= '-- Copyright (c) 2004 Auteo, LLC' . "\n";

$sql_backup .= '-- --------------------------------------------------------' . "\n";


// Select the table names

$where = 'SHOW TABLES';

$table_results = db_query('raw',$data = '',$tables = '',$where);


while($db_table = mysql_fetch_array($table_results)) {

$sql_backup .= "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= '--  Structure for Table `' . $db_table['0'] . '`' . "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= "\n";

$sql_backup .= 'DROP TABLE IF EXISTS `' . $db_table['0'] . '`;' . "\n";

$sql_backup .= 'CREATE TABLE `' . $db_table['0'] . '` (' . "\n";


// Reset Primary Key

$primary_key = '';


// Get fields in this table

$where = 'SHOW FIELDS FROM `' . $db_table['0'] . '`';

$field_results = db_query('raw',$data = '',$tables = '',$where);

$num_results = mysql_num_rows($field_results);

$i = 1;


while($db_table_fields = mysql_fetch_array($field_results)) {

$def_null = false;

$sql_backup .= '  `' . $db_table_fields['0'] . '` ' .$db_table_fields['1'] . ' ';


if($db_table_fields['2'] == 'YES') {

if($db_table_fields['4'] == NULL) {

$sql_backup .= 'default NULL';

} else {

$sql_backup .= 'NULL';

$def_null = true;

}

} else {

if($db_table_fields['4'] != '') {

$sql_backup .= 'NOT NULL default \'' . $db_table_fields['4'] . '\'';

} else {

$sql_backup .= 'NOT NULL';

}

}


if($db_table_fields['Key'] == 'PRI') {

$primary_key = $db_table_fields['0'];

}


if($def_null == true) {

$sql_backup .= ' default ' . $db_table_fields['4'];

}


if(!$db_table_fields['5'] == '') {

$sql_backup .= ' ' . $db_table_fields['5'];

}


if($i < $num_results) {

$sql_backup .= ",\n";

} else if($i == $num_results) {

if($primary_key != '') {

$sql_backup .= ",\n";

$sql_backup .= '  PRIMARY KEY (`' . $primary_key . '`)';

}

}


$i = $i + 1;

}

// Determine the next auto index number

$where = 'SHOW TABLE STATUS';

$index_results = db_query('raw',$data = '',$tables = '',$where);


while($row = mysql_fetch_array($index_results)) {

if($row['0'] == $db_table['0']) {

if($row['9'] != null) {

$sql_backup .= "\n" . ') TYPE=MyISAM AUTO_INCREMENT=' . $row['9'] . ';' . "\n";

} else {

$sql_backup .= "\n" . ');' . "\n";

}

}

}


// Dump the data from the table right here

$sql_backup .= "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= '--  Data from Table `' . $db_table['0'] . '`' . "\n";

$sql_backup .= '--' . "\n";

$sql_backup .= "\n";


$where = 'SELECT * FROM ' . $db_table['0'] . ';';

$data_results = db_query('raw',$data = '',$tables = '',$where);

$field_count = mysql_num_fields($data_results);


while($data_row = mysql_fetch_array($data_results)) {

$sql_backup .= "INSERT INTO `" . $db_table['0'] . "` VALUES (";

for($i=0;$i<$field_count;$i++) {

if(strpos($data_row[$i], "'")) {

if($i == 0) {

$sql_backup .= '"' . $data_row[$i] . '"';

} else {

$sql_backup .= ', "' . $data_row[$i] . '"';

}

} else {

if($i == 0) {

$sql_backup .= "'" . $data_row[$i] . "'";

} else {

$sql_backup .= ", '" . $data_row[$i] . "'";

}

}

}

$sql_backup .= ");" . "\n";

}


$sql_backup .= "\n";

$sql_backup .= '-- --------------------------------------------------------' . "\n";

$sql_backup .= "\n";

}


//Write the query results to the file

fwrite($file, $sql_backup);


//Close the file when done writing

fclose($file);

If all else fails, you can IM me and I can probably help you out. I've got next week off with nothing to do.

________________________________________
I have proof-read this post 500 times, but I guarantee you'll still manage to find a flaw.