Disclaimer

This information HAS errors and is made available WITHOUT ANY WARRANTY OF ANY KIND and without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. It is not permissible to be read by anyone who has ever met a lawyer or attorney. Use is confined to Engineers with more than 370 course hours of engineering.
If you see an error contact:
+1(785) 841 3089
inform@xtronics.com

Mysql

mysql 5.0 on Debian

If you are using binary logs

There has been a change (march of 07?? - with etch?) you need to add to /etc/mysql/my.cnf

expire_logs_days = 15

Some time in days that would be more than your back up time would make sense.

Editing MySQL tables as CSV

Using gnumeric to edit Mysql tables

The mySQL Query Browser allows one to double click on a table to return the listing.

Next , select File/Export result set/as CSV.

The named file can be edited in gnumeric.

Saving as CSV causes only the displayed values to get exported - no formulas or functions are stored - if you need theses save as a spreadsheet file first..

Loading the updated file back to MySQL

Make a copy of the old table with a SQL query:

 create table table_name_new Like table_name;
 

Now delete the old data:

 Delete from `table_name_new`
 

Now load the new table with the new data

 LOAD DATA LOCAL INFILE '/path/newdata.csv'
 INTO TABLE table_name_new
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 (field1, filed2, field3);
 

The last line can be left off if the data is in order and the line before as well if it is on a Linux system.

Moving from a test server to live server

On the test server:

 mysqldump  -ppassword --opt --single-transaction db_name --tables table_name1 table_name2 > filename.sql
 

Move filename.sql over to the live server and -

 mysql -u root -ppassword db_name < filename.sql