To save a table into a CSV

 To save a table into a CSV (spreadsheet) file:


select * from TableName INTO OUTFILE '/var/lib/mysql-files/spreadsheet.csv' FIELDS ENCLOSED BY '"' TERMINATED by ';' ESCAPED BY '"' LINES TERMINATED BY '\n'


This will drop the file into the folder /var/lib/mysql-files/


Comments

Post a Comment

Popular posts from this blog

dump a specific table

Sometimes you just want a specific mysql table. mysqldump -u [username] -p --no-create-info source_database_name source_table_name > saved_database_content.sql You can then load that content into a new database: mysql -u [username] -p target_database_name < saved_database_content.sql
Read more

Tablespaces error

If you are trying to dump a database in a mysql instance running inside a docker container and you get the following error, despite logging in as a user who has read permission on it, it means that the user you are using does not have the required  PROCESS  privilege to perform certain tasks during the database dump. This is common when attempting to dump information such as  tablespaces , which may require elevated permissions. mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces fix with: mysqldump -u <username> -p --no-tablespaces <database_name> > dump.sql The --no-tablespaces option in mysqldump prevents the tool from including tablespace information in the dump. Details: Tablespace Information : MySQL uses tablespaces to store table data on disk, especially when dealing with InnoDB tables. When dumping a database, mysqldump by default tries to include metadat...
Read more

mysql says you can't login as root but you have definitely got the right password

If mysql says you can't login as root but you have definitely got the right password and you have just installed it for the first time, it can be a nuisance. Do the below as user root. My suggestion is: 1. If you have existing databases, and want to keep them, back them up with mysqldump.       mysqldump -u user_with_access -pPassword database_to_rescue > database_to_rescue.sql Assuming that you have at least one user account, e.g. "user_with_access", who can read the database called “database_to_rescue". 2. Erase the mysql directory and start over:       service mysql stop rm -rf /var/lib/mysql/* 3. Re-run mysqld initialisation to set the password to blank: mkdir -p /var/run/mysqld chown -R /var/run/mysqld chmod 777 /var/run/mysqld # you can change this back to 755 later mysqld --initialize-insecure --user=mysql If it says it can't find the socket in /var/run/mysqld, repeat the commands mkdir-chown above. 4. It will then set up mysql from scratch w...
Read more