MySQL - ycTIN

How to Install MySQL 8.0 on RHEL/CentOS 7

August 7, 2019August 7, 2019 Timmy Tin Leave a comment

Basic Server Setup

# update system
sudo yum install epel-release -y
sudo yum update -y

# update system

sudo yum install epel-release -y

sudo yum update -y

# temporary switches off SELinux enforcing 
sudo setenforce 0

1 2	# temporary switches off SELinux enforcing sudo setenforce 0

# setup timezone
sudo rm /etc/localtime
sudo ln -s /usr/share/zoneinfo/Hongkong /etc/localtime

# setup ntp service
sudo yum -y install ntp
sudo ntpdate pool.ntp.org
sudo systemctl enable ntpd
sudo systemctl start ntpd

# setup timezone

sudo rm /etc/localtime

sudo ln -s /usr/share/zoneinfo/Hongkong /etc/localtime

# setup ntp service

sudo yum -y install ntp

sudo ntpdate pool.ntp.org

sudo systemctl enable ntpd

sudo systemctl start ntpd

Setup MySQL 8.0

# adding the MySQL Yum Repository
sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# setup mysql server
sudo yum install mysql-server -y
sudo systemctl enable mysqld
sudo systemctl start mysqld

# adding the MySQL Yum Repository

sudo rpm -Uvh http://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# setup mysql server

sudo yum install mysql-server -y

sudo systemctl enable mysqld

sudo systemctl start mysqld

# get temporary root password
sudo cat /var/log/mysqld.log | grep "temporary password"
# 2016-10-22T06:14:44.222048Z 1 [Note] A temporary password is 
# generated for root@localhost: [TEMPORARY ROOT PASSWORD]

# config mysql server
mysql_secure_installation

# Securing the MySQL server deployment.
# Enter password for user root: [TEMPORARY ROOT PASSWORD]
# The existing password for the user account root has expired. Please set a new password.
# New password: [NEW PASSWORD]
# Re-enter new password: [NEW PASSWORD]
# Change the password for root ? : N
# Remove anonymous users? : Y
# Disallow root login remotely? : Y
# Remove test database and access to it? : Y
# Reload privilege tables now? : Y

# get temporary root password

sudo cat /var/log/mysqld.log | grep "temporary password"

# 2016-10-22T06:14:44.222048Z 1 [Note] A temporary password is

# generated for root@localhost: [TEMPORARY ROOT PASSWORD]

# config mysql server

mysql_secure_installation

# Securing the MySQL server deployment.

# Enter password for user root: [TEMPORARY ROOT PASSWORD]

# The existing password for the user account root has expired. Please set a new password.

# New password: [NEW PASSWORD]

# Re-enter new password: [NEW PASSWORD]

# Change the password for root ? : N

# Remove anonymous users? : Y

# Disallow root login remotely? : Y

# Remove test database and access to it? : Y

# Reload privilege tables now? : Y

Setup Firewall (Optional)

# setup firewall
sudo yum install firewalld -y
sudo systemctl enable firewalld
sudo systemctl start firewalld
sudo firewall-cmd --add-service=mysql --permanent

# setup firewall

sudo yum install firewalld -y

sudo systemctl enable firewalld

sudo systemctl start firewalld

sudo firewall-cmd --add-service=mysql --permanent

Reboot after Setup

reboot

reboot

Optional Settings for MySQL 8.0

Disable MySQL Strict Mode

# disable MySQL strict mode
vi /etc/my.cnf
sql-mode=
sudo systemctl restart mysqld

# disable MySQL strict mode

vi /etc/my.cnf

sql-mode=

sudo systemctl restart mysqld

Reference: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Fix MySQL Password Expired

To establish a global policy such that passwords never expire.

Update /etc/my.cnf

[mysqld]
default_password_lifetime=0

1 2	[mysqld] default_password_lifetime=0

Reference: https://dev.mysql.com/doc/refman/8.0/en/password-management.html#password-expiration-policy

How to fix MySQL connection error: Authentication plugin ‘caching_sha2_password’ cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

May 14, 2018May 14, 2018 Timmy Tin Leave a comment

The problem is some MySQL clients not yet support new password encryption method.

Just change the user password encryption back to legacy version, then problem will be fixed.

The following SQL update specific user to legacy encryption:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';

1	ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';

Also, you can change the default authentication plugin to legacy version.

Update /etc/my.cnf and add the following line after [mysqld]:

[mysqld]
default_authentication_plugin=mysql_native_password

1 2	[mysqld] default_authentication_plugin=mysql_native_password

MySQL ERROR 1366 (HY000): Incorrect integer value: ” for column ‘id’ at row 1 Problem & Fix

March 12, 2017March 12, 2017 Timmy Tin Leave a comment

Problem:

MySQL throw the following error message on insert or update statement executed

ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

1	ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

Why?

Try to update string value into integer column, and strict sql mode is enabled.

Mainly occurred on MySQL 5.x or above.

How to Fix:

1. Disable strict sql mode

Update /etc/my.cnf , and remove STRICT_TRANS_TABLES from sql-mode

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

References:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables

Most Simple and Fastest Way to Backup and Restore a MySQL Database (mysqldump & mysql client)

February 8, 2017March 12, 2017 Timmy Tin Leave a comment

How to mysqldump and mysql to backup and restore a database:

Backup to .sql file

mysqldump -u DATABASE_USER –p DATABASE_NAME > DATABASE_NAME.sql

1	mysqldump -u DATABASE_USER –p DATABASE_NAME > DATABASE_NAME.sql

Restore from .sql file

mysql -u DATABASE_USER –p DATABASE_NAME < DATABASE_NAME.sql

1	mysql -u DATABASE_USER –p DATABASE_NAME < DATABASE_NAME.sql

Suggested to run the command in database server to get the fastest speed.