[root@host61 ~]# vim /etc/my.cnf[mysqld]Server_id = 61log_bin=master61:wq[root@host61 ~]# systemctl restart mysqld[root@host61 ~]# mysql -uroot –p123qqq...AMysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";Mysql> show master status ;+-----------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| master61.000001 | 441 | | | |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
把host62 设置为slave数据库服务器
[root@host62 ~]# vim /etc/my.cnf[mysqld]Server_id = 62:wq[root@host62 ~]# systemctl restart mysqld[root@host62 ~]# mysql -uroot -p暗码Mysql> change master to master_host="192.168.88.61" ,Master_user="repluser" , Master_password="123qqq...A" ,Master_log_file="master61.000001" ,Master_log_pos=441 ;Mysql> start slave;Mysql> show slave status \G Slave_IO_Running: Yes Slave_SQL_Running: Yes第二步：设置署理服务器（读写分离服务器）
[root@host61 ~]# mysql -uroot -p123qqq...A # 添加监控用户 mysqla 用户mysql> grant replication slave , replication client on *.* to mysqla@"%" identified by "123qqq...A"; # 权限分析：# replication client 监视数据库服务的运行状态 # replication slave 数据库服务器的主从角色 # 添加路由用户 mysqlb 用户mysql> grant select on mysql.* to mysqlb@"%" identified by "123qqq...A"; # 对授权库下的表有查询权限# 在从服务器查看用户是否同步[root@host62 ~]# mysql -uroot -p123qqq...A select user from mysql.user where user="mysqla";select user from mysql.user where user="mysqlb";第三步：启动读写分离服务
排错方法 : 查看日志里的报错信息 vim /var/log/maxscale/maxscale.log
第四步：测试设置读写分离服务的设置
客户端能够毗连读写分离服务器访问数据库服务
# 起首在主数据库服务器host61 添加客户端毗连利用的用户[root@host61 ~]# mysql -uroot -p暗码 create database bbsdb;create table bbsdb.a(id int);grant select,insert on bbsdb.* to yaya@"%" identified by "123qqq...A";# 在从服务器host62查看存储数据库表和添加用户[root@host62 ~]# mysql -uroot -p暗码desc bbsdb.a;select user from mysql.user where user="yaya"; # 客户端host50毗连读写分离服务器host60访问数据库服务mysql -h读写分离服务器的ip -P读写分离服务的端标语 -u数据库授权用户名 -p暗码 [root@host50 ~]# mysql -h192.168.88.60 -P4006 -uyaya -p123qqq...A
毗连读写分离服务后，可以对数据做查询和存储操纵
mysql> select * from bbsdb.a;Empty set (0.00 sec)mysql> insert into bbsdb.a values(8888);Query OK, 1 row affected (0.06 sec)mysql> select * from bbsdb.a;+------+| id |+------+| 8888 |+------+1 row in set (0.00 sec)第五步：验证
怎么验证查询select 访问就在host62从服务器获取的数据呢？
在从服务本机向表里添加1条记载（在从服务添加的新数据主服务器不会同步）
# 从服务器插入1条数据[root@host62 ~]# mysql -uroot -p123qqq...A -e 'insert into bbsdb.a values(6262)'[root@host62 ~]# mysql -uroot -p123qqq...A -e 'select * from bbsdb.a'mysql: [Warning] Using a password on the command line interface can be insecure.+------+| id |+------+| 8888 || 6262 |+------+# 主服务器查询[root@host11 ~]# mysql -uroot -p123qqq...a -e 'select * from bbsdb.a'mysql: [Warning] Using a password on the command line interface can be insecure.+------+| id |+------+| 8888 |+------+# 客户端访问读写分离服务器查询数据（查询结果为从服务器数据源） [root@host50 ~]# mysql -h192.168.88.60 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'mysql: [Warning] Using a password on the command line interface can be insecure.+------+| id |+------+| 8888 || 6262 |+------+
怎么验证存储数据insert 访问 就是存储在了主机服务器host61上？
# 客户端机插入数据[root@host50 ~]# mysql -h192.168.88.60 -P4006 -uyaya -p123qqq...A -e 'insert into bbsdb.a values(666)' # 在主服务器本机查看数据[root@host61 ~]# mysql -uroot -p123qqq...a -e 'select * from bbsdb.a'mysql: [Warning] Using a password on the command line interface can be insecure.+------+| id |+------+| 8888 || 666 |+------+[root@host50 ~]# mysql -h192.168.88.60 -P4006 -uyaya -p123qqq...A -e 'select * from bbsdb.a'mysql: [Warning] Using a password on the command line interface can be insecure.+------+| id |+------+| 8888 || 6262 || 666 |+------+
