0

I have a giant mysql including several databases on a server; I am connected by ubuntu to the server.

I login to the server by

mysql -u root -p

It shows all the databases that I have . then I take the one that i need

use mydatabase; 

Then I see what tables I have

show tables;

Now I want to extract one of the tables on my desktop , how can I do this ? Note that the server is connected through network

I also have a specific table and i want to add user to it I get the table like this

select * from user_mine

it shows me somethinig like this

---------------------------
username  |  user_ID
---------------------------
user1     |ADMIN
user2     |ADMIN
--------------------------

I want to add user3| ADMIN

how can i do it?

1

Now I want to extract one of the tables on my desktop , how can I do this ? Note that the server is connected through network

From Ubuntu command line (not SQL command prompt):

mysqldump -u {user} -p {database} {table} > export.sql

This will create a file "export.sql" with the table definition and table content in it in SQL format. The '--no-create-info' will not export the table information, only data. You can also use it like this:

mysql -B -u {user} -p {database} -e "SELECT * FROM {table};"  | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > export.csv

to create a CSV file.

And then use ssh to copy export.sql or export.csv to your desktop.

I want to add user3| ADMIN

insert ignore into user_mine (username, user_id) values ('user3','ADMIN');

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.