can some one help me out with a good tutorial on how to restore a mysql database using ssh (putty) your help would be much appreciated as i have tried phpmy admin route and it just times out every time, been trying for the last 3 days. the data base is 24mb zipped and i need to restore it badly and asap as i had to close down the forum a few months back which had 36000+ members
Here's what I do: enter IP address into putty, select ssh radio button, then click open. Then login, navigate to the right directory by entering cd directorynamehere then enter mysql -sitename.com -uusername -ppassword databasename < filename.sql Make sure the username and password have the u and p infront of them like that.
will try that thanks for your help does any one know a very very descriptive tutorial on this plz or do i learn it the hard way and write one myself for other noobs
Not sure about the -sitename.com above - I've never used that personally. Perhaps you mean -h sitename.com (meaning use sitename.com as the database server). This defaults to "localhost" - you can leave it blank if your database server is the same server that the backup file is on. The post above is also assuming that the zipped copy of the database was from a text dump of the database. Was your backup performed using the mysqldump command or is it just a straight zip of the database directory itself (/var/lib/mysql/data/databasename/*.MYI, etc.). mysqldump is the preferred route for backing up a database but it's still possible to get yourself back online with a zipped copy of the database files. Post more info here and I'll try to help you out.
You must contact your hosting provider to get the correct hostname and maybe the dbname as well. See if anyone can help you with the query itself.
i am now able to log into my mysql thing but the only thing i dont understand in this : IMPORTING DATA INTO YOUR MYSQL DATABASE: To import a sql file to an existing mysql database hosted on your account: %/usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseID < File.sql is the last file.sql bit do i upload the file to the server in root or something or do i type in the path on my computer ? any help will be highly appreciated regards smfseo forgot to mention the back up file ive got is in sql.gz format if that makes any difference
thanks will try that but before i go back into that stupid black window again i have one more question the file i have is database.sql.gz would that make any difference or shall i just do it the way mentioned above ? or would there be a different command for that
Thanks for your quick reply will try this and if this aint working this time ive got a gun will use that and if i miss a shot will come back here again
still no luck below is what i am getting with putty although that file is in the root % /usr/local/nf/bin/mysql--force--quick-h mysql.netfirms.com-u******-p**** d*****< vbdatabase.sql.gz > bash: vbdatabase.sql.gz: No such file or directory any clue whats going on or do i need to upload the file else where?
now that seems like a good idea will give it a try with account folder name this time otherwise i am uploading the text file now to the server and planning to use bigdump just thought i might aswell try that may be it will work (didint work with gz file so i am uploading 94mb text file now) thanks for your help again Mattmdesign
tried this and something did happen but i am not sure what happend can you plz have a quick look i am stuck here dont know what to do checked forum home pane no restore yet no posts or members added nothing just the putty black window got weird messages i dont know what to do might make some sense to you if you could kindly have a look for me plz holiday-travel-blog % /usr/local/nf/bin/mysql --force --quick -h ****** -u u****** -p ****** d****** < desisensation.com/vbdesisensation.sql /usr/local/nf/bin/mysql Ver 11.18 Distrib 3.23.55, for unknown-freebsd4.10 (i386) Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: /usr/local/nf/bin/mysql [OPTIONS] [database] -?, --help Display this help and exit. -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. -B, --batch Print results with a tab as separator, each row on a new line. Doesn't use history file. --character-sets-dir=... Directory where character sets are located. -C, --compress Use compression in server/client protocol. -D, --database=.. Database to use. --default-character-set=... Set the default character set. -e, --execute=... Execute command and quit. (Output like with --batch) -E, --vertical Print the output of a query (rows) vertically. -f, --force Continue even if we get an sql error. -g, --no-named-commands Named commands are disabled. Use \* form only, or use named commands only in the beginning of a line ending with a semicolon (;) Since version 10.9 the client now starts with this option ENABLED by default! Disable with '-G'. Long format commands still work from the first line. -G, --enable-named-commands Named commands are enabled. Opposite to -g. -i, --ignore-spaces Ignore spaces after function names. -h, --host=... Connect to host. -H, --html Produce HTML output. --local-infile=[1|0] Enable/disable LOAD DATA LOCAL INFILE -L, --skip-line-numbers Don't write line number for errors. --no-pager Disable pager and print to stdout. See interactive help (\h) also. --no-tee Disable outfile. See interactive help (\h) also. -n, --unbuffered Flush buffer after each query. -N, --skip-column-names Don't write column names in results. -O, --set-variable var=option Give a variable an value. --help lists variables. -o, --one-database Only update the default database. This is useful for skipping updates to other database in the update log. --pager[=...] Pager to use to display results. If you don't supply an option the default pager is taken from your ENV variable PAGER (). Valid pagers are less, more, cat [> filename], etc. See interactive help (\h) also. This option does not work in batch mode. -p[password], --password[=...] Password to use when connecting to server If password is not given it's asked from the tty. -P, --port=... Port number to use for connection. -q, --quick Don't cache result, print it row by row. This may slow down the server if the output is suspended. Doesn't use history file. -r, --raw Write fields without conversion. Used with --batch -s, --silent Be more silent. -S --socket=... Socket file to use for connection. -t, --table Output in table format. -T, --debug-info Print some debug info at exit. --tee=... Append everything into outfile. See interactive help (\h) also. Does not work in batch mode. -u, --user=# User for login if not current user. -U, --safe-updates[=#], --i-am-a-dummy[=#] Only allow UPDATE and DELETE that uses keys. -v, --verbose Write more. (-v -v -v gives the table output format) -V, --version Output version information and exit. -w, --wait Wait and retry if connection is down. Default options are read from the following files in the given order: /etc/my.cnf /usr/local/nf/var/my.cnf ~/.my.cnf The following groups are read: mysql client The following options may be given as the first argument: --print-defaults Print the program argument list and exit --no-defaults Don't read default options from any options file --defaults-file=# Only read default options from the given file # --defaults-extra-file=# Read this file after the global files are read Possible variables for option --set-variable (-O) are: connect_timeout current value: 0 max_allowed_packet current value: 16777216 net_buffer_length current value: 16384 select_limit current value: 1000 max_join_size current value: 1000000 holiday-travel-blog % PHP: i am stuck here got no where to go and the instructions i was following havent got anything after this infact no mention of this message commin up regards smfseo
The file you have is compressed with the unix gzip utility. You'll need to uncompress it first in order to end up with a .sql file. Here's the command to use first, after you upload the file to the server: gunzip vbdatabase.sql.gz You'll end up with an even bigger file called vbdatabase.sql and NOW you can run the mysql command in the above post. Also it seems like you aren't in the same directory as the file you uploaded when you run that command. You should probably do all of this from /root or /tmp (if /tmp is large enough) or some path that you know you can safely work in. Everything on unix is path relevant. So if your FTP client uploads to /root, when you go into ssh you'll want to change to that directory first before you run the commands... cd /root, then gunzip vbdatabase.sql.gz, then the mysql --force command. Try it out and report your results back and we'll all try to help if something doesnt work right!
so nothing really happened !!! i thought i did something there will gonna try the way you just advised while trying not to pull my hair any more
By the way, I would be more than happy to log in to your server for you and restore the database if you'd like. Or I will continue to try to help you here, your call. Personally I'd never let anyone onto any of my servers as root, but I figured I'd throw the offer out there if you're interested.