MySQL/MariaDB - Collection

====================================================

INSTALLATION

1. CentOS

Ref: https://www.hostinger.com/tutorials/how-to-install-mysql-on-centos-7

 // get update
# yum update

//download repository
 # wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

// Install
# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

// Install mysql server
# yum install mysql-server

#Uninstall MySQL
$ sudo service mysql stop
$ sudo killall -KILL mysql mysqld_safe mysqld

$ sudo yum remove mysql-client mysql-server -y

====================================================

MYSQL UNINSTALLATION

====================================================

MARIA DB INSTALLATION (CENTOS)

$ yum install mariadb-server

$ systemctl start mariadb

$ systemctl enable mariadb

$ mysql_secure_installation

MARIA DB INSTALLATION (Amazon Linux 2)

Note: Default Mariadb on this repository is 5.3 which older version. If we want to install higher version, do that:

Enable extras repository: 

$ sudo amazon-linux-extras enable lamp-mariadb10.2-php7.2=latest
$ sudo yum install mariadb mariadb-server

Disabled after installing completed.
$ sudo amazon-linux-extras disable lamp-mariadb10.2-php7.2=latest

Ref: https://stackoverflow.com/questions/63069237/mariadb-installation-on-amazon-linux-2

====================================================

MARIAB UNINSTALLATION

$ rpm -qa | grep mariadb

Remove packages show as above:

rpm -e --nodeps "mariadb-errmsg-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-server-utils-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-connector-c-config-3.1.11-2.el8_3.noarch"

rpm -e --nodeps "mariadb-connector-c-3.1.11-2.el8_3.x86_64"

rpm -e --nodeps "mariadb-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-common-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-backup-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

rpm -e --nodeps "mariadb-gssapi-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64"

Remove config

rm -f /var/log/mariadb

rm -f /var/log/mariadb/mariadb.log.rpmsave

rm -rf /var/lib/mysql

rm -rf /usr/lib64/mysql

rm -rf /usr/share/mysql

====================================================

MYSQL - COMMAND LINE FOR CHECK

 // Change password of root user
# sudo grep 'password' /var/log/mysqld.log  => get tmp password when you first install
# sudo mysql_secure_installation
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
Remember: must be restart mysql

//Check current MYSQL version
# mysql -u root -p

 //Reset root password
#sudo mysqld_safe --skip-grant-tables // restart SAFE MODE
#mysql -uroot // connect mySQL

//remove Mysql in CentOS
$ yum remove mysql mysql-server
$ mv /var/lib/mysql /var/lib/mysql_bkup

2. Command line in MYSQL terminal

2.1 Update root password
USE MYSQL;
UPDATE USER SET PASSWORD=PASSWORD(“newpassword”) WHERE USER=’root’;
FLUSH PRIVILEGES;
EXIT

 //Restart MYSQL service after update password

2.2  Show password policy
Ref: https://dev.mysql.com/doc/refman/5.6/en/validate-password-options-variables.html

# SHOW VARIABLES LIKE 'validate_password%';

Result: 

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+

| Variable_name                        | Value  |

+--------------------------------------+--------+

| validate_password.check_user_name    | ON     |

| validate_password.dictionary_file    |        |

| validate_password.length             | 8      |

| validate_password.mixed_case_count   | 1      |

| validate_password.number_count       | 1      |

| validate_password.policy             | MEDIUM |

| validate_password.special_char_count | 1      |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

#ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

2.4 Grant priviliges

#GRANT ALL PRIVILEGE ON <databases>.* TO 'hr_admin'@'%';

#GRANT ALL PRIVILEGE ON <databases>.*TO 'hr'@'localhost';

#GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON  db_hr.* TO 'hr_admin'@'%';

2.5 Check Mariadb Version

$mysql -u root -p

$select @@version;

2.5 Change Mariadb Port

$ netstat -nltp | grep <your_port> // Check your port is exist or not before change

$ vi /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mariadb/mariadb.log

pid-file=/run/mariadb/mariadb.pid

port=<enter your port here>

$systemctl restart maridb

- Check port open.

Problems/Solution

1. Fail to start mariadb in CenTos

Error:

========

2022-11-15 11:11:48 0 [Warning] mysqld: GSSAPI plugin : default principal 'mariadb/hrapprd@' not found in keytab

2022-11-15 11:11:48 0 [ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure.  Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty. 

2022-11-15 11:11:48 0 [ERROR] Plugin 'gssapi' init function returned error.

2022-11-15 11:12:10 0 [Note] InnoDB: Using Linux native AIO

2022-11-15 11:12:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2022-11-15 11:12:10 0 [Note] InnoDB: Uses event mutexes

2022-11-15 11:12:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2022-11-15 11:12:10 0 [Note] InnoDB: Number of pools: 1

2022-11-15 11:12:10 0 [Note] InnoDB: Using SSE2 crc32 instructions

2022-11-15 11:12:10 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2022-11-15 11:12:10 0 [Note] InnoDB: Completed initialization of buffer pool

2022-11-15 11:12:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2022-11-15 11:12:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2022-11-15 11:12:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2022-11-15 11:12:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2022-11-15 11:12:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-11-15 11:12:10 0 [Note] InnoDB: 10.3.28 started; log sequence number 1625716; transaction id 20

2022-11-15 11:12:10 0 [Note] Plugin 'FEEDBACK' is disabled.

2022-11-15 11:12:10 0 [Warning] mysqld: GSSAPI plugin : default principal 'mariadb/hrapprd@' not found in keytab

2022-11-15 11:12:10 0 [ERROR] mysqld: Server GSSAPI error (major 851968, minor 2529639093) : gss_acquire_cred failed -Unspecified GSS failure.  Minor code may provide more information. Keytab FILE:/etc/krb5.keytab is nonexistent or empty. 

2022-11-15 11:12:10 0 [ERROR] Plugin 'gssapi' init function returned error.

2022-11-15 11:12:10 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-11-15 11:12:10 0 [Note] InnoDB: Buffer pool(s) load completed at 221115 11:12:10

2022-11-15 11:12:10 0 [Note] Server socket created on IP: '::'.

2022-11-15 11:12:11 0 [Note] Reading of all Master_info entries succeeded

2022-11-15 11:12:11 0 [Note] Added new Master_info '' to hash table

2022-11-15 11:12:11 0 [Note] /usr/libexec/mysqld: ready for connections.

Version: '10.3.28-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

=====

Solution:

rm /var/lib/mysql-files /var/lib/mysql-keyring
rm -rf /var/lib/mysql/*

2. No Result in Heidi

Ref: https://www.heidisql.com/forum.php?t=6073
Solution:

This seems to be a popular cosmetic issue in HeidiSQL. You could solve it the hard way:
- exit all heidisql.exe processes
- start regedit.exe
- go to HKEY_CURRENT_USER\Software\HeidiSQL\
- delete value "querymemoheight"
- start heidisql.exe                              

3. Create users

$ CREATE USER 'username'@'host' IDENTIFIED  BY 'password';

4. Error "#1030 - Got error 176 "Read page with wrong checksum" from storage engine Aria" 

Ref:  https://stackoverflow.com/questions/60864367/1030-got-error-176-read-page-with-wrong-checksum-from-storage-engine-aria

- Select the 'mysql' database

- Select all the tables as described in other fixes, but run a 'check tables' instead of a 'repair tables'.

- Check table is broken.

- Back to Step #1 and select this broken table and then repair tables.

 5. Change Data File of Mariadb

$ systemctl stop mariadb
$ mv /var/lib/mysql  /var/lib/mysql.bak // Backup current database
$ rsync -av /var/lib/mysql /new_place //Sync data to new location

$ vi /etc/my.cnf.d/mariadb-server.cnf
datadir=/new_place/mysql

$ vi /etc/my.cnf

[client-server]
socket=/new_place/mysql/mysql.sock
port=3370  // we can change default port also

$vi /etc/selinux/config 

$ SELINUX=enforcing => disabled // turn off SELLinux

6. Show grant of user

SHOW GRANTS FOR 'myuser'@localhost; 

7. Drop user

DROP USER 'vcare'@'%';

8. List of User

select user,host from mysql.user;