How to monitor the hard disk status

Checking if SMART is enabled

Find out the names of your hard disks by using one of the following commands:

df -h
fdisk -l

Let’s become familiar with the smartctl utility. The first thing we want to check is if S.M.A.R.T support is active on the device. To perform this operation we can run the smartctl utility with the -i option (short for --info):

$ sudo smartctl -i /dev/sda
The output of the command is the following:
smartctl 7.1 2020-04-05 r5049 [x86_64-linux-4.18.0-193.28.1.el8_2.x86_64] (local build)
Copyright (C) 2002-19, Bruce Allen, Christian Franke, www.smartmontools.org

=== START OF INFORMATION SECTION ===
Vendor:               LSILOGIC
Product:              Logical Volume
Revision:             3000
User Capacity:        498 999 492 608 bytes [498 GB]
Logical block size:   512 bytes
Logical Unit id:      0x600508e0000000002bc75cd385bf0d0c
Device type:          disk
Local Time is:        Tue Oct 25 14:17:01 2022 CEST
SMART support is:     Unavailable - device lacks SMART capability.

We can see that basic information are displayed such as the device family, model, sector sizes, etc. What interests us the most, however is the content of the last two lines. From there we can see that the device has SMART capabilities and that, in this case, SMART support is disabled. What if we want to enable it? All we have to do is to run smartctl with the -s option, using “on” as argument:

smartctl 7.1 2020-04-05 r5049 [x86_64-linux-4.18.0-193.28.1.el8_2.x86_64] (local build)
Copyright (C) 2002-19, Bruce Allen, Christian Franke, www.smartmontools.org

=======> INVALID ARGUMENT TO -s: /dev/sda
=======> VALID ARGUMENTS ARE: on, off, aam,[N|off], apm,[N|off], dsn,[on|off], lookahead,[on|off], security-freeze, standby,[N|off|now], wcache,[on|off], rcache,[on|off], wcreorder,[on|off[,p]], wcache-sct,[ata|on|off[,p]] <=======

Use smartctl -h to get a usage summary
Getting familiar with smartctl
To get all the available SMART information about a storage device, we can launch the utility with the -a option (short for -all) and of course pass the path of the device we want to check as the last argument of the command. Suppose we want to check the current status of the /dev/sda device; we would run:

$ sudo smartctl -a /dev/sda
The command above produces a lot of output. Among the other things, we can see the status of various SMART parameters:

smartctl 7.1 2020-04-05 r5049 [x86_64-linux-4.18.0-193.28.1.el8_2.x86_64] (local build)
Copyright (C) 2002-19, Bruce Allen, Christian Franke, www.smartmontools.org

=== START OF INFORMATION SECTION ===
Vendor:               LSILOGIC
Product:              Logical Volume
Revision:             3000
User Capacity:        498 999 492 608 bytes [498 GB]
Logical block size:   512 bytes
Logical Unit id:      0x600508e0000000002bc75cd385bf0d0c
Device type:          disk
Local Time is:        Tue Oct 25 14:19:37 2022 CEST
SMART support is:     Unavailable - device lacks SMART capability.

=== START OF READ SMART DATA SECTION ===
Current Drive Temperature:     0 C
Drive Trip Temperature:        0 C

Error Counter logging not supported

Device does not support Self Test logging

RAID Commands

fdisk -l

lblsk

lshw -class disk - show all disk with information

smartctl --scan   - show all installed disks

smartctl -d sat -all /dev/sg1 

lspci |grep -i raid

lspci -vv | grep -i raid

cat /proc/scsi/scsi

# smartctl -a /dev/sda -d sat+megaraid,00

• Show all physical disks on controller 0

  $ omreport storage pdisk controller=0
  

• Show all logical disks on controller 0

  $ omreport storage vdisk controller=0
  

• Show all physical disks on vdisk 0

  $ omreport storage pdisk controller=0 vdisk=0
  

• Reconfigure a vdisk to be raid1 from raid0 (COOL!!!!)

  $ sudo omconfig storage vdisk action=reconfigure controller=0 vdisk=1 raid=r1 pdisk=0:0:2,0:0:3

• omreport storage pdisk controller=0 vdisk=0 | grep -v ": Not "

How To Change a MariaDB Data Directory to a New Location on CentOS

1 — Moving the MariaDB Data Directory

To prepare for moving MariaDB’s data directory, let’s verify the current location by starting an interactive session using the administrative credentials.

#mysql -u root -p

When prompted, supply the MariaDB root password. Then from the mysql prompt, select the data directory:

> select @@datadir;

/var/lib/mysql/

This output confirms that MariaDB is configured to use the default data directory, /var/lib/mysql/, so that’s the directory we need to move.

To ensure the integrity of the data, we’ll stop service - MariaDB before we actually make changes to the data directory:

# systemctl stop mariadb

to check if the service is stopped use the command:

#systemctl status mariadb

Sep 11 12:21:04 mysql systemd[1]: Stopped MariaDB database server.

Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.

SQL. Search for any text or integer in the database

Small queries to search for any number or text in the desired MS SQL database.

Find an integer.

1. set nocount on
2. declare @name varchar(128), @substr bigint, @column varchar(128)
3. set @substr = 462083 -- ИСКОМОЕ ЦЕЛОЕ ЧИСЛОВОЕ ЗНАЧЕНИЕ
4. /* Create a table for output */
5. create table #rslt2
6. (table_name varchar(128), field_name varchar(128), value bigint)
7. /* Populate the created table with the found data */
8. declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
9. open s
10. fetch next from s into @name
11. while @@fetch_status = 0
12. begin
13. declare c cursor for
14.       select quotename(column_name) as column_name from information_schema.columns
15.            where data_type in ('int', 'bigint') and table_name = @name
16. set @name = quotename(@name)
17. open c
18. fetch next from c into @column
19. while @@fetch_status = 0
20. begin
21.       print 'Processing table - ' + @name + ', column - ' + @column
22.       exec('insert into #rslt2 select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
23.            ' from' + @name + ' where ' + @column + ' = ' + @substr )
24. fetch next from c into @column
25. end
26. close c
27. deallocate c
28. fetch next from s into @name
29. end
30. select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt2
31. group by table_name, field_name
32. order by table_name, field_name
33. /* Display found values ​​if needed (uncomment next line) */
34. -- select * from #rslt2 order by table_name, field_name
35. drop table #rslt2
36. close s
37. deallocate s

Wyszukaj ciąg znaków.

1. set nocount on
2. declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
3. set @substr = '%Пружинкин%' -- ФРАГМЕНТ ИСКОМОЙ СТРОКИ
4. /* Create a table for output */
5. create table #rslt
6. (table_name varchar(128), field_name varchar(128), value ntext)
7. /* Populate the created table with the found data */
8. declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
9. open s
10. fetch next from s into @name
11. while @@fetch_status = 0
12. begin
13. declare c cursor for
14.       select quotename(column_name) as column_name from information_schema.columns
15.            where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name = @name
16. set @name = quotename(@name)
17. open c
18. fetch next from c into @column
19. while @@fetch_status = 0
20. begin
21.       print 'Processing table - ' + @name + ', column - ' + @column
22.       exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
23.            ' from' + @name + ' where ' + @column + ' like ''' + @substr + '''')
24.       fetch next from c into @column
25. end
26. close c
27. deallocate c
28. fetch next from s into @name
29. end
30. select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
31. group by table_name, field_name
32. order by table_name, field_name
33. /* Display found values ​​if needed (uncomment next line) */
34. --select * from #rslt order by table_name, field_name
35. drop table #rslt
36. close s
37. deallocate s