Shulou(Shulou.com)06/01 Report--

Abstract: in the real environment, in order to make full use of hardware resources, multiple mysql instances may run on the same machine. This paper shares an implementation method.

Original link: https://www.modb.pro/db/22741?cyn

1. Virtual machine ready 1.1. VMware Workstation Pro 12.1

Memory: at least 2G is recommended

Hard disk: at least 40g is recommended, dynamic allocation

1.2. Operating system preparation

The operating system is CentOS 6.564bit

IP:192.168.0.13

Hostname: source

1.3. MySQL software preparation

Mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

two。 Pre-installation system environment configuration adjustment

2.1. Close SELinux

2.2. Turn off the system firewall

2.3. Adjust the Ipaw O scheduling system to deadline mode

2.4. Settings for swap Partition

2.5. Operating system limitations

2.6. Close numa

For this section, please refer to a previously published article to configure

Install mysql 5.7.20 on centos6.5

3.MySQL installation planning

Package directory: / opt/soft

MySQL home directory / usr/local

MySQL data directory / data/mysql1 / data/mysql2

Configuration file directory / etc/my1.cnf / etc/my2.cnf

Instance 1 port 3306

Instance 2 port 3307

4. Create a directory

Mkdir-p / opt/soft

Mkdir-p / data/mysql1

Mkdir-p / data/mysql2

5. Create a MySQL user

Useradd mysql1-s / sbin/nologin

Passwd mysql1

Useradd mysql2-s / sbin/nologin

Passwd mysql2

-password is oracle123

6. Install MySQL softwar

Cd / opt/soft

Tar-xzf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

Mv mysql-5.7.20-linux-glibc2.12-x86_64 / usr/local/mysql-5.7.20-linux-glibc2.12-x86_64_1

Cd / usr/local

Cp-r mysql-5.7.20-linux-glibc2.12-x86_64_1 mysql-5.7.20-linux-glibc2.12-x86_64_2

Ln-s mysql-5.7.20-linux-glibc2.12-x86_64_1 mysql1

Ln-s mysql-5.7.20-linux-glibc2.12-x86_64_2 mysql2

Chown-R mysql1:mysql1 / usr/local/mysql1

Chown-R mysql2:mysql2 / usr/local/mysql2

Chown-R mysql1:mysql1 / data/mysql1

Chown-R mysql2:mysql2 / data/mysql2

7. Edit parameter file

Vi / etc/my1.cnf

[client]

Port = 3306

Socket = / tmp/mysql1.sock

[mysql]

Prompt= "\ u@db\ R:\ m:\ s [\ d] >"

No-auto-rehash

[mysqld]

User = mysql1

Port = 3306

Basedir = / usr/local/mysql1

Datadir = / data/mysql1

Socket = / tmp/mysql1.sock

Pid-file= / tmp/mysql3306.pid

Vi / etc/my2.cnf

[client]

Port = 3307

Socket = / tmp/mysql2.sock

[mysql]

Prompt= "\ u@db\ R:\ m:\ s [\ d] >"

No-auto-rehash

[mysqld]

User = mysql2

Port = 3307

Basedir = / usr/local/mysql2

Datadir = / data/mysql2

Socket = / tmp/mysql2.sock

Pid-file= / tmp/mysql3307.pid

Note: socket parameter is required, otherwise an error is reported when the second instance is started, and the second instance cannot be started.

8.mysql database initialization

Cd / usr/local/mysql1/bin

. / mysqld-initialize-user=mysql1-basedir=/usr/local/mysql1-datadir=/data/mysql1

Cd / usr/local/mysql2/bin

. / mysqld-initialize-user=mysql2-basedir=/usr/local/mysql2-datadir=/data/mysql2

9. Change login password

-Skip the permission table and start the mysql instance

/ usr/local/mysql1/bin/mysqld_safe-user=mysql1-port=3306-socket=/tmp/mysql1.sock-pid-file=/tmp/mysql3306.pid-basedir=/usr/local/mysql1-datadir=/data/mysql1-skip-grant-tables &

/ usr/local/mysql2/bin/mysqld_safe-user=mysql2-port=3307-socket=/tmp/mysql2.sock-pid-file=/tmp/mysql3307.pid-basedir=/usr/local/mysql2-datadir=/data/mysql2-skip-grant-tables &

Connect the mysql instance locally

Mysql-uroot-P 3306-- protocol=tcp-p

Mysql-uroot-P 3307-- protocol=tcp-p

You can log in to the mysql database by skipping the permission table with-skip-grant-tables, starting the mysql instance, and entering the wrong password or empty password when logging in to the database.

Use mysql

Select Host,user,authentication_string,password_expired from user

Update user set password_expired='N' where user='root'

# change the password of a root user to never expire

Update user set authentication_string=password ('oracle123') where user='root'

# change the password of the root user corresponding to localhost to oracle123

Flush privileges

10. Start and shut down an mysql instance

Kill the mysql process and restart the mysql instance

Ps-ef | grep mysql

Kill-9 process number

-start the mysql instance

/ usr/local/mysql1/bin/mysqld_safe-defaults-file=/etc/my1.cnf &

/ usr/local/mysql2/bin/mysqld_safe-defaults-file=/etc/my2.cnf &

The process is as follows:

[root@source] # ps-ef | grep mysqlroot 6449 2826 0 18:09 pts/0 00:00:00 / bin/sh / usr/local/mysql1/bin/mysqld_safe-- defaults-file=/etc/my1.cnfmysql1 6592 6449 3 18:09 pts/0 00:00:00 / usr/local/mysql1/bin/mysqld-- defaults-file=/etc/my1.cnf-- basedir=/usr/local/mysql1-- datadir=/data/mysql1-- plugin-dir=/usr/ Local/mysql1/lib/plugin-user=mysql1-log-error=source.err-pid-file=/tmp/mysql3306.pid-socket=/tmp/mysql1.sock-port=3306root 6622 2826 0 18:09 pts/0 00:00:00 / bin/sh / usr/local/mysql2/bin/mysqld_safe-defaults-file=/etc/my2.cnfmysql2 6765 6622 4 18:09 pts/0 00:00:00 / usr/local/mysql2/bin/mysqld-defaults-file=/ Etc/my2.cnf-- basedir=/usr/local/mysql2-- datadir=/data/mysql2-- plugin-dir=/usr/local/mysql2/lib/plugin-- user=mysql2-- log-error=source.err-- pid-file=/tmp/mysql3307.pid-- socket=/tmp/mysql2.sock-- port=3307root 6797 2826 18:09 pts/0 00:00:00 grep mysql [root@source] #

-close the mysql instance

/ usr/local/mysql1/bin/mysqladmin-defaults-file=/etc/my1.cnf-uroot-poracle123 shutdown

/ usr/local/mysql2/bin/mysqladmin-defaults-file=/etc/my2.cnf-uroot-poracle123 shutdown

11. Create a database account

Create the following users in both instances:

Grant all privileges on. To 'root'@'%' identified by' oracle123'

Flush privileges

twelve。 Connect the mysql instance

Local:

Mysql-uroot-P 3306-- protocol=tcp-p

Mysql-uroot-P 3307-- protocol=tcp-p

Remote:

Mysql-h292.168.0.13-P 3306-uroot-p

Mysql-h292.168.0.13-P 3307-uroot-p

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.