MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Data Back Up / Question No: 21

How can I restore MySQL database?

We're in the process of changing our InnoDB databases to file-per-table. So, I went ahead and backed up all databases, deleted that data and log files, re-created the MySQL database from the script, then restored all the user databases. Everything is fine, except of course I'm missing all the users. So my questions are:

  1. I had to create the new mysql database in order to get the server to start without error ([ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist). After creating a new mysql db from the script, should I have first restored the old mysql database, then the user databases?
  2. Somewhere I saw that I still needed to create the InnoDB shared tablespace. What does InnoDB use this for? For the production system, I need to estimate what size to start with.
  3. I restored the original mysql database as mysql_old, so I have access to the old grant tables. Any suggestions for how to recover the user permissions without redoing all the steps?

Answer No: 21

The simplest solution is to stop the database, remove the contents of datadir/mysql directory and move there all the files from datadir/mysql_old (or copy preserving owner/group/permissions). Then start the server again.

Related MySQL FAQs to the Above FAQ

How-to-create-duplicate-table-in-MySQL How to create duplicate table in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.