[ubuntu] [Command Line Server] Setting an automated incremental backup to a network location

[pcstru]

Just to be safe I confirmed the credentials I'm using for the SQL database were correct (mysql --user= --password=) and didn't encounter any error.


That will get you into the server but not necessarily the database. Fire up mysql as you did, and  :
 

[b]show databases;[/b]
 

We need to determine the name of the database (it should be listed in the wp-config.php file in the wordpress root path on the line define('DB_NAME', 'wordpress'); ).

[Garacesh]

Yeah, I'd done that earlier, which is how I arrived at the database name being 'wordpress'

 

mysql> show databases;
+--------------------+
¦ Database           ¦
+--------------------+
¦ information_schema ¦
¦ mysql              ¦
¦ performance_schema ¦
¦ wordpress          ¦
+--------------------+
4 rows in set (0.00 sec)

[pcstru]

Yeah, I'd done that earlier, which is how I arrived at the database name being 'wordpress'

Ah OK. I thought I'd stuck that in! I'm losing track!

 

Try just :

 

mysqldump -h localhost -u user -p password wordpress

[Garacesh]

mysqldump -h localhost -u user -p password wordpress

Bizarre! It asks me for a password even though I'm putting it in :S

 

user@Wordpress-Server:~$ mysqldump -h localhost -u  -p  wordpress
Enter password: 
-- MySQL dump 10.13  Distrib 5.5.38, for debian-linux-gnu (x86_64)
--
-- Host: localhost     Database: [i][/i]
[b][/b]
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1049: Unknown database '[i][/i]' when selecting the database

 

However..

user@Wordpress-Server:~$ mysqldump -h localhost -u  -p wordpress
Enter password:

If I put the password in there, I get about 6 screens flicking by at way too quick to read until finally..

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-09-29 15:00:20

So that's progress..

[pcstru]

Sorry my fault. When I tested that yesterday I was working on a different server with an old version of MySQL. So the correct syntax for you should be :

 

mysqldump --host=localhost --user= --password= wordpress

 

Then

 

mysqldump --host=localhost --user=wordpress --password= wordpress > db_backup.sql

 

will/should pipe it out to a file.

[Garacesh]

sudo mysqldump --host=localhost --user=[i][/i] --password=[i][/i] wordpress > db_backup.sql
-bash: db_backup.sql: Permission denied

 

Works without the pipe. Specifying /home/user/db_backup.sql still receives permission denied even though I should be able to write there no problem?

I am right in saying the user and password I should use is the MySQL user and password, not the Ubuntu user/password? (I.e. the same credentials I use for mysql --user= --password=) Dumb question. It would fail without the pipe, too, if I were using the wrong credentials.

Edited September 29, 2014 by Garacesh

[pcstru]

Lose the sudo on the mysqldump, you don't need it. You could use a full path to the backup file (i.e. /home//db_backup.sql).

 

(just to be clear, things in <> are things you need to fill in (and when you do, no angled brackets)).

 

You can check the permissions on your home path :

 

ls -al /home

 

and look for your dir. Check it's not owned by root or something silly.

 

Also, don't despair - we are making progress!

[Garacesh]

Yep, not using angled brackets (edit: except for piping with wordpress > db_backup.sql)

 

The sudo was only used after I got permission denied without it, I thought maybe it just needed to be elevated.

 

I did try /home/user/db_backup.sql and still got permission denied.

 

user@Wordpress-Server:~$ ls -al /home
total 12
drwxr-xr-x	3	root	root	4096	Sep	18	09:44	[color="#0000FF"].[/color]
drwxr-xr-x	22	root	root	4096	Sep	18	09:39	[color="#0000FF"]..[/color]
drwxr-xr-x	3	user	user	4096	Sep	29	14:12	[color="#0000FF"]user[/color]

 

I have no idea what any of that means, but that's the result I get.

 

Edit: For the record, the (failed) cron job from earlier is saved as /home/user/backup.sh, so I have previously written a file there..

 

Further edit:

Directory

User: Read/Write/Execute

Group: Read/-/Execute (Write denied)

World: Read/-/Execute (Write denied)

 

That's some weird way of listing permissions..

 

Edit again:

user@Wordpress-Server:~$ mysqldump --h=localhost --u= --p= wordpress > /mnt/ntfs/db_backup.sql
-bash: /mnt/ntfs/db_backup.sql: Permission denied
user@Wordpress-Server:~$ nano
testing writing to remote share (again)
^O /mnt/ntfs/test2.txt
[ Error writing /mnt/ntfs/test2.txt: Permission denied ]
^X
user@Wordpress-Server:~$ sudo nano
testing writing to remote share (again)
^O /mnt/ntfs/test2.txt
[ Wrote 1 line ]
^O /mnt/ntfs/MySQL/test2.txt
[ Error writing /mnt/ntfs/MySQL/test2.txt: No such file or directory ]

 

So:

• I can definitely write to the root of the share (but not its subfolders) if I use sudo nano.
  
• I can definitely write to /home/user/
  
• I cannot seem to export the MySQL database
  
• I can, however, perform mysqldump without piping out the contents.
  

Edited September 30, 2014 by Garacesh

[pcstru]

Can you

 

ls -al /home/user

 

Perhaps there is a db_backup.sql file already there owned by root.

 

(also do the mysql dump but repalce db_backup.sql with a different, unique name).

[Garacesh]

$ ls -al /home/user
total 56
drwxr-xr-x 3 user user 4096 Sep 29 14:12 [color="#0000CD"].[/color]
drwxr-xr-x 3 user user 4096 Sep 18 09:44 [color="#0000CD"]..[/color]
-rw-rw-r-- 1 user user  483 Sep 30 09:22 backup.sh
-rw------- 1 user user 4732 Sep 30 10:45 .bash_history
-rw-r--r-- 1 user user  220 Sep 18 09:44 .bash_logout
-rw-r--r-- 1 user user 3637 Sep 18 09:44 .bashrc
drwx------ 2 user user 4096 Sep 18 09:45 [color="#0000FF"].cache[/color]
-rw-r--r-- 1 root root    0 Sep 29 11:20 db_backup.sql
-rw------- 1 user user   16 Sep 29 14:12 .mysql_history
-rw------- 1 root root   23 Sep 30 09:44 .nano_history
-rw-r--r-- 1 user user  675 Sep 18 09:44 .profile
-rw-rw-r-- 1 user user   66 Sep 29 10:23 .selected_editor
-rw-r--r-- 1 root root   46 Sep 29 11:26 [color="#FF0000"]wp_code.tar.bz2[/color]
-rw-r--r-- 1 root root   46 Sep 29 11:26 [color="#FF0000"]wp_data.tar.bz2[/color]

So it looks like the file already does exist.. I would have thought it would have been overwritten?

 

mysqldump --h=localhost --u= --p= wordpress > /home/user/testbackup.sql
mysqldump: ambiguous option '--h=localhost' (help, host)

Same result if I replace localhost with the server IP

[pcstru]

So it looks like the file already does exist.. I would have thought it would have been overwritten?
[/quote]
No, because it is owned by root and you only have 'world' permissions to read it.
[quote]
[code]mysqldump --h=localhost --u= --p= wordpress > /home/user/testbackup.sql
mysqldump: ambiguous option '--h=localhost' (help, host)

Same result if I replace localhost with the server IP

Switch to the full syntax (or revert to the exact syntax where it was spitting out screenfulls of database) :

mysqldump --host=localhost --user= --password= wordpress > /home/user/testbackup.sql

 

Generally, the IP for localhost (the loopback interface) is 127.0.0.1 (which is what mysql is configured to listen to by default).

[Garacesh]

Okay, reverting to the 'older' version of the command..

$ mysqldump -h localhost -u [i][/i] -p wordpress > /home/user/testbackup.sql
Enter password:
$ ls -al /home/user
total 100
drwxr-xr-x 3 user user  4096 Sep 30 11:03 [color="#0000CD"].[/color]
[i][/i]
-rw-rw-r-- 1 user user 43790 Sep 30 11:37 testbackup.sql

does seem to work. But it still asks for the password to access the database.

 

If I do -p it still asks. If I don't include -p I get

mysqldump: Got error: 1045: Access denied for user ''@'localhost' (using password: NO) when tryi
ng to connect

so it looks like I need to include -p but manually enter a password.. This will cause problems automating it, will it not?

[pcstru]

Ah sorry (I seem to be apologising a lot!), I forgot that you were being prompted for the password.

 

mysqldump --host=localhost --user= --password= wordpress <

 

Doesn't work?

 

(mysqldump --help might give you some useful info. If you can't scroll the terminal you can pipe to | more)

Edited September 30, 2014 by pcstru

[Garacesh]

Ah sorry (I seem to be apologising a lot!), I forgot that you were being prompted for the password.

 

mysqldump --host=localhost --user= --password= wordpress <

 

Doesn't work?

 

*facepalm*

Yes, that works Woohoo!

 

Nothing to apologise for. You're being a great help!

 

So, that works , yes. However,

mysqldump --host=localhost --user= --password= wordpress > /mnt/ntfs/wp_dp_backup.sql
-bash /mnt/ntfs/wp_db_backup.sql: Permission denied

[pcstru]

So again this is permissions on /mnt/ntfs, but if this is now run from the cron as root, it should be OK. Even so,

 

sudo chmod 777 /mnt/ntfs

 

should make /mnt/ntfs the plaything of anyone. Normally that might be bad but this is just a webserver with no real users right? And the share at the end of that will only be mounted for a brief period anyway.

 

If we

 

cd /mnt/ntfs

pwd

sudo chmod -R 777 *

 

It should change the permissions of all files and recurse the directory structure (and if you start from the wrong directory it can really screw things up! the pwd (print working directory) is just a check).

 

Just on permissions we have user/group/world which are displayed as drwxrwxrwx where each rwx is read/write/execute for the user, group or world. I find using Octal easy for that, so in the 777, each digit is an Octal representation of the binary mask - 1 bit each for rwx (111). So if we just want to set read and execute it would be 101 which is 5. Restricting that to just the owner would give chmod 500.

 

We can change the owner and group for a file or directory with chown : :

 

sudo chown user:user /mnt/ntfs

 

Should change the ntfs dir to be owned by the user user and the group user (user is a bad name for a user!).

 

So ... we now need to grapple with tar. Do you know where the wordpress user content is? It is probably (by default) inside the wordpress root but I think it is possible to put it elsewhere. It's where uploads are stored?

Edited September 30, 2014 by pcstru

[Garacesh]

$ cd /mnt/ntfs
$ pwd
/mnt/ntfs
$ sudo chmod -R 777 *
chmod: cannot access '777': No such file or directory

 

sudo chown user:user /mnt/ntfs worked though, or at least, didn't throw an error.

 

Should change the ntfs dir to be owned by the user user and the group user (user is a bad name for a user!).

A) Whoops and B) Is that what 'user user' and 'root root' are when I run ls -al? The user and group ownership?

 

Do you know where the wordpress user content is? It is probably (by default) inside the wordpress root but I think it is possible to put it elsewhere. It's where uploads are stored?

I'm not sure. There's no wordpress folder I can find.

 

$ ls -al
total 89
[color="#0000FF"].
..
bin
boot
dev
etc
home[/color]
[color="#00FFFF"]initrd.img[/color] -> boot/initrd.img-3.13.0.32-generic
[color="#0000FF"]lib
lib64
lost+found
media
mnt
opt
proc
root
run
sbin
srv
sus[/color]
[color="#00FF00"]tmp[/color]
[color="#0000FF"]usr
var[/color]
[color="#00FFFF"]vmlinuz[/color] -> boot/vmlinuz-3.13.0.32-generic

According to this page there should be a /wordpress/ directory but I can't find it.. Maybe I'm just looking in the wrong place.

Edited September 30, 2014 by Garacesh

[pcstru]

chmod: cannot access '777': No such file or directory

Is /mnt/ntfs it empty? Check with ls -al (list all files, long output). You can check permissions by trying to write a file there without using sudo. Then check the file is owned by you with ls -al.

 

Is the share mounted??!?

sudo chown user:user /mnt/ntfs worked though, or at least, didn't throw an error.

A) Whoops and B) Is that what 'user user' and 'root root' are when I run ls -al? The user and group ownership?

Yes. Owner is shown first then the group.

I'm not sure. There's no wordpress folder I can find.

The web root is usually /var/www so probably /var/www/wordpress. If you cd to /var/www and then list the content of that (ls -al) you should see it. Otherwise we will have to check the apache config and see where it thinks the web root is.

Edited September 30, 2014 by pcstru

[Garacesh]

Is /mnt/ntfs it empty? Check with ls -al (list all files, long output). You can check permissions by trying to write a file there without using sudo. Then check the file is owned by you with ls -al.

 

Is the share mounted??!?

Yeah, the share is mounted (sudo mount -t cifs //10.114.192.61/backup-misc/ -o username=,dom=10.114.192.61,password= /mnt/ntfs)

 

The web root is usually /var/www so probably /var/www/wordpress. If you cd to /var/www and then list the content of that (ls -al) you should see it. Otherwise we will have to check the apache config and see where it thinks the web root is.

Aha.. I do remember /var/www being mentioned during setup, now that you mention it.

 

/var/www/html$ ls -al
total 20
drwxr-xr-x 2 root root  4096 Sep 18 10:01 [color="#0000FF"].[/color]
drwxr-xr-x 2 root root  4096 Sep 18 09:42 [color="#0000FF"]..[/color]
-rw-r--r-- 1 root root 11510 Sep 18 09:43 index.html
lrwxrwxrwx 1 root root    20 Sep 18 10:01 [color="#00FFFF"]wordpress[/color] -> [color="#0000FF"]/usr/share/wordpress[/color]

 

Navigating to usr/share/wordpress gets me..

/usr/share/wordpress$ ls-al
total 164
drwxr-xr-x   5 root root  4096 Sep 18 10:00 [color="#0000FF"].[/color]
drwxr-xr-x 112 root root  4096 Sep 29 10:20 [color="#0000FF"]..[/color]
lrwxrwxrwx   1 root root    23 Apr  9 18:45 [color="#00FFFF"].htaccess[/color] -> /etc/wordpress/htaccess
-rw-r--r--   1 root root   418 Apr  9 13:29 index.php
-rw-r--r--   1 root root  7210 Apr  9 18:45 readme.html
-rw-r--r--   1 root root  4892 Apr  9 13:29 wp-activate.php
drwxr-xr-x   9 root root  4096 Sep 18 10:00 [color="#0000FF"]wp-admin[/color]
-rw-r--r--   1 root root   271 Apr  9 13:29 wp-blog-header.php

Etcetera, etcetera. Including the wp-admin, wp-content and wp-includes directories.

 

So, I'm guessing where D is Directory, L is Link (from sudo ln -s run during the Wordpress setup) which sounds like it's the Linux equivalent of a shortcut?

 

Edit: As for the mnt/ntfs folder..

/mnt/ntfs$ ls -al
total 16
drwxr-xr-x 2 user user 4096 Sep 29 11:31 [color="#0000FF"].[/color]
drwxr-xr-x 3 root root 4096 Sep 29 11:31 [color="#0000FF"]..[/color]
--w------- 1 root root   46 Sep 29:11:31 [color="#FF0000"]wp_code.tar.bz2[/color]
--w------- 1 root root   46 Sep 29 11:31 [color="#FF0000"]wp_data.tar.bz2[/color]

Edited September 30, 2014 by Garacesh

[pcstru]

It's an odd set up with everything being owned by root. Usually you would want web application files to be owned by www-data, which is the user Apache runs as. Is everything in wordpress working OK? You can upload images, get updates etc? How did you install wordpress?

 

I like the permissions on the wp_code.tar.bz2 files - write only, I think my mother does that! You could get rid of those files :

 

cd /mnt/ntfs

sudo rm -f *

 

(you need the sudo on the remove because the files are owned by root)

 

then check they have gone.

 

I guess this is 14.02, since you have /var/www/html. Ubuntu has been /var/www up to very recently.

 

Check you can write as a user to /mnt/ntfs perhaps try writing the mysqldump file there or try creating a text file without sudo.

 

For the archive we probably just want (in the backup shell file)

 

tar -cjf /mnt/ntfs/wp_data.tar.bz2 /usr/share/wordpress

 

but if test it from the command line by preceding with sudo - otherwise it will barf just trying to access the source files.

[Garacesh]

It's an odd set up with everything being owned by root. Usually you would want web application files to be owned by www-data, which is the user Apache runs as. Is everything in wordpress working OK? You can upload images, get updates etc? How did you install wordpress?

I don't recall ever having a choice about who owns what..

 

I installed Wordpress using the instructions on their website.. Good thing I kept a record of every command I executed!

$ sudo apt-get update
$ sudo apt-get install lamp-server^
$ sudo nano /etc/apache2/conf-available/fqdn.conf
ServerName [i][/i]
$ sudo a2enconf fqdn
$ service apache2 reload
$ sudo nano /etc/php5/apache2/php.ini
memory_limit = 256M
$ sudo nano /etc/mysql/my.cnf
bind-address = [i][/i]
$ sudo /usr/sbin/apache2ctl start
$ sudo /usr/sbin/apache2ctl configtest
$ sudo apt-get install wordpress
$ sudo ln -s /usr/share/wordpress /var/www/html/wordpress
$ sudo gzip -d /usr/share/doc/wordpress/examples/setup-mysql.gz
$ sudo bash /usr/share/doc/wordpress/examples/setup-mysql -n wordpress [i][/i]

(There was also editing of /etc/network/interfaces/ and such to set up the static IP etc, but I don't think that's relevant)

 

Moving on..

$ cd /mnt/ntfs
$ sudo rm -f *
$ ls -al
drwxr-xr-x 2 user user 4096 Sep 29 11:31 [color="#0000FF"].[/color]
drwxr-xr-x 3 root root 4096 Sep 29 11:31 [color="#0000FF"]..[/color]

All gone!

 

Check you can write as a user to /mnt/ntfs perhaps try writing the mysqldump file there or try creating a text file without sudo.

$ nano

Error reading /home/user/.nano_history: Permission denied

Press Enter to continue starting nano.
GNU nano 2.2.6 				New Buffer
Testing non-su file creation on remote share
^O /mnt/ntfs/nonsu.txt
[ Error writing /mnt/ntfs/nonsu.txt: Permission denied ]

 

For the archive we probably just want (in the backup shell file)

tar -cjf /mnt/ntfs/wp_data.tar.bz2 /usr/share/wordpress

but if test it from the command line by preceding with sudo - otherwise it will barf just trying to access the source files.

$ sudo tar -cjf /mnt/ntfs/wp_data.tar.bz2 /usr/share/wordpress
tar: Removing leading '/' from member names

 

That worked fine, 10.9MB wp_data.tar.bz2 appeared in the backup folder.

Edited September 30, 2014 by Garacesh

[pcstru]

Just to satisfy my curiosity, can you run

 

lsb_release -a

 

I'm not quite sure why we appear to have a write issue as user to /mnt/ntfs - possibly a clash with inheritance from /mnt. The shell file which does the business will be run by/as root, so it doesn't matter (you can test the shell by sudo ./.sh (or dangerously become root by sudo su root - which will probably give rise to tut-tutting)).

 

Apart from that, everything that you need you have working. You can mount and unmount a share, you can use mysqldump, you can use tar to backup a directory structure and spit out a compressed tarball.The shell file looks like that in my third post on the first page, but you need to correct the commands for your situation. If it's not obvious how to do that, shout.

 

Into that shell, I'd add a log. At the beginning of the shell file

 

date >> backup.log

echo "Started" >> backup.log

 

and at the end

echo "Finished" >> backup.log

 

Or you can just check cron logs ( grep CRON /var/log/syslog) but obviously only if you are logged in.

 

Finally, you need to test the restore.

[Garacesh]

$ lsb_release -a
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.1 LTS
Release:        14.04
Codename:       trusty

 

Shell file is:

# Timestamp
date >> backup.log
echo "Started" >> backup.log

# Mount the backup share
mount -t cifs //[i][/i]/ -o username=,dom=[i][/i],password=[i][/i] /mnt/ntfs

# Make the backup share the current directory
cd /mnt/ntfs

# Backup MySQL database
mysqldump --host=localhost --user=[i][/i] --password=[i][/i] wordpress > /mnt/ntfs/db_backup.sql

# Backup Wordpress application and assets
tar -cjf /mnt/ntfs/wp_data.tar.bz2 /usr/share/wordpress

# Unmount backup share
unmount /mnt/ntfs

echo "Finished" >> backup.log

 

So, the only remaining thing is to test it runs fine, figure out how to write to a subfolder inside the backup share, and test restoring.

$ sudo nano ./backup.sh
^X
$ sudo ./backup.sh
sudo: ./backup.sh: command not found

[pcstru]

$ sudo nano ./backup.sh
^X
$ sudo ./backup.sh
sudo: ./backup.sh: command not found

 

You will need to set the permissions to rwx on backup.sh.

 

chmod 711 ./backup.sh

 

(owner does all, anyone else can execute). You will need to store the backup.sh somewhere sensible - /home/user/scripts perhaps?

 

In the backup.sh, the echo finished will need to be before the unmount, since the log is being written to a file there. If you want it in a subdir, mount, create one and set appropriate permissions.

[Garacesh]

$ chmod 711 ./backup.sh
$ mkdir scripts
$ mv backup.sh ./scripts/backup.sh
$ cd scripts
$ ls -al
drwxrwxr-x 2 user user 4096 Oct  1 10:16 [color="#0000FF"].[/color]
drwxr-xr-x 4 user user 4096 Oct  1 10:16 [color="#0000FF"]..[/color]
-rwx--x--x 1 user user  553 Oct  1 09:49 [color="#00FF00"]backup.sh[/color]

 

Also added "mkdir WebServer" into the script and moved the echo "Finished" >> backup.log" to before the unmount. Also corrected 'unmount' to 'umount'

So the script now goes: Mount, Create subdir, write Started to log, backup SQL, backup wordpress, write Finished to log, unmount.

Not to jinx it, but I think I might be starting to get the hang of this, little by little.

 

~/scripts$ sudo ./backup.sh
./backup.sh: 10: ./backup.sh: Echo: not found
mysqldump: ambiguous option 'h=localhost' (help, host)
tar: removing leading '/' from member names
tar: /wordpress/var: Cannot stat: No such file or directory
tar: Exiting with failure status due to previous errors
umount: /mnt/ntfs: device is busy.
       (In some cases useful info about the processes that use
        the device is found by lsof(8) or fuser(1))

... Jinxed it *Facepalm*

Edited October 1, 2014 by Garacesh

[pcstru]

~/scripts$ sudo ./backup.sh
./backup.sh: 10: ./backup.sh: Echo: not found

Case sensitive - it is echo, not Echo.

mysqldump: ambiguous option 'h==localhost' (help, host)

Should be --host=

tar: removing leading '/' from member names

tar: /wordpress/var: Cannot stat: No such file or directory

tar: Exiting with failure status due to previous errors

Not sure about this. I think you need to drop the full path from the archive name. You are in the directory you want so /mnt/ntfs/ should not be needed, just wp_data.tar.bz2 .

 

umount: /mnt/ntfs: device is busy.

(In some cases useful info about the processes that use

the device is found by lsof(8) or fuser(1))[/code]

you probably need to cd /home/user (or somewhere) before you unmount the share.