If your sole objective is to get the Salesforce data out into a CSV then there are several options outside of apex. I see in your question you tagged the question as apex, so these options may not suit your requirements.
- You could use the Data Loader to export the data. As @eyescream points out in the comments, this can be further automated if required - Using Data Loader from the command line
- You could create a Data Export (Setup | Data Management | Data Export). This can be scheduled to run on an interval (weekly or monthly) and creates zip files that you can download with the required CSV data.
- You could use the Soap API to run an Apex query and then save the results to CSV yourself. There are existing tools that can do this for you if required.
If you need a purely native apex solution you could try and implement some form of server side pagination combined with the @ReadOnly annotation (see Working with Large Sets of Data).
For pagination, you can use a combination of Limit and Offset. That said, Offset still appears to be in developer preview (according to the documentation) and there are notes about efficiency with offsets into a large result sets. The maximum offset of 2000 rows will limit the usefulness of this approach.
Using a Standard Set Controller as covered in How to query more than 50000 records in apex and bind it in vf page in jqgrid and paging? seems like a good option for paging through large amounts of data, but I'm not sure how you would utilise it to create a CSV.
If neither of those options work for pagination you will most likely need to devise your own method to create pages based on the data being exported.
Beyond that I don't think it will be possible to create a CSV of arbitrary size. One way or another the limits will stop you.