what is MySql signed / unsigned option?


When to use signed / unsigned option in MySql.

Mysql Says: 

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column’s range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

When to use UNSIGNED data type?

If your field will never stores negative values, then its best to use UNSIGNED, that will give you faster index and more range.

When to use  SIGNED data type?

If you need to store negative then use signed, but remember your number range is half now.


Note: Common mistake people do is to store primary number ( auto -increment ) as as signed. Which mean you are already reducing your number range even you will never use negative number as your primary key.

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer typesTINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

rssnapshot – How to backup files / website to remote backup location?

How to backup files from one server to another server using rssnapshot?


In this tutorial, we will setup backup in on server, which will pull data from main server as  backup files. This way, if the main server goes offline, all our important files will be there in backup server. For this we will use ‘rsnapshot’. rsnapshot is based on rsync and can be used to backup in remote server or different server.

Since we are backing up in stand alone or different server, all the setup we are doing is in backup server. Before we start with rsnapshot make sure your backup server has ssh access to your main server. To enable ssh in backup server please read this article to create ssh login.

We also need third party repository called EPEL, so that we can install rsnapshot by ‘yum’ command.

1) Install rsnapshot via Yum:

yum install rsnapshot

if you get message like package not found, them please update your repository with EPEL.  To update your repo follow following:

RHEL/Centos 7 64-Bit:

wget http://dl.fedoraproject.org/pub/epel/beta/7/x86_64/epel-release-7-0.2.noarch.rpm
rpm -ivh epel-release-7-0.2.noarch.rpm

RHEL/Centos 6 64-Bit:

 wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -ivh epel-release-6-8.noarch.rpm
  RHEL/Centos 6 32-Bit:
  wget http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
  rpm -ivh epel-release-6-8.noarch.rpm

 2) Setup Config File:

vi /etc/rsnapshot.conf

In config file we are intend to do two things:
a) setup the interval for backup.
b) Setup location from where we need backup files and where to store it.

a) Set snapshot_root to path where you want to keep the backups:

snapshot_root   /root/rsnapshot/

b) Set up the list of directories/files to back up.

look for “backup” text in config file, its in lower part of the file, here we can set multiple backup source and detination.  format is

 backup    <source>    <destination>

please note directory required trailing slash.  so /etc is wrong while /etc/ is right format. And seperator is ‘TAB’  not just a space.

c) few examples of backup path setup.

i) If we want to back /folder/ from one location to another in same server we can do the following.

backup /folder/            localhost/

here every contain of /folder/ is backed up to /root/rssnapshot/localhost/
ii) Backing up files from remote server to current backup server.
Make sure you have ssh login access from backup server to remote server ( no password setup).

backup    root@example.com:/home/mywebsite/   mywebsite/

here all the files of /home/mywebsite/ form remote server example.com will be backed up to /root/rsnapshot/mywebsite/ folders.

ii) Backing up mysql files to remote location.
Best way to backup mysql is create folder in your current server and run the cron script to dump mysql, and backing up that folder from remote rssnapshot script. But you can also find pre-created scripts in rssnapshot package.

a) copy script from rssnapshot utility.

cp /usr/share/doc/rsnapshot/examples/utils/backup_mysql.sh /usr/local/bin/

b) give proper permission to the file.

chown root.root /usr/local/bin/backup_mysql.sh
chmod o-w /usr/local/bin/backup_mysql.sh

c) edit script to suite your needs.

vi /usr/local/bin/backup_mysql.sh

d) Now finally edit rssnapshot.conf file to runbackup

backup_script   /usr/local/bin/backup_mysql.sh  localhost/mysqldump

 d) Setup backup interval:

interval        hourly      6
interval        daily         7
interval        weekly     4
interval        monthly    3

This will take backup every six hours on a day. Define your own schedule as per your requirements.

3) Check config:

# rsnapshot configtest

4) Running Backup:

a) manually running

 rsnapshot hourly

b) running as corn job

vi /etc/cron.d/rsnapshot

and paste following lines, you can change the timing as per your requirement.

 0 */4		* * *		root  /usr/bin/rsnapshot hourly
 30 3  	* * *		root	/usr/bin/rsnapshot daily
 0  3  	* * 1		root	/usr/bin/rsnapshot weekly
 30 2  	1 * *		root	/usr/bin/rsnapshot monthly