If you have access on the machine with MySQL and you need to export all tables in a database you can use:
hpatoio@namazu:~$ mysqldump -uroot -p DATABASE_NAME -T '/home/simone/export_csv' --fields-terminated-by "," --fields-enclosed-by '"' --lines-terminated-by "\n"
the script will create a files for each table of the DB and will place it in export_csv.
If you need to export a custom resultset or you need to export data from a remote MySQL server on your machine you can use this command :
hpatoio@namazu:~$ mysql -uroot -h HOSTNAME -p DATABASE_NAME -B -e "select field1,field2 ... fieldX from \`TABLE_NAME\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > my_export.csv
Amazing online tool to convert MDB Microsoft Access file to MySQL SQL scripts !
Check it out -> http://www.mdb2mysql.com/
Oh no ! It happend again !!
The sysadmin turned on the gpc_magic_quotes and you got the DB full of \'
Here a fast way to get back the correct values.
UPDATE table_name SET field_name = REPLACE(field_name,'\"',"")
For security reason, MySQL doesn't allow access from other computer.
If you try to connect with a MySQL administration client like MySQL Administrator you will get a Mysql Error Number 2003.
All this operations modify system settings, so they must be executed with root privileges
If you want connect from a computer in your LAN to your mysql server you have to :
root@namazu:~#sudo vi /etc/mysql/my.conf
and change the line
bind-address = 127.0.0.1
with
bind-address = 192.168.178.100
Now, to apply changes you have to reload the MySQL server
root@namazu:~#sudo /etc/init.d/mysql start
No MySQL listen on your external interface, to verify that the server is up and accept connection type
root@namazu:~#telnet 192.168.178.100 3306
and you should get:
Trying 192.168.178.100...
Connected to 192.168.178.100.
Escape character is '^]'.
@
5.0.75-0ubuntu10.2
Now MySQL is reachable on the "network" level, we have to enable access to databases.
Let's access MySQL with the mysql client, we still use localhost because we don't have privileges to access MySQL from others hosts yet.
root@namazu:~#mysql -uroot -pmyRootPass -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.22-Debian_0ubuntu6.06-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Modify access privileges to the server
mysql>GRANT ALL PRIVILEGES ON DB_NAME TO 'USERNAME'@'HOST' IDENTIFIED BY
'PASSWORD';
N.B.
So, for instance, with this command :
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'foopass';
we allow root (whose pass is foopass) to access all databases whichever host.
Exit the client
mysql>exit
Reload the privileges
root@namazu:~#mysqladmin reload
For more info on GRANT command: GRANT Syntax on MySQL Reference Manual
Have fun …