将单实例mysql改双实例安装

【实验背景】:

原mysql为单实例,所有数据及日志文件都放在my3306目录中

现将原只有3306一个实例的mysql改装为3306,3307两实例,同时也对原来的目录进行调整;

【改装思路】

删除原单实例3306的mysql

重新编译3306(因为目录结构改变,所以要重新对3306进行编译)

重新安装3306,3307实例

【多实例目录结构】

新的目录结构如下:

/u01

├── conf

├── logs

├── mydata

├── mysql-5.6.35

├── sh

├── svr

└── tmp

把参数文件,日志文件,参数文件按实例分放

├── logs

│   ├── my3306

│   └── my3307

├── mydata

│   ├── my3306

│   └── my3307

【实验细节】

1.删除原单实例mysql

检查并删除原来的mysql

[mysql@node1 ~]$ rpm -qa|grep mysql [root@node1 ~]# yum remove mysql*

删除原mysql对应的数据,日志等文件,因为之前的这些文件都存在/u01/my3306中,所以直接删除这个目录即可

[root@node1 u01]# rm -rf my3306/

2.创建新的目录

根据双实例的目录布局创建新的目录结构

创建参数文件目录

[root@node1 u01]# mkdir conf [root@node1 u01]# mkdir -p conf/mysql/my{3306,3307}

创建数据文件目录

root@node1 u01]# mkdir -p mydata/my{3306,3307}

创建日志文件目录
 

[root@node1 u01]# mkdir -p logs/my{3306,3307}

[root@node1 u01]# mkdir logs/my3306/binlog iblog

[root@node1 u01]# mkdir logs/my3306/binlog iblog

创建临时文件目录

[root@node1 u01]# mkdir tmp

创建脚本文件,用于存放自己编写的mysql启动,停止等管理脚本

[root@node1 u01]# mkdir sh

创建mysql的程序目录

[root@node1 u01]# mkdir -p svr/mysql5.6

修改文件目录的属主属组为mysql

[root@node1 u01]# chown -R mysql:mysql /u01

3.重新编译mysql

因为之前安装单实例时已下载了mysql5.6的安装文件并解压,所以这里直接进入mysql解压后的安装目录,重新进行编译安装即可.

重新编译时只编译3306实例即可

使用cmake使用配置mysql安装环境

注意编译时要到mysql解压后的mysql-5.6.35目录中执行cmake命令

[root@node1 u01]# cd mysql-5.6.35/

[root@node1 mysql-5.6.35]# cmake \ 
> -DCMAKE_INSTALL_PREFIX=/u01/svr/mysql5.6 \ #指定mysql的程序目录 
> -DINSTALL_DATADIR=/u01/mydata/my3306 \ #指定mysql 3306实例的数据目录 
> -DDEFAULT_CHARSET=utf8 \ 
> -DDEFAULT_COLLATION=utf8_general_ci \ 
> -DEXTRA_CHARSETS=all \ > -DWITH_SSL=yes \ 
> -DWITH_EMBEDDED_SERVER=1 \ 
> -DENABLED_LOCAL_INFILE=1 \ 
> -DWITH_MYISAM_STORAGE_ENGINE=1 \ 
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \ 
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ 
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ 
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \ 
> -DWITH_PARTITION_STORAGE_ENGINE=1 \ 
> -DMYSQL_UNIX_ADDR=/tmp/mysql3306.sock \ 
> -DMYSQL_TCP_PORT=3306 \ > -DENABLED_LOCAL_INFILE=1 \ 
> -DSYSCONFDIR=/etc \ 
> -DOPENSSL_ROOT_DIR=/usr/local/openssl \ 
> -DOPENSSL_LIBRARIES=/usr/local/openssl/lib \ 
> -DWITH_READLINE=on

执行make命令对mysql进行预编译

[mysql@node1 mysql-5.6.35]$ make

使用make install的目的是把编译好的文件复制到指定目录中

[mysql@node1 mysql-5.6.35]$ make install

4.安装3306实例

进行3306的配置目录创建3306实例的参数文件

[root@node1 my3306]# pwd /u01/conf/mysql/my3306 
[root@node1 my3306]# ll total 4 -rwxr-xr-x 1 mysql mysql 2176 Mar 25 01:19 my.cnf

参数文件my.cnf的内容如下:

[root@node1 my3306]# cat my.cnf 
[client]
port=3306
socket=/tmp/my3306.sock

[mysql]
pid_file=/u01/mydata/my3306/mysqld.pid

[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
sha256_password_private_key_path=/u01/mydata/my3306/mykey.pem
sha256_password_public_key_path=/u01/mydata/my3306/mykey.pub

# system
basedir=/u01/svr/mysql5.6
datadir=/u01/mydata/my3306
max_allowed_packet=1g
max_connections=214
max_user_connections=2800
#open_files_limit=65535
open_files_limit=1024
pid_file=/u01/mydata/my3306/mysqld.pid
port=3306
server_id=101
skip_name_resolve=ON
socket=/tmp/mysql3306.sock
tmpdir=/u01/tmp

#binlog
log_bin=/u01/logs/my3306/binlog/mysql-bin
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=1

#logging
log_error=/u01/logs/my3306/error3306.log
slow_query_log_file=/u01/logs/my3306/slow3306.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/u01/logs/my3306/relaylog
relay_log_index=/u01/logs/my3306/relay.index
relay_log_info_file=/u01/logs/my3306/relay-log.info

#slave
slave_load_tmpdir=/u01/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/u01/logs/my3306/iblog
innodb_log_group_home_dir=/u01/logs/my3306/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=104857600
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=600
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld_safe]
datadir=/u01/mydata/my3306

进入/u01/svr/mysql5.6目录中执行安装命令

[root@node1 svr]# cd mysql5.6/ 
[mysql@node1 mysql5.6]$ ./scripts/mysql_install_db --defaults-file=/u01/conf/mysql/my3306/my.cnf --datadir=/u01/mydata/my3306 --user=mysql

如查安装中出现问题可以查看3306的错误日志/u01/logs/my3306/error3306.log

 

5.安装3307实例

把3306的参数文件复制到3307对应目录中

[root@node1 mysql]# cp -rf my3306/* my3307/ 
[root@node1 mysql]# chown -R mysql:mysql my3307

vim打开复制到3307中的my.cnf中把所有的3306替换为3307

可在vim中使用以下命令进行全文替换

:%s/3306/3307/g

替换后内容如下:

[client]
port=3307
socket=/tmp/my3307.sock

[mysql]
pid_file=/u01/mydata/my3307/mysqld.pid

[mysqld]
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
sha256_password_private_key_path=/u01/mydata/my3307/mykey.pem
sha256_password_public_key_path=/u01/mydata/my3307/mykey.pub

# system
basedir=/u01/svr/mysql5.6
datadir=/u01/mydata/my3307
max_allowed_packet=1g
max_connections=3000
max_user_connections=2800
open_files_limit=65535
pid_file=/u01/mydata/my3307/mysqld.pid
port=3307
server_id=101
skip_name_resolve=ON
socket=/tmp/mysql3307.sock
tmpdir=/u01/tmp

#binlog
log_bin=/u01/logs/my3307/binlog/mysql-bin
binlog_cache_size=32768
binlog_format=row
expire_logs_days=7
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=1

#logging
log_error=/u01/logs/my3307/error3307.log
slow_query_log_file=/u01/logs/my3307/slow3307.log
log_queries_not_using_indexes=0
slow_query_log=1
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1

#relay
relay_log=/u01/logs/my3307/relaylog
relay_log_index=/u01/logs/my3307/relay.index
relay_log_info_file=/u01/logs/my3307/relay-log.info

#slave
slave_load_tmpdir=/u01/tmp
slave_skip_errors=OFF


#innodb
innodb_data_home_dir=/u01/logs/my3307/iblog
innodb_log_group_home_dir=/u01/logs/my3307/iblog
innodb_adaptive_flushing=ON
innodb_adaptive_hash_index=ON
innodb_autoinc_lock_mode=1
innodb_buffer_pool_instances=8

#default
innodb_change_buffering=inserts
innodb_checksums=ON
innodb_buffer_pool_size= 128M
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_doublewrite=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_lock_wait_timeout=10
innodb_log_buffer_size=67108864
innodb_log_file_size=104857600
innodb_log_files_in_group=4
innodb_max_dirty_pages_pct=60
innodb_open_files=600
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_support_xa=ON
innodb_use_native_aio=OFF
innodb_write_io_threads=10

[mysqld_safe]
datadir=/u01/mydata/my3307

3307的参数文件配置好后,执行安装

[mysql@node1 mysql5.6]$ ./scripts/mysql_install_db --defaults-file=/u01/conf/mysql/my3307/my.cnf --datadir=/u01/mydata/my3307 --user=mysql

 

6.启动双实例

启动3306

[root@node1 ~]# su - mysql 
[mysql@node1 ~]$ cd /u01/svr/mysql5.6/bin
[mysql@node1 bin]$ ./mysqld_safe --defaults-file=/u01/conf/mysql/my3306/my.cnf --user=mysql &

启动3307

[mysql@node1 bin]$ ./mysqld_safe --defaults-file=/u01/conf/mysql/my3307/my.cnf --user=mysql &

检查实例启动情况

[mysql@node1 my3306]$ ps -ef |grep 330|grep -v grep
mysql     12930  12315  0 05:38 pts/2    00:00:00 /bin/sh ./mysqld_safe --defaults-file=/u01/conf/mysql/my3307/my.cnf --user=mysql
mysql     13807  12930  0 05:38 pts/2    00:00:00 /u01/svr/mysql5.6/bin/mysqld --defaults-file=/u01/conf/mysql/my3307/my.cnf --basedir=/u01/svr/mysql5.6 --datadir=/u01/mydata/my3307 --plugin-dir=/u01/svr/mysql5.6/lib/plugin --log-error=/u01/logs/my3307/error3307.log --open-files-limit=65535 --pid-file=/u01/mydata/my3307/mysqld.pid --socket=/tmp/mysql3307.sock --port=3307
mysql     13839  12315  0 05:40 pts/2    00:00:00 /bin/sh ./mysqld_safe --defaults-file=/u01/conf/mysql/my3306/my.cnf --user=mysql
mysql     14716  13839  0 05:40 pts/2    00:00:00 /u01/svr/mysql5.6/bin/mysqld --defaults-file=/u01/conf/mysql/my3306/my.cnf --basedir=/u01/svr/mysql5.6 --datadir=/u01/mydata/my3306 --plugin-dir=/u01/svr/mysql5.6/lib/plugin --log-error=/u01/logs/my3306/error3306.log --open-files-limit=1024 --pid-file=/u01/mydata/my3306/mysqld.pid --socket=/tmp/mysql3306.sock --port=3306

7.登录mysql

登录3306实例

[mysql@node1 bin]$ ./mysql --socket=/tmp/mysql3306.sock --port=3306
mysql> show variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 3306  |
| report_host         |       |
| report_password     |       |
| report_port         | 3306  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.01 sec)

登录3307实例

[mysql@node1 bin]$ ./mysql --socket=/tmp/mysql3307.sock --port=3307 
mysql> show variables like '%port%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_support_xa   | ON    |
| large_files_support | ON    |
| port                | 3307  |
| report_host         |       |
| report_password     |       |
| report_port         | 3307  |
| report_user         |       |
+---------------------+-------+
7 rows in set (0.00 sec)

8.关闭双实例

关闭3306

[mysql@node1 bin]$ ./mysqladmin -S /tmp/mysql3306.sock shutdown &

关闭3307

[mysql@node1 bin]$ ./mysqladmin -S /tmp/mysql3307.sock shutdown &

 

 

发布了3 篇原创文章 · 获赞 0 · 访问量 1402
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览