Restore MySQL lost ? pmadb and pma user ??

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Restore MySQL lost ? pmadb and pma user ??

LLLActive@GMX.Net
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Restore MySQL lost ? pmadb and pma user ??

LLLActive@GMX.Net
> 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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Restore MySQL lost ? pmadb and pma user ??

Per Jessen
[hidden email] 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: [hidden email]
To contact the owner, e-mail: [hidden email]