MySQL 多实例安装

Check glibc version on your Linux Server(here is glibc-2.28)

rpm -qa| grep glibc

glibc-langpack-en-2.28-225.el8_8.11.x86_64
glibc-gconv-extra-2.28-225.el8_8.11.i686
glibc-2.28-225.el8_8.11.i686
glibc-devel-2.28-225.el8_8.11.x86_64
glibc-common-2.28-225.el8_8.11.x86_64
glibc-2.28-225.el8_8.11.x86_64
glibc-headers-2.28-225.el8_8.11.x86_64
glibc-gconv-extra-2.28-225.el8_8.11.x86_64

Login in oracle support

https://support.oracle.com/portal/

Note: we should make sure glibc version is same with server’s

Check and cleanup existed MySQL or MariaDB

systemctl stop mysqld
rpm -qa |grep mysql  |xargs yum -y remove
rpm -qa |grep mariadb |xargs yum -y remove
find / -name mysql |xargs rm -rf
mv /etc/my.cnf /tmp

Create service user for mysql

useradd mysql 
passwd mysql
chage -l mysql
chage -M 99999 mysql

Create folder for software and mysql

mkdir -p /opt/software
mkdir -p /u01/app/binaries
mkdir -p /u01/app/data/mysql/{data,mysql_scripts,mysql_backup,tmp,mysql-files}
mkdir -p /u01/app/data/mysql/logs/binlog
mkdir -p /u01/app/data/mysql/logs/others
mkdir -p /var/log/mysqlbackup

Upload downloaded tar ball under /opt/software and uncompressed

cd /opt/software
tar -xf mysql-commercial-8.4.2-linux-glibc2.28-x86_64.tar.xz

Create soft link for uncompressed folder

ln -s /opt/software/mysql-commercial-8.4.2-linux-glibc2.28-x86_64 /usr/local/mysql

Create folders and change owner

chown -R mysql.mysql /u01/app/data/mysql
chown -R mysql.mysql /usr/local/mysql
chown -R mysql.mysql /var/log/mysqlbackup
chmod 750 /u01/app/data/mysql/mysql-files

Create config file for mysql

vi /etc/my.cnf
[mysqld@servicename]
user = mysql
server_id = 1
port = 3306
log_timestamps=SYSTEM
pid-file=/u01/app/data/mysql/data/mysqld.pid
mysqlx_socket=/u01/app/data/mysql/data/mysqlx.sock
socket=/u01/app/data/mysql/data/mysql.sock
default_storage_engine = Innodb

datadir = /u01/app/data/mysql/data
basedir=/usr/local/mysql
tmpdir = /u01/app/data/mysql/tmp
general_log=0
general_log_file=/u01/app/data/mysql/logs/others/general.log
log-error = /u01/app/data/mysql/logs/others/error.log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /u01/app/data/mysql/logs/others/slow-queries.log

#binlog保留时间 默认s
binlog_expire_logs_seconds = 259200
log-bin = /u01/app/data/mysql/logs/binlog/mysql-bin.log
character_set_server = utf8mb4
max_allowed_packet = 73400320
lower_case_table_names = 1
autocommit = on
transaction_isolation = READ-COMMITTED
open_files_limit = 65535
lock_wait_timeout = 3600
max_connections = 1000

# innodb settings #
innodb_data_file_path = ibdata1:12M;ibdata2:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 4
innodb_redo_log_capacity=5368709120
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT

skip-grant-tables = FLASE

Initialize mysql(Single instance and Multi instance)

cd /usr/local/mysql

./bin/mysqld --no-defaults --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/u01/app/data/mysql/data --lower_case_table_names=1

Configure startup

Multi instance:

Vi /etc/systemd/system/mysqld@weaver
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/u01/app/data/mysql/data/mysqld.pid

ExecStart=/usr/bin/mysqld --defaults-file=/etc/my.cnf --defaults-group-suffix=@%I --daemonize --pid-file=/u01/app/data/mysql/data/mysqld.pid $MYSQLD_OPTS

PermissionsStartOnly=true

Single instance:

cp ./support-files/mysql.server /et/init.d/mysqld

chkconfig -add mysqld

Start MySQL service

systemctl daemon-reload
systemctl start mysqld@servicename
systemctl start mysqld

Set environment variables

vim /etc/profile

MYSQL_HOME=/usr/local/mysql
PATH=$MYSQL_HOME/bin/:$PATH
export MYSQL_HOME
export PATH

source /etc/profile

Get root password,connect and modify root password

shell>cat /u01/app/data/mysql/logs/error.log |grep password
shell>mysql -uroot -p'ct-<4rguatgJ'
mysql> alter user user() identified by '********';
mysql> flush privileges;