LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices


Reply
  Search this Thread
Old 11-28-2003, 08:58 PM   #1
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 14.0.4 - Server Ubuntu 14.04.5 LTS
Posts: 952

Rep: Reputation: 32
mysql via shell


hi people.

is there any other way to run sql scripits via the command line, instead of using webmin, or any other browser program.

i have some pre built sql scripits i wish to inport in a database.


any ideas please.


tommytomato
 
Old 11-29-2003, 03:49 AM   #2
BlurredWeasel
Member
 
Registered: Oct 2003
Distribution: RH9
Posts: 38

Rep: Reputation: 15
Actually, there is, I was just playing with this today....

at command prompt, type 'mysql -uusername -p' the first option is a dash u, with your mysql username right after it (no space).

if that works, it'll prompt you for a password, type it in.

next you should see a prompt very similar to:

mysql>

At this prompt, type 'source thesqlfile.sql'

That will run the sql file. If the sql file doesn't have a use statement at the top of the file (use databasename) then you need to type that before sourcing it. Also, when you run this command, be in the directory where the sql file is residing at the moment (to avoid having to type a whole path to the sql file).

Hopefully that was clear enough, if it fails because of 'no database selected' then just issue a 'use foo;' command (use databasename).

Otherwise, have fun,
Chris
 
Old 11-29-2003, 04:51 AM   #3
alican333
LQ Newbie
 
Registered: Sep 2003
Location: istanbul
Distribution: SuSE 9.0
Posts: 6

Rep: Reputation: 0
to backup :
mysqldump --user=myUSER --password=myPASS --add-drop-table -h localhost TABELLENAME > backupFILE

to restore :
mysql --user=MyUSER --password=MyPASS TABELLENAME < backupFILE

see ya..
 
Old 11-29-2003, 05:10 AM   #4
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 14.0.4 - Server Ubuntu 14.04.5 LTS
Posts: 952

Original Poster
Rep: Reputation: 32
thank you,

I give it ago tonight and try it out.

I put most of my sql files to my users account

/home/user/downloads.

My System is a shell OS, trying to keep away from the GUI side of things.

thanks again.

tommytomato
 
Old 12-01-2003, 04:44 AM   #5
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 14.0.4 - Server Ubuntu 14.04.5 LTS
Posts: 952

Original Poster
Rep: Reputation: 32
i tried what you said to do.
mysql> /home/user/downloads/my.sql

i didn't seem to get any error's, but i'm un able to view the database to see if it added the sql.

mmm the file is a nuke.sql file.

mysql> show databases;

mysql> create database database_name;

then i did this
mysql> /home/user/downloads/nuke.sql

can you give me any more ideas please..

tommytomato
 
Old 12-01-2003, 06:47 AM   #6
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 14.0.4 - Server Ubuntu 14.04.5 LTS
Posts: 952

Original Poster
Rep: Reputation: 32
any one tell me how i can view the database details please.
if wish to see if the nuke.sql file was inported into the nuke database

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| nuke |
| test |
+----------+
3 rows in set (0.01 sec)


[root@www downloads]# source nuke.sql
-bash: --: command not found
: command not found
-bash: --: command not found
: command not found---------------------------------------------
-bash: --: command not found
: command not found
: command not found
-bash: --: command not found
: command not found
: command not found
-bash: nuke.sql: line 11: syntax error near unexpected token `('
'bash: nuke.sql: line 11: `CREATE TABLE confirm (
[root@www downloads]#


O yer i just tried this.

ERROR 1046: No Database Selected
ERROR 1046: No Database Selected
ERROR 1046: No Database Selected
ERROR 1046: No Database Selected
mysql> use nuke source nuke.sql
Database changed
mysql>
any one tell me what this means please

any ideas please or tips

thanks tommytomato


Last edited by tommytomato; 12-01-2003 at 06:50 AM.
 
Old 12-01-2003, 12:18 PM   #7
bleah-man
Member
 
Registered: Nov 2002
Location: London UK
Distribution: RedHat 9
Posts: 58

Rep: Reputation: 15
I think you are asking how to examine the database. So here is how I did it with outputs... Step by step... (mysql.com is a useful place to look too!)

-bash> mysql -u<username> -p<password>

mysql> show databases;
+----------------+
| Database |
+----------------+
| mysql |
| nuke |
| test |
+----------------+
3 rows in set (0.00 sec)

mysql> \unuke
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------------+
| Tables_in_nuke |
+----------------------------+
| confirm |
| nuke_authors |
| nuke_autonews |
| nuke_banner |
| nuke_bannerclient |
| nuke_bbauth_access |
| nuke_bbbanlist |
| nuke_bbcategories |
| nuke_bbconfig |
| nuke_bbdisallow |
| nuke_bbforum_prune |
| nuke_bbforums |
| nuke_bbgroups |
| nuke_bbposts |
| nuke_bbposts_text |
| nuke_bbprivmsgs |
| nuke_bbprivmsgs_text |
| nuke_bbranks |
| nuke_bbsearch_results |
| nuke_bbsearch_wordlist |
| nuke_bbsearch_wordmatch |
| nuke_bbsessions |
| nuke_bbsmilies |
| nuke_bbthemes |
| nuke_bbthemes_name |
| nuke_bbtopics |
| nuke_bbtopics_watch |
| nuke_bbuser_group |
| nuke_bbvote_desc |
| nuke_bbvote_results |
| nuke_bbvote_voters |
| nuke_bbwords |
| nuke_blocks |
| nuke_comments |
| nuke_config |
| nuke_contactbook |
| nuke_counter |
| nuke_downloads_categories |
| nuke_downloads_downloads |
| nuke_downloads_editorials |
| nuke_downloads_modrequest |
| nuke_downloads_newdownload |
| nuke_downloads_votedata |
| nuke_encyclopedia |
| nuke_encyclopedia_text |
| nuke_ephem |
| nuke_faqAnswer |
| nuke_faqCategories |
| nuke_headlines |
| nuke_journal |
| nuke_journal_comments |
| nuke_journal_stats |
| nuke_links_categories |
| nuke_links_editorials |
| nuke_links_links |
| nuke_links_modrequest |
| nuke_links_newlink |
| nuke_links_votedata |
| nuke_main |
| nuke_message |
| nuke_modules |
| nuke_pages |
| nuke_pages_categories |
| nuke_poll_check |
| nuke_poll_data |
| nuke_poll_desc |
| nuke_pollcomments |
| nuke_popsettings |
| nuke_priv_msgs |
| nuke_public_messages |
| nuke_queue |
| nuke_quotes |
| nuke_referer |
| nuke_related |
| nuke_reviews |
| nuke_reviews_add |
| nuke_reviews_comments |
| nuke_reviews_main |
| nuke_seccont |
| nuke_sections |
| nuke_session |
| nuke_stats_date |
| nuke_stats_hour |
| nuke_stats_month |
| nuke_stats_year |
| nuke_stories |
| nuke_stories_cat |
| nuke_topics |
| nuke_users |
| nuke_users_temp |
+----------------------------+
90 rows in set (0.00 sec)

mysql> show columns from nuke_config;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| sitename | varchar(255) | | | | |
| nukeurl | varchar(255) | | | | |
| site_logo | varchar(255) | | | | |
| slogan | varchar(255) | | | | |
| startdate | varchar(50) | | | | |
| adminmail | varchar(255) | | | | |
| anonpost | tinyint(1) | | | 0 | |
| Default_Theme | varchar(255) | | | | |
| foot1 | text | | | | |
| foot2 | text | | | | |
| foot3 | text | | | | |
| commentlimit | int(9) | | | 4096 | |
| anonymous | varchar(255) | | | | |
| minpass | tinyint(1) | | | 5 | |
| pollcomm | tinyint(1) | | | 1 | |
| articlecomm | tinyint(1) | | | 1 | |
| broadcast_msg | tinyint(1) | | | 1 | |
| my_headlines | tinyint(1) | | | 1 | |
| top | int(3) | | | 10 | |
| storyhome | int(2) | | | 10 | |
| user_news | tinyint(1) | | | 1 | |
| oldnum | int(2) | | | 30 | |
| ultramode | tinyint(1) | | | 0 | |
| banners | tinyint(1) | | | 1 | |
| backend_title | varchar(255) | | | | |
| backend_language | varchar(10) | | | | |
| language | varchar(100) | | | | |
| locale | varchar(10) | | | | |
| multilingual | tinyint(1) | | | 0 | |
| useflags | tinyint(1) | | | 0 | |
| notify | tinyint(1) | | | 0 | |
| notify_email | varchar(255) | | | | |
| notify_subject | varchar(255) | | | | |
| notify_message | varchar(255) | | | | |
| notify_from | varchar(255) | | | | |
| footermsgtxt | text | | | | |
| email_send | tinyint(1) | | | 1 | |
| attachmentdir | varchar(255) | | | | |
| attachments | tinyint(1) | | | 0 | |
| attachments_view | tinyint(1) | | | 0 | |
| download_dir | varchar(255) | | | | |
| defaultpopserver | varchar(255) | | | | |
| singleaccount | tinyint(1) | | | 0 | |
| singleaccountname | varchar(255) | | | | |
| numaccounts | tinyint(2) | | | -1 | |
| imgpath | varchar(255) | | | | |
| filter_forward | tinyint(1) | | | 1 | |
| moderate | tinyint(1) | | | 0 | |
| admingraphic | tinyint(1) | | | 1 | |
| httpref | tinyint(1) | | | 1 | |
| httprefmax | int(5) | | | 1000 | |
| CensorMode | tinyint(1) | | | 3 | |
| CensorReplace | varchar(10) | | | | |
| copyright | text | | | | |
| Version_Num | varchar(10) | | | | |
+-------------------+--------------+------+-----+---------+-------+
55 rows in set (0.02 sec)
 
Old 12-01-2003, 06:55 PM   #8
tommytomato
Member
 
Registered: Nov 2003
Location: Narrogin Western Australia
Distribution: GUI Ubuntu 14.0.4 - Server Ubuntu 14.04.5 LTS
Posts: 952

Original Poster
Rep: Reputation: 32
yer thats it i did get it in the end , but i was to sleepy to post, but now its another day..

does this look ok ?

Changing root password for mysql <-------

mysqladmin -u root password 'new_password'
mysql -u root password 'new_password'
mysql> SET PASSWORD FOR root@localhost=PASSWORD 'new_password'

Showing databases <----------

[root@www downloads]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases; <------command
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

mysql>

Creating a database <--------

[root@www downloads]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database nuke; <----command
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye

test to see if database nuke is there <-----------

[root@www downloads]# mysql nuke -u root -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases; <---command
+----------+
| Database |
+----------+
| mysql |
| nuke |
| test |
+----------+
3 rows in set (0.00 sec)

mysql>

[root@www downloads]#

importing the sql file, must be in the directory were the sql file is.

[root@www downloads]# mysql -u root -p nuke </home/tommytomato/downloads/nuke.sql
Enter password:
[root@www downloads]#

show tables <---------# mysql your_database -u root -p


mysql> \q
Bye
[root@www downloads]# mysql nuke -u root -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables; <-----command
+----------------------------+
| Tables_in_nuke |
+----------------------------+
| confirm |
| nuke_authors |
| nuke_autonews |
| nuke_banner |
| nuke_bannerclient |
| nuke_bbauth_access |
| nuke_bbbanlist |
| nuke_bbcategories |
| nuke_bbconfig |
| nuke_bbdisallow |
| nuke_bbforum_prune |
| nuke_bbforums |
| nuke_bbgroups |
| nuke_bbposts |
| nuke_bbposts_text |
| nuke_bbprivmsgs |
| nuke_bbprivmsgs_text |
| nuke_bbranks |
| nuke_bbsearch_results |
| nuke_bbsearch_wordlist |
| nuke_bbsearch_wordmatch |
| nuke_bbsessions |
| nuke_bbsmilies |
| nuke_bbthemes |
| nuke_bbthemes_name |
| nuke_bbtopics |
| nuke_bbtopics_watch |
| nuke_bbuser_group |
| nuke_bbvote_desc |
| nuke_bbvote_results |
| nuke_bbvote_voters |
| nuke_bbwords |
| nuke_blocks |
| nuke_comments |
| nuke_config |
| nuke_contactbook |
| nuke_counter |
| nuke_downloads_categories |
| nuke_downloads_downloads |
| nuke_downloads_editorials |
| nuke_downloads_modrequest |
| nuke_downloads_newdownload |
| nuke_downloads_votedata |
| nuke_encyclopedia |
| nuke_encyclopedia_text |
| nuke_ephem |
| nuke_faqAnswer |
| nuke_faqCategories |
| nuke_headlines |
| nuke_journal |
| nuke_journal_comments |
| nuke_journal_stats |
| nuke_links_categories |
| nuke_links_editorials |
| nuke_links_links |
| nuke_links_modrequest |
| nuke_links_newlink |
| nuke_links_votedata |
| nuke_main |
| nuke_message |
| nuke_modules |
| nuke_pages |
| nuke_pages_categories |
| nuke_poll_check |
| nuke_poll_data |
| nuke_poll_desc |
| nuke_pollcomments |
| nuke_popsettings |
| nuke_priv_msgs |
| nuke_public_messages |
| nuke_queue |
| nuke_quotes |
| nuke_referer |
| nuke_related |
| nuke_reviews |
| nuke_reviews_add |
| nuke_reviews_comments |
| nuke_reviews_main |
| nuke_seccont |
| nuke_sections |
| nuke_session |
| nuke_stats_date |
| nuke_stats_hour |
| nuke_stats_month |
| nuke_stats_year |
| nuke_stories |
| nuke_stories_cat |
| nuke_topics |
| nuke_users |
| nuke_users_temp |
+----------------------------+
90 rows in set (0.00 sec)

mysql>


I'm making my own home doc's, does it look ok ?

tommytomato
 
Old 12-01-2003, 09:05 PM   #9
BlurredWeasel
Member
 
Registered: Oct 2003
Distribution: RH9
Posts: 38

Rep: Reputation: 15
Quote:
mysql> SET PASSWORD FOR root@localhost=PASSWORD 'new_password'
Should be

Quote:
mysql> SET PASSWORD FOR root@localhost=PASSWORD ('new_password');
(not sure it actually matters, but that is the official syntax off mysql.com (so to be compliant )

Quote:
importing the sql file, must be in the directory were the sql file is.

[root@www downloads]# mysql -u root -p nuke </home/tommytomato/downloads/nuke.sql
Enter password:
Should be (or can be easily)

Quote:
mysql -u root -p
mysql> use nuke;
mysql> source nuke.sql;
do all that from the directory with the .sql file.

-Chris
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
PHP Shell Script (with MySql) Tony Empire Programming 1 09-20-2005 10:59 AM
MySQL in a shell script?! zaubara Programming 2 05-31-2004 06:27 PM
How get a mysql shell response in a script. philipina Programming 3 03-16-2004 12:51 PM
mysql via shell help needed tommytomato Linux - Newbie 3 12-01-2003 01:03 PM
Installing MySQL via shell Luc Linux - Newbie 21 11-02-2002 11:52 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 10:08 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration