[opensuse] Restore MySQL lost ? pmadb and pma user ??
Hi all, I just restored a MySQL database with phpMySQL. There are more databases sharing the MySQLServer, each with it's own prefix in the one Database. I made phpMySQL export of all databases to backup.sql. Oddly, my most important database and pmadb was not restored from the backup.sql file, but 4 other almost empty databases did restore. I then started checking the phpMySQL reports about the failing entries in the config.inc.php, and now only have to do the pma setups. I also created the pma tables with the script /scripts/create_tables.sql. I then imported the update script '/scripts/upgrade_tables_mysql_4_1_2+.sgl', then the settings for pma was recognised from the config.inc.php file. I saw with the import of the backup.sql that the max file size was set to 2.048 KiB. I set the option: $cfg['MemoryLimit'] string [number of bytes] as "$cfg['MemoryLimit'] = '16M';" The notes from the Documentation: Set the number of bytes a script is allowed to allocate. If set to zero, no limit is imposed. This setting is used while importing/exporting dump files and at some other places in phpMyAdmin so you definitely don't want to put here a too low value. It has no effect when PHP is running in safe mode. You can also use any string as in php.ini, eg. '16M'. Ensure you don't omit the suffix (16 means 16 bytes!) I rested "rcmysql reload" and "rcapache2 restart". The max fie size in phpMySQL remains at 2,048KiB in the GUI and also aborts the import. I can read the backup.sql and see the "most important database" named and all the tables in the file. What am I missing? .oOOo. .Dreiel. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Hi all,
I just restored a MySQL database with phpMySQL. There are more databases sharing the MySQLServer, each with it's own prefix in the one Database. I made phpMySQL export of all databases to backup.sql.
Oddly, my most important database and pmadb was not restored from the backup.sql file, but 4 other almost empty databases did restore.
I then started checking the phpMySQL reports about the failing entries in the config.inc.php, and now only have to do the pma setups. I also created the pma tables with the script /scripts/create_tables.sql. I then imported the update script '/scripts/upgrade_tables_mysql_4_1_2+.sgl', then the settings for pma was recognised from the config.inc.php file.
I saw with the import of the backup.sql that the max file size was set to 2.048 KiB.
I set the option:
$cfg['MemoryLimit'] string [number of bytes] as "$cfg['MemoryLimit'] = '16M';"
The notes from the Documentation: Set the number of bytes a script is allowed to allocate. If set to zero, no limit is imposed. This setting is used while importing/exporting dump files and at some other places in phpMyAdmin so you definitely don't want to put here a too low value. It has no effect when PHP is running in safe mode. You can also use any string as in php.ini, eg. '16M'. Ensure you don't omit the suffix (16 means 16 bytes!)
I rested "rcmysql reload" and "rcapache2 restart". The max fie size in phpMySQL remains at 2,048KiB in the GUI and also aborts the import.
I can read the backup.sql and see the "most important database" named and all the tables in the file. What am I missing?
.oOOo. .Dreiel.
OK, I got all fixed with the php.ini to allow large imports. Try these different settings in php.ini (found the right one with "php -i | grep php.ini")# Here it was: php -i | grep php.ini Configuration File (php.ini) Path => /etc/php5/cli Loaded Configuration File => /etc/php5/cli/php.ini Now replacing the options: I found: post_max_size = 8M upload_max_filesize = 2M max_execution_time = 30 max_input_time = 60 memory_limit = 8M And changed them to: post_max_size = 750M upload_max_filesize = 750M max_execution_time = 5000 max_input_time = 5000 memory_limit = 128M (some say 1000M, for big databases?) ++++++++ Now the question is how to recover the backup. -------------- Are the databases "information_schema" and "mysql" not installed with the new installation by default? How is it taken from the old backup.sql? I had to drop the databases that were not restored properly. Now only the "information_schema" and "mysql" still exist. (Cannot drop them) Now I try to import with phpMyAdmin the backup.sql When I restore, it fails with: _______________________________________ Error *SQL query:* -- -- Database: `information_schema` -- CREATE DATABASE http://landoag.com/phpMyAdmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fcreate-database.html&token=dc0c82259df8e026b7ce2ae14ad874e0 `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; *MySQL said: *Documentation http://landoag.com/phpMyAdmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Ferror-messages-server.html&token=dc0c82259df8e026b7ce2ae14ad874e0 |#1044 - Access denied for user 'pma'@'localhost' to database 'information_schema' | ______________________________________ Only some of the databases were restored, but not the 'most important' one. How do I go about cleaning all and restore all the databases with the import: backup.sql with phpMyAdmin? Any ideas welcome. .oOOo. .Dreiel. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
LLLActive@GMX.Net wrote:
And changed them to: post_max_size = 750M upload_max_filesize = 750M max_execution_time = 5000 max_input_time = 5000 memory_limit = 128M (some say 1000M, for big databases?)
Its a per-process PHP limit - the appropriate setting depends on your machine and your applications.
++++++++
Now the question is how to recover the backup.
--------------
Are the databases "information_schema" and "mysql" not installed with the new installation by default?
They are both system databases, but afaik, information_schema is virtual.
How is it taken from the old backup.sql?
Like the contents of any other database. I wouldn't expect information_schema to be in your backup, though.
I had to drop the databases that were not restored properly. Now only the "information_schema" and "mysql" still exist. (Cannot drop them)
If you _really_ want to get rid of them, you can just stop mysql and erase them, but there is no need.
Now I try to import with phpMyAdmin the backup.sql
When I restore, it fails with: _______________________________________ Error *SQL query:* -- -- Database: `information_schema` -- CREATE DATABASE
[snip]
|#1044 - Access denied for user 'pma'@'localhost' to database 'information_schema' |
information_schema is a read-only database, it is presumably maintained internally.
Only some of the databases were restored, but not the 'most important' one.
How do I go about cleaning all and restore all the databases with the import: backup.sql with phpMyAdmin?
Edit backup.sql and remove the restore of information_schema, then retry the restore. -- Per Jessen, Zürich (4.2°C) -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
participants (2)
-
LLLActive@GMX.Net
-
Per Jessen