mysql多实例_Mysql多实例配置

Mysql多实例配置

1.什么是多实例?

在linux系统中代表：多个进程+多个线程+多个预分配内存结构

一般用来测试环境中，测试主从，高可用等。

多实例配置方案：(多个数据库管理系统)

端口port分开

配置文件分开

socket分开

日志分开

多serverid

都可以使用mysql用户

#总结，初始化多次数据库

2.多实例实战

#1.创建多个数据目录

[root@db01 ~]# mkdir /data/{3307,3308,3309} -p

[root@db01 data]# tree /data

/data

├── 3307

├── 3308

└── 3309

#2.准备多个配置文件

[root@db01 data]# vim /data/3307/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3307/data

port=3307

socket=/data/3307/mysql.sock

log-error=/data/3307/data/mysql.err

log-bin=/data/3307/data/mysql-bin

server_id=7

-------------------------------------------

[root@db01 data]# vim /data/3308/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3308/data

port=3308

socket=/data/3308/mysql.sock

log-error=/data/3308/data/mysql.err

log-bin=/data/3308/data/mysql-bin

server_id=8

--------------------------------------------

[root@db01 data]# vim /data/3309/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3309/data

port=3309

socket=/data/3309/mysql.sock

log-error=/data/3309/data/mysql.err

log-bin=/data/3309/data/mysql-bin

server_id=9

---------------------------------------------

[root@db01 data]# tree /data/

/data/

├── 3307

│   └── my.cnf

├── 3308

│   └── my.cnf

└── 3309

└── my.cnf

#3.初始化多套数据目录

3307:

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

3308:

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data

3309:

[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

#### 在5.7版本中 mysqld --initialize-insecure 进行初始化

#4.授权

[root@db01 scripts]# chown -R mysql.mysql /data

#5.启动MySQL多实例

[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf &

[root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &

[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &

#6.关闭多实例

[root@db01 scripts]# mysqladmin -S /data/3307/mysql.sock shutdown

[root@db01 scripts]# mysqladmin -S /data/3308/mysql.sock shutdown

[root@db01 scripts]# mysqladmin -S /data/3309/mysql.sock shutdown

#7.检查端口

[root@db01 scripts]# netstat -lntup|grep 330

tcp6 0 0 :::3307 :::* LISTEN 25550/mysqld

tcp6 0 0 :::3308 :::* LISTEN 25722/mysqld

tcp6 0 0 :::3309 :::* LISTEN 25894/mysqld

#8.设置多实例密码

[root@db01 scripts]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'

[root@db01 scripts]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'

[root@db01 scripts]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'

#9.验证库连接( 查看server_id )

[root@db01 scripts]# mysql -uroot -p3307 -S /data/3307/mysql.sock -e "show variables like 'server_id';"Warning: Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 7 |

+---------------+-------+

[root@db01 scripts]# mysql -uroot -p3308 -S /data/3308/mysql.sock -e "show variables like 'server_id';"Warning: Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 8 |

+---------------+-------+

[root@db01 scripts]# mysql -uroot -p3309 -S /data/3309/mysql.sock -e "show variables like 'server_id';"Warning: Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 9 |

+---------------+-------+

连接的小技巧:

[root@db01 scripts]# vim /usr/bin/mysql3309

mysql -uroot -p3309 -S /data/3309/mysql.sock

[root@db01 scripts]# vim /usr/bin/mysql3308

mysql -uroot -p3308 -S /data/3308/mysql.sock

[root@db01 scripts]# vim /usr/bin/mysql3307

mysql -uroot -p3307 -S /data/3307/mysql.sock

[root@db01 scripts]# chmod +x /usr/bin/mysql*

连接:[root@db01 scripts]# mysql3309