database backup

[expired user #1386]'s profile image [expired user #1386] posted 8 years ago in General Permalink

I want to backup the latest state of my database. If I make a change (say I duplicate a line) and restart Heidi, the duplicate line shows up. However in the datadir C:\ProgramData\MySQL\MySQL Server 5.7\data there is no file (for example a *.MYD file) with a Latest Modification Time equal to the time of the change. If HeidiSQL is able to display the new state of the database, how and where does it find it? Note that in the UAC I have made ProgramData visible. Nowhere I can find the changed file.

kalvaro's profile image kalvaro posted 8 years ago Permalink

Unlike single user database engines as Microsoft Access or SQLite, MySQL has a client-server architecture. That means that the client interacts with the server rather than manipulating files manually. That involves calling certain functions in the underlying C API or running regular SQL queries.

If you enable logging in HeidiSQL preferences you'll be able to see actual SQL statements in the bottom pane.

[expired user #1386]'s profile image [expired user #1386] posted 8 years ago Permalink

Sorry, I don't see any SQL statements in the last session log. So my question amounts to "Where does the MySQL Server, not Heidi, stores and read the database". I just need the Windows PATH and filename(s) to backup the files.

ansgar's profile image ansgar posted 8 years ago Permalink

MyISAM based tables are stored indeed in .myd, .myi and .frm files in the database sub directory of your data directory. But InnoDB based tables (which are default since quite some MySQL releases now, spread their data into several other files, such as the "ibdata1" in the data directory, and additional sequencially named files, and additional .ibd files in the database sub directory.

[expired user #1386]'s profile image [expired user #1386] posted 8 years ago Permalink

Thank you for your answer. I found the ibdata1 file, but it is a binary file. If I copy this file somewhere and I loose my data, is copying back the saved file in the datadir, enough for Heidisql to recover the database? I found another solution: Tools > Export the database in SQL, in a ZIP file. After decompression, execute the SQL file. Is that OK?

ansgar's profile image ansgar posted 8 years ago Permalink

If I copy this file somewhere and I loose my data, is copying back the saved file in the datadir, enough for Heidisql to recover the database?

No. My post included more than this file name, and even that was just a wild guess. I am in no way sure where MySQL or MariaDB stores data. However, you should not backup random binary files in which you're guessing your important data. Instead, use HeidiSQL's SQL export. Such an .sql file can be loaded afterwards into HeidiSQL's query editor and run.

kalvaro's profile image kalvaro posted 8 years ago Permalink

Sorry, I don't see any SQL statements in the last session log.

Alright...

Please login to leave a reply, or register at first.