linux_6.8配置mysql_5.5.32多实例

最新推荐文章于 2021-02-12 12:51:31 发布

Steam noodle

最新推荐文章于 2021-02-12 12:51:31 发布

阅读量107

点赞数

本文链接：https://blog.csdn.net/weixin_40908896/article/details/100985521

版权

1.   安装说明
1)   本次安装操作系统版本
[root@mysql_5 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
[root@mysql_5 ~]# uname -r
2.6.32-642.el6.x86_64
[root@mysql_5 ~]# uname -m
x86_64
2)   安装mysql版本
[root@mysql_5 ~]# ls /software/
cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz
3)   安装方法
本次安装采用cmake编译安装。
2.   安装前准备
1)   安装前检查
[root@mysql_5 ~]# rpm -qa|grep mysql
mysql-libs-5.1.73-7.el6.x86_64
2)   删除已安装的mysql包
[root@mysql_5 ~]# rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps
[root@mysql_5 ~]# rpm -qa|grep mysql
[root@mysql_5 ~]#

3.   安装
1)   编译安装cmake
A.   安装cmake所需要的gcc包
[root@mysql_5 ~]# mount /dev/cdrom /mnt/cdrom
[root@mysql_5 ~]# yum install gcc* -y

B.   编译安装cmake
[root@mysql_5 ~]# cd /software/
[root@mysql_5 software]# ls
cmake-2.8.8.tar.gz mysql-5.5.32.tar.gz
[root@mysql_5 software]# tar xf cmake-2.8.8.tar.gz
[root@mysql_5 software]# cd cmake-2.8.8
[root@mysql_5 cmake-2.8.8]# ./configure
[root@mysql_5 cmake-2.8.8]# gmake
[root@mysql_5 cmake-2.8.8]# echo $?
0
[root@mysql_5 cmake-2.8.8]# gmake install
2)   安装mysql
A.   安装mysql所需的依赖包
[root@mysql_5 software]# rpm -qa|grep ncurses
ncurses-base-5.7-4.20090207.el6.x86_64
ncurses-libs-5.7-4.20090207.el6.x86_64
ncurses-5.7-4.20090207.el6.x86_64
[root@mysql_5 software]# yum list|grep ncurses-devel
ncurses-devel.i686 5.7-4.20090207.el6 rhel-source
ncurses-devel.x86_64 5.7-4.20090207.el6 rhel-source
[root@mysql_5 software]# yum install ncurses-devel -y

B. 创建mysql的用户和组
[root@mysql_5 mysql-5.5.32]# useradd mysql -s /sbin/nologin -M
[root@mysql_5 mysql-5.5.32]# id mysql
uid=500(mysql) gid=500(mysql) groups=500(mysql)
C. 编译安装mysql
[root@mysql_5 software]# mkdir /application
[root@mysql_5 software]# tar zxf mysql-5.5.32.tar.gz
[root@mysql_5 software]# cd mysql-5.5.32
[root@mysql_5 mysql-5.5.32]#

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

[root@mysql_5 mysql-5.5.32]#make && make install

D.   软连接mysql安装目录
[root@mysql_5 software]# cd /application/
[root@mysql_5 application]# ls
mysql-5.5.32
[root@mysql_5 application]# ln -s mysql-5.5.32/ mysql
[root@mysql_5 application]# ll
total 4
lrwxrwxrwx. 1 root root 13 Sep 18 06:52 mysql -> mysql-5.5.32/
drwxr-xr-x. 13 root root 4096 Sep 18 06:51 mysql-5.5.32
3)   配置mysql多实例
A.   创建mysql多实例目录
[root@mysql_5 ~]# ls /application/mysql/data/
[root@mysql_5 ~]# mkdir /application/mysql/data/{3306,3307}/data -p
[root@mysql_5 ~]# tree /application/mysql/data/
/application/mysql/data/
├── 3306
│ └── data
└── 3307
└── data

4 directories, 0 files
B. mysql多实例配置
第一个实例的配置文件：
[root@mysql_5 ~]# cat /application/mysql/data/3306/my.cnf
[client]
port = 3306
socket = /application/mysql/data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /application/mysql/data/3306/mysql.sock
basedir = /application/mysql
datadir = /application/mysql/data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
pid-file = /application/mysql/data/3306/mysql.pid
log-bin = /application/mysql/data/3306/mysql-bin
relay-log = /application/mysql/data/3306/relay-bin
relay-log-info-file = /application/mysql/data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/application/mysql/data/3306/mysql_oldboy3306.err
pid-file=/application/mysql/data/3306/mysqld.pid

第二个实例的配置文件：
[root@mysql_5 ~]# cat /application/mysql/data/3307/my.cnf
[client]
port = 3307
socket = /application/mysql/data/3307/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3307
socket = /application/mysql/data/3307/mysql.sock
basedir = /application/mysql
datadir = /application/mysql/data/3307/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
pid-file = /application/mysql/data/3307/mysql.pid
log-bin = /application/mysql/data/3307/mysql-bin
relay-log = /application/mysql/data/3307/relay-bin
relay-log-info-file = /application/mysql/data/3307/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M

lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

server-id = 2

[mysqld_safe]
log-error=/application/mysql/data/3307/mysql_oldboy3307.err
pid-file=/application/mysql/data/3307/mysqld.pid

C. mysql多实例数据目录授权
[root@mysql_5 ~]# chown -R mysql.mysql /application/mysql/data/
[root@mysql_5 ~]# ll /application/mysql/data/
total 8
drwxr-xr-x. 3 mysql mysql 4096 Sep 18 22:34 3306
drwxr-xr-x. 3 mysql mysql 4096 Sep 18 22:32 3307
D. 初始化mysql
初始化第1个实例：
[root@mysql_5 ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/3306/data/ --user=mysql
WARNING: The host 'mysql_5.5.32_mul_instance' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/application/mysql/bin/mysqladmin -u root password 'new-password'
/application/mysql/bin/mysqladmin -u root -h mysql_5.5.32_mul_instance password 'new-password'

Alternatively you can run:
/application/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /application/mysql ; /application/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /application/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /application/mysql/scripts/mysqlbug script!

初始化第2个实例：
[root@mysql_5 ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/3307/data/ --user=mysql
WARNING: The host 'mysql_5.5.32_mul_instance' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/application/mysql/bin/mysqladmin -u root password 'new-password'
/application/mysql/bin/mysqladmin -u root -h mysql_5.5.32_mul_instance password 'new-password'

Alternatively you can run:
/application/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /application/mysql ; /application/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /application/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /application/mysql/scripts/mysqlbug script!

E. 启动mysql
[root@mysql_5 ~]# /application/mysql/bin/mysqld_safe --defaults-file=/application/mysql/data/3306/my.cnf &
[1] 2399
[root@mysql_5 ~]# 190918 22:44:24 mysqld_safe Logging to '/application/mysql/data/3306/mysql_oldboy3306.err'.
190918 22:44:24 mysqld_safe Starting mysqld daemon with databases from /application/mysql/data/3306/data

[root@mysql_5 ~]# netstat -tulnp |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3111/mysqld
[root@mysql_5 ~]# /application/mysql/bin/mysqld_safe --defaults-file=/application/mysql/data/3307/my.cnf &
[2] 3131
[root@mysql_5 ~]# 190918 22:45:00 mysqld_safe Logging to '/application/mysql/data/3307/mysql_oldboy3307.err'.
190918 22:45:00 mysqld_safe Starting mysqld daemon with databases from /application/mysql/data/3307/data

[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3111/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3855/mysqld
F. 添加mysql的环境变量
[root@mysql_5 ~]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[root@mysql_5 ~]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@mysql_5 ~]# source /etc/profile
[root@mysql_5 ~]# which mysql
/application/mysql/bin/mysql

G. 进入mysql数据库
进入第1个数据库：
[root@mysql_5 ~]# mysql -S /application/mysql/data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> create database db3306;
Query OK, 1 row affected (0.02 sec)

进入第2个数据库：
[root@mysql_5 ~]# mysql -S /application/mysql/data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db3307;
Query OK, 1 row affected (0.00 sec)

4) mysql数据库为root添加密码
[root@mysql_5 ~]# /application/mysql/bin/mysqladmin -u root -S /application/mysql/data/3306/mysql.sock password 'system123...'
[root@mysql_5 ~]# /application/mysql/bin/mysqladmin -u root -S /application/mysql/data/3307/mysql.sock password 'system123...'
[root@mysql_5 ~]# mysql -u root -S /application/mysql/data/3306/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
[root@mysql_5 ~]# mysql -u root -S /application/mysql/data/3307/mysql.sock -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.32-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

5) mysql多实例启动和关闭脚本编辑
第1个实例的脚本：
[root@mysql_5 ~]# cat /application/mysql/data/3306/mysql
#!/bin/bash
################################################
#script is created by lbw at 2019-09-18
################################################
port=3306
mysql_user="root"
mysql_pwd="system123..."
ComPath="/application/mysql/bin"
mysql_sock="/application/mysql/data/${port}/mysql.sock"
con_file="/application/mysql/data/${port}/my.cnf"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
/usr/bin/printf "Starting MySQL...\n"
/bin/sh ${ComPath}/mysqld_safe --defaults-file=$con_file 2>&1 > /dev/null &
else
/usr/bin/printf "MySQL is running...\n"
exit
fi
}

#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
/usr/bin/printf "MySQL is stopped...\n"
exit
else
/usr/bin/printf "Stoping MySQL...\n"
${ComPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S $mysql_sock shutdown
fi
}

#restart function
function_restart_mysql()
{
/usr/bin/printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

第2个实例的脚本：
[root@mysql_5 ~]# cat /application/mysql/data/3307/mysql
#!/bin/bash
################################################
#script is created by lbw at 2019-09-18
################################################
port=3307
mysql_user="root"
mysql_pwd="system123..."
ComPath="/application/mysql/bin"
mysql_sock="/application/mysql/data/${port}/mysql.sock"
con_file="/application/mysql/data/${port}/my.cnf"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
/usr/bin/printf "Starting MySQL...\n"
/bin/sh ${ComPath}/mysqld_safe --defaults-file=$con_file 2>&1 > /dev/null &
else
/usr/bin/printf "MySQL is running...\n"
exit
fi
}

#restart function
function_restart_mysql()
{
/usr/bin/printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

6)   mysql多实例启动和关闭脚本测试
第1个实例脚本测试：
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3111/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3855/mysqld
[root@mysql_5 ~]# /application/mysql/data/3306/mysql stop
Stoping MySQL...
190918 23:22:46 mysqld_safe mysqld from pid file /application/mysql/data/3306/mysqld.pid ended
[1]- Done /application/mysql/bin/mysqld_safe --defaults-file=/application/mysql/data/3306/my.cnf
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3855/mysqld
[root@mysql_5 ~]# /application/mysql/data/3306/mysql start
Starting MySQL...
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4793/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3855/mysqld

第2个实例脚本测试：
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4793/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5555/mysqld

[root@mysql_5 ~]# /application/mysql/data/3307/mysql stop
Stoping MySQL...
190918 23:26:56 mysqld_safe mysqld from pid file /application/mysql/data/3307/mysqld.pid ended
[2]+ Done /application/mysql/bin/mysqld_safe --defaults-file=/application/mysql/data/3307/my.cnf
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4793/mysqld
[root@mysql_5 ~]# /application/mysql/data/3307/mysql start
Starting MySQL...
[root@mysql_5 ~]# netstat -tulnp |grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4793/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5555/mysqld

7) 建议删除root用户，建立system用户的优化
mysql> grant all privileges on *.* to 'system' identified by 'system123...' with grant option;
Query OK, 0 rows affected (0.00 sec)

Steam noodle

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
linux_6.8配置mysql_5.5.32多实例

1. 安装说明1) 本次安装操作系统版本[root@mysql_5 ~]# cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 6.8 (Santiago)[root@mysql_5 ~]# uname -r2.6.32-642.el6.x86_64[root@mysql_5 ~]#...
复制链接

扫一扫