Current time: 03-24-2017, 12:58 PM Hello There, Guest! (LoginRegister)

Post Reply 
Using PuTTY and mysql
11-05-2010, 12:29 PM
Post: #1
Using PuTTY and mysql
Alright, so I am incredibly new to mysql and command line stuff and I'm trying to feel my way through all of this.

Basically, I'm in the process of converting my invisionfree forum over to SMF, which I am hosting with dreamhost. I received my database dump from IF and am trying to get it uploaded into my database.

I was given the following instructions:
Quote:Be sure you have a freshly installed IPB (v1.3.x) with the default mysql table prefix (ibf_).
Next empty the following tables on the fresh install (again make sure the install is using the prefix ibf_):
ibf_badwords
ibf_posts
ibf_calendar_events
ibf_categories
ibf_contacts
ibf_emoticons
ibf_forums
ibf_groups
ibf_members
ibf_messages
ibf_moderators
ibf_polls
ibf_stats
ibf_titles
ibf_topics
ibf_tracker
ibf_voters
ibf_cache_store
ibf_forum_perms
ibf_topic_mmod
ibf_validating;

or you can use these queries:



DELETE FROM ibf_badwords;
DELETE FROM ibf_posts;
DELETE FROM ibf_calendar_events;
DELETE FROM ibf_categories;
DELETE FROM ibf_contacts;
DELETE FROM ibf_emoticons;
DELETE FROM ibf_forums;
DELETE FROM ibf_groups;
DELETE FROM ibf_members;
DELETE FROM ibf_messages;
DELETE FROM ibf_moderators;
DELETE FROM ibf_polls;
DELETE FROM ibf_stats;
DELETE FROM ibf_titles;
DELETE FROM ibf_topics;
DELETE FROM ibf_tracker;
DELETE FROM ibf_voters;
DELETE FROM ibf_cache_store;
DELETE FROM ibf_forum_perms;
DELETE FROM ibf_topic_mmod;
DELETE FROM ibf_validating;




Then run the queries in the file you downloaded. See http://dev.mysql.com/doc/mysql/en/batch-commands.html

I got through most of that until the very end with the link to batch-commands. I downloaded PuTTY and have been trying to use that, but I'm a bit lost. After successfully connecting with my...domain? I tried using the command:

Quote:mysql db_name < text_file

But that just returns the following error:
Quote:Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

I have a feeling that I'm in the wrong mode or I was supposed to do something else first which is why I'm having that problem. Help please? I'm kind of clueless.
Find all posts by this user
Quote this message in a reply
11-05-2010, 12:50 PM (This post was last modified: 11-05-2010 12:50 PM by seanthegeek.)
Post: #2
RE: Using PuTTY and mysql
You are getting that error because MySQL is hosted on a separate server from SSH and Apache. You need to use your MySQL hostname like this.

Quote:mysql -h mysql.example.com -u dbuser db_name < text_file

Where mysql.example.com is whatever MySQL domain you added with the DH panel, and dbuser is the name of the MySQL user who has access to the database.

If you need more help, feel free to ask.

Click here to learn why I chose DreamHost as my host, and why (maybe) you should too.
Visit this user's website Find all posts by this user
Quote this message in a reply
11-05-2010, 06:48 PM
Post: #3
RE: Using PuTTY and mysql
Thank you, that worked!
Find all posts by this user
Quote this message in a reply
11-07-2010, 05:40 PM
Post: #4
RE: Using PuTTY and mysql
So, I've been trying to import that above raw database into the tables, but I keep getting the following error:
Quote:ERROR 1136 (21S01) at line 14956: Column count doesn't match value count at row 1

This is line 14956:
Quote:INSERT IGNORE INTO ibf_members (id, name, mgroup, password, email, joined, ip_address, avatar, avatar_size, posts, aim_name, icq_number, location, signature, website, yahoo, title, allow_admin_mails, time_offset, interests, hide_email, email_pm, email_full, skin, warn_level, warn_lastwarn, language, msnname, restrict_post, view_sigs, view_img, view_avs, view_pop, bday_day, bday_month, bday_year, new_msg, msg_from_id, msg_msg_id, msg_total, vdirs, show_popup, last_visit, last_activity, dst_in_use, view_prefs, coppa_user, mod_posts, auto_track, org_supmod, org_perm_id, temp_ban) VALUES ('0','Guest','2','','','0','','','','0','','0','','','','','','0','0','','0','0'​,'0','0','0','0','0','','1','1','1','1','1','0','0','0000','0','0','0','0','','0​','1287189099','1287189099','0','-1&-1','0','0','0','0','','');

I've counted and recounted, but I'm pretty sure I have the same number of fields and values, so I'm not really sure what's up here. :/
Find all posts by this user
Quote this message in a reply
11-07-2010, 08:40 PM
Post: #5
RE: Using PuTTY and mysql
It was simple enough to convert your INSERT statement into a csv and verify the number of column names and inputs match.

Did a column get added/removed/renamed in the table? Maybe the columns in the table don't match the columns in the INSERT?
Find all posts by this user
Quote this message in a reply
11-07-2010, 10:35 PM
Post: #6
RE: Using PuTTY and mysql
Ah, that was the problem. Everything seems to be good now! Thank you!
Find all posts by this user
Quote this message in a reply
02-17-2017, 09:53 AM
Post: #7
RE: Using PuTTY and mysql (MARIADB)
I need to use Maria DB from PUTTY. I access this way:

mysql -u username -p databaseName


When I run a query, I get the following:

MariaDB [databaseName]> select * from tableName
->



When I try to send query results to file, I get this:
MariaDB [databaseName]> select * from tableName INTO OUTFILE order.txt
->


What do I need to change in the syntax to make it work, please? (THX)

Putty/mysql barfs at my syntax returning generic message for all saying that syntax not correct.
Find all posts by this user
Quote this message in a reply
02-17-2017, 11:06 AM
Post: #8
RE: Using PuTTY and mysql
I believe you're missing the ";" at the end of your SQL commands.
Find all posts by this user
Quote this message in a reply
02-17-2017, 10:17 PM
Post: #9
RE: Using PuTTY and mysql
Yes. I am missing the ";"

So, this command now produces a resultset,

MariaDB[databaseName]> select * from tableName;

However, I get the follow error message from the other command,

MariaDB[databaseName]> select * from tableName INTO OUTFILE 'order.txt';

Error 1045 (28000): Access denied for user 'userName'@'%' (using password: YES)
Find all posts by this user
Quote this message in a reply
03-12-2017, 09:02 PM
Post: #10
RE: Using PuTTY and mysql
My the same trouble is good now! Thank you!
Visit this user's website Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump: