## Backup and restore

### msqldump from commandline

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

Average of ratings: -
Re: msqldump from commandline

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'.

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: -
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: -
Re: Ang: Re: msqldump from commandline

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:

mysqldump ... command ...;

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

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: - 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 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 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).sqlls -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: -
Re: Ang: Re: Ang: Re: msqldump from commandline

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: -
Ang: Re: Ang: Re: Ang: Re: msqldump from commandline

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

Average of ratings: -
Re: Ang: Re: Ang: Re: msqldump from commandline

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/;
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) Ang: Re: Ang: Re: Ang: Re: msqldump from commandline Now it helps 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).sqlls -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: -
Re: Ang: Re: Ang: Re: Ang: Re: msqldump from commandline

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: -
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

Average of ratings: -
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) 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: -
Ang: Re: Ang: Re: OT

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

I like that

Average of ratings: -
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: -
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: -
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: -
Ang: Re: Ang: Re: msqldump from commandline

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

Average of ratings: -
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)