Mysql – Remotely shutdown a MySQL server

You are using a very old, end-of-life, and unsupported version of MySQL.

There is not exactly a correct answer to your question, because it is impossible in many cases to safely back up or clone a MySQL server that is still running.

The only absolutely safe way to copy data using this method is by stopping the mysqld process, gracefully, and copying all of the files, for all of the databases.

If you are using MyISAM, which, again, you almost certainly should not, it is possible to copy a single database by copying all of the files in that database's named directory, but the tables have to be locked and flushed, or the backup will either not contain the most recent changes, or will be corrupt.

If you are using InnoDB, it is almost impossible to copy only one database from one server by copying files, unless you use the "transportable tablespaces" feature introduced in MySQL 5.6.

The correct answer to the question is "don't do it this way."

The correct solution is to use mysqldump to make the backup, which does not copy files at all. Instead, it creates a file containing the SQL statements necessary to recreate your tables and reinsert the data that was in those tables when the backup was made.

The output of mysqldump is also human-readable, so you can actually examine what's in the backup.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

The cause for the problem was most likely an HDD error, while MySQL was writing in table "xyz". Finally I was able to recover the system. Here is what I did:

1. Data Backup including MySQL datadir (table "xyz" and my "ibdata" could be read/copied, so no complete backup possible) Ran chkdsk —> found but didn’t repair bad sectors
2. ran HDD Regenerator v2011 —> found and repaired bad sectors
3. Completed Backup
4. Tested if MySQL would run after regeneration —> didn’t work
5. added innodb_force_recovery=1 … 6 to [mysqld] section in my config file (MySQL started only with max value "6")
6. used mysqlcheck db_name -u root -p to check which table(s) are corrupted
7. used mysqluc > mysqlfrm to obtain the table structure of the corrupted table (worked only with --diagnostic option)
8. used myisamchk -r -q table_name to fix the broken table (this table used the myisam engine)
9. see this: http://dev.mysql.com/doc/refman/5.7/en/myisam-repair.html
10. I had to use this trick to get it working: FOR %G IN (dir \b c:\mysql\data\mydb\*.myi) DO myisamchk -r -f %G (https://iandunn.name/myisamchk-error-22-on-windows/)
11. Originally the table had 55721190 rows, after myisamchk repair it had 55721166 rows (no problem for me, because i could identify and restore the lost rows)
12. Backup
13. mysqldump -u root -p my_schema > .../recovery_dump.sql
14. mysql drop all_databases, stopped server, moved ibdata1 and iblogs to tmp folder, deleted innodb_force_recovery=6 in config file, started server
15. mysql -u root -p my_schema < .../recovery_dump.sql

This "InnoDB Corruption Repair Guide" was a great help. (https://forums.cpanel.net/threads/innodb-corruption-repair-guide.418722/)