地产网站怎么做/会计培训班的费用是多少

MySQL多实例安装配置

什么是MySQL多实例？

多实例就是在一台机器上开启多个不同服务端口，运行多个MySQL服务进程，这些服务通过进程通过不同饿socket监听不同的服务端口来提供各自的服务

这些MySQL多实例是公用一套MySQL安装程序，使用不同的my.conf配置文件，启动文件，数据文件。在提供服务同时，多实例MySQL在逻辑上看来就是各自独立的。但是他们是公用资源，所以会有影响。

多实例的作用与问题

1、 有效的利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余资源提供更多服务。

2、 节约服务器资源

当资金紧张但是数据库又需要各自尽量独立提供给服务，而且需要主从同步等技术时，多实例就很适合

3、 资源互相抢占问题

当某个服务实例并发很高或者有慢查询时，整个实例就会消耗更多的内存、CPU、磁盘io资源，导致服务器上其他实例提供服务的质量下降。这就相当于大家住在一个房子里，做一件事，其他人就需要等待

MySQL配置方案

多配置文件部署方案

通过配置多个配置文件及多个启动程序来实现多实例的方案；

以端口形式划分，3306，3307端口创建实例目录，每个目录下都有各自的实例的数据文件(data)，实例的配置文件(my.conf)，实例的启动文件(mysql)

单一配置文件部署方案

mysqld_multi

缺点，耦合性太高

安装多实例的数据库

创建MySQL多实例的数据文件目录

一般情况下我们采用/data目录作为MySQL多实例总的根目录然后规划不同的MySQL实例端口来作为/data下的二级目录，不同的端口号就是不同的实例目录，用来区别不同的实例

这里以cmake为例子安装多实例

安装MySQL依赖包

yum install -y ncurses-develyum install -y libaio-deve

建立MySQL账号

group mysql

useradd-s /sbin/nologin -g mysql -M mysql

创建相关MySQL多实例的目录如下

[root@localhost ~]# mkdir -pv /data/{3306,3307}/datamkdir: created directory ‘/data’mkdir: created directory ‘/data/3306’mkdir: created directory ‘/data/3306/data’mkdir: created directory ‘/data/3307’mkdir: created directory ‘/data/3307/data’

先将我们单实例的MySQL给kill掉

[root@localhost ~]# pkill mysqld

[root@localhost~]# ps -ef | grepmysqld

root3314 2827 0 09:03 pts/0 00:00:00 grep --color=auto mysqld

[root@localhost~]# ps -ef | grepmysql

root3316 2827 0 09:03 pts/0 00:00:00 grep --color=auto mysql

[root@localhost~]# rm -f /etc/init.d/mysqld

这个操作相当于沃恩前面做实验中初始化之后的操作就不做了

[root@localhost~]# tree /data/

/data/总的多实例根目录

├──33063306实例的目录

│ └── data 3306实例的数据文件目录

└──33073307实例目录

└── data 3307实例的数据文件目录4 directories, 0 files

MySQL数据库多实例的配置文件

MySQL数据库默认用户提供了多个配置文件模板，用户可以根据硬件配置大小来选择

my.cnf

port =3306socket=/data/3306/mysql.sock

port=3306socket=/data/3306/mysql.sock

datadir=/data/3306/data

long_query_time= 1#log-error= /data/3306/error.log

#log-slow-queries = /data/3306/slow.log

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin

relay-log = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.infoserver-id = 1log-error = /data/3306/mysql_oldboy3306.err

pid-file = /data/3306/mysql.pid3307port=3307socket=/data/3307/mysql.sock

port=3307socket=/data/3307/mysql.sock

datadir=/data/3307/data

#long_query_time= 1#log-error= /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file = /data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log = /data/3307/relay-bin

relay-log-info-file = /data/3307/relay-log.infoserver-id = 3log-error = /data/3307/mysql_oldboy3307.err

pid-file = /data/3307/mysql.pid

[root@localhost ~]# tree /data/

/data/├──3306│ ├── data

│ └── my.cnf 这就是3306实例的配置文件

└──3307├── data

└── my.cnf 这就是3307实例的配置文件4 directories, 2 files

然后创建多实例的启动文件

#!/bin/shport=3306mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunctionfunction_start_mysql()

{if [ ! -e "$mysql_sock" ];thenprintf"Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

elseprintf"MySQL is running...\n"exitfi}

#stopfunctionfunction_stop_mysql()

{if [ ! -e "$mysql_sock" ];thenprintf"MySQL is stopped...\n"exitelseprintf"Stoping MySQL...\n"${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdo

wnfi}

#restartfunctionfunction_restart_mysql()

{

printf"Restarting MySQL...\n"function_stop_mysqlsleep 2function_start_mysql

}case $1 instart)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;*)

printf"Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

3307#!/bin/shport=3307mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunctionfunction_start_mysql()

{if [ ! -e "$mysql_sock" ];thenprintf"Starting MySQL...\n"

/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

elseprintf"MySQL is running...\n"exitfi}

#stopfunctionfunction_stop_mysql()

{if [ ! -e "$mysql_sock" ];thenprintf"MySQL is stopped...\n"exitelseprintf"Stoping MySQL...\n"${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdo

wnfi}

#restartfunctionfunction_restart_mysql()

{

printf"Restarting MySQL...\n"function_stop_mysqlsleep 2function_start_mysql

}case $1 instart)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;*)

printf"Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

多实例启动文件的启动MySQL服务实质

mysql_safe –defaults-file=/data/3306/my.cnf 2>&1 > /dev/nul &mysql_safe –defaults-file=/data/3307/my.cnf 2>&1 > /dev/nul &多实例启动文件的停止MySQL服务实质

mysqladmin-u root -p123456 -S /data/3306/mysql.sock shutdown

mysqladmin-u root -p123456 -S /data/3307/mysql.sock shutdown

[root@localhost ~]# tree /data/

/data/├──3306│ ├── data

│ ├── my.cnf

│ └── mysql

└──3307├── data

├── my.cnf

└── mysql4 directories, 4 files

配置MySQL多实例文件的权限

[root@localhost ~]# chown -R mysql.mysql /data

[root@localhost~]# find /data/ -type f -name "mysql" | xargs ls -l-rw-r--r--. 1 mysql mysql 1307 Oct 23 09:44 /data/3306/mysql-rw-r--r--. 1 mysql mysql 1307 Oct 23 09:44 /data/3307/mysql

[root@localhost~]# find /data/ -type f -name "mysql" | xargs chmod +x

[root@localhost~]# find /data/ -type f -name "mysql" | xargs ls -l-rwxr-xr-x. 1 mysql mysql 1307 Oct 23 09:44 /data/3306/mysql-rwxr-xr-x. 1 mysql mysql 1307 Oct 23 09:44 /data/3307/mysql

配置MySQL全局使用路径

[root@localhost ~]# tail -1 /etc/profile

export PATH=/application/mysql/bin:$PATH

第二种办法

就是把/usr/local/mysql/bin下的命令拷贝到全局命令路径下/usr/local/sbin下/bin/cp /usr/local/mysql/bin/*/usr/local/sbin

初始化数据库

cd /application/mysql/scripts/./mysql_install_db –basedir=/application/mysql –datadir=/data/3306/data –user=mysql

./mysql_install_db –basedir=/application/mysql –datadir=/data/3307/data –user=mysql

为什么要初始化，就是创建基础的数据库文件

最直接的结果就是在你data目录下放了库表的数据了

这个时候就可以启动数据库了

[root@root scripts]# /data/3306/mysql start

Starting MySQL...

[root@root scripts]#/data/3307/mysql start

Starting MySQL...

此时服务已经启动了

如果出错，检查自己的错误日志，这里里面会告诉你

多实例的登录

[root@root scripts]# mysql -S /data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 1Server version:5.5.32-log Source distribution

Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 clearthe current input statement.

mysql>mysql>create database d3306;

Query OK,1 row affected (0.00sec)

mysql>

对于多实例的登录可以指定sock登录

[root@root ~]# mysql -S /data/3307/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 1Server version:5.5.32Source distribution

Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 clearthe current input statement.

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00sec)

mysql>create database d3307;

Query OK,1 row affected (0.01sec)

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| d3307 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00sec)

mysql> system ls

2019 data.zipDownloads Music Templates zhangsan__01

anaconda-ks.cfg Desktop install.log Pictures Videos zhangsan__02

data Documentsinstall.log.syslog Public zhangsan__00

mysql> system mysql -S /data/3306/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.5.32-log Source distribution

Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 clearthe current input statement.

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| d3306 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00sec)

mysql>exit

Bye

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| d3307 |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00sec)

mysql>

登录数据库

mysql -S /data/3306/mysql.sock

mysql-S /data/3306/mysql.sock

重启数据库

/data/3306/mysql stop/data/3307/mysql stop/data/3306/mysql start/data/3307/mysql start

此时可以发现，MySQL无法停止，这是因为你的MySQL没有密码

[root@root 3306]# /data/3306/mysql stop

Stoping MySQL.../application/mysql/bin/mysqladmin: connect to server at 'localhost'failed

error:'Access denied for user'root'@'localhost'(using password: YES)'

MySQL安全配置

为root增加密码

mysqladmin -u root -S /data/3306/mysql.sock password ‘123456’

mysqladmin-u root -S /data/3307/mysql.sock password '123456'

此时会发现无法登录了

[root@root 3306]# mysql -S /data/3306/mysql.sock

ERROR1045 (28000): Access denied for user 'root'@'localhost'(using password: NO)

[root@root3306]# vim /data/3306/mysql

[root@root3306]# vim /data/3307/mysql

修改其中mysql_pwd为自己设置的密码

但是由于密码是在脚本文件中的，我们出与安全的考虑需要设置一下他的权限

[root@root 3306]# find /data/ -type f -name "mysql" -exec chmod 700{} \;

[root@root3306]# find /data/ -type f -name "mysql" -exec chownroot.root {} \;

[root@root3306]# find /data/ -type f -name "mysql" -exec ls -l {} \;-rwx------. 1 root root 1307 Oct 23 21:28 /data/3306/mysql-rwx------. 1 root root 1307 Oct 23 21:28 /data/3307/mysql

此时我们将mysqldpkill掉

[root@root 3306]# pkill mysqld

[root@root3306]# pkill mysqld

[root@root3306]# netstat -lntup | grep 330

重新启动mysql

[root@root 3306]# /data/3306/mysql start

Starting MySQL...

[root@root3306]# /data/3307/mysql start

Starting MySQL...

也可以停止mysql服务了

[root@root 3306]# /data/3306/mysql stop

Stoping MySQL...

[root@root3306]# /data/3307/mysql stop

Stoping MySQL...

删除多余的mysql用户

mysql> selectuser,host from mysql.user;

mysql> delete from mysql.user where user="";

如何再增加一个MySQL实例

以3308端口为例

[root@root ~]# mkdir /data/3308/data -p

[root@root~]# cp /data/3306/my.cnf /data/3308/[root@root~]# cp /data/3306/mysql /data/3308/[root@root~]# chown -R mysql.mysql /data/

Vim对内容做一个替换g/3306/s//3307/g或者：%s/3306/3307/g

[root@root scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3308/data/

[root@root ~]# /data/3308/mysql start

Starting MySQL...

[root@root~]# ss -tnl | grep --color=auto 330LISTEN0 128 *:3306 *:*LISTEN0 128 *:3307 *:*LISTEN0 128 *:3308 *:*修改密码

[root@root~]# mysqladmin -u root -S /data/3308/mysql.sock password '123456'登录3308端口的mysql

[root@root~]# mysql -S /data/3308/mysql.sock -u root -p123456

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 3Server version:5.5.32-log Source distribution

Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 clearthe current input statement.

mysql> selectuser,host from mysql.user->;+------+-----------+

| user | host |

+------+-----------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | root |

| root | root |

+------+-----------+

6 rows in set (0.00sec)

mysql>mysql本地登录指定sock

远程登录mysql-uroot -p123456 -P3308 -h 192.168.17.13

至此，MySQL多实例结束