From time to time I need to perform some MySQL tasks that are only possible from the command line. Considering that I don’t have to use them everyday, I tend to forget the exact syntax for them. So then I am forced to take the time to search through google to find examples for the exact thing that I want to do. I decided to create this post as a place where I can add them as they come up and use for easy reference. I’m also sure that this may be useful for some other people, so here they go!
Copy (dump) Local Database to Remote Server
Usually you can get away with exporting an entire database from your development server to an sql file through PHPMyAdmin or a similar tool. But if you have some sort of large table that needs to be dumped (zipcode or geo-ip tables) you will find that it’s a painfully slow and frustrating experience (i.e. making sure that you have the correct file upload limits in php.ini, your browser sitting there importing/exporting for an hour or more).
This is clearly not the best way to do things, so that is where the mysqldump command comes in handy. The secret is that you can specify it to automatically dump from you local server to an external server. Here’s the syntax:
mysqldump --opt --compress --user=LOCAL_USERNAME --password=LOCAL_PASSWORD
--host=127.0.0.1 LOCAL_DB_NAME | mysql --user=REMOTE_USERNAME
--password=REMOTE_PASSWORD --host=REMOTE_HOST -D LOCAL_DB_NAME -C
REMOTE_DB_NAME
Before running the command you need to make sure that you create the empty destination database. Also make sure that the user on the destination has the proper permissions to create tables, etc.
Importing a MySQL Dump FIle
Here is the command to run if your already have a dump file that you need to import into MySQL:
mysql –user=USERNAME –password=PASSWORD DATABASE_NAME < DUMP_FILE.sql
Adding New Users
Sometimes I end up having to use the following command when doing an initial install of mysql. This creates a user with privileges on all databases, but only from ‘localhost’. ‘localhost’ could be changed to ‘*.*’ to give the user access from anywhere.
GRANT ALL PRIVILEGES ON *.* TO 'NEW_USERNAME'@'localhost' IDENTIFIED BY
'NEW_PASSWORD' WITH GRANT OPTION;
Once the user is created, then you can go ahead connect to MySQL through PHPMyAdmin, etc to easily start creating more users, databases, and so on.
More to Come….
For this command:
mysql –user=USERNAME –password=PASSWORD DATABASE_NAME < DUMP_FILE.sql
Won’t this entire line, including the password, get saved to your ~./bashrc file, enabling anyone with read permissions on your ~/.bashrc to see your mysql password for that database?
When I use commands like this, I usually just do “-p” with no password, then I’m prompted for a password after I press enter.
Woops, meant ~/.bash_history, not ~/.bashrc.