Dump All MySQL Databases into Individual SQL Files

Anyone who is responsible for managing a MySQL database will eventually run into this problem. You either need to dump all your MySQL databases for a backup, or to prepare for an upgrade. Whatever your circumstances are there are several different methods that can be employed to dump your MySQL DBs. Hopefully I can give you the basic tools to get you on your way.

Dump All DBs into a Single SQL File

This first method is what I would call the quick and dirty method. It is straight forward and just dumps all of the databases on a server into a single SQL file. For many individuals this is sufficient, but may not be a good option for larger databases or backup/restore processes. Since all of the DBs are bundled into a single file. Each of the values in brackets “[]” are placeholders for your own values.

mysqldump -u [username] -p --all-databases > allDB.sql

If all you need is to get a quick backup of everything this may be your ticket. The “–all-databases” flag does the magic here, dumping all of the MySQL databases into a single SQL file. More details on this method can be found here. But if you are wanting to be able to easily restore an individual database you may want to use an approach like this.

Dump All MySQL Databases into Individual Files

for I in $(mysql -u [username] -p[mypassword] -h [Hostname/IP] -e 'show databases' -s --skip-column-names); do mysqldump -u [username] -p[mypassword] -h [Hostname/IP] $I > "/home/user/$I.sql"; done

This command first calls “mysql” and gets a list of databases on the server. The command feeds that list of MySQL databases into “mysqldump” to get an individual SQL file for each database on the server. Finally those SQL files are then saved in the location indicated, “/home/user/dbname.sql” in this example.

A few things to note in this command are that first you will notice that I have included the password in the command. The command will complain that using the password on the command line is not safe. However it is required for the command to work. The “-p” is immediately followed by the password, without a space. This is how the option functions, if you add a space it will not work.

The “$I” is a variable and it will have all the database names in it, as it iterates through your listing of DBs. So as you modify the command to fit your specific setup, just make sure to keep that for consistency.

Additional Mysqldump Options

There are a couple of additional mysqldump options that you may want to add depending on your requirements.

--single-transaction

This option keeps mysqldump from trying to get a complete lock on the database. It tells mysqldump to just grab a single transaction and dump the DB contents at the time of that transaction. This can be especially helpful when you are dumping a DB that is especially large, or on a very busy server. Without this option mysqldump may timeout while waiting to get a lock on the DB.

--default-character-set=utf8mb4 

If you are working with data that may contain emoticons you will want this flag. This ensures that your dumped sql file has the correct information to recreate the emoticons when the file gets reimported. Without this option, your emoticons will show up as strange/random text characters when you restore your backup.

Adding Compression

I don’t typically use compression on my SQL dumps since I like to use rdiff-backup as my backup mechanism. But for those who would like to compress their mysqldump in one step here is the basic gist of it.

mysqldump -u [username] -p --all-databases | gzip > allDB.sql.gz

You just pipe the output from the mysqldump command into gzip, or bzip2 to compress the contents. That can be easily added to the command above to dump all MySQL databases into individual files like so.

for I in $(mysql -u [username] -p[mypassword] -h [Hostname/IP] -e 'show databases' -s --skip-column-names); do mysqldump -u [username] -p[mypassword] -h [Hostname/IP] $I | gzip > "/home/user/$I.sql.gz"; done

Hopefully these examples help you get the backups you need. Have some fun along the way.

How to Use Rdiff-backup – A Simply Powerful Backup Tool

In this post I hope to help you understand how to use Rdiff-backup. I stumbled across Rdiff-backup several years ago. It has helped me streamline and simplify most of my file based backup processes from Linux servers. As the name implies, a diff is performed on the files being backed up, so only differences get backed up. As you can probably guess only storing the diffs of changes can lead to a much smaller backup.

Some advantages of Rdiff-backup is that it utilizes rsync, so it can quickly and easily mirror a directory. Backups can happen in seconds if there have been few changes. Your data all travels over a secure ssh connection, so your files are safe during transport. And being a simple command line tool, you can easily script out a backup scenario. Or just use it straight from your terminal.

How I Use Rdiff-backup

I typically use Rdiff-backup with my web hosting clients, it allows for quick backup and restore of their web files. And because most of the the files don’t change from day to day the backup is lightning fast.

rdiff-backup --exclude '**cache/' --exclude '**debug.log' /var/www user@ipAddress::/home/user/backup
rdiff-backup --remove-older-than 52W user@ipAddress::/home/user/backup

Let’s break down the command(s), the first Rdiff-backup command has two “–exclude” options. Those options will as indicated exclude the referenced files or directories from the backup. The exclude options can either have a full relative directory structure or in this case a “**” will match any path. A single asterisk “*” could also be used, but it matches any part of a path not containing a “/” in it.

The “/var/www” part of the command is the directory to backup. Using standard ssh authentication methods “user@ipAddress” ie:”bob@10.1.1.1″ for the login credentials. And then a double colon, which is different from normal rsync/scp formatting. The “::” proceeds the backup destination directory.

So to explain in basic terms, the first command will backup everything under the “/var/www” directory, except any directory ending in “cache/” or file ending in “debug.log”. The backup will be made in the “/home/user/backup” directory.

Backup Lifecycle

Now that you have a backup created how do you manage how long the backup will be kept? Without a command like the second one above, the Rdiff-backup will be kept indefinitely. But adding the second command helps us manage the how long to keep the backups.

rdiff-backup --remove-older-than 52W user@ipAddress::/home/user/backup

This command is a little simpler than the first, it doesn’t specify a source directory. But rather only specifies how long to keep files. The “–remove-older-than” option can take a number of different options. I like to keep my backups for a year, so “52W” gives me 52 weeks of backups. Any existing diffs older than the specified time are removed. Other options are s, m, h, D, W, M, or Y (indicating seconds, minutes, hours, days, weeks, months, or years respectively). Additionally a “B” can be used to indicate the number of backups, ie: “3B” would keep the last three backups.

After specifying the number or timeframe of backups to keep, the only other thing to specify is the backup location. The trimming of backup content is then performed on the given location.

Performing a Restore

So now you have your content backed up, and you need to restore something. A backup is only as good as the data you can restore out of it right? Fortunately the restore process fairly simple as well.

rdiff-backup -r 5D user@ipAddress::/home/user/backup/example.com/index.php /home/localUser/www/restore/

The “-r” option tells Rdiff-backup to restore files, and uses the same time format as the delete option above. In this case we are restoring a version of the file “example.com/index.php” from 5 days ago. The file is being restored/copied to “/home/localUser/www/restore/”. The same can be done for an entire directory structure.

rdiff-backup -r 3B user@ipAddress::/home/user/backup/example.com/ /home/localUser/www/restore/

This command restores/copies all the contents of the example.com directory to “/home/localUser/www/restore/” from 3 Backups ago. Or if you are hunting for a specific day you can always do something like this.

rdiff-backup -r 03-05-2020 user@ipAddress::/home/user/backup/example.com/ /home/localUser/www/restore/

That will perform the same restore, but specifically as of the 5th of March 2020. The date used can be “03/05/2020” or “2020-03-05”, and all indicate midnight as of that day.

For a full rundown on all the options and other details for Rdiff-backup check out the project documentation.

There you have a basic rundown on how to use Rdiff-backup. I find it a very useful and powerful tool, and hope it will help you keep your backups running.

Find Files Not Owned By Specific User or Group

Recently ran into an issue where I needed to search recursively through a file structure. And find files that were not owned by a specific user or group. The issue came up because a client would run a recursive chown on a directory before running git. As a result of the chown they would periodically see the process hang. The chown was to ensure that all the files were writable and wouldn’t gum up the git process. However the hung chown command would cause extra load on the server and lead to system instability.

These files were shared between a few web servers on an NFS share. After some lengthy research I am still stumped as to exactly what the source of the issue is. But it lead me to craft a command that would allow the client to easily check permissions on their files. But without forcing a change to the ownership, which seemed to possibly be the cause of the hanging process.

Find files not owned by specific user

The command was created using some help from this site and this site.

find . ! -group web -or ! -user web -printf "%p - user:%u group:%g\n"

The find command searches from the “.” current directory and recursively checks all files and folders. The “! -group web” section tells the find command to check for files that are NOT “!” owned by the “web” group. Then “! -user web” specifies that it should check for files that are NOT “!” owned by the “web” user. The “-or” tells the find command to match files that are either not owned by the “web” user or group. Changing that to “-and” would only show files that are not owned by the “web” user or group.

The final section ‘-printf “%p – user:%u group:%g\n”‘ tells find how to output the results. “%p” outputs the filename and relative directory structure. “%u” and “%g” output the user and group of the file that is found. Some sample output would look like this.

./logs/access.log.2016_09_30.03.gz - user:root group:web
./logs/access.log.2016_10_04.09.gz - user:root group:web
./logs/access.log.2016_10_06.32.gz - user:root group:web
./logs/access.log.2016_10_04.38.gz - user:root group:web

This command easily helps you determine if your permissions are set correctly. And identify which files will need to have their ownership changed. It is more lightweight and doesn’t force any changes to the filesystem when they are unneeded.

How to Fix: “warning: device is not properly aligned” with Parted

Because you are on this page I assume you have received the same warning I did. “warning: device is not properly aligned”. I allocated a new 1 TB iSCSI drive and I attached it to the initiating server. After the iSCSI device was connected I partitioned it to use 100% of the disk. Then I attempted to format the partition with the XFS file system using the following command.

mkfs.xfs /dev/sdb1 

I immediately got the following response:

warning: device is not properly aligned /dev/sdb1
Use -f to force usage of a misaligned device 

Finding the fix for “device is not properly aligned”

So I did some digging and came across this article about how to properly align the partition. I followed the post to grab the following values from the device.

[root@server ~]# cat /sys/block/sdb/queue/optimal_io_size
1048576
[root@server ~]# cat /sys/block/sdb/queue/minimum_io_size
131072
[root@server ~]# cat /sys/block/sdb/alignment_offset
0
[root@server ~]# cat /sys/block/sdb/queue/physical_block_size
131072 

Following the formula that was outlined (optimal_io_size + alignment_offset) / physical_block_size. And got the following results: (1048576 + 0) / 131072 = 8. I plugged that information into parted, which resulted in mixed results.

(parted) mklabel gpt                                                      
Warning: The existing disk label on /dev/sdb will be destroyed and all data on this disk will be lost. Do you want to continue?
Yes/No? Yes 
                                                              
(parted) mkpart primary 8s 100%
Warning: You requested a partition from 4096B to 1074GB.                  
The closest location we can manage is 17.4kB to 1074GB.
Is this still acceptable to you?
Yes/No? Yes     
                                                          
Warning: The resulting partition is not properly aligned for best performance.
Ignore/Cancel? Ignore 
                                                    
(parted) align-check optimal 1                                            
1 not aligned

Making Sense of the Parted Alignment Response

So my drive was still not properly aligned, but I had some better info which would help me fix the problem. So let’s break down the Parted commands

(parted) mklabel gpt 

Destroyed the existing partition table and cleared things out to start fresh.

(parted) mkpart primary 8s 100%

This command told Parted to create a new primary partition starting at sector 8 and use the rest of the drive. The response that came from this command gave me the clue that I needed to fix the alignment.

Warning: You requested a partition from 4096B to 1074GB.
The closest location we can manage is 17.4kB to 1074GB.

The warning output indicated that 8 sectors of 512B would only come to 4096B or 4kB. But Parted could only adjust the alignment to 17.4kB, so this was still out of alignment as the rest of the output indicated. That gave me the clue I needed to fix it though.

Alignment Fixed

Knowing now that Parted could only get within 17.4kB of the start of my drive I adjusted the sectors to match that. Since 8 sectors was 4096B, I adjusted it to be an even number of sectors beyond the 17.4kB point. Choosing 32768kB, or 64 sectors. This change yielded the following results.

(parted) mklabel gpt
Warning: The existing disk label on /dev/sdb will be destroyed and all data on this disk will be lost. Do you want to continue?
Yes/No? Yes                                                               

(parted) mkpart primary 64s 100%

(parted) align-check optimal 1
1 aligned

(parted) quit                                                             
Information: You may need to update /etc/fstab.

The partitioning was successful, using the 64 sector starting point pushed it beyond the 17.4kB point. Then it was properly aligned. I formatted the partition with XFS and all went swimmingly.

[root@server ~]# mkfs.xfs /dev/sdb1
 specified blocksize 4096 is less than device physical sector size 131072
 switching to logical sector size 512
 meta-data=/dev/sdb1              isize=256    agcount=32, agsize=8191872 blks
          =                       sectsz=512   attr=2, projid32bit=0
 data     =                       bsize=4096   blocks=262139648, imaxpct=25
          =                       sunit=32     swidth=256 blks
 naming   =version 2              bsize=4096   ascii-ci=0
 log      =internal log           bsize=4096   blocks=128000, version=2
          =                       sectsz=512   sunit=32 blks, lazy-count=1
 realtime =none                   extsz=4096   blocks=0, rtextents=0

Now the device is formatted, mounted, and working like a champ. Who would have known that a simple offset of 32kB could make such a problem.

If you are working with iSCSI then you may find you need to force disconnect and reconnect iSCSI on from time to time. Check out my post on how I handle that.

Rename a file that starts with a hyphen/dash

While working on your *nix based system you may run into this issue from time to time. The file may have a hyphen (-) at the start of the filename because of user error. Or it may have been added by a malicious bit of software. However the hyphen got there it can be a pain to deal with. In this post we will answer how to rename a file that starts with a hyphen.

The first time I encountered a filename like this was while searching the files of a compromised website. The offending malware had created several different new files on the site. And one had been created with a starting hyphen in the filename. This causes issues when you run ‘rm’ to remove the file.

rm -filename.php

The hyphen typically tells a command line utility to anticipate an execution option. So in this case the ‘rm’ command attempts to interpret -filename.php as an option. This would just cause ‘rm’ to return the following on a linux based machine.

rm: invalid option -- 'l'
Try `rm --help' for more information.

Both -f and -i are options for the command so when it gets to ‘l’ it assumes it is just an invalid option. So this becomes an issue when you try to remove or otherwise edit/manipulate a file with a hyphen in the name.

Dealing with the hyphenated filename

Fortunately there is a relatively easy method to deal with a file that starts with a hyphen/dash. Adding a double dash (–) before the filename will fix it.

 rm -- -filename.php

This behavior should be universal for GNU/Linux commands. The double dash/double hyphen tells the command that no more command line options will be given. As a result your favorite command will ignore any further dashes and allow you to mv/cp/rm the file.

So now you know how to rename a file that starts with a hyphen on the command line. Hopefully that makes your life a little better.

I originally learned of this technique from this helpful post on superuser.com.

Want to learn how to recursively delete specific files, check out this post to find out how.

Recursive Find and Replace on the Command Line

The Problem

Recently I moved a website that used a ton of legacy php code from the clients production server to a development location. After the move was complete I found that the previous developers had been extremely sloppy. Rather than having a single location/file for DB credentials, they had it in 4 places.

After I figured out where all the locations for DB credentials were I started getting Open_Basedir errors. The original developer had hard coded the web root location hundreds of times in hundreds of files. For just a moment I felt just a bit overwhelmed, then I remembered that I have the terminal to solve problems like this.

The Solution: Recursive Find and Replace on the Command Line

After a bit of research I came up with the following command to recursively search through the entire codebase. When an instance the old web root is detected it replaces it with the correct one.

grep -rl [search for string] . | xargs sed -i s@[search for string]@[replace with string]@g

Or another example with actual search/replace strings

grep -rl /var/www/vhosts/example.com/httpdocs . | xargs sed -i s@/var/www/vhosts/example.com/httpdocs@/var/www/vhosts/newdomain.com/subdomain/dev@g

That command breaks down in the following manner.

“grep -rl” searches recursively for the string you specified “/var/www/vhosts/example.com/httpdocs” starting in the current directory “.”, the “-r” option specifies the recursive search, and “-l” specifies that the system should return only the filenames that contain the string.

those results are then piped “| ” into “sed”, the “-i” option specifies that it should make the changes in place. Then the find replace sequence in this case “s@[search for string]@[replace with string]@g”. The “@” signs could be almost any other value, typically they are a “/” but in this case the strings to find and replace both had “/” in each one so it wouldn’t work as the bordering character. So replacing the “/” with “@” helps SED keep on track. It could easily have been a “#” or “$”, just use what you need to depending on your string.

And with that I was home free no more Open_Basedir issues. Thanks to the command line recursive find and replace all those entries didn’t have to be done by hand.

Thanks Linux Shell

Here are some of the resources that I used when researching this topic:

Find and Replace string in all files recursive using grep and sed

Now you’ve learned to perform a command line recursive find and replace, why not read this post. Learn how to recursively delete specific files using the command line.