Restoring MySQL Database

[DiecastAviation]

Right, new to MySQL and PHP so bear with me - but have been developing a Forum on my Local PC, this now needs to be uploaded to our Hosting ISP.

Have successfully dumped the Database, and I am using myphpadmin to restore the data to the database supplied by the Hosting ISP - however I am receiving the following message:-

Database < (http://mysql1.streamline.net/phpmyadmin/db_details_structure.php?lang=en-iso-8859-1&server=1&db=diecast)name> running on <database (http://mysql1.streamline.net/phpmyadmin/main.php?lang=en-iso-8859-1&server=1) server>

Error

SQL-query :

LOCKTABLES`access`WRITE

MySQL said:

#1044 - Access denied for user: '<username>%' to database '<databasename>'

I have contacted our ISP, but I'm still awaiting a reply - any ideas - My first guess, was they have not given us full access to our own database. :(

[slappy]

This generally means that you either did not identify the username or the username does not have write permission in the folder into which you are trying to restore the backup. The general ussage command would be:

mySQL -u your_username -p (the password and if you leave it blank it will prompt for the password) your_databasename < (the direction of the arrow is the direction the file is going, in this case, into your_database) path/to/sql

or

mysql -u username -p databasename < file.sql (which assums the file has been uploaded to root.)

Have you installed vBulletin on the server and created an admin account with a username and password? Have you checked with phpMyAdmin that the "username" attempting the write actually has "write" permission on the database?

Regards,

[Semere Liif]

The hardest part I had to understand this process is that there are no spaces after -u username so for a test run it would kinda look like this.

mysql -uADMINISTRATOR -pPASSWORD dbname < /path/to/dump.sql

Hopefully this helps some. I don't know if I am technichally right but it worked for me.

Semere

[slappy]

I have had no problems using the "-u" with, or without a space between it and the "username." The one thing you must avoid is when you do not put the password (and wait for it to come with the prompt) you must have a space between the "-p" and the databasename. If you include the actual password, you must NOT have a space between the "-p" and the password.

As in mysql –uUSERNAME –p NEWDBNAME < /PATH/TO/NEW/DUMP.SQL
which prompts for the password.

But I have successfully restored a backup several times using:

mysql –u USERNAME –p NEWDBNAME < /PATH/TO/NEW/DUMP.SQL

and/or

mysql –uUSERNAME –p NEWDBNAME < /PATH/TO/NEW/DUMP.SQL

and or

mysql –u USERNAME –ppassword NEWDBNAME < /PATH/TO/NEW/DUMP.SQL

See generally:

http://dev.mysql.com/doc/mysql/en/User_names.html

"When you connect to a MySQL server with a command-line client, you should specify the username and password for the account that you want to use:

shell> mysql --user=monty --password=guess db_name

If you prefer short options, the command looks like this:

shell> mysql -u monty -pguess db_name

There must be no space between the -p option and the following password value. See section 5.5.4 Connecting to the MySQL Server."

Regards,

[Semere Liif]

Cool thanks for the explanation. I was only sharing what I had just learned by trying to do the same thing.

Thanks agian,
Semere Liif

[DiecastAviation]

This generally means that you either did not identify the username or the username does not have write permission in the folder into which you are trying to restore the backup. The general ussage command would be:

mySQL -u your_username -p (the password and if you leave it blank it will prompt for the password) your_databasename < (the direction of the arrow is the direction the file is going, in this case, into your_database) path/to/sql

or

mysql -u username -p databasename < file.sql (which assums the file has been uploaded to root.)

Have you installed vBulletin on the server and created an admin account with a username and password? Have you checked with phpMyAdmin that the "username" attempting the write actually has "write" permission on the database?

Regards,


This is the question I have posed to our ISP - which they have not yet got back to me on - but basically they supplied us with a database; username and password; which is the username I am logged in under on PhpMyAdmin.

Complete novice with MySql but from the message I guessed it's either a permissions things on the Database, or the username we have been given has not been assigned the correct rights for administrating our OWN database.

Thanks for the Replies - I'll see what they come back with....

[slappy]

If you are logged into phpMyAdmin, you should be able to see your database listed in the database list on the left dropdown menu. If not, you have an initial problem.

With the left menu set on (databases)... on the right, one of the choices in the left most column is "Users." Clicking that entry will open the database table of "Users" showing "And Host" and "Any Users."

From the left you should see columns for "Action" : Edit; Grant; Delete; Host; User; Password; and Privileges. Somewhere in the list of "Users" you should find the name you were given by the ISP. Look at the name of the "host" that "user" is assigned to. It might be "localhost", it might be "root," it might be something else.

The password column will tell you whether or not a password is required of that "user." One should be required in most cases.

The "Privileges" column will show what permissions that "user" has when acting through the identified "host" Usually those permissions will be:

"Select Insert Update Delete Create Drop Reload Shutdown Process File Grant References Index Alter"

which is a list of mySQL commands that "user," if using the correct "password," and acting from the proper "host," is permitted to do to the databases.

These things are important because the backup file you are trying to upload and install has phrases in it, such as:

DROP TABLE IF EXISTS access;
CREATE TABLE access (
userid int(10) unsigned NOT NULL default '0',
forumid smallint(5) unsigned NOT NULL default '0',
accessmask smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (userid,forumid),
KEY userid (userid),
KEY userid_2 (userid)
) TYPE=MyISAM;

LOCK TABLES access WRITE;
INSERT INTO access VALUES (
UNLOCK TABLES;

If your "user" doesn't have permission to execute these commands, you can't restore a database.

Hope that helps.

Regards,

[Revz]

LOCK TABLES access WRITE;
INSERT INTO access VALUES (
UNLOCK TABLES;

If your "user" doesn't have permission to execute these commands, you can't restore a database.

I struggled for a few hours tracking down a problem with my new host that relates to this. On their system a normal user is not allowed to use the LOCK TABLES command which, as you mention, is a problem because by default the mysqldump --opt command (which is pretty much the recommended way of backing up a database over the command line) will produce a dump with various LOCK and UNLOCK statements in it.

There is a way around this though. You can use --skip-lock-tables as a parameter which causes it to dump the database normally but skipping the LOCK / UNLOCK part.

[DiecastAviation]

I struggled for a few hours tracking down a problem with my new host that relates to this. On their system a normal user is not allowed to use the LOCK TABLES command which, as you mention, is a problem because by default the mysqldump --opt command (which is pretty much the recommended way of backing up a database over the command line) will produce a dump with various LOCK and UNLOCK statements in it.

There is a way around this though. You can use --skip-lock-tables as a parameter which causes it to dump the database normally but skipping the LOCK / UNLOCK part.

Thanks for all the replies - most informative - well still waiting for the ISP to reply - I'm getting better service on this Forum - Nice Job.

Anyway where would I modify the dump, the SQL is as follows:-

/*!40000 ALTER TABLE `access` DISABLE KEYS */;
LOCK TABLES `access` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `access` ENABLE KEYS */;

Would that be on the LOCK TABLES commands??


Thanks again...

[slappy]

Yes. The essential purpose of the LOCK TABLE command it what it says. It LOCKs the table while the WRITE is occurring. You don't want someone posting something that alters the database while you are writing to it with it unlocked. I'm not positive, but I believe an alternative for this would be to turn vBulletin off while you were doing the Restore. That way, no one but you will be writing to the database at the moment. When it's done, you can turn it back on. There is a simple command selection in the vBulletin Options inside the AdminCP, if you aren't familiar with it.

I've never looked at a backup which wasn't done with the -opt command, but you could easily make another backup with the --skip-lock-tables command and then look at the file with a text editor to see how it handles the WRITE instruction without the LOCK instruction. I would also guess in is in the online mySQL manual.

Here's the reference: http://dev.mysql.com/doc/mysql/en/mysqldump.html
Regards,

[slappy]

In the discussion of the --opt command it states:

--opt
This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. As of MySQL 4.1, --opt is on by default, but can be disabled with --skip-opt. To disable only certain of the options enabled by --opt, use their --skip forms; for example, --skip-add-drop-table or --skip-quick.

So if opt is on by default, which might not be true with Revz' server, the --skip-lock-tables should work. If --opt was not on by default, one could still list the individual commands which compose the --opt command and simply leave off the --add locks and --lock tables from the string.

Regards,

[slappy]

DiecastAviation:

Do you remember what command you used to make this backup? The use of ALTER TABLE is not generally part of a normal mysqldump.

The mysqldump section has this discussion of the ALTER TABLE ... DISABLE KEYS portion:

--disable-keys, -K
For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file into a MySQL 4.0 server faster because the indexes are created after all rows are inserted. This option is effective only for MyISAM tables.

Just wondering.

Regards,

[DiecastAviation]

DiecastAviation:

Do you remember what command you used to make this backup? The use of ALTER TABLE is not generally part of a normal mysqldump.

The mysqldump section has this discussion of the ALTER TABLE ... DISABLE KEYS portion:

--disable-keys, -K
For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file into a MySQL 4.0 server faster because the indexes are created after all rows are inserted. This option is effective only for MyISAM tables.

Just wondering.

Regards,


Yeah, I actually read the section in the version 3 Manual under Moving Servers - Appendix 3 - Technical Documents - I used

mysqldump --opt -Q -uUSERNAME -p DATABASENAME > /PATH/TO/DUMP.SQL

[slappy]

Hum.... A little closer reading, during more normal hours, causes me to notice better something I already had posted, which does explain the code you have in your backup.

One of the included parts of the shorthand -opt instruction is the --disable-keys function and when I looked at it in the wee hours of this morning, I read it too quickly to observe that the "--disable-keys, -k" was not the code, but the code and the shorthand symbol "-k." :-) So your use of the -opt command, as recommended by vBulletin's moving servers instructions, should put both the --lock-tables and --diable-keys results you posted. The -Q (with the capital Q, not the small q, which is for quick) does the following:

"--quote-names, -Q
Quote database, table, and column names within ``' characters. If the server SQL mode includes the ANSI_QUOTES option, names are quoted within `"' characters. As of MySQL 4.1.1, --quote-names is on by default, but can be disabled with --skip-quote-names."

and explains the single quote marks aroung 'access' in your line:

/*!40000 ALTER TABLE `access` DISABLE KEYS */;

It's been some time since I've written out a mysqldump instruction, because the owner of the board installed a chron backup program which makes a nightly backup, sequentially numbering them as backup1, backup 2, etc. After reaching seven, it starts the process over again, overwriting the original backup1 with the current dates backup.

That script is called the Funky Duck Backup Script and is available on the net, if you are interested. Since upgrades and repairs are more my job now, rather than the backups, I had not closely reviewed the script, simply because I knew the restores were working, having installed them on my home machine to insure they worked. I remember that several years ago, when I was doing manual backups myself, I had also used the -opt command.

But looking at the script now, I notice that it only uses the mysqldump command without an -opt or -Q command, which are recommended by vBulletin, particularily for large databases. Our database is really not that large, only around 60 Mg with .GZip compression, which the script does automatically. That works out to around 150 Mg uncompressed.

But you've peaked my interest in trying an experiment of creating a manual backup again using the -opt and -Q commands to see if it seems to run any faster and/or is more compressed. The backup script, itself, is easy to alter to add those options to the mysqldump command.

So we all are learning additional things, and that is generally good. Let us know if you get your situation worked out. Remember, as I said here previously, if your ISP won't allow the LOCK statement, you can just create another backup with all the individual options contained in the -opt command except those which involve LOCK or use the --skip-lock-tables as Revz suggested.

Regards,

[DiecastAviation]

Right Success,

The --skip-lock-table did not appear to work, and I still had the lock commands in there, so I found out exactly what the --opt did, removed any reference to locking and the following command worked:-

mysqldump.exe --add-drop-table --all --quick --extended-insert --disable-keys -Q -uroot -p forum > c:\DUMP1.SQL

Had to split the resulting file into two, as my ISP has a 2meg limit on Imports, but successfully imported both files using phpmyadmin.

Did the tweak on the config.php file, and the forum is now successfully running on the Web.

Just need to do some more testing now.

Thanks for all the help and replies.... An Invaluable Resource......

[slappy]

Hi DiecastAviation:

Glad it finally worked. I don't think you ment to say you used "mysqldump.exe." since the command is usually just "mysqldump."

Next time you might also give this a try:

mysqldump --opt -Q --skip-locks --skip-lock-tables -uroot -p forum > c:\DUMP1.SQL

There is both an --add-locks and --lock-tables in the --opt command, so you would need to skip both. That's probably why just --skip-lock-tables didn't work. The way you did it works, as I had suggested, but is just a little longer to type. If it doesn't work, you still have a script that does. What I always do is save the instruction in a file so next time I can just cut and past it into the command window or at least see what I should be typing.

Regards,

[megri]

hello sir

i need some help

Database abc
username abc
password abc123

I am usig below command to dump database

mysqldump -u abc -p abc > abc.sql


procedure applied by me is by loging into SSH of the domain abc.com and then going into httpdocs folder

When i run this command on shell it dump only 788433 database while my database is of 2885367

[gigahost]

This is code, you can using dump database, and gzip:


mysqldump --opt -u USERNAME -pPASSWORD DATABASENAME | gzip> /path/to/backup.gz


very good for me.

[megri]

I have tried this command also but maximum database dump is only 788433 database while my orgional database is of 2885367