Install multiple MySQL instances on a Linux server -use a separate MySQL configuration file

curl -L -O https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-el7-x86_64.tar

[root]# rpm -qa|grep mysql
[root]# rpm -qa |grep mariadb-libs
yum remove mariadb-libs -y

tar -xvf mysql-8.0.23-el7-x86_64.tar

[root]# groupadd mysql
[root]# useradd -r -g mysql -s /bin/false mysql

[root]# cd /usr/local/
[root local]# tar xzvf /root/mysql-8.0.23-el7-x86_64.tar.gz

sudo ln -s /usr/local/mysql-8.0.23-el7-x86_64/ mysql

mkdir -p /mysql/{3306,3307,3308,3309}/data
chown -R mysql:mysql /mysql
cd /mysql

Example 1 (3306)

[mysqld] port = 3306
basedir=/usr/local/mysql/
datadir=/mysql/3306/data
lower_case_table_names=1
innodb_buffer_pool_size=128M
socket=/tmp/mysql_3306.sock
log_error=/mysql/3306/data/mysql06.log

Example 2 (3307)

[mysqld] port = 3307
basedir=/usr/local/mysql/
datadir=/mysql/3307/data
lower_case_table_names=1
innodb_buffer_pool_size=128M
socket=/tmp/mysql_3307.sock
log_error=/mysql/3307/data/mysql07.log

Example 3 (3308)

[mysqld] port = 3308
basedir=/usr/local/mysql/
datadir=/mysql/3308/data
lower_case_table_names=1
innodb_buffer_pool_size=128M
socket=/tmp/mysql_3308.sock
log_error=/mysql/3308/data/mysql08.log

3306
/usr/local/mysql/bin/mysqld –defaults-file=/mysql/3306/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3306/data

3307
/usr/local/mysql/bin/mysqld –defaults-file=/mysql/3307/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3307/data

3308
/usr/local/mysql/bin/mysqld –defaults-file=/mysql/3308/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3308/data

ln -s /usr/local/mysql/bin/mysqld /usr/bin

[root]# vi /etc/profile
or vi .bash_profile
# Add the following information at the end of the file
export PATH=$PATH:/usr/local/mysql/bin

#Make environment variables take effect
[root]# source /etc/profile

# After testing, some of the file permissions newly generated by mysql during initialization are root, so it is best to re-authorize the datadir path to mysql before starting
chown -R mysql:mysql /mysql

nohup /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3306/my.cnf –user=mysql &
nohup /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3307/my.cnf –user=mysql &
nohup /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3308/my.cnf –user=mysql &

ps -ef| grep mysql

netstat -ntl

#Change password for all instance one by one:
mysql -S /tmp/mysql_3306.sock -p
#enter temp password from log:

alter user ‘root’@’localhost’ identified by ‘Not_so_secure1!’;

to shutdown an instance
[[email protected] bin]# pwd
/usr/local/mysql/bin
[[email protected] bin]# ./mysqladmin -h127.0.0.1 -uroot -p -P3306 shutdown
Enter password:

[email protected] bin]# ps -ef| grep mysql
mysql 22218 21246 0 11:56 pts/1 00:00:03 /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3307/my.cnf –user=mysql
mysql 22259 21246 0 11:56 pts/1 00:00:03 /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3308/my.cnf –user=mysql
root 22673 21537 0 12:24 pts/2 00:00:00 mysql -S /tmp/mysql_3306.sock -p
root 22705 21537 0 12:26 pts/2 00:00:00 grep –color=auto mysql

 7,471 views last month,  4 views today

About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on [email protected]

9 comments

  1. MARCOS MELCHIORRI

    Excelente trabajo, Segui los pasos y la instalacion fue perfecta.
    Muchas gracias.

  2. Hi
    When I execute below command showing error

    /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3306/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3306/data

    Error-:
    /usr/local/mysql/bin/mysqld –defaults-file=/mysql/3306/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3306/data
    2021-04-08T10:40:23.297292Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.23) starting as process 769822
    2021-04-08T10:40:23.301373Z 0 [Warning] [MY-010091] [Server] Can’t create test file /usr/local/mysql-8.0.23-el7-x86_64/data/mysqld_tmp_file_case_insensitive_test.lower-test
    2021-04-08T10:40:23.301388Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.23-el7-x86_64/data/ is case insensitive
    2021-04-08T10:40:23.301420Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!
    2021-04-08T10:40:23.301492Z 0 [ERROR] [MY-010119] [Server] Aborting
    2021-04-08T10:40:23.301689Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server – GPL.

    Please guide me .

  3. Same here,
    I am also facing the issue after executing the command
    “/usr/local/mysql/bin/mysqld –defaults-file=/mysql/3306/my.cnf –initialize –basedir=/usr/local/mysql/ –datadir=/mysql/3306/data”

    Error:
    [email protected]:/mysql/3306# /usr/local/mysql/bin/mysqld defaults-file=/mysql/3306/my.cnf initialize basedir=/usr/local/mysql/ datadir=/mysql/3306/data
    2021-05-24T12:37:45.882735Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.23) starting as process 13420
    2021-05-24T12:37:45.886120Z 0 [Warning] [MY-010091] [Server] Can’t create test file /usr/local/mysql-8.0.23-el7-x86_64/data/mysqld_tmp_file_case_insensitive_test.lower-test
    2021-05-24T12:37:45.886133Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql-8.0.23-el7-x86_64/data/ is case insensitive
    2021-05-24T12:37:45.886165Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root!
    2021-05-24T12:37:45.886630Z 0 [ERROR] [MY-010119] [Server] Aborting
    2021-05-24T12:37:45.886794Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server – GPL.

Leave a Reply