Installation of MySQL on Linux use denver#-serverb
Objective: Tarball Installation of MySQL 8 on Linux using OFA architecture
Notes:
This lab can be completed by each student inside ist own VM
we call this instance mysql-advanced
1. Create a new user/group for your MySQL service and add ‘oracle’ group to opc. Usually the is used
the user “mysql”, but because he’s already available we show here how create a new one
shell> sudo groupadd oracle
shell> sudo useradd -r -g oracle -s /bin/false oracle
shell> sudo usermod -a -G oracle opc
2. Close the SSH connection and open a new one to let opc user have the new group.
ssh -i ~/.ssh/id_rsa opc@[server ip]
3. Create new directory structure:
shell> sudo mkdir /mysql/ /mysql/etc /mysql/data
shell> sudo mkdir /mysql/log /mysql/temp /mysql/binlog
4. Extract the tarball in your /mysql folder
shell> cd /mysql/
shell> sudo tar xvf /workshop/linux/mysql-commercial-8.0.17-
linux-glibc2.12-x86_64.tar/mysql-commercial-8.0.17-linux-
glibc2.12-x86_64.tar.xz
5. Create a symbolic link to datadir (this help when you upgrade the software)
shell> sudo ln -s mysql-commercial-8.0.17-linux-glibc2.12-x86_64
bin
6. Create a new configuration file my.cnf inside /mysql/etc with these lines inside
(e.g. with “sudo vi /mysql/etc/my.cnf” or “sudo nano /mysql/etc/my.cnf”)
[mysqld]
# General configurations
port=3307
mysqlx_port=33070
server_id=10
socket=/mysql/temp/mysql.sock
user=oracle
# File locations
basedir=/mysql/bin
plugin-dir=/mysql/bin/lib/plugin
datadir=/mysql/data
tmpdir=/mysql/temp
log-error=/mysql/log/err_log.log
general_log_file=/mysql/log/gl_log.log
slow_query_log_file=/mysql/log/sq_log.log
# Maximum limits
max-connections=200
open-files-limit=5000
# Security setting for file load
secure-file-priv=/mysql/data
# InnoDB settings
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=512M
# MyISAM settings
key_buffer_size=124M
# Enable binary logs
log-bin=/mysql/binlog/binlog
binlog-format=row
sync_binlog = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=on
enforce-gtid-consistency=true
# Performance Monitor
performance_schema_consumer_events_statements_history_long = ON
# Plugin load example (MySQL Enterprise Thread Pool)
plugin-load=thread_pool.so
7. For security reasons change ownership and permissions
shell> sudo chown -R oracle:oracle /mysql
shell> sudo chmod -R 750 /mysql
8. initialize your database and retrieve
shell> sudo /mysql/bin/bin/mysqld
--defaults-file=/mysql/etc/my.cnf --initialize --user=oracle
9. Start your new mysql instance
shell> sudo /mysql/bin/bin/mysqld
--defaults-file=/mysql/etc/my.cnf --user=oracle &
10. Verify that process is running
shell> ps -ef | grep mysqld
shell> netstat -an | grep 3307
11. Another way is search the message “ready for connections” in error log as one of the last
shell> netstat -an | grep 3307
12. Retrive root password for first login
shell> sudo grep 'temporary password' /mysql/log/err_log.log
13. Before version 5.7 it was recommended to run the ' mysql_secure_installation ' script. From version
5.7 all these settings are “by default”, but the script can be used also to setup the validate_password
plugin (used later). Execute now mysql_secure_installation
shell> /mysql/bin/bin/mysql_secure_installation -P3307 -
h127.0.0.1
using these values
root password: retrieved from previous step
new password: Welcome1!
setup VALIDATE PASSWORD component: Y
password validation policy: 2
Change the password for root: N
Remove anonymous users: Y
Disallow root login remotely: Y
Remove test database: Y
Reload privilege tables now: Y
14. Login to you mysql-advanced installation and check the status (you will be asked to change
password)
shell> mysql -uroot -p -h 127.0.0.1 -P 3307
mysql> status
15. Create a new administrative user called 'admin' with remote access and full privileges
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'Welcome1!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT
OPTION;
16. Check whether the commercial Thread Pool Plugin is loaded an active:
mysql> select * from information_schema.plugins where plugin_name
like 'thread%';
mysql> select * from information_schema.plugins where plugin_name
like 'thread%'\G
1. Shutdown and restart:
shell> /mysql/bin/bin/mysqladmin -uadmin -h127.0.0.1 -p -P3307
shutdown
shell> sudo /mysql/bin/bin/mysqld
--defaults-file=/mysql/etc/my.cnf --user=oracle &
