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:newbie:

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

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..

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 :newbie:

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:study:

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
:study:

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)

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 :study:

Should be

(not sure it actually matters, but that is the official syntax off mysql.com (so to be compliant :) )

Should be (or can be easily)

do all that from the directory with the .sql file.

-Chris