mysql多实例my.cnf_mysql多实例配置

多实例MYSQL 这是转载的

优点:分开便于管理,使用多配置文件方式

缺点:争夺资源

#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

#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]# 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

#7.设置多实例密码

[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'

#8.验证库连接( 查看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

这是自己实验总结的 mysql 5.5.62

1.安装mysql 5.5

2. 拷备配置文件范本 创建 /server/app/mysql/3306 3307 文件夹及里的LOG DATA 目录

cp /usr/local/mysql/support-files/my-medium.cnf /server/app/mysql/3306/my.cnf

修改如下

[client]

port = 3306

socket = /server/app/mysql/3306/mysql.sock

[mysqld]

port = 3306

socket = /server/app/mysql/3306/mysql.sock

##mysql安装目录

basedir = /usr/local/mysql

datadir = /server/app/mysql/3306/data

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

skip-name-resolve

log-bin=mysql-bin

binlog_format=mixed

max_binlog_size = 500M

server-id = 1

[mysqld_safe]

log-error=/server/app/mysql/3306/log/ilanni.err

pid-file=/server/app/mysql/3306/log/ilanni.pid

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

3.同样方法做3307,配置文件用3306的拷备过去, 替换3306 为3307

sed -i 's#3306#3307#g' my.cnf

4. 初始化数据库

**##这里需要注意 创建的目录需要 chown -R mysql.mysql

##这里需要注意 mysql_install_db 有可能没有权限 命令前加sh

##这里需要注意 有可能log文件需要手动创建**

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/server/app/mysql/3306/data --user=mysql

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/server/app/mysql/3306/data --user=mysql

##这里需要注意 初始化完成后,再次 chown -R mysql.mysql

5.启动多实例

##不加& 会一直提示在运行,没法ctrl+C

/usr/local/mysql/bin/mysqld_safe --defaults-file=/server/app/mysql/3306/my.cnf &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/server/app/mysql/3307/my.cnf &

#检查开启状态

netstat -tnulp |grep mysqld

6.登陆多实例,默认没有密码

##-S 指定socket

mysql -uroot -p -S /server/app/mysql/3306/mysql.sock

mysql -uroot -p -S /server/app/mysql/3307/mysql.sock

进去测试....