LampJunkie.com - Everything related to Linux, PHP, MySQL, Apache, AJAX, Symfony and more

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