Sunday, January 20, 2019

How to Check MySQL Database & Tables Size on linux ?

MySQL is a Relational Database Management System, widely used as a database system for Linux systems. This article will help you to calculate the size of tables and database in MySQL or MariaDB servers though SQL queries. MySQL stored all the information related to tables in a database in the information_schema database. We will use the information_schema table to find tables and databases size.


How to find each data base size ?


Check ALL Databases Size in MySQL



Using mysql query


SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema;


Sample output:


mysql> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema
   -> ;
+--------------------+------------+
| Database           | Size (MB) |
+--------------------+------------+
| information_schema | 0.00878906 |
| mylabdb            | 0.00111008 |
| mysql              | 0.68704987 |
| performance_schema | 0.00000000 |
+--------------------+------------+



mysql> SELECT
   ->   table_schema 'Database Name',
   ->   SUM(data_length + index_length) 'Size in Bytes',
   ->   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
   -> FROM information_schema.tables
   -> GROUP BY table_schema;





Check Single Table Size in MySQL Database


To find out the size of a single MySQL database called mylabdb (which displays the size of all tables in it) use the
following mysql query.


mysql> SELECT table_name AS "Table Name",ROUND(((data_length + index_length) / 1024 / 1024),
2) AS "Size in (MB)" FROM  information_schema.TABLES WHERE table_schema = "mylabdb"
ORDER
BY (data_length + index_length) DESC;




Finally, to find out the actual size of all MySQL database files on the disk (filesystem), run the
du command below.


sudo du -h /var/lib/mysql



Thursday, January 17, 2019

Linux How do I display failed login attempt?


/var/log/faillog is a log file for failed login attempts. This file maintains a count of login failures and the limits for each account. The file is fixed length record, indexed by numerical ID. Each record contains the count of login failures since the last successful login; the maximum number of failures before the account is disabled; the line the last login failure occurred on; and the date the last login failure occurred. Since data is in binary format you need to use faillog command to display failed login attempt.

faillog [options]

Options:
 -a, --all                     display faillog records for all users
 -h, --help                    display this help message and exit
 -l, --lock-secs SEC           after failed login lock account for SEC seconds
 -m, --maximum MAX             set maximum failed login counters to MAX
 -r, --reset                   reset the counters of login failures
 -R, --root CHROOT_DIR         directory to chroot into
 -t, --time DAYS               display faillog records more recent than DAYS
 -u, --user LOGIN/RANGE        display faillog record or maintains failure
                               counters and limits (if used with -r, -m,
                               or -l) only for the specified LOGIN(s)



How do I use faillog?

To display failed login attempt for user root with following command:
$ faillog -u root

Sample output

faillog -u root
Login       Failures Maximum Latest                   On

root            0 0 01/01/70 05:30:00 +0530  



To display all failed login attempt try:
$ faillog -a

faillog -a
Login       Failures Maximum Latest                   On

root            0 0 01/01/70 05:30:00 +0530  
daemon          0 0 01/01/70 05:30:00 +0530  
bin             0 0 01/01/70 05:30:00 +0530  
sys             0 0 01/01/70 05:30:00 +0530  
sync            0 0 01/01/70 05:30:00 +0530  
games           0 0 01/01/70 05:30:00 +0530  
man             0 0 01/01/70 05:30:00 +0530  
lp              0 0 01/01/70 05:30:00 +0530  
mail            0 0 01/01/70 05:30:00 +0530  
news            0 0 01/01/70 05:30:00 +0530  
uucp            0 0 01/01/70 05:30:00 +0530  



How to find cpu minimum, current & maximum frequency in linux ?

cpu manufacturers pro-grammatically reduce the frequency of the processor. You can find out the current and possible frequency with the command:

How to find available frequencies ?

cat  /sys/devices/system/cpu/cpu*/cpufreq/scaling_available_frequencies

Sample output:

cat  /sys/devices/system/cpu/cpu*/cpufreq/scaling_available_frequencies
2933000 2128000 1596000
2933000 2128000 1596000


Finding each core   minimum, current & maximum frequency


grep '' /sys/devices/system/cpu/cpu0/cpufreq/scaling_{min,cur,max}_freq

Sample output:

/sys/devices/system/cpu/cpu0/cpufreq/scaling_min_freq:1596000
/sys/devices/system/cpu/cpu0/cpufreq/scaling_cur_freq:1596000
/sys/devices/system/cpu/cpu0/cpufreq/scaling_max_freq:2933000

Here we can see that the current processor frequency-1596 Mhz and the maximum-2933 Mhz.

Above example for core 0 if you have N number of cores use *:


grep '' /sys/devices/system/cpu/cpu*/cpufreq/scaling_{min,cur,max}_freq

Sample output :

/sys/devices/system/cpu/cpu0/cpufreq/scaling_min_freq:1596000
/sys/devices/system/cpu/cpu1/cpufreq/scaling_min_freq:1596000
/sys/devices/system/cpu/cpu0/cpufreq/scaling_cur_freq:1596000
/sys/devices/system/cpu/cpu1/cpufreq/scaling_cur_freq:2128000
/sys/devices/system/cpu/cpu0/cpufreq/scaling_max_freq:2933000
/sys/devices/system/cpu/cpu1/cpufreq/scaling_max_freq:2933000

How to find cpu count ?

grep -c 'model name' /proc/cpuinfo


Sample output :

2



Use various performance governors.

ondemand: The CPU freq governor "on-demand" sets the CPU depending on the current usage.
To do this the CPU must have the capability to switch the frequency very quickly.

conservative: The CPU freq governor "conservative", much like the "on-demand" governor, sets
the CPU depending on the current usage. It differs in behavior in that it gracefully increases
and decreases the CPU speed rather than jumping to max speed the moment there is any
load on the CPU. This behavior is more suitable in a battery powered environment.

userspce: The CPU freq governor "user-space" allows the user, or any user-space program
running with UID "root", to set the CPU to a specific frequency by making a sysfs
file "scaling_setspeed" available in the CPU-device directory.

powersave: The CPU freq governor "powersave" sets the CPU statically to the lowest
frequency within the borders of scaling_min_freq and scaling_max_freq.

performance: The CPU freq governor "performance" sets the CPU statically to the highest
frequency within the borders of scaling_min_freq and scaling_max_freq.



How to find available_governors?

cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_available_governors

Sample output:

conservative ondemand userspace powersave performance


Set permanently one eg.:


sudo echo "performance" > /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor

cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor

performance







Tuesday, January 1, 2019

How to count days since a specific date until today using Bash shell ?


The easiest way to perform days count since a specifics day is to first get a number of seconds
since epoch time ( 1970-01-01 ) for both dates. As an example let’s count number of days
since 28.12.1999 until today 8.1.2018. Consider a following example:
$ echo $((($(date +%s)-$(date +%s --date "1999-12-28"))/(3600*24))) days
6586 days

Let's add little bit of readability to the above command by using variables. First, we get
seconds since epoch time ( 1970-01-01 ) until now:

$ now=$(date +%s)

$ echo $now
1515370378

Next we do the same for the 28.12.1999 date:

past=$(date +%s --date "1999-12-28")
$ echo $past
946299600

Next, calculate the difference:

$ difference=$(($now-$past))
$ echo $difference
569070778

Lastly, convert the difference in seconds to days:
$ echo $(($difference/(3600*24)))
6586

All done. The same principle can be used to calculate days between any specific days.
For example let's count days between 1.1.2016 and 31.12.2016 dates:

$ echo $((($(date +%s --date "2016-12-31")-$(date +%s --date "2016-1-1"))/(3600*24))) days
365 days