坚持#第256天~数据库4(router+负载均衡+周五测试)

之前做的AB复制就是为了解决服务器数据库压力的问题，就是“负载均衡”

出现一个问题：

主从复制：两台服务器，别人要连的话连那一台呢？

要告诉别人服务器的ip是什么，就诞生了“读写分离”：

       client(别人)

      

    proxy被放弃了，出现了router

 

A                               B

别人要读的话就通过proxy连接到B从机(别忘了对从机操作主机并不会跟着改变,所有最好是用作读)

别人要写的话就通过proxy连接到A主机(主机也能读)

 

实验的话，不做主从，因为读的话看不到效果

 

以下就是集群操作：

集群操作前要理清思路：

router:192.168.10.13

master:192.168.10.11

slave:192.168.10.12

 

start：

克隆虚拟机

先把主从关掉(开启服务器会自动开启主从)

stop slave;

 

主机：

mysql

grant all on *.* to wing@’%’(客户端ip知道就写ip喽) identified by ‘123’;

flush privileges;

从机：

mysql

grant all on *.* to wing@’%’(客户端ip知道就写ip喽) identified by ‘123’;

flush privileges;

主机：

use db1;

create table test007(id int);

insert into test values(1);

从机：

use db1;

create table test007(id int);

insert into test values(2);

克隆机：

改ip为192.168.10.13

把软件scpmysql-router-2.14-linux.. 传到克隆机(在我的电脑F:\software\yunjisuanTry\mysql里面有)

这个是二进制包

tar xf 包名字    解压到并改名为mysql-router：/usr/local/mysql-router

cd /usr/local/mysql-router

ls

bin是命令

data数据

不用管include、lib、run

share是模板文件，如文档

cd share

ls

cd doc/mysqlrouter

ls

把配置文件拷贝到主目录下面

cp sample_mysqlrouter.conf哦；    /usr/local/mysql-router/mysqlrouter.conf并改名

cd /usr/local/mysql-router

vim /usr/local/mysql-router/mysqlrouter.conf

[router]

不打开就存放

[logger]里面的打开注释

[routing:basic_failover]

打开下面3行

bind_port是监听的端口，在上面加bind_address=192.168.10.13是route

将最后的改为10.11写服务器也就是主服务器

读是两台服务器

复制这一块6行

 

[routing:balancing]负载均衡

bind_address=192.168.10.13（注意是下划线）

bind_port=7002改为这个（读服务）

mode=read-only只读

destinations=192.168.10.11,192.168.10.12读操作分配到主从两台服务器

 

keepalive是长连接不用管

保存退出

./bin/mysqlrouter --help查看帮助信息怎么启动服务

{

./bin/mysqlrouter -v版本

 

}

./bin/mysqlrouter &放到后台运行

netstat -luantp | grep 700可以看到7001和7002起来了

那么客户端连接呢？

需要一个客户端来测试一下

别人来啦:

mysql -uwing -p123 -h 192.168.10.13 -P 7001写操作，报错拒绝访问

 

克隆机：

查看防火墙、selinux一切正常

cd /usr/local/mysql-router

ls

vim mysqlrouter.log看一下日志到底哪里出错了：

wq

vim 将之前keepalive注释掉

kill `pgrep mysqlrouter`

netstat -luantp | grep 700

找到mysqlrou名字1091

kill 1091

放在后台重启服务

netstat -luantp | grep 700

别人：

mysql -uwing -p123 -h 192.168.10.13 -P 7002读操作，再连接一下

 

主机：

mysql -uwing -p123 -h 192.168.10.11发现果然不行

授权了连不了

select host,user,password from mysql.user;

把localhost和clone1.up.com匿名用户删掉，怎么删掉匿名用户

delete from mysql.user where user=’’;

flush privileges;

mysql -uwing -p123 -h 192.168.10.11发现ok了

别人(10.1)：

mysql -uwing -p123 -h 192.168.10.13 -P 7001写，可以登

mysql -uwing -p123 -h 192.168.10.13 -P 7002读，不能登

创建好数据库以后要删除匿名用户

韩惠的还是不能成功：7001不能登，7002可以登录

mysql -urouter -p123 -h 192.168.1.76 -P 7002 -e “select* from db2.test;”

用其他人反复连接7002 查看数据会不同访问主机或从机，一下访问主机，一下访问从机的数据库

7001登陆不进入的原因是没有重新启动这个服务，要先kill一下再启动

 

 

从机：

delete from mysql.user where user=’’;

flush privileges;

 

别人：

create database ddd;创建数据库ddd

主服务器：

show databases;

从机：

show databases;

别人：

7002

select * from db2.test;

\q

再连

mysql -uroot -p123 -h 192.168.10.13 -P 7002读 -e “select * fromdb2.test;”

 

12/29周五测试：

1. 将管理员密码修改为uplooking

首先启动数据库服务：

/usr/local/mysql/support-files/mysql.server start

假如之前root没有设置密码，就输入

mysqladmin -u root password回车然后输入两次uplooking

 

假如之前root密码为123，想改为uplooking，就输入

mysqladmin -u root -p123 password回车然后输入两次uplooking

 

2. 创建up库

/usr/local/mysql/support-files/mysql.server start启动数据库服务

ln -s /usr/local/mysql/bin/* /usr/bin

mysql -uroot -puplooking 进入数据库

create database up;

 

3. 在up库中创建score表,有id(编号:主键,自增),class_id(班级号:非空,值只能在1001,1002中选择一个),name(姓名:非空),sex(性别:只能为male或female,如果不写则为male),linux,shell,mysql字段.后面三个字段为成绩.将存储引擎设置为myisam.

mysql -uroot -puplooking进入数据库

use up;

mysql> create table score(id int uniqueauto_increment,class_id enum('1001','1002') not null,name char(10) not null,sexenum('male','female') default'male',linux int,shell int,mysql int);

 

mysql> desc score;                                                                       +----------+-----------------------+------+-----+---------+-----

| Field    |Type                  | Null | Key | Default |Extra |

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

| id       |int(11)       |NO   | PRI      |NULL|auto_increment|

| class_id | enum('1001','1002')   | NO  |  | NULL    |     |

| name     |char(10)              | NO   |  |NULL    |      |

| sex      |enum('male','female') | YES  |  | male   |      |

| linux    |int(11)               | YES  |  |NULL    |      |

| shell    | int(11)               | YES  |  |NULL    |      |

| mysql    |int(11)               | YES  |  |NULL    |      |

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

 

4.向score表中插入5条以上数据,两个班级都要有

mysql -uroot -puplooking进入数据库

use up;

insert into score(class_id,name,sex,linux,shell,mysql)values('1001','xiaoyi','female',60,61,62),('1002','xiaoer','female',63,64,65),('1001','xiaosan','male',66,67,68),('1002','xiaosi','female',69,70,71),('1001','xiaowu','male',72,73,74),('1002','xiaoliu','female',75,76,77);

mysql> select * from score;

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

| id | class_id | name    | sex   | linux | shell | mysql |

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

|  7 |1001     | xiaoyi  | female |   60 |    61 |    62 |

|  8 |1002     | xiaoer  | female |   63 |    64 |    65 |

|  9 |1001     | xiaosan | male   |   66 |    67 |    68 |

| 10 | 1002    | xiaosi  | female |    69 |   70 |    71 |

| 11 | 1001    | xiaowu  | male   |   72 |    73 |    74 |

| 12 | 1002    | xiaoliu | female |    75 |    76 |   77 |

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

 

5. 统计出各班人数

mysql> select class_id,count(name) from scorewhere class_id='1001';

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

| class_id | count(name) |

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

| 1001    |           3 |

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

mysql> select class_id,count(name) from scorewhere class_id='1002';

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

| class_id | count(name) |

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

| 1002    |           3 |

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

 

6. 打印出linux成绩最高的学生信息

mysql> select * from score where linux=(selectmax(linux) from score);

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

| id | class_id | name    | sex   | linux | shell | mysql |

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

| 12 | 1002    | xiaoliu | female |    75 |    76 |   77 |

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

 

7. 打印出1002班shell总分

mysql> select sum(shell) from score whereclass_id='1002';                     +------------+

| sum(shell) |

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

|        210|

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

 

8. 打印出mysql成绩前三名

mysql> select name,mysql from score order bylinux desc limit 3;

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

| name    |mysql |

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

| xiaoliu |   77 |

| xiaowu |    74 |

| xiaosi |    71 |

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

 

9. 打印出所有学生的班级,姓名及平均分,按照平均分从高到低排列

mysql> selectclass_id,name,(linux+shell+mysql)/3 as pingjunScore from score order bypingjunScore desc;

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

| class_id | name   | pingjunScore |

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

| 1002     |xiaoliu |      76.0000 |

| 1001     |xiaowu  |      73.0000 |

| 1002     |xiaosi  |      70.0000 |

| 1001     |xiaosan |      67.0000 |

| 1002     |xiaoer  |      64.0000 |

| 1001     |xiaoyi  |      61.0000 |

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

 

10.授权一个帐户teacher能够在192.168.10.0网段的任意机器上登录,并对score表有select,update权限

mysql -uroot -puplooking -e "grantselect,update on up.score to 'teacher'@'192.168.10.%';"

mysql -uroot -puplooking -e "flush privileges;"

 

11.开启二进制日志

/usr/local/mysql/support-files/mysql.server stop停止服务

vim /etc/my.cnf

在[mysqld]下面添加

log-bin=newlogname

保存退出

/usr/local/mysql/support-files/mysql.server start启动服务

 

12.每周二凌晨三点备份一下所有数据,注意每次备份不要覆盖之前的备份文件,备份后刷新一下日志

vim /tmp/quanBei.sh

# 启动服务

/usr/local/mysql/support-files/mysql.server start

mysql -uroot -puplooking

# 全备 并 刷新日志

/usr/local/mysql/bin/mysqldump -A >/tmp/AllBeiFen$(date +\%F).sql

mysql -uroot -puplooking -e "flush logs;"

chmod +x /tmp/quanBei.sh

然后写一个计划任务调用脚本

crontab -e

03 * * 2 /tmp/quanBei.sh