如何从ibdata1文件恢复test1库中丢失的catalog_product_index_price表

Hey there, let's work through this table recovery step by step. Since you only need to get back the catalog_product_index_price table from the test1 database using your extracted ibdata1 backup, here's a safe, isolated approach that won't risk your live site:

Step 1: Set up a temporary MySQL environment #

We'll use a separate MySQL instance to work with the backup ibdata1 so we don't interfere with your running database.

• If you're working on the same server, temporarily stop your live MySQL service (skip this if using a separate machine):

  sudo systemctl stop mysql  # For systemd systems (Ubuntu 16.04+, CentOS 7+)
  # OR
  sudo service mysql stop    # For older sysvinit systems
  

• Create a temporary directory to hold the temp MySQL data:

  mkdir -p /tmp/mysql_temp/data
  

• Copy your extracted ibdata1 file into this temp data folder:

  cp /path/to/your/extracted/ibdata1 /tmp/mysql_temp/data/
  

Step 2: Create a temporary MySQL config file #

Make a custom my.cnf to point the temp instance to our isolated data directory:

vi /tmp/mysql_temp/my.cnf

Paste this content into the file:

[mysqld]
datadir=/tmp/mysql_temp/data
socket=/tmp/mysql_temp/mysql.sock
innodb_data_file_path=ibdata1:10M:autoextend
skip-grant-tables  # Bypass login for temporary, isolated access

Step 3: Start the temporary MySQL instance #

Launch the temp server using our custom config:

mysqld --defaults-file=/tmp/mysql_temp/my.cnf &

Step 4: Export the missing table #

Now we'll connect to the temp instance and extract the table data:

• Connect to the temp MySQL shell:

  mysql -S /tmp/mysql_temp/mysql.sock
  

• Switch to the test1 database and confirm the table exists:

  USE test1;
  SHOW TABLES LIKE 'catalog_product_index_price';
  

  If the table appears here, we're ready to export it.

• Exit the shell (exit;), then use mysqldump to save the table to an SQL file:

  mysqldump -S /tmp/mysql_temp/mysql.sock test1 catalog_product_index_price > /tmp/recovered_catalog_price.sql
  

Step 5: Clean up the temporary environment #

• Stop the temp MySQL instance:

  mysqladmin -S /tmp/mysql_temp/mysql.sock shutdown
  

• Restart your live MySQL service (if you stopped it earlier):

  sudo systemctl start mysql
  # OR
  sudo service mysql start
  

• (Optional) Delete the temp directory to free up space:

  rm -rf /tmp/mysql_temp
  

Step 6: Import the recovered table to your live database #

Finally, import the exported SQL file into your live test1 database:

mysql -u your_db_username -p test1 < /tmp/recovered_catalog_price.sql

Replace your_db_username with your actual MySQL username, and enter your password when prompted.

Key Notes #

• Ensure the MySQL version used for the temporary instance matches your live server's version. Mismatched versions can cause compatibility issues with the ibdata1 file.
• If the table doesn't show up in the temp instance, you'll first need to recreate its schema (use a schema backup or generate it from a working copy of the table) before the data from ibdata1 becomes accessible.

内容的提问来源于stack exchange，提问作者Hector