MySQL stuff
This page is mostly for my benefit, so I can remember some of this stuff. But it may also help someone else if they search on google or somewhere else for, what you would consider to be simple operations, but the MySQL manual makes look incredibly complicated!
How to import a CSV file into MySQL via the command line
mysqlimport –fields-optionally-enclosed-by=”\”" –fields-terminated-by=”,” –lines-terminated-by=”\r\n” –local -u USERNAME -p DATABASE_NAME FILE_TO_IMPORT_NAME
Most of that should be self explanatory, but here are some caveats:
- The “–local” needs to be used if the file you are importing is on the same system as the database, I usually run this command form the directory the file is in, otherwise, mysql will try and look for the file like “./DATABASE_NAME/FILE_TO_IMPORT_NAME”.
- FILE_TO_IMPORT_NAME should be named like so: table_name.csv. So, if your table is called “monkey_banana_cheese” (for whatever reason, freak) then your file to import should be called “monkey_banana_cheese.csv”. Or .txt or .poo, whatever, mysql doesn’t care, it strips the extension to look for the table name.
- Ideally, the data should be in the same order in the importfile as the columns are in the table, so, if your import file is name,email,age,shoesize; then that’s the order your columns should be in. There might be another way to do it, but this strikes me as the easiest, no?
How to dump a database
This is easy:
mysqldump —opt -Q DATABASENAME -u USERNAME -p > DUMP_FILE_NAME.sql
- –opt will do extended inserts instead of full inserts.
- -Q will add backticks `blah` around things, this is incase you’ve stupidly used reserved words as table/column names
- You know the rest.
How to import an SQL file into MySQL (like the one you just made)
Easy:
mysql -u USERNAME -p < DUMP_FILE_NAME.sql
If you have a need to dump a database from one server and load it onto another, then you could dump it, ftp it down, ftp it up and reload it, or, you could do this:
mysqldump —opt -Q DATABASENAME -u USERNAME -p > DUMP_FILE_NAME.sql | mysql -u USERNAME -p -C -hREMOTEHOSTNAME REMOTEHOSTDATABASENAME
This may not work, if not, you can try this:
mysqldump –opt -Q LOCAL_DATABASENAME -u LOCAL_USERNAME -pLOCAL_PASSWORD | mysql -u REMOTE_USERNAME -pREMOTE_PASSWORD -C -hREMOTE_ADDRESS REMOTE_DATABASE
You should know what most of this means, except these:
- | - this is called a pipe, you “pipe” one thing into another.
- -C on some versions of MySQL, this will compress the data as it sends, if your MySQL doesnt’ support this, it will ignore it (I think)
The only caveats are that you need to have port 3306 (this is the default MySQL port) open (or whichever port you have MySQL on) and that the database user isn’t locked to “localhost” as a connection method.
