MYSQL====================================================
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
1. Restart/Stop MYSQL service in RELHAT/CENTOS
//Start MySQL
# systemctl start mysqld
//Stop mysql
# systemctl stop mysqld
//Check mysql status
# systemctl status mysqld
// 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)
We can change this policy by command line below:
# SET GLOBAL validate_password.length = 6;
2.3 Change password
#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;
9. Backup Mariab DB Tool
-- Full Backup
mariabackup --backup --target-dir=xxx --user=xxx --password=xxx
-- Single Table Backup
mariadb-dump --user=xxx --password =xxx --lock-tables --databases xxx> /data/backup/db1.sql
Home
Previous Article
