CentOS7下yum安装mysql配置多实例

最新推荐文章于 2024-06-04 14:12:10 发布

poklau123

最新推荐文章于 2024-06-04 14:12:10 发布

阅读量5.6k

点赞数 1

分类专栏： mysql 文章标签： mysql mysql多实例运维

本文链接：https://blog.csdn.net/poklau/article/details/54951798

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

最近想试试Mysql的主从复制功能“Mysql Replication”，但是苦于没多台电脑，于是考虑在Linux上配置多个Mysql实例做为测试环境。

环境：虚拟机上的CentOS7

首先得在CentOS上安装MySQL，下载源码编译太麻烦，于是考虑用yum直接安装。由于在CentOS7上已经没有了MySQL的yum源，于是我们这次用MariaDB替换Mysql，毕竟两者的差距不大，在使用上基本上是互通的。

yum安装MariaDB：

yum install mariadb mariadb-server

安装完成后要进行手动开启MySQL服务并初始化：

service mariadb start

[root@localhost ~]# mysql_secure_installation


NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.


Enter current password for root (enter for none): 
OK, successfully used password, moving on...


Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.


Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!




By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.


Remove anonymous users? [Y/n] Y
 ... Success!


Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.


Disallow root login remotely? [Y/n] n
 ... skipping.


By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.


Remove test database and access to it? [Y/n] n
 ... skipping.


Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.


Reload privilege tables now? [Y/n] Y
 ... Success!


Cleaning up...


All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.


Thanks for using MariaDB!

然后测试是否能够正常登录：

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

说明已经能正常登录了，我们可以查看MySQL的用户权限了：

MariaDB [(none)]> select user,host,password from mysql.user \G;
*************************** 1. row ***************************
    user: root
    host: localhost
password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F
*************************** 2. row ***************************
    user: root
    host: 127.0.0.1
password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F
*************************** 3. row ***************************
    user: root
    host: ::1
password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F
*************************** 4. row ***************************
    user: root
    host: %
password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F
4 rows in set (0.01 sec)

在这里我们可以看到有四个登录账号，user表示登录名，host表示登录主机限制，password为散列后的登录密码，其中%表示任意，如host的%表示可以任意主机登录，在之后会写到怎样修改这些登录数据。

现在我们退出MySQL登录，准备做MySQL上的多实例。

[root@localhost home]# mkdir /home/multiMysql
[root@localhost home]# mkdir /home/multiMysql/{etc,socket,bin,datadir}

在/home目录下创建multiMysql文件夹，并在里面创建etc,socket,bin,datadir这四个文件夹备用。
现在我们在datadir中创建3个文件夹以放置三个实例的数据文件：3307,3308,3309

[root@localhost multiMysql]# mkdir /home/multiMysql/datadir/{3307,3308,3309}

然后用mysql_install_db来生成即将使用的多个实例的数据文件，首先需要对/home/multiMysql进行递归授权防止之后的操作出现权限不够的情况：

chmod -R 777 /home/multiMysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --user=mysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3308 --user=mysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3309 --user=mysql

其中的参数--basedir是指mysql的二进制文件目录（误？），--datadir是指即将安装到的数据库文件目录，如果不知道--basedir该怎么填，可以登录进mysql后查询：

MariaDB [(none)]> show variables like '%basedir%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir     | /usr |
+---------------+-------+
1 row in set (0.00 sec)

--user是指mysql实例将使用的在linux系统中的用户，最好命名为mysql，yum安装后一般都有这个用户，如果没有可以自主创建：

groupadd mysql
adduser -g mysql mysql

现在来查看三份数据文件有没有生成，例如查看3308的：

[root@localhost multiMysql]# ls /home/multiMysql/datadir/3308/
aria_log.00000001  aria_log_control  mysql  performance_schema  test

如果里面有文件代表生成成功。

接下来我们来做多实例的配置：

先创建一个公用配置文件：

mkdir /home/multiMysql/etc/my.cnf.d/

vim /home/multiMysql/etc/my.cnf.d/my.cnf

[mysqld]
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
server-id = 1
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
prompt=\\u@\\d \\R:\\m>
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

然后分别创建三个实例的配置文件：

[root@localhost etc]# vim 3307.cnf

[client]
port = 3307
socket = /home/multiMysql/socket/mysql3307.sock
[mysqld]
datadir=/home/multiMysql/datadir/3307
port = 3307
socket = /home/multiMysql/socket/mysql3307.sock

!includedir /home/multiMysql/etc/my.cnf.d

然后把3307.cnf复制两份：

[root@localhost etc]# cp 3307.cnf 3308.cnf
[root@localhost etc]# cp 3307.cnf 3309.cnf

然后分别编辑复制的两份配置文件，把端口和socket进行修改：

[root@localhost etc]# vim 3308.cnf

[client]
port = 3308
socket = /home/multiMysql/socket/mysql3308.sock
[mysqld]
datadir=/home/multiMysql/datadir/3308
port = 3308
socket = /home/multiMysql/socket/mysql3308.sock

!includedir /home/multiMysql/etc/my.cnf.d

[root@localhost etc]# vim 3309.cnf

[client]
port = 3309
socket = /home/multiMysql/socket/mysql3309.sock
[mysqld]
datadir=/home/multiMysql/datadir/3309
port = 3309
socket = /home/multiMysql/socket/mysql3309.sock

!includedir /home/multiMysql/etc/my.cnf.d

在配置文件中，port是实例的端口，socket是实例运行时的sock文件，datadir是之前我们生成的数据库文件位置。

然后我们来编辑三个启动脚本：

[root@localhost bin]# vim /home/multiMysql/bin/mysql3307

#!/bin/bash
mysql_port=3307
mysql_username="root"
mysql_password=""
function_start_mysql()
{
printf "Starting MySQL...\n"
mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo

[root@localhost bin]# vim /home/multiMysql/bin/mysql3308

#!/bin/bash
mysql_port=3308
mysql_username="root"
mysql_password=""
function_start_mysql()
{
printf "Starting MySQL...\n"
mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
esac

[root@localhost bin]# vim /home/multiMysql/bin/mysql3309

#!/bin/bash
mysql_port=3309
mysql_username="root"
mysql_password=""
function_start_mysql()
{
printf "Starting MySQL...\n"
mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
case $1 in
start)
function_start_mysql;;
stop)
function_stop_mysql;;
kill)
function_kill_mysql;;
restart)
function_stop_mysql
function_start_mysql;;
*)
echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;
esac

因为是yum安装，所以mysqld_safe和mysqladmin可以不用加路径直接运行，另外mysql_port是指这个bash简要打开的实例的端口，mysql_username和mysql_userpassword为我们即将在实例中配置的可关闭mysql进程的mysql用户名和密码。

现在给三个bash文件权限来执行，并尝试打开三个实例：

[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3307
[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3308
[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3309

先关闭yum安装的默认mysql实例进程:

sudo service mariadb stop

启动三个实例：

[root@localhost bin]# /home/multiMysql/bin/mysql3307 start
[root@localhost bin]# /home/multiMysql/bin/mysql3308 start
[root@localhost bin]# /home/multiMysql/bin/mysql3309 start

查看是否有三个mysql进程：

[root@localhost bin]# ps -ef | grep mysql
root      47013      1  0 19:57 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3307.cnf
mysql     47680  47013  2 19:57 pts/0    00:00:04 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3307.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3307/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3307.sock --port=3307
root      50504      1  0 20:00 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3308.cnf
mysql     51183  50504  9 20:00 pts/0    00:00:03 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3308.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3308 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3308/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3308.sock --port=3308
root      51224      1  0 20:00 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3309.cnf
mysql     51952  51224  2 20:00 pts/0    00:00:00 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3309.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3309 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3309/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3309.sock --port=3309
root      52445   3644  0 20:01 pts/0    00:00:00 grep --color=auto mysql

可以看到三个实例已经启动，我们来尝试连接三个实例的sock：

[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3307.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> quit
Bye
[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3308.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> quit
Bye
[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3309.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> quit
Bye

可见三个实例已经启动，可以进每个实例后查看当前实例的端口以确认是否成功打开实例：

例如连接3307的sock后执行sql：

MariaDB [(none)]> show variables like '%port%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| extra_port                          | 0     |
| innodb_import_table_from_xtrabackup | 0     |
| innodb_support_xa                   | ON    |
| large_files_support                 | ON    |
| port                                | 3307  |
| progress_report_time                | 5     |
| report_host                         |       |
| report_password                     |       |
| report_port                         | 3307  |
| report_user                         |       |
+-------------------------------------+-------+
10 rows in set (0.00 sec)

可见确实已经成功打开了。

下一步尝试远程连接mysql实例：

查得虚拟机桥接的ip地址为：192.168.1.156

提示不允许远程连接，可知是权限不够。在虚拟机中分别sock连接三个数据库并创建用户权限：

MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by 'poklau123' with grant option;
MariaDB [(none)]> flush privileges;

解释一下这两句sql的意思，grant是权限授予，all privileges表示授予所有权限，on表示授予的操作对象，*.*表示所有数据库的所有表，to表示授予的对象‘root’@'%'表示任意主机的root登录用户，identified by 'poklau123'表示设置密码为poklau123。flush privileges表示刷新用户权限，不刷新的话权限是不会立刻生效的。

现在重新尝试连接：

连接成功，三个实例都授予权限后三个实例都能分别连接了。

关闭实例：

[root@localhost bin]# /home/multiMysql/bin/mysql3307 stop
[root@localhost bin]# /home/multiMysql/bin/mysql3308 stop
[root@localhost bin]# /home/multiMysql/bin/mysql3309 stop

然后查看进程是否成功关闭：

[root@localhost bin]# ps -ef | grep mysql
root      74999   3644  0 20:27 pts/0    00:00:00 grep --color=auto mysql

可见三个实例已经成功关闭，如果未能成功关闭，说明是bash文件中用户名密码对应mysql实例里的用户密码权限不够或错误，调整权限即可。至此，三个mysql实例创建到此结束。

poklau123

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
CentOS7下yum安装mysql配置多实例

最近想试试Mysql的主从复制功能“Mysql Replication”，但是苦于没多台电脑，于是考虑在Linux上配置多个Mysql实例做为测试环境。环境：虚拟机上的CentOS7首先得在CentOS上安装MySQL，下载源码编译太麻烦，于是考虑用yum直接安装。由于在CentOS7上已经没有了MySQL的yum源，于是我们这次用MariaDB替换Mysql，毕竟两者的差距不大，在使用上
复制链接

扫一扫

专栏目录