Configure two MySQL instances on the same machine
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
