【实验背景】:
原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 &