多实例简单说就是在一台服务器主机上运行多个数据库服务
多实例的优点:节约成本,提高硬件的利用率
多实例的缺点:并发量很高或者有慢查询时,会消耗服务器更多的cpu、内存、磁盘IO等资源,影响其他实例提供的服务,访问质量下降。
部署多实例
1.准备环境
一台服务器mysql1 192.168.4.1
一台客户机mysql2 192.168.4.2
2.准备mysql软件
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
一丶部署服务器
1.安装软件mysql
源码包安装过程 解包,建用户,移动位置就可以应用
PATH设置快捷方式
[root@mysql1 ~]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql1 ~]# yum -y install libaio
[root@mysql1 ~]# useradd mysql
[root@mysql1 ~]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
[root@mysql1 ~]# PATH=/usr/local/mysql/bin:$PATH
[root@mysql1 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mysql1 ~]# vim /etc/bashrc
export PATH=/usr/local/mysql/bin:$PATH
:wq
2.修改配置文件,实现多实例
主配置文件:/etc/my.cnf
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld_multi] //启用多实例
mysqld = /usr/local/mysql/bin/mysqld_safe //指定京城文件路径
mysqladmin = /usr/local/mysql/bin/mysqladmin //指定管理命令路径
user = root //指定进程用户
[mysqld1] //多实例进程名称
port = 3307 //指定端口号 端口号不能与其他服务的端口号冲突
datadir = /yxt //数据库目录 需要自行创建
socket = /yxt/mysql.sock //指定sock文件的路径名称
pid-file = /yxt/mysqld.pid //指定pid号的文件位置
log-error = /yxt/mysqld.err //错误日志位置
[mysqld2]
port = 3308
datadir = /zwy
socket = /zwy/mysql.sock
pid-file = /zwy/mysqld.pid
log-error = /zwy/mysqld.err
~
:wq
[root@mysql1 ~]# mkdir /yxt
[root@mysql1 ~]# mkdir /zwy
3.启动多实例
启动服务
命令格式:mysqld_multi start x //x是实例编号
停止服务
命令格式:mysqld_multi --user=root --password=密码 stop x //x为实例编号
默认密码自动出现
不修改密码无法查看数据库信息
第一个进程
[root@mysql1 ~]# mysqld_multi start 1
Installing new database in /yxt
2019-09-06T12:12:33.369542Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-09-06T12:12:35.425932Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-09-06T12:12:35.900884Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-09-06T12:12:36.084330Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9cd12fd9-d09f-11e9-9b05-525400216c04.
2019-09-06T12:12:36.122474Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-09-06T12:12:36.123576Z 1 [Note] A temporary password is generated for root@localhost: ht?y;QlVT1;t
[root@mysql1 ~]# mysql -uroot -p"ht?y;QlVT1;t" -S /yxt/mysql.sock
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@"localhost" identified by "123Yxt...";
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
第二个服务器进程
[root@mysql1 ~]# mysqld_multi start 2
......
2019-09-06T12:20:11.945247Z 1 [Note] A temporary password is generated for root@localhost: n;pz7dNAv8ct
[root@mysql1 ~]# mysql -uroot -p"n;pz7dNAv8ct" -S /zwy/mysql.sock
mysql> alter user root@"localhost" identified by "123Zwy...";
4.授权给客户端
实例1
mysql> create database t
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> create table t.t(age int);
Query OK, 0 rows affected (1.98 sec)
mysql> grant select,insert on t.* to tt@"%" identified by "123Yxt...";
Query OK, 0 rows affected, 1 warning (0.00 sec)
实例2
mysql> create database y;
Query OK, 1 row affected (0.00 sec)
mysql> create table y.y(id int);
Query OK, 0 rows affected (0.32 sec)
mysql> grant insert,select on y.* to yy@"%" identified by "123Zwy...";
二丶客户端访问
格式:mysql -h访问ip -u授权用户 -p授权密码 -P进程号
访问3307进程实例一
[root@mysql50 ~]# mysql -h192.168.4.1 -utt -p"123Yxt..." -P3307
mysql> select * from t.t;
Empty set (0.02 sec)
mysql> insert into t.t values(23);
Query OK, 1 row affected (0.12 sec)
mysql> select * from t.t;
+------+
| age |
+------+
| 23 |
+------+
1 row in set (0.00 sec)
访问3308进程实例二
[root@mysql50 ~]# mysql -h192.168.4.1 -uyy -p"123Zwy..." -P3308
mysql> insert into y.y values(1);
Query OK, 1 row affected (0.04 sec)
mysql> select * from y.y;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql>