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

How to Check CentOS Version From Command Line

lsb Command to Display Details of CentOS Linux Release

LSB (Linux Standard Base) is a joint project of several Linux distributions to standardize software system structure. One of the commands available from the command line lsb_release. The output will indicate which OS version you are running.

1. Before you can use lsb commands, you have to install the package first. Use the following command:

# sudo yum install redhat-lsb-core 

2. Type in your sudo password to authorize the installation and then press Y and Enter to confirm.
3. Once you have installed it, you can check your CentOS/REHL version as shown with the command:

# lsb_release -d 
or
# lsb_release -a



Find CentOS Version with the hostnamectl Command CentOS version 7 and next distributions, 
hostnamectl command is used to query and set Linux system hostname, and show other system related information, such as operating system release version 
It displays information from the 
/etc/centos-release file, 
uname –a file and others. 
In addition to version number, it indicates which Linux kernel your system is using. 
To see these specifics, use the command: 
# hostnamectl



Find CentOS Version with RPM - RPM (Red Hat Package Manager) is a popular open-source and core package management utility for Red Hat based systems like (RHEL, CentOS, Fedora). 
With the rpm command, you can find out the full package name and the release version of CentOS you are running. 
Use the command: 
# rpm –qa centos-release 
or
# rpm -ql centos-release | grep release$
or
# rpm -qf /etc/redhat-release


Check CentOS Version in Release File To check which Linux distro and major release version you have installed, 
open the release file using the command: 
# cat /etc/os-release 
The output reveals the name of the operating system, its major release version, 
and other specifics, as displayed in the image below.

Check Linux Kernel Version in CentOS Knowing the kernel version you are running is often as useful as finding the release version. 
Find out which Linux kernel version you running, with the command: 
# uname –r 
or 
# uname -s -r
The output you receive is the version number of the kernel of your operating system.

The 4 files shown below provides the update version of the CentOS/Redhat OS.
/etc/centos-release 
/etc/os-release 
/etc/redhat-release 
/etc/system-release