backupMySQLquerytable
I had a crash on my server.
The only thing I could do was hard copy the mysql folder
Now I am trying to access my database again through the PHPMyAdmin interface.
Some databases (mostly WordPress) run and run well
But some databases are only viewed and their content is not displayed
For example
I used REPAIR TABLE query but it doesn't help
This will depend a bit on your table type. With MyISAM tables, you can generally just copy the folder and files and everything will work. With other table types, it sometimes doesn't work as well.
The first thing you can try is to completely replace your MySQL data directory with the one from the backup. I would move it out of the way (mv /var/lib/mysql /var/lib/mysql-orig) so that you can easily restore it. Do this with the MySQL server not running and preferably on a testing server so you don't accidentally break something on your production machine.
Since you have the old hard drive data but can't get the drive to boot, it may be relatively painless to start MySQL from that drive (you can do this in a couple of ways; chroot to the old drive and try to start it there, copy the files to a server with a working MySQL instance, stop mysqld, edit the configuration file to point to the old mysql data directory, then restart MySQL, or finally just follow the steps above to completely replace the mysql data directory on the working instance with the one from the old server.
As D. Kasipovic points out, you should check the file permissions on the files you have restored, it could be as simple as a file permission problem.
You should make sure you're connecting as the root user or another user with administrative permissions; otherwise it's likely that a MySQL permissions will keep you from being able to see the databases. Depending what method ends up working for you, you may need to create any users that had permissions on that database.
InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.
Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)
You cannot recover just a database. You must restore the entire data folder c:/ProgramData/Mysql/Mysql Server 5.5/data. Please see my old post restore table from .frm and .ibd file? on reattaching .ibd files to the data dictionary.
If the database folder has no .ibd files but only .frm files, then all the data are contained within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.
You may have mangled the data dictionary. You have to do a complete restore of your datadir or mysqldump from an original database, shutdown mysqld, delete all of datadir, restart mysqld, and reload the mysqldump.
Best Answer
FINALLY Thanx Dexter!
Answer link after about 6hours searching the internet: https://stackoverflow.com/a/56641702/12873930
###Files require to restore
xampp(old folder)/mysql/data/###Step 1
###Step 2
mysql/datafolder and replace theibdata1,ib_logfile0, andib_logfile1databasefolder from your old xampp backup to the newly installed xampp folderc:/xampp/mysql/data/that contain.frmand.ibdfiles, If you are not sure try with one database.###Step 3
c:/xampp/mysql/binand look formy.cn.my.cnfile and look for#skip-innodband under that look for the line that saysinnodb_log_file_size=5Mchange it to170M.innodb_log_file_size=170M. This is your log file size and if you are not sure just set it to170###Step 4
Now open the file
mysql_start.bat(Windows Batch file) that is in thec:/xampp/directory.Add
–innodb_force_recovery=6after the... --console###Step 5
phpmyadminand check for your database and its tables. if you do not get any errors you are on the right track.