File Backups of Innodb databses/tables doesn’t work like MyISAM where you can just rsync or cp the files in to place and expect it to work. It relies on the ib_log and ibdatafiles to keep its data in sync. To restore from theses types of files, we can use a second MYSQL instance to avoid having to shutdown the running instance, we can achieve that trhu the use of different flags to specify the socket, PID, skip networking etc… If this does not convince you why Linux modularity is ages ahead when compared to Windows, nothing will.

Note: This process can damage your system and should be performed with caution. 

Second mysqld instance

Start a screen if not using one already:

screen 

Restore the data to $dir where $user is your  username

 dir=/home/temp/$user

Copy `ibdata* ib_logfile*, mysql dir, and db folder from backup server

 [backupServer>] rsync -avHP mysql ibdata1 ib_logfile* my_db IP:/home/temp/$user/ 

You will need to have networking open the firewall from the backup server depending if it is on a local o remote network.

 Chown 

chown mysql. $dir -R 

You may need additional perms on /home/temp so the process can access the directory: 

chmod 751 /home/temp 

Start mysql using an alternate socket, pid, and log. Use –skip-networking to not worry about conflicting ports. & to send it to the background. 

mysqld \ –datadir=$dir \ –socket=$dir/socket.mysql \ –pid-file=$dir/mysql.pid \ –log-error=$dir/mysql.err \ –skip-grant-tables \ –skip-networking \ –user=mysql & 

You’ll get the following if you were successful: 

2016-10-12 10:44:39 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use — explicit_defaults_for_timestamp server option (see documentation for more details). 2016-10-12 10:44:39 0 [Note] mysqld (mysqld 5.6.33) starting as process 6691 … 

still running into issues? if you need force recovery, you can pass the –innodb-force-recovery=# flag to use it on the secondary instance. On plesk server you may need to check for location of mysqld binary using ps command and change the command above accordingly to launch 2nd mysql instance. As an example, on a plesk 17.5 with mariadb 5.5 installed. My command was:

 /usr/libexec/mysqld \ –datadir=$dir \ –socket=$dir/socket.mysql \ –pid-file=$dir/mysql.pid \ –log-error=$dir/mysql.err \ –skip-grant-tables \ –skip-networking \ –user=mysql & 

Connect to the newly created mysql instance, confirm your database is there.

 mysql -S $dir/socket.mysql -e ‘show databases;’ 

Create dump 

mysqldump -S $dir/socket.mysql $database > $database.sql 

or pipe into live db: 

mysqldump -S $dir/socket.mysql $database | mysql $database

 To shutdown the instance: 

mysqladmin -S $dir/socket.mysql shutdown