Setup MySQL 80/57/56/55 in CentOS 7 [or Apache+PHP Web Server CentOS]

This is the ad hoc tutorial on how to setup MySQL 80/57/56/55 in CentOS 7 or Apache+PHP web server in CentOS 7. First we will prepare the system update and configure necessary rpm packages. Next we will install and go through basic configurations of MySQL 8. Finally, we will connect to new created database using MySQL client.

Below content is complementary to the video tutorial above.

Install and configure MySQL (check for repo name https://dev.mysql.com/downloads/repo/yum/):

yum install -y wget nano
wget http://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
yum -y localinstall mysql80-community-release-el7-1.noarch.rpm
yum repolist all | grep mysql

If needed enable the mysql version you want to install:

nano /etc/yum.repos.d/mysql-community.repo

Continue the installation process:

yum install -y mysql-community-server
systemctl start mysqld && systemctl enable mysqld
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation

yum -y update mysql-server
mysqld -V
mysql --version
rm -f mysql*.rpm

Configure MySQL:

nano /etc/my.cnf

Set the following parameters in my.cnf file:

innodb_buffer_pool_size = 512M
join_buffer_size = 256M
tmp_table_size = 128M
max_heap_table_size = 128M
sql-mode=""
slow_query_log=1
general_log=0
log-output=TABLE
default_authentication_plugin=mysql_native_password

Restart the mysql service and configure the firewall:

service mysqld restart
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

Or disable the firewall:

systemctl stop firewalld && systemctl disable firewalld

mysql -u root -p'Adhoctuts2018#'
CREATE DATABASE adhoctuts;
CREATE USER 'adhoctuts'@'192.168.%.%' identified by 'Adhoctuts2018#';
GRANT ALL PRIVILEGES on adhoctuts.* to 'adhoctuts'@'192.168.%.%' ;
CREATE USER 'adhoctuts'@'localhost' identified by 'Adhoctuts2018#';
exit

Execute below step if you install MySQL on Apache+PHP web server to test PHP PDO:

php -r '$con=new PDO("mysql:host=localhost;dbname=information_schema;port=3306;charset=utf8", "adhoctuts", "Adhoctuts2018#");$stmt = $con->prepare("select version() as MYSQL_VERSION, now() as TIME");$stmt->execute();$stmt->setFetchMode(PDO::FETCH_ASSOC);var_dump($stmt->fetch());'

Download and install HeidiSQL into your PC (or any MySQL client you prefer). Connect and test your newly created database.

PS: If you get Authentication plugin ‘caching_sha2_password’ cannot be loaded error for the user try the following in MySQL console:

ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Related resources: