Top

NSD RDBMS2 DAY02

  1. 案例1：实现MySQL读写分离
  2. 案例2：配置MySQL多实例

1 案例1：实现MySQL读写分离

1.1 问题

1.2 方案

使用4台虚拟机，如图-1所示。其中192.168.4.51和192.168.4.52，分别提供读、写服务，均衡流量，通过主从复制保持数据一致性，由MySQL代理192.168.4.57面向客户端提供服务，收到SQL写请求时，交给主服务器处理，收到SQL读请求时，交给从服务器处理。在客户机192.168.4.50测试配置。

图－1

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：搭建MySQL一主一从同步结构

1）配置主服务器192.168.4.51

  1. ]# vim /etc/my.cnf
  2. [mysqld]
  3. server_id=51    //指定服务器ID号 
  4. log-bin=master51        //启用binlog日志，并指定文件名前缀
  5. ...
  6. [root@master10 ~]# systemctl restart mysqld        //重启mysqld

2）主服务器授权用户，并查看binlog日志信息

  1. ]# mysql -uroot -p123456
  2. mysql> grant all on *.* to 'repluser'@'%' identified by '123456';
  3. Query OK, 0 rows affected, 1 warning (0.00 sec)
  4.  
  5. mysql> show master status;
  6. +-----------------+----------+--------------+------------------+-------------------+
  7. | File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  8. +-----------------+----------+--------------+------------------+-------------------+
  9. | master51.000001 |      449 |              |                  |                   |
  10. +-----------------+----------+--------------+------------------+-------------------+
  11. 1 row in set (0.00 sec)

3）配置从服务器192.168.4.52

]# vim /etc/my.cnf

[mysqld]

server_id=52 //指定服务器ID号，不要与Master的相同

:wq

]# systemctl restart mysqld

4）配置从服务器192.168.4.52，指定主服务器信息，日志文件、偏移位置（参考MASTER上的状态输出）

  1. ]# mysql -uroot -p123456
  2. mysql> change master to master_host='192.168.4.51',
  3.     -> master_user='repluser',
  4.     -> master_password='123456',
  5.     -> master_log_file='master51.000001',
  6.     -> master_log_pos=449;
  7. Query OK, 0 rows affected, 2 warnings (0.01 sec)
  8.  
  9. mysql> start slave;
  10. Query OK, 0 rows affected (0.01 sec)
  11.  
  12. mysql> show  slave status\G;
  13. *************************** 1. row ***************************
  14.                Slave_IO_State: Waiting for master to send event
  15.                   Master_Host: 192.168.4.51
  16.                   Master_User: repluser
  17.                   Master_Port: 3306
  18.                 Connect_Retry: 60
  19.               Master_Log_File: master51.000001
  20.           Read_Master_Log_Pos: 738
  21.                Relay_Log_File: slave20-relay-bin.000002
  22.                 Relay_Log_Pos: 319
  23.         Relay_Master_Log_File: master51.000001
  24.              Slave_IO_Running: Yes        //IO线程YES
  25.             Slave_SQL_Running: Yes        //SQL线程YES
  26.               Replicate_Do_DB: 
  27.           Replicate_Ignore_DB: 
  28.            Replicate_Do_Table: 
  29.        Replicate_Ignore_Table: 
  30.       Replicate_Wild_Do_Table: 
  31.   Replicate_Wild_Ignore_Table: 
  32.                    Last_Errno: 0
  33.                    Last_Error: 
  34.                  Skip_Counter: 0
  35.           Exec_Master_Log_Pos: 738
  36.               Relay_Log_Space: 528
  37.               Until_Condition: None
  38.                Until_Log_File: 
  39.                 Until_Log_Pos: 0
  40.            Master_SSL_Allowed: No
  41.            Master_SSL_CA_File: 
  42.            Master_SSL_CA_Path: 
  43.               Master_SSL_Cert: 
  44.             Master_SSL_Cipher: 
  45.                Master_SSL_Key: 
  46.         Seconds_Behind_Master: 0
  47. Master_SSL_Verify_Server_Cert: No
  48.                 Last_IO_Errno: 0
  49.                 Last_IO_Error: 
  50.                Last_SQL_Errno: 0
  51.                Last_SQL_Error: 
  52.   Replicate_Ignore_Server_Ids: 
  53.              Master_Server_Id: 10
  54.                   Master_UUID: 95ada2c2-bb24-11e8-abdb-525400131c0f
  55.              Master_Info_File: /var/lib/mysql/master.info
  56.                     SQL_Delay: 0
  57.           SQL_Remaining_Delay: NULL
  58.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  59.            Master_Retry_Count: 86400
  60.                   Master_Bind: 
  61.       Last_IO_Error_Timestamp: 
  62.      Last_SQL_Error_Timestamp: 
  63.                Master_SSL_Crl: 
  64.            Master_SSL_Crlpath: 
  65.            Retrieved_Gtid_Set: 
  66.             Executed_Gtid_Set: 
  67.                 Auto_Position: 0
  68.          Replicate_Rewrite_DB: 
  69.                  Channel_Name: 
  70.            Master_TLS_Version: 
  71. 1 row in set (0.00 sec)

5）测试配置，在主服务器本机创建数据库 aa库

  1. ]# mysql –uroot –p123456
  2. mysql> create database aa;
  3. Query OK, 1 row affected (0.00 sec)
  4.  
  5. mysql> show databases;
  6. +--------------------+
  7. | Database           |
  8. +--------------------+
  9. | information_schema |
  10. | aa                 |
  11. | mysql              |
  12. | performance_schema |
  13. | sys                |
  14. +--------------------+
  15. 5 rows in set (0.00 sec)

6）从服务器上查看，有aa库

  1. mysql> show databases;
  2.  
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | aa                 |
  8. | mysql              |
  9. | performance_schema |
  10. | sys                |
  11. +--------------------+
  12. 5 rows in set (0.00 sec)

步骤二：配置maxscale代理服务器

1）环境准备

关闭防火墙和SElinux，保证yum源可以正常使用，安装提供服务的软件

  1. ]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm         //安装maxscale
  2. warning: maxscale-2.1.2-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
  3. Preparing...                          ################################# [100%]
  4. Updating / installing...
  5.    1:maxscale-2.1.2-1                 ################################# [100%]

2）修改主配置文件

  1. ]# vim /etc/maxscale.cnf
  2. [maxscale]
  3. threads=auto            //运行的线程的数量
  4.  
  5. [server1]            //定义数据库服务器
  6. type=server
  7. address=192.168.4.51        //主服务器ip
  8. port=3306
  9. protocol=MySQLBackend        
  10.  
  11. [server2]
  12. type=server
  13. address=192.168.4.52        //从服务器IP
  14. port=3306
  15. protocol=MySQLBackend
  16.  
  17.  
  18. [MySQL Monitor]                //定义监控的数据库服务器
  19. type=monitor
  20. module=mysqlmon
  21. servers=server1, server2        //监控的数据库列表，不能写ip
  22. user=maxscalemon                    //监控用户
  23. passwd=123qqq...A                //密码
  24. monitor_interval=10000        
  25.  
  26.  
  27.  
  28. #[Read-Only Service]        //不定义只读服务
  29. #type=service
  30. #router=readconnroute
  31. #servers=server1
  32. #user=myuser
  33. #passwd=mypwd
  34. #router_options=slave
  35.  
  36. [Read-Write Service]            //定义读写分离服务
  37. type=service
  38. router=readwritesplit
  39. servers=server1, server2
  40. user=maxscalerouter            //路由用户
  41. passwd=123qqq…A                //密码
  42. max_slave_connections=100%
  43.  
  44. [MaxAdmin Service]        //定义管理服务
  45. type=service
  46. router=cli
  47.  
  48. #[Read-Only Listener]        //不定义只读服务使用的端口号
  49. #type=listener
  50. #service=Read-Only Service
  51. #protocol=MySQLClient
  52. #port=4008
  53.  
  54. [Read-Write Listener]            //定义读写服务使用的端口号
  55. type=listener
  56. service=Read-Write Service
  57. protocol=MySQLClient
  58. port=4006
  59.  
  60. [MaxAdmin Listener]        //管理服务使用的端口号
  61. type=listener
  62. service=MaxAdmin Service
  63. protocol=maxscaled
  64. socket=default
  65. port=4016     //手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少

3）添加授权用户

根据maxscale.cnf文件配置，在主/从服务器上添加对应的授权用户，因为2台数据库服务器是主从同步结构，只在主数据库服务器添加用户即可，从服务器会自动同步

  1. mysql> grant replication slave,replication client on *.* to  maxscalemon@'%' identified by "123qqq…A"; //授权监控用户
  2.  
  3. mysql> grant select on mysql.* to maxscalerouter@"%" identified by "123qqq…A"; //授权路由用户

4）查看授权用户

分别在主/从服务器上面查看

  1. mysql> select user,host from mysql.user where user like “maxscale%”;
  2. +----------------+------+
  3. | user           | host |
  4. +----------------+------+
  5. | maxscalemon    | %    |
  6. | maxscalerouter | %    |
  7. +----------------+------+
  8. 2 rows in set (0.00 sec)

在代理服务器57主机，测试授权用户

  1. ]# yum -y  install mariadb  //安装提供mysql命令的软件包
  2. ]# mysql -h 192.168.4.51 -umaxscalemon -p123qqq…A
  3. ]# mysql -h 192.168.4.52 -umaxscalemon -p123qqq…A
  4. ]# mysql -h 192.168.4.51 -umaxscalerouter -p123qqq…A
  5. ]# mysql -h 192.168.4.52 -umaxscalerouter -p123qqq…A

5）启动服务代理服务

  1. ]# maxscale -f  /etc/maxscale.cnf   
  2. ]# ps -C  maxscale        //查看进程
  3. PID TTY          TIME CMD
  4. 17930 ?        00:00:00 maxscale   
  5.  
  6. ]# netstat  -antup | grep :4006  //查看读写分离端口
  7. tcp6       0      0 :::4006      :::*                    LISTEN      17930/maxscale
  8.  
  9. ]# netstat  -antup | grep :4016  //查看管理服务端口
  10. tcp6       0      0 :::4016       :::*                    LISTEN      17930/maxscale

步骤三：测试配置

1）查看监控信息（在主机57 本机自己访问自己）

  1. ]# maxadmin  -uadmin -pmariadb -P4016
  2.  
  3. MaxScale> list  servers
  4. Servers.
  5. -------------------+-----------------+-------+-------------+--------------------
  6. Server             | Address         | Port  | Connections | Status
  7. -------------------+-----------------+-------+-------------+--------------------
  8. server1            | 192.168.4.51    |  3306 |           0 | Master, Running
  9. server2            | 192.168.4.52    |  3306 |           0 | Slave, Running
  10. -------------------+-----------------+-------+-------------+--------------------

2）在主服务器上添加访问数据连接用户

在主服务器添加即可，从服务器会自动同步数据

  1. mysql> create database gamedb;
  2. mysql> create table gamedb.a(id int);
  3. mysql> grant select,insert on  gamedb.* to  yaya66@"%" identified by "123qqq...A";

客户端连接代理服务57 访问数据

]# mysql -h192.168.4.57 -P4006 -uyaya66 -p123qqq...A

mysql> select * from gamedb.a;

mysql> insert into gamedb.a values(99);

mysql> select * from gamedb.a;

mysql> select * from gamedb.a;

Empty set (0.00 sec)

mysql>

mysql> insert into gamedb.a values(99);

Query OK, 1 row affected (0.06 sec)

mysql>

mysql> select * from gamedb.a;

+------+

| id |

+------+

| 99 |

+------+

1 row in set (0.00 sec)

3）验证57主机的数据读写分离功能

在从服务器添加新纪录

Mysql> insert into gamedb.values(52);

Mysql> select * from mysql> select * from gamedb.a;

+------+

| id |

+------+

| 99 |

| 52 |

+------+

在主服务器查看记录

Mysql> select * from mysql> select * from gamedb.a;

+------+

| id |

+------+

| 99 |

+------+

客户端连接代理服务器57 访问数据

]# mysql -h192.168.4.57 -P4006 -uyaya66 -p123qqq...A

Mysql> select * from mysql> select * from gamedb.a;

+------+

| id |

+------+

| 99 |

| 52 |

+------+

2 案例2：配置MySQL多实例

2.1 问题

在主机192.168.4.57上：

配置第1个MySQL实例

配置第2个MySQL实例

步骤一：配置多实例（192.168.4.57上操作）

什么是多实例：

在一台物理主机上运行多个数据库服务，可以节约运维成本，提高硬件利用率

1）解压软件、修改目录名、设置PATH路径

  1. ]# yum –y  install libaio
  2. ]# useradd  mysql
  3. ]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
  4. ]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
  5. ]# PATH=/usr/local/mysql/bin:$PATH
  6. ]# vim /etc/bashrc
  7. export PATH=/usr/local/mysql/bin:$PATH
  8. :wq

2）编辑主配置文件/etc/my.cnf

每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件

  1. ]# vim /etc/my.cnf
  2. [mysqld_multi]        //启用多实例
  3. mysqld = /usr/local/mysql/bin/mysqld_safe        //指定进程文件路径
  4. mysqladmin = /usr/local/mysql/bin/mysqladmin    //指定管理命令路径
  5. user = root        //指定进程用户
  6.  
  7. [mysqld1]        //实例进程名称
  8. port=3307        //端口号
  9. datadir=/dir1        //数据库目录 ，要手动创建
  10. socket=/dir1/mysqld1.sock        //指定sock文件的路径和名称
  11. pid-file=/dir1/mysqld1.pid        //进程pid号文件位置
  12. log-error=/dir1/mysqld1.err        //错误日志位置 
  13.  
  14. [mysqld2]
  15. port=3308
  16. datadir=/dir2
  17. socket=/dir2/mysqld2.sock
  18. pid-file=/dir2/mysqld2.pid
  19. log-error=/dir2/mysqld2.err 
  20. :wq

3）创建数据库目录

  1. ]# mkdir  /dir2
  2. ]# mkdir  /dir1

4）启动多实例

首次启动服务会做数据初始化 并初始和提示数据库管理员本机登录密码

  1. [root@host57 ~]# mysqld_multi  start 1 //启动实例1
  2.  
  3. Installing new database in /dir1
  4.  
  5. 2019-06-13T10:46:29.307866Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  6. 2019-06-13T10:46:30.997233Z 0 [Warning] InnoDB: New log files created, LSN=45790
  7. 2019-06-13T10:46:31.436904Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  8. 2019-06-13T10:46:31.582129Z 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: 816bf015-8dc8-11e9-b492-525400cffedc.
  9. 2019-06-13T10:46:31.605276Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  10. 2019-06-13T10:46:31.606321Z 1 [Note] A temporary password is generated for root@localhost: ly#LryiFE5fT  管理员本机登录密码
  11.  
  12. ]# ls /dir1 //查看数据库目录文件列表
  13. auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql3307.log  mysql3307.pid  mysql3307.sock  mysql3307.sock.lock  performance_schema  sys
  14.  
  15. ]# mysqld_multi  start 2  //启动实例2
  16.  
  17.  
  18. Installing new database in /dir1
  19.  
  20. 2019-06-13T10:56:55.580796Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  21. 2019-06-13T10:56:57.199217Z 0 [Warning] InnoDB: New log files created, LSN=45790
  22. 2019-06-13T10:56:57.571839Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  23. 2019-06-13T10:56:57.708168Z 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: f69f30fa-8dc9-11e9-8a17-525400cffedc.
  24. 2019-06-13T10:56:57.724096Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  25. 2019-06-13T10:56:57.724677Z 1 [Note] A temporary password is generated for root@localhost: qedTjrZs*8ma  管理员本机登录密码
  26.  
  27. ]# ls /dir1 //查看数据库目录文件列表
  28. auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql3308.log  mysql3308.pid  mysql3308.sock  mysql3308.sock.lock  performance_schema  sys

5）查看端口

  1. ]# netstat -utnlp  | grep :3307
  2. tcp6       0      0 :::3307                 :::*                    LISTEN      1151/mysqld         
  3.  
  4. ]# netstat -utnlp  | grep :3308
  5. tcp6       0      0 :::3308                 :::*                    LISTEN      1339/mysqld         
  6.  
  7. ]# netstat -utnlp | grep mysqld
  8. tcp6       0      0 :::3307                 :::*                    LISTEN      1151/mysqld         
  9. tcp6       0      0 :::3308                 :::*                    LISTEN      1339/mysqld         
  10.  
  11. # ps -C mysqld
  12.   PID TTY          TIME CMD
  13.  1151 pts/1    00:00:00 mysqld
  14.  1339 pts/1    00:00:00 mysqld
  15. [root@host57 ~]#

6）访问多实例

使用初始化密码登录实例1

  1. [root@host57 ~]# mysql -uroot -p'ly#LryiFE5fT' -S /dir1/mysqld1.sock 
  2.     mysql> alter user root@"localhost" identified by "123456";    //修改密码
  3. mysql> exit
  4. Bye
  5. [root@host57 ~]# mysql -uroot -p123456  -S /dir1/mysqld1.sock //新密码登录
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. Welcome to the MySQL monitor.  Commands end with ; or \g.
  8. Your MySQL connection id is 4
  9. Server version: 5.7.20 MySQL Community Server (GPL)
  10.  
  11. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  12.  
  13. Oracle is a registered trademark of Oracle Corporation and/or its
  14. affiliates. Other names may be trademarks of their respective
  15. owners.
  16.  
  17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  18.  
  19. mysql> show databases;
  20. +--------------------+
  21. | Database           |
  22. +--------------------+
  23. | information_schema |
  24. | mysql              |
  25. | performance_schema |
  26. | sys                |
  27. +--------------------+
  28. 4 rows in set (0.00 sec)
  29.  
  30. mysql> create database db1; //创建新库db1
  31. Query OK, 1 row affected (0.00 sec)
  32.  
  33. mysql> show databases; //查看已有的库
  34. +--------------------+
  35. | Database           |
  36. +--------------------+
  37. | information_schema |
  38. | db1                  |  //db1库
  39. | mysql              |
  40. | performance_schema |
  41. | sys                |
  42. +--------------------+
  43. 5 rows in set (0.00 sec)
  44.  
  45. mysql> exit  //断开连接
  46. Bye
  47. [root@host56 ~]# ls /dir1  //查看数据库目录文件列表 有db1库的文件夹
  48. auto.cnf        ibdata1      ibtmp1       mysqld1.pid          performance_schema
  49. db1             ib_logfile0  mysql        mysqld1.socket       sys
  50. ib_buffer_pool  ib_logfile1  mysqld1.err  mysqld1.socket.lock
  51. [root@host56 ~]#

使用初始化密码登录实例2

  1. [root@host57 ~]# mysql -uroot -p'qedTjrZs*8ma' -S /dir2/mysqld2.sock 
  2.     mysql> alter user root@"localhost" identified by "654321";    //修改密码
  3. mysql> exit
  4. Bye
  5. [root@host57 ~]# mysql -uroot –p654321  -S /dir2/mysqld2.sock //新密码登录
  6. mysql: [Warning] Using a password on the command line interface can be insecure.
  7. Welcome to the MySQL monitor.  Commands end with ; or \g.
  8. Your MySQL connection id is 4
  9. Server version: 5.7.20 MySQL Community Server (GPL)
  10.  
  11. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
  12.  
  13. Oracle is a registered trademark of Oracle Corporation and/or its
  14. affiliates. Other names may be trademarks of their respective
  15. owners.
  16.  
  17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  18.  
  19. mysql> show databases;
  20. +--------------------+
  21. | Database           |
  22. +--------------------+
  23. | information_schema |
  24. | mysql              |
  25. | performance_schema |
  26. | sys                |
  27. +--------------------+
  28. 4 rows in set (0.00 sec)
  29.  
  30. mysql>
  31. mysql> create database db2;
  32. Query OK, 1 row affected (0.00 sec)
  33.  
  34. mysql> show databases;
  35. +--------------------+
  36. | Database           |
  37. +--------------------+
  38. | information_schema |
  39. | db2                |
  40. | mysql              |
  41. | performance_schema |
  42. | sys                |
  43. +--------------------+
  44. 5 rows in set (0.00 sec)
  45.  
  46. mysql> exit
  47. Bye
  48. [root@host56 ~]# ls /dir2
  49. auto.cnf        ib_logfile0  mysqld2.err          performance_schema
  50. db2             ib_logfile1  mysqld2.pid          sys
  51. ib_buffer_pool  ibtmp1       mysqld2.socket
  52. ibdata1         mysql        mysqld2.socket.lock
  53. [root@host56 ~]#

7）停止多实例服务

mysqld_multi --user=root --password=密码 stop 实例编号

  1. ]# netstat -utnlp  | grep  mysqld
  2. tcp6       0      0 :::3307                 :::*                    LISTEN      1250/mysql
  3. tcp6       0      0 :::3308                 :::*                    LISTEN      1451/mysql
  4.  
  5. ]# mysqld_multi  --user=root --password=123456  stop 2
  6.  
  7.  [root@host56 ~]# netstat -utnlp  | grep  mysqld
  8. tcp6       0      0 :::3307                 :::*                    LISTEN      1250/mysql
  9.  
  10. ]# mysql -uroot   -p123456   -S    /dir2/mysqld2.sock //拒绝连接
  11. mysql: [Warning] Using a password on the command line interface can be insecure.
  12. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/dir2/mysqld2.sock' (2)