Backup and restore

msqldump from commandline

 
Picture of Bente Olsen
msqldump from commandline
 

I hope someone can help me with a useful command to do a mysqldump.

I have tried the method described in moodle docs:

mysqldump -h example.com -u myusername --password=mypassword -C -Q -e --create-options mydatabasename > moodle-database.sql

it returns

mysqldump: unknown option '--'

Then I tried the method mentioned here:

mysqldump -u root -p (pw) moodledb > moodleSQL.sql

which returns

mysqldump: Got error: 1044: Access denied for user 'moodleuser'@'localhost' to database '(pw)' when selecting the database
mysqldump -u root -p moodledb > moodleSQL.sql

returns

mysqldump: Got error: 1044: Access denied for user 'moodleuser'@'localhost' to database 'moodle' when using LOCK TABLES

 

Thanks in advance

 

 
Average of ratings: -
Picture of Ken Task
Re: msqldump from commandline
Group Particularly helpful Moodlers

The DB information in your config.php ... user, password, host, etc. provides information for you concerning how to use mysqldump from CLI on the server where the Moodle code resides.

If the DB server is localhost means the DB server IS on the same server as the code, that should mean one could use mysqldump *minus* the host option/switch in command.

As an example:

In config.php DB server is localhost, DB user=mdbadmin, DB=moodle, DB Password="SomeSecureTypePassword".

From CLI of the server where the code and DB server resides.

mysqldump -u [dbuserfromconfig] -p[dbpasswordfromconfig] [databasename] > /path/to/[databasename].sql

There is *NO SPACE* between the -p option and the [dbpasswordfromconfig] *AND* if it contains any characters that might be mis-interpreted from the shell using, enclose the password in 'ticks'.

-p'[dbpasswordfromconfig]'

Make sure the 'direction' is always > and NOT <.   > = export ... < = import

There is also a catch 22 ... the DB user in config.php may not have privileges (permissions as they are called in MySQL-ese) to do it all.    Might have to use the 'superuser' of the DB server in your command.

Your errors, however, indicate a syntax issue or the user in config.php is not the user you are using in your command.

Generally, when one sees/reads docs/blogs, etc.. things like DB names, logins, passwords shoud be considered 'placeholders' AND shouldn't be used verbatim.

Hope that helps!

'spirit of sharing', Ken

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: msqldump from commandline
 

Hi Ken

Thank you for your reply, it made me a bit wiser, though it did not solve my actual problem.

I just discovered that

Access denied for user 'moodleuser'@'localhost' to database 'moodle' when using LOCK TABLES

means, that the user do not have a privilege called LOCK TABLES, so that must be required for the command mysqldump. It could also indicate that a mysqldump will lock the db entries, so it should only be performed when Moodle is in maintenance mode? Anyway, after applying LOCK TABLES to my db-user - which is not recommended in Moodle Docs - I could make my db dump. That could indicate that it is better to let the mysql superuser make the db dump on a production server?

BTW I prefer the command from the forum prior to the one in Moodle Docs, as it does not include the pw in the command.

 
Average of ratings: -
Picture of Ken Task
Re: Ang: Re: msqldump from commandline
Group Particularly helpful Moodlers

Sounds like you are on some sort of VPS as provided by 'provider' ... which could put you in a 'jail' so to speak.   While it's generally a good security measure to create a separate user with password for an application, one also would need to know what the application needs when it comes to priv's for DB.  And certainly be able to use superuser.

Anyyyyy Hooooo ...

Since you know the superuser login/pass for the DB server and you are concerned about locking records during the dump, then combine what tools you have in moodle to do what needs to be done.

In moodlecode/admin/cli/ one will find a maintenance.php script that has options.   So to do your sql dump from moodlecode directory:

php admin/cli/maintenance.php --enable;

mysqldump ... command ...;

ls -l /path/to/where/you/saved/thedump/[filename].sql (or *.sql) - see note at bottom for name.

php admin/cli/maintenance.php --disable;

Put all that in a bash shell script in /usr/bin/ ... so you can call it up from anywhere you are located when logged on .. then add a line at beginning ...

cd /path/to/moodlecode/;

Execute rest of the above here.

The maintenance script does have options ... the one provided denies anyone access via browser (including admins - although think now they can use URL direct to /login/index.php to bypass the notice Moodle throws to most users.

You could also do a 'paranoid' version ...

first line of script (this for CentOS) /usr/sbin/services httpd stop

That shuts down apache  but you can still use php and mysql commands.

Last line in the script would then restart httpd daemon ...

/sbin/service httpd restart

You'll see:

Stopping httpd:                                            [  OK  ]
Starting httpd:                                            [  OK  ]

If doing paranoid way ... think I'd also check to see httpd restarted ok by showing the last lines of the httpd error log:

tail /var/log/httpd/error_log

which should display stuff I won't share here but it does end with "resuming normal operations" ... and because one would need to make sure one could:

ps aux |grep httpd

as well as an optional watch of the realtime access logs:

tail -f /var/log/httpd/access_log

Last one would require [ctrl][c] to exit.

Restart of apache will take a little longer but has the added affect of killing off zombie apaches, etc..

NOTE about filename from the dump ... I use a -$(date +%Y%m%d%-H%M%S) variable in the filename of the dumped sql file to save so I can have multiples that won't overwrite the others ... handy IF I ever had to roll back a site ... have matching code directory and data directory backups as well.   The Ymd is year month day ... the HMS is hour minute seconds.

'spirit of sharing', Ken

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: msqldump from commandline
 

Hi Ken,

Oh, interesting, but... well... you certainly hit me at the edge of my zone of proximal development, as I believe Vygotsky said smile

I could be using a VPS, I just can't tell, I do not know what it is, but hopefully it doesn't put me in a jail, it is my desktop pc with AMP & Moodle installed wink

I do know the superuser data for mysql, but concerned about locking records... I guessed what the code meant. Anyway it seems as if CLI have some interesting possibilities. I will try it your way, so let me see if I understood you correctly

first I touch  /usr/bin/moodledbbackup

then I add this to it:

cd /var/www/html/moodle/;
php admin/cli/maintenance.php --enable;
mysqldump -u moodleuser -p'pw' moodle > /var/www/moodlebu/mdldb-$(date +%Y%m%d%-H%M%S).sql
ls -l /moodledbbackup/*.sql;
php admin/cli/maintenance.php --disable;

 Then what? Use URL direct to /login/index.php - what do you mean? Go to url-to-moodle/login/index.php and then go to url-to-moodle/admin/cli/maintenance.php?

 

 
Average of ratings: -
Picture of Ken Task
Re: Ang: Re: Ang: Re: msqldump from commandline
Group Particularly helpful Moodlers

Somewhere along this thread I got the impression you were on Linux but we say "desktop pc with AMP & Moodle installed" ... sounds like Windows with apache/MySQL/PHP and Moodle.  But could be a virtual OS (centOS/Ubuntu) that runs an AMP stack ... OR you've got Bitnami .... so how is anyone supposed to help with specific commands etc. IF they don't really know? 

IF it's Windows, then forget what I've shared ... the specific commands.   But look at them and translate to Windows batch  files/powershell or whatever is best on the Winders platform.   Maybe Usman (the resident Windows ace in these forums that does enlighten) will pop in here and share his solution.

Some of what you've replied with (touch) kinda indicates to me Linux ... but which distro? etc.?

So try this from command line:

uname -an [ENTER]

'spirit of sharing', Ken


 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
 

Sorry, I can see I missed your point, I am on Xubuntu.

 
Average of ratings: -
Picture of Ken Task
Re: Ang: Re: Ang: Re: msqldump from commandline
Group Particularly helpful Moodlers

Ok, now we know it's a Xbuntu (Linux) machine.   What I'm sharing with you does work on a CentOS flavored linux box.   I don't have a Xbuntu machine, but this sort of scripting should work on an Xbuntu machine as well.  What's the diff?   Primarily paths ... where one finds things in Xbuntu as opposed to CentOS (for which the info has been provided).

** All of what has been shared is command line ONLY! **   Cannot use the browser to execute.   Must login to the machine via terminal session (ssh) and type the commands as root user (or in your case, you might need to add 'sudo' in front of all commands).   Sudo is 'substitute user' ... allows a regular user account that is in sudo users group to be able to use root commands without having to be the root user all the time.

touch  /usr/bin/moodledbbackup

That created a blank file at /usr/bin/ called 'moodledbbackup'.

Then, you entered into the above file the following:

cd /var/www/html/moodle/;
php admin/cli/maintenance.php --enable;
mysqldump -u moodleuser -p'pw' moodle > /var/www/moodlebu/mdldb-$(date +%Y%m%d%-H%M%S).sql
ls -l /moodledbbackup/*.sql;
php admin/cli/maintenance.php --disable;

First ... is the moodle code located in /var/www/html/
If it is one can see a config.php file there ... ls config.php at that location will list it if the file is there.

The php commands provide the path relative to location ... in /var/www/html/moodle/
one  can see an admin directory and contained therein a cli directory.

The directory in your script: /var/www/moodlebu *MUST* exist.   The script will not create
it for you.

The ls -l command is to show the .sql file.
It needs to be adjusted to show the full path to where the .sql file was saved.
In the mysqldump line that is /var/www/moodlebu/
so the ls -l command *must* be
ls -l /var/www/moodlebu/*.sql

After you've made the corrections, you must make the 'moodledbbackup' file executable.
in /usr/bin/
chmod u+x moodledbbackup
chmod ... changes/modifies a files permissions and settings
u means user +x means add to the file the ability to execute it's contents.

ls -l moodledbbackup
would show/reflect your changes to persmissons:

*example is from a server that has several backup type scripts for versions of Moodle:

-rwxr----- 1 root   root  191 Jan 22  2014 backupmoodle25
-rwxr----- 1 root   root  191 Jan 22  2014 backupmoodle26
-rwxr----- 1 root   root  191 Feb 18  2015 backupmoodle27
-rwxr----- 1 root   root  191 Feb 18  2015 backupmoodle28
-rwxr----- 1 root   root  345 Jan 17  2016 backupmoodle29
-rwxr----- 1 root   root  267 Jan  3  2016 backupmoodle30
-rwxr----- 1 root   root  368 Jun  8 07:55 backupmoodle31

All belong to root user and are in root group

The first -rwx in file attributes means it is readable, writable, and executable
to the root user.

the second rw- indicates any other user that is part of the root group can read and write
but cannot execute the script.

The last set of there --- means all other users .... who have no permissions to read the file, nor write to the file nor execute the file.

Now because the moodledbbackup file is in /usr/bin/ one can execute it from any location.

So right after you login via terminal, at the prompt type: moodledbbackup [ENTER]

*** Since you are on Xbuntu, one might have to use "sudo" then a space before the command.   You will be prompted for your password then.

'spirit of sharing', Ken


 
Average of ratings: Useful (1)
Picture of Bente Olsen
Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
 

Now it helps smile

I edited the shell script to:

cd /var/www/html/moodle/;
php admin/cli/maintenance.php --enable;
mysqldump -u moodleuser -p'pw' moodle > /var/www/moodlebu/mdldb-$(date +%Y%m%d%_-H%M%S).sql
ls -l ../../moodlebu/*.sql;
php admin/cli/maintenance.php --disable;

Unfortunately it does not use my underscore in the filename, but that is a minor thing.

During execution of the script it returns a warning:

Warning: Using a password on the command line interface can be insecure.

I think not to mention having a password stored in plain text in the system must be insecure, it must be better not to apply the password in the script?

Now I also think that it might be possible also to include backing up (cp -r) of the moodledata directory and the moodle directory as well?

 
Average of ratings: -
Picture of Ken Task
Re: Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
Group Particularly helpful Moodlers

Congrats!   You are on your way to becoming a CLI Ninja!

When including what is a shell (operating system) variable, like the date/time stamp, one has to use the options provided - usually best not to be 'inventive' with things like an 'underscore' cause an 'underscore' isn't defined in the options for the variable - unless you want to recompile the kernel/command ... hard core programming then.

"I think not to mention having a password stored in plain text in the system must be insecure, it must be better not to apply the password in the script?"

Security experts would frown on including passwords in scripts, yes.   And the warning is valid.   Let's remember you have a single Moodle instance so you could strip out the password in the script.   I have multiple Moodles on one server .. many times a 'stealthy' dev clone of production.   I'd have to remember unique passwords for X number of instances.

Besides that ... the script cannot be run from Web ... only way to run it is via ssh.   So a cracker would have to gain ssh access to run it.   If they can do that, then you are in more trouble than you know so what diff would it really make.   If they gained root access they can change the password anyway.   Most crackers now a days are not after defacing your site nor leaving clues that they've been into your box, but they do want to use resources of your server as part of a bot network.

Yes, could extend the script capabilities ... now you are thinking! ;)

With Linuxes there might be a memory limitation using the cp -pr command + doing that on moodledata could take a very long time AND eat up space on the partition of the drive where you are storing the backups.

IF there is enough room on the HD, one could do full  tar ball of moodledata, but think I'd use rsync to clone only what is needed from moodledata to re-coup from a serious loss and I'd rsync ONLY the moodledata/filedir directory.  First run of rsync takes time but subsequent runs if using the right 'swtches' to the command deletes things no longer present in adtive moodledata/filedir and updates only files/diretories that are new - thus taking a lot less time to complete.

Ya didn't think I'd feed you fish all the time, did ya? ;)    man rsync and man tar is your online manual friend ... so is a google search

For the code directory, I'll let you dive into the usage of the 'tar' command ... just save to the same directory where you are doing the sql dump to have all things in one place.

Feel free to say 'Thanks' at any time by rating postings useful. ;)

'spirit of sharing', Ken

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
 

Hi Ken

You definitely deserve a big thank you and a 'useful' rating. Now I really am looking forward to being a real CLI Ninja, and you are right, I am on my way!

Thanks, Bente smile

 

 
Average of ratings: -
Picture of Peter West
Re: Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
 

Just saw this while browsing.

With respect to:

mysqldump -u moodleuser -p'pw' moodle > /var/www/moodlebu/mdldb-$(date +%Y%m%d%_-H%M%S).sql
you can always try the embedded command ('date' in this case) from the command line, like so

$ date +%Y%m%d%_-H%M%S

For me, that gives

20160912-H2924

Note the 'H'.  That's happening because you don't have a '%' in front of the 'H' in the command.  As you noted, the '_' is not appearing in the output.  That's because you do have a % in front of the '_'.  date tries to interpret all characters with a preceding '%' as format specifiers.  It doesn't know any format associated with '%_" so it just drops it.

The correct date command involves shifting the errant %.  You can try from the command line

$ date +%Y%m%d_-%H%M%S

which gives me

20160912_-232952

The command line is very picky about the commands it receives.

Incidentally, you mentioned in your initial post that you got an error message about an unrecognised argument '--'.  I suspect that you had a space between the '--' and the argument name for one of your arguments.


 
Average of ratings: Useful (1)
Picture of Bente Olsen
Ang: Re: Ang: Re: Ang: Re: Ang: Re: msqldump from commandline
 

Hi Peter

I've just tried your solution, that is the way to do it!

From now I will use

$(date +%Y%m%d-%H%M%S)

Thanks.

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: OT
 

Fantastic, Moodle have the 'zone of proximal development' in its glossary!

I like that approve

 

 
Average of ratings: -
Picture of frans daniel
Re: Ang: Re: msqldump from commandline
 


Long too path error!
"Long path tool" is very helpful for this problem. You can solve this
problem like copy, delete, long path files by using this tool.I used to have similar problems too, but after using "long path tool" everything was solved.

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: msqldump from commandline
 

Hi Frans Daniel

I am afraid I do not understand which problem you are referring to, that you think the "Long path tool" can solve.

 
Average of ratings: -
Picture of Richard Trout
Re: Ang: Re: msqldump from commandline
 

Among your questions Bente you asked about the mysqldump error on linux:

mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

This is relatively new behaviour when performing mysqldump and doesn't seem to be reported elsewhere in the Moodle forums, but to avoid this error you can either assign privilege to your user (as you suggested) or add the parameter --single-transaction to mysqldump, eg.

mysqldump --single-transaction -u moodle_user -p -C -Q -e --create-options moodle > moodle-database.sql

As I figure at some point the moodle code may update to grant the moodle db user account the correct priviledges, I would just add the --single-transaction parameter as my preferred solution. Hope this helps others.

 
Average of ratings: -
Picture of Bente Olsen
Ang: Re: Ang: Re: msqldump from commandline
 

Thanks, Richard, good to know. I will keep it in mind.

 
Average of ratings: -
Picture of William Hooper
Re: Ang: Re: msqldump from commandline
 

I had the same error message about LOCK TABLES, using MariaDB with the InnoDB engine. After finding a similar problem discussed here:

http://stackoverflow.com/a/105207/2619926

I added the '--single-transaction' option to the mysqldump command line, i.e.:

mysqldump --single-transaction -h example.com -u myusername --password=mypassword -C -Q -e --create-options mydatabasename > moodle-database.sql

The command worked for me, without the need to change user privileges or modify any tables.

 
Average of ratings: Useful (3)