How to take backup of your files regularly to external drive or to a new mount point ?

Hello Everyone,
As I have mentioned in other posts, I am using Arch based Endeavour distro in my ThinkPad.
No matter how stable the OS is, its always recommended to backup your important data.
So by doing very small tasks I achieve something like regular backup and OnDemand backup in my Arch Linux.

Step 1: Identify What folder you like to backup

So just to avoid any crazy permission related issues I got, like mentioned here , I set myself to use my home folder only for learnings.
So I have created a folder called `my_learning` and I am going to keep all my notes, code at that location. So if you want to create a folder you can simply do

mkdir ~/my_learning

Step 2: Install rsync ( If not installed )

If you dont know what rsync from Wikipedia

rsync is a utility for efficiently transferring and synchronizing files between a computer and an external hard drive and across networked computers by comparing the modification times and sizes of files

so what ever the distro you are using the binary name stays same to install rsync.
RedHat/CentOS/Fedora

sudo yum install rsync

Debian/Ubuntu

sudo apt-get install rsync

Arch

sudo pacman -S rsync

Step 3: Lets identify what Rsync options needed

So We need to backup to be happen in recusrive order and while its happening I need compress to happen just to save sometime. And verbose and human readable output format and pretty much needed anyway. So overall I need below options

-r, --recursive             recurse into directories
-z, --compress              compress file data during the transfer
-v, --verbose               increase verbosity
-h, --human-readable        output numbers in a human-readable format

But Rsync isnt limited to jus these 4 options, if you want to know more about rsync options, please check out its man page.

Step 4: source and target locations

So In one of my mount point I have created a folder with same name as source( make sure its mounted before creating the folder)

mkdir /run/media/username/ContinousImprovement/my_learning

.
and source is anyway my home folder

my_learning

location.
And the syntax of rsync is similar to cp command in linux i.e

cp [OPTIONS] source destination

rsync -zrvh /home/username/my_learning /run/media/username/ContinousImprovement/my_learning

Step 4: lets make the command handy

So I am using ZSH as my default shell, so I have opened my

.zshrc

file. If you are using bash, you can use

.bashrc

and I have added below function using shell scripting at very bottom

# backup home directory
function backup_home
  rsync -zrvh /home/username/my_learning /run/media/username/ContinousImprovement/my_learning

and execute

exec $SHELL

or

source ~/.zshrc

.
That's it, now if you call

backup_home

from terminal, your source directory will be backup to remote directory. if you want to automate it using a scheduler job, you can achieve same using

cron

, but make sure target is available during execution.

➜  my_learning backup_home 
sending incremental file list
my_learning/ansible/ansible.cfg
my_learning/ansible/inventory
my_learning/ansible/test.yaml
...
...
...

sent 51.93M bytes  received 141 bytes  103.86M bytes/sec
total size is 55.07M  speedup is 1.06
➜  my_learning 

Hope it helps.
Thank you.

Rename Database in MySQL

Today I am writing about how to rename MySQL Database.

So If you want to rename a database in MySQL dont forget taking backup of your Views,Procedures and Functions of current database. Because while you renaming changes wont apply for Views,Procedures and functions.

After you have taken the backup , We can go for renaming with the command below.

RENAME TABLE old_db.table TO new_db.table;

Here old_db is your old database name and new_db is your new database name. You have to execute this command for each table. And this is the way.

But what if we have a big database with 1000 tables ? executing the same above command for 1000 times is a stupid idea.

If you are using Linux you can use following BASH script to do that for you

for table in $(mysql -p -s -N -e "show tables from db1"); do mysql -s -N -e "rename table db1.$table to db2.$table"; done;

db1 is your old database name and db2 is your new database name and remaining everything will tool care by script only except giving password.:P

After executing the command successfully without any errors , you must have to restore that backup of Views,Proc's and Functions.

Errors: I have got one error with triggering. Generally some tables have triggers. So you copy the code of triggers and then delete those triggers. Then try to rename the table again.

After it got successful , just recreate the triggers for the same tables in new database.

I have done this trigger creation in MYSQL Workbench.

If you want to backup only Functions and not data of database then you can use this command

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt  <database> > outputfile.sql

Here : 

--routines                 =       for including routines ( views,functions,procedures)

--no-create-info       =       Do not write CREATE TABLE statements that re-create each dumped

                              table.

--no-data                  =        Do not write any row information for the table. This is very useful

                        if you want to get a dump of only the structure for a table.

 --no-create-db         =        This option suppresses the CREATE DATABASE /*!32312 IF NOT EXISTS*/

                        db_name statements that are otherwise included in the output if the
                        --databases or --all-databases option is given.

--skip-out        = --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.

Basic MySQL Administration

1. How to create a user and along with his privileges automatically ?

GRANT ALL PRIVILEGES ON database.* To 'username'@'hosr or IP ' IDENTIFIED BY 'password';

flush privileges;

2. How to give only specific privileges to users ?

GRANT select,lock tables ON *.* To 'user'@'host or IP ' IDENTIFIED BY 'password';

flush privileges;

3. How to see permissions of a user ?

show grants for 'username'@'hostanme';

4. How to revoke all given privileges ?

revoke all privileges on *.* from 'user'@'host';

5. How to take backup of database ?

mysqldump --user <username> --password=<password> databasename > databasebackup.sql

6. How to take backup of two databases ?

mysqldump --user <username> --password=<password> databasename1 databasename2 > databasebackup.sql

7. How to take backup of all databases in MySQL ?

mysqldump --user <username> --password=<password> -all-databases > alldbbackup.sql

8. How to take backup of table in database ?

mysqldump --user <username> --password=<password> database_name database_table \  >  databasebackup.sql 

9. How to restore one database ?

mysql --user <username> --password=<password> databasename < databasebackup.sql
mysql_upgrade -p -u root --force