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
 

Top Page wiki Index

Disclaimer

This information may have errors; It is not permissible to be read by anyone who has ever met a lawyer.
Use is confined to Engineers with more than 370 course hours of electronic engineering for theoretical studies.
ph +1(785) 841-3089

Email inform@xtronics.com

(C) Copyright 1994-2017, Transtronics, Inc. All rights reserved
TranstronicsĀ® is a registered trademark of Transtronics, Inc.
All trademarks are the property of their respective owners.