mysql多实例创建后修改密码 mysql单机多实例

转载

mob6454cc6bcf40 2023-10-26 22:16:30

文章标签 mysql多实例创建后修改密码 mysql单机多实例配 mysql sed d3 文章分类 MySQL 数据库

第二种方案：多个实例共用同一个my.cnf配置文件

datadir: /mydata/data/3306

/mydata/data/3307

my.cnf: /etc/my.cnf

一点基础知识：MySQLd_multi

要配置MySQL多实例，首先我们需要了解一下mysqld_multi这个脚本。mysqld_multi是管理多个mysqld的服务进程。这些服务进程用不同的unix socket或是监听于不同的端口，通过简单的命令，它可以启动，关闭和报告所管理的服务器的状态。

我们看一看官方的说明：

mysqld_multi is designedto manage several mysqld processes that listen for connections on differentUnix socket files and TCP/IP ports. It can start or stop servers, or reporttheir current status.

--如果我们的一个节点上有多个mysql，可以使用mysqld_multi 来管理。

mysqld_multi searchesfor groups named [mysqldN] in my.cnf (or in the file named by the --config-fileoption). N can be any positive integer. This number is referred to in thefollowing discussion as the option group number, or GNR. Group numbersdistinguish option groups from one another and are used as arguments to mysqld_multito specify which servers you want to start, stop, or obtain a status reportfor. Options listed in these groups are the same that you would use in the [mysqld]group used for starting mysqld. (See, for example, Section 2.10.1.2, “Startingand Stopping MySQL Automatically”.) However, when using multiple servers, it isnecessary that each one use its own value for options such as the Unix socketfile and TCP/IP port number. For more informationon which options must beunique per server in a multiple-server environment, see Section 5.6, “RunningMultiple MySQL Instances on One Machine”.

-- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。

由于前面的步骤与上述方式相同，所以这里只是简述一下：

### 添加mysql用户和组

[root@localhost ~]# groupadd -r mysql

[root@localhost ~]# useradd -r -g mysql -s /sbin/nologin mysql

### 目录规划

[root@localhost ~]# mkdir -pv /mydata/data/330{6,7}

mkdir: created directory `/mydata'

mkdir: created directory `/mydata/data'

mkdir: created directory `/mydata/data/3306'

mkdir: created directory `/mydata/data/3307'

[root@localhost ~]# chown -R mysql:mysql /mydata/data/3306

[root@localhost ~]# chown -R mysql:mysql /mydata/data/3307

### 解压

[root@localhost ~]# tar xf mysql-5.5.38-linux2.6-i686.tar.gz -C /usr/local/src

### 创建链接

[root@localhost local]# ln -sv src/mysql-5.5.38-linux2.6-i686 mysql

`mysql' -> `src/mysql-5.5.38-linux2.6-i686'

### 把bin添加到PATH

[root@localhost mysql]# vi /etc/profile.d/mysql.sh

export PATH=$PATH:/usr/local/mysql/bin

[root@localhost mysql]# . /etc/profile.d/mysql.sh

提供配置文件：/etc/my.cnf

[root@localhost ~]# cd /usr/local/mysql

[root@localhost mysql]# cp support-files/my-small.cnf /etc/my.cnf

[root@localhost mysql]# vi /etc/my.cnf

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

# Here follows entries for some specific programs

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /mydata/data/mysqld_multi.log

user = root

[mysqld3306]

port = 3306

socket = /tmp/mysqld_3306.sock

pid-file = /mydata/data/3306/mysqld.pid

datadir = /mydata/data/3306

basedir = /usr/local/mysql

lc-messages-dir = /usr/local/mysql/share/english

### These support master - master replication

#auto-increment-increment = 4

#auto-increment-offset = 1 # Since it is master 1

# log-bin = /data/mysql/binlogs/bin-log-mysqld1

# log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index

#binlog-do-db = # Leave this blank if you want to control it on slave

# max_binlog_size = 1024M

[mysqld3307]

port = 3307

socket = /tmp/mysqld_3307.sock

pid-file = /mydata/data/3307/mysqld.pid

datadir = /mydata/data/3307

basedir = /usr/local/mysql

lc-messages-dir = /usr/local/mysql/share/english

### Disable DNS lookups

#skip-name-resolve

### These support master - slave replication

#log-bin = /data/mysql/binlogs/bin-log-mysqld2

#log-bin-index = /data/mysql/binlogs/bin-log-mysqld2.index

#binlog-do-db = # Leave this blank if you want to control it on slave

#max_binlog_size = 1024M

### Relay log settings

#relay-log = /data/mysql/log/relay-log-mysqld2

#relay-log-index = /data/mysql/log/relay-log-mysqld2.index

#relay-log-space-limit = 4G

### Slow query log settings

#log-slow-queries = /data/mysql/log/slow-log-mysqld2

#long_query_time = 2

#log-queries-not-using-indexes

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

pid-file = /mydata/data/mysqld.pid

datadir = /mydata/data

basedir = /usr/local/mysql

lc-messages-dir = /usr/local/mysql/share/english

skip-external-locking

key_buffer_size = 16K

max_allowed_packet = 1M

table_open_cache = 4

sort_buffer_size = 64K

read_buffer_size = 256K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

### Incrase the max connections

max_connections = 200

### Set expiration time for logs, including binlogs

expire_logs_days = 14

### set the character as utf8

character-set-server = utf8

collation-server = utf8_unicode_ci

#skip-networking

server-id = 1

# Uncomment the following if you want to log updates

#log-bin=mysql-bin

# binary logging format - mixed recommended

#binlog_format=mixed

### set engine

default-storage-engine = INNODB

## enable per table data for innodb to shrink ibdata

innodb_file_per_table = 1

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /usr/local/mysql/data

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /usr/local/mysql/data

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 16M

innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 5M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

[mysqlhotcopy]

interactive-timeout

[mysql.server]

user = mysql

[mysqld_safe]

log-error = /mydata/data/mysqld.log

pid-file = /mydata/data/mysqld.pid

open-files-limit = 8192

初始化实例：

### mysqld3306

[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3306 --user=mysql

### mysqld3307

[root@localhost mysql]# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/mydata/data/3307 --user=mysql

提供多实例管理脚本：

[root@localhost mysql]# cp support-files/mysqld_multi.server /etc/init.d/

[root@localhost mysql]# vi /etc/init.d/mysqld_multi.server

#!/bin/sh

# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.

# This script assumes that my.cnf file exists either in /etc/my.cnf or

# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the

# mysqld_multi documentation for detailed instructions.

# This script can be used as /etc/init.d/mysql.server

# Comments to support chkconfig on RedHat Linux

# chkconfig: 2345 64 36

# description: A very fast and reliable SQL database engine.

# Version 1.0

basedir=/usr/local/mysql

bindir=/usr/local/mysql/bin

conf = /etc/my.cnf

export PATH=$PATH:$bindir

if test -x $bindir/mysqld_multi

then

mysqld_multi="$bindir/mysqld_multi";

else

echo "Can't execute $bindir/mysqld_multi from dir $basedir";

exit;

case "$1" in

'start' )

"$mysqld_multi" --defaults-extra-file=$conf start $2

;;

'stop' )

"$mysqld_multi" --defaults-extra-file=$conf stop $2

;;

'report' )

"$mysqld_multi" --defaults-extra-file=$conf report $2

;;

'restart' )

"$mysqld_multi" --defaults-extra-file=$conf stop $2

"$mysqld_multi" --defaults-extra-file=$conf start $2

;;

echo "Usage: $0 {start|stop|report|restart}" >&2

;;

esac

管理实例：

-- mysqld_multi 会在my.cnf 里搜索mysqldN的参数配置。

同时启动mysqld3306, mysqld3307

[root@localhost mysql]# /etc/init.d/mysqld_multi.server start 3306,3307

### 我们看到两个实例已经启动成功

[root@localhost mysql]# netstat -tulpn | grep -i mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2876/mysqld

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2877/mysqld

同时关闭mysqld3306, mysqld3307

### 先不要操作哈，等测试登录后再关闭

[root@localhost mysql]# /etc/init.d/mysqld_multi.server stop 3306,3307

登录实例：

### 注意，因为这里mysql的root用户并没有设置密码，所以可以直接登录

### 登录mysqld3306

[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3306.sock

### 登录mysqld3307

[root@localhost mysql]# mysql -uroot -S /tmp/mysqld_3307.sock

### 或者

[root@localhost mysql]# mysql -uroot -h127.0.0.1 -P3306

总结：

这里实验仅配置2个实例，只要你的机器足够强劲，那么可以配置更多的实例。

本实验仅做了初步的设置，mysql的root用户密码，以及其他匿名用户需要手动去设置或删除

非常困扰的是如何手动指定配置文件，以及Mysql读取配置文件的顺序

MySQL自带了几个不同的配置文件，放置在/opt/mysql/support-files目录下，分别是my-huge.cnf,my-innodb-heavy-4G.cnf,my-large.cnf,my-medium.cnf,my-small.cnf，通过名称我们可以很直观的了解到他们是针对不同的服务器配置的，本文中仅有的一点关于InnoDB的配置，是取自于my-small.cnf的，因为我是在虚拟机上进行的设置；在生产环境中，我们可以通过参考my-huge.cnf或my-innodb-heavy-4G.cnf中的部分参数配置，来对服务器进行优化；

关于MySQL缓存参数的优化，主要用于提升I/O能力。

在单机运行多实例的情况下，切忌使用 mysql -hlocalhost 或直接忽略-h参数登陆服务器，这应该算是MySQL的一个bug，就是如果使用localhost或忽略-h参数，而不是指定127.0.0.1的话，即使选择的端口是3307，还是会登陆到3306中去，因此应尽量避免这种混乱的产生，统一用127.0.0.1绑定端口或采用socket 来登陆；

问题：第二种方案 my.cnf 配置文件中，为什么还要配置 [mysqld] 呢？？

还希望各位帮忙解答一下。

mysql多实例创建后修改密码 mysql单机多实例_d3