MySQL Shell Commands


#1

[BACKGROUND INFO]

My apologies for this lengthy post. I have been trying to find out the best way manage my databases outside of using phpMyAdmin. Several of you have been kind enough to help, but I am still confused. I hope I am not being a pain, but I just need a little more guidance and I’ll be on my way (and hopefully be in a position to help other noobs with similar questions).

I has been suggested that the best way to manage databases is through shell. That being the case, could someone please help me with the different commands?

[WHAT I HAVE LEARNED]

Through another thread, Ipstenu-DH and sXi explained how to log into my server via ssh and how to create a backup using this command:

mysqldump --opt --user=username --password=password --host=yourMySQLHostname dbname > output.sql

This I have been able to do. But then, on a different thread, when I inquired about importing/exporting my database, sierracircle suggested these commands:

export: mysql --user=cheese_dbadmin --password=yourpassword --host=mysql.cheeseball.com cheeseball_db < cheeseball.sql

import: mysql --user=cheese_dbadmin --password=yourpassword --host=mysql.cheeseball.com cheeseball_db > cheeseball.sql

[SO MY QUESTIONS ARE…]

  1. Is there a difference between backing up and exporting a database? (I noticed the < is facing in opposite directions.)

  2. If using the backup command above (mysqldump), can I replace the word ‘output’ with my database name, or does it have to be entered as output.sql?

  3. If I import my database using the import command above, will it overwrite my existing data or add to it?

  4. If the answer to #3 above is to add to it, could someone tell me the command for overwriting an existing database?

  5. Am I assuming correctly that to import a database, all I need to do is make sure that a copy of the database I am wanting to import has been uploaded to my root directory (home/username) and that there is a database by the same name at my MySQL server (mysql.mydomain.com)?

Thank you everyone for your time!


#2

The instructions you have been given by sierracircle are partially inaccurate. The “mysql” shell command cannot be used to export a database - to do that, you must use the “mysqldump” command.

No. The terms are synonymous.

Yes, you can.

The exact results may depend on the contents of the file you are importing, but, in most cases, it will overwrite existing data.

Correct.


#3

Thank you for your reply, Andrew.

Is there a better command for ensuring that all existing data is removed/deleted and new data is imported?

Is there a command for only overwriting tables that have changed?

Thank you for your time.


#4

… oops …


#5

cjohnson: If you really need to make sure the existing data is removed, the best way of doing that would be by either removing all the existing tables (either using the MySQL command line or something like phpMyAdmin), or by dropping and recreating the database (again, either using the command line or a graphical tool).

As far as “only overwriting tables that have changed” goes, that would depend on what your idea of “changed” is. It may help to keep in mind that a MySQL dump is literally just a list of MySQL commands that drop, recreate, and insert data into all the tables that are part of the backup, and importing the dump simply runs all of those commands.

sierracircle: Short of manually running a bunch of SHOW CREATE TABLE and SELECT statements, or using SELECT … INTO OUTFILE (neither of which will get you a normal-looking dump), there is no way to dump a database using the “mysql” command. Trying to use it as a substitute for “mysqldump”, like you did in your sample command, will just make it silently sit there and wait for you to enter something.


#6

Andrew, when I tried to overwrite my existing database, I got a Duplicate entry ‘1’ for key ‘PRIMARY’ error. Could you tell me the MySQL command for removing all the existing tables? Or perhaps I did something wrong when I exported my test database from my localhost using phpMyAdmin? Your help is very appreciated.


#7

When using phpMyAdmin to create a dumpfile, check the following option in the EXPORT window:


#8

Worked perfectly! Thanks yet again, sXi.