实验环境:
主库端: server1:172.25.26.1
从库端: server2:172.25.26.2
一.mysql的主从复制:
1.server1主端配置:
(1)解压安装
[root@server1 mysql]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
[root@server1 mysql]# ls
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.24-1.el7.x86_64.rpm
mysql-community-test-5.7.24-1.el7.x86_64.rpm
[root@server1 mysql]# yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y
(2)更改配置文件
[root@server1 mysql]# vim /etc/my.cnf
添加
28 log-bin=mysql-bin
29 server-id=1
[root@server1 ~]# systemctl restart mysqld ##设置完之后,重启生效
(3)启动服务并查询初始化密码
[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# systemctl start mysqld
[root@server1 ~]# grep password /var/log/mysqld.log
2019-05-08T05:56:29.707178Z 1 [Note] A temporary password is generated for root@localhost: ywanwwya!1Ar
(4)安全初始化
[root@server1 mysql]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: ##这里输入刚刚查询的初始化密码
The existing password for the user account root has expired. Please set a new password.
New password: ##设置密码,注意:密码需要有数字 大小写字母 特殊字符 三者构成 缺一不可! 密码位数大于8
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
(5)登录数据库,
[root@server1 mysql]# mysql -p
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> grant replication slave on *.* to repl@'172.25.26.%' identified by 'Aa316256.';
##设置主端
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> show master status; ##查看主库状态,这里的两个参数,表示二进制文件和端口
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 447 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
参数解释:
replication ##表示授权复制的权限
*.* ##表示所有数据库可以进行同步
repl ##表示授权名,可以随意填写
'172.25.26.%' ##表示授权172.25.26.0/24的网段所有服务器可以同步, %表示任意
查看二进制日志是否打开
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON ##表示二进制日志打开 |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
21 rows in set (0.36 sec)
(6)查看用于同步二进制日志文件
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf ibdata1 mysql-bin.000002 public_key.pem
ca-key.pem ib_logfile0 mysql-bin.index server-cert.pem
ca.pem ib_logfile1 mysql.sock server-key.pem
client-cert.pem ibtmp1 mysql.sock.lock sys
client-key.pem mysql performance_schema
ib_buffer_pool mysql-bin.000001 private_key.pem
(7)物理机测试远程登录(物理机上需要安装mysql)
[root@foundation26 ~]# mysql -h 172.25.26.1 -urepl -pAa316256.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
2.从属服务器环境配置:
(1)安装软件
[root@server2 mnt]# yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y
(2)编辑配置文件
[root@server2 ~]# vim /etc/my.cnf
添加
28 server-id=2
(3)开启服务并查询密码
[root@server2 ~]# systemctl start mysqld
[root@server2 ~]# grep password /var/log/mysqld.log
2019-05-08T05:56:48.370209Z 1 [Note] A temporary password is generated for root@localhost: Ap&;fsAqv4eC
(4)安全初始化
[root@server2 ~]# mysql_secure_installation -p'Ap&;fsAqv4eC'
mysql_secure_installation: [Warning] Using a password on the command line interface can be insecure.
Securing the MySQL server deployment.
The existing password for the user account root has expired. Please set a new password.
New password: ##设置密码,这里为了方便起见和主库设置相同的密码
Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
或者
[root@server2 ~]# mysql -p
Enter password:
mysql> alter user root@localhost identified by 'Aa316256.'; ##安全初始化
Query OK, 0 rows affected (0.00 sec)
(5)登录数据库 并设置主从复制
mysql> change master to master_host='172.25.26.1', master_user='repl', master_password='Aa316256.', master_log_file='mysql-bin.000002',master_log_pos=447;
##这里host填入主库ip,log_file和log_pos填入查询主库状态时的得到的参数
Query OK, 0 rows affected, 2 warnings (0.40 sec)
mysql> start slave; ##开启slave模块,让设置生效
Query OK, 0 rows affected (0.09 sec)
mysql> show slave status\G; ##查询主从复制状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.26.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 447
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes ##这里要上下两个Yes,才表示主从复制设置成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 447
Relay_Log_Space: 529
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0633a285-7156-11e9-8edd-525400402603
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
3.测试
server1上添加数据看server2是否同步过来
(1)server1创建:
mysql> create database westos; ##创建库
Query OK, 1 row affected (0.01 sec)
mysql> use westos ##进入库
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table usertb(
-> username varchar(10) not null,
-> password varchar(15) not null); ##创建表头及格式
Query OK, 0 rows affected (0.03 sec)
mysql> desc usertb; ##显示表格式
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into usertb values('user1','123');##插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from usertb; ##显示表的数据
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
(2)server2查看:
mysql> show databases; ##查看是否有新的库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
mysql> use westos ##进入库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb; ##查看指定数据
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
二、基于GDIT主从复制
-
mysql数据库从5.6.5开始新增一种基于GDIT的复制方式。GTID (Global Transaction ID)
是对于一个已提交事务的编号,并且是一个全局唯一的编号。 GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个
MySQL 实例的唯一标识。主从复制,默认是通过pos复制(postion),就是说在日志文档里,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号,我们在配置主从复制时从节点时,要输入master的log_pos值就是这个原因,要求它从哪个pos开始同步数据库里的数据,这也是传统复制技术。
MySQL5.6增加了GTID复制,GTID就是类似于pos的一个作用,不过它是整个mysql复制架构全局通用的,就是说在这整个mysql冗余架构中,它们的日志文件里事件的GTID值是一致的.
GTID (Global Transaction ID) 是对于一个已提交事务的编号,并且是一个全局唯一的编号。 GTID 实际上 是由
UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID
代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性,故障恢复以及容错能力。
pos与GTID有什么区别?
- 两者都是日志文件里事件的一个标志,如果将整个mysql集群看作一个整体,pos就是局部的,GTID就是全局的.
1.环境部署:
(1)修改配置文件并重启服务
server1:
vim /etc/my.cnf
32 gtid_mode=ON
33 enforce-gtid-consistency=true
server2:
vim /etc/my.cnf
30 gtid_mode=ON
31 enforce-gtid-consistency=true
systemctl restart mysqld ##server1和server2都重启服务
(2)在server2从库端先停掉slave,然后重新创建连接
如果进行change master to时使用MASTER_AUTO_POSITION = 1, slave连接master将使用基于GTID的复制协议。
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='172.25.26.1', master_user='repl', master_password='Aa316256.', master_auto_position=1;
##重新创建连接
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave; ##重启slave
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; ##查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.26.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 698
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 698
Relay_Log_Space: 623
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0633a285-7156-11e9-8edd-525400402603
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 0633a285-7156-11e9-8edd-525400402603:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2.测试:
(1)在server1添加数据
mysql> insert into usertb values('user4','123');
Query OK, 1 row affected (0.09 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
(2)在server2查看:
mysql> select * from westos.usertb; ##效果1
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> use mysql;
Database changed
mysql> select * from gtid_executed; ##效果2查看
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 0633a285-7156-11e9-8edd-525400402603 | 1 | 1 |
| 0633a285-7156-11e9-8edd-525400402603 | 2 | 2 |
| 0633a285-7156-11e9-8edd-525400402603 | 3 | 3 |
+--------------------------------------+----------------+--------------+
3 rows in set (0.00 sec)
三、基于GDIT的半同步
- MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。
- 半同步复制就是为了解决数据丢失的问题。
server1主库环境配置:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';##安装服务插件
Query OK, 0 rows affected (0.02 sec)
mysql> set global rpl_semi_sync_master_enabled=ON; ##开启服务
Query OK, 0 rows affected (0.00 sec)
mysql> select plugin_name,plugin_status
-> from information_schema.plugins
-> where plugin_name like '%semi%'; ##查看服务状态
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.00 sec)
mysql> show variables like '%semi%'; ##查看环境变量
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
mysql> show status like '%rpl_semi_sync%'; ##查看状态变量
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
server2从属库环境配置
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';##安装服务
Query OK, 0 rows affected (0.01 sec)
mysql> set global rpl_semi_sync_slave_enabled=ON;##开启端口
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%'; ##查看环境变量
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread; ##重启IO线程
Query OK, 0 rows affected (0.00 sec)
- 注意:要重启从上的IO线程,如果没有重启,则默认还是异步复制,重启后,slave会在master上注册为半同步复制的slave角色
测试:
(1)半同步失败时:
server2关闭IO线程:
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
server1插入数据,这是由于server2的IO线程关闭,server1会等待十秒,十秒后会变成异步复制
mysql> insert into usertb values ('user5','555');
Query OK, 1 row affected (10.01 sec)
mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |表示没有成功接收slave提交的次数,也就是使用半同步失败的次数,10s后没有得到反馈信息,会转为异步复制
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |使用半同步成功的次数,数据的一致性能提高
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
这时再次插入数据,主端在等待了一次之后,默认变成了异步复制,无需再等待10s
mysql> insert into usertb values ('user6','555');
Query OK, 1 row affected (0.01 sec)
mysql> show status like '%rpl%'; ##查看状态
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |##异步次数变为2
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |半同步还没有成功
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> select * from usertb; ##主端查询数据
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
| user5 | 555 |
| user6 | 555 |
+----------+----------+
6 rows in set (0.00 sec)
server2中先不开启IO线程,直接查看数据
mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
+----------+----------+
4 rows in set (0.00 sec)
mysql> start slave io_thread; ##开启IO线程
Query OK, 0 rows affected (0.00 sec)
mysql> select * from usertb; ##查看数据是否异步同步过来
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
| user5 | 555 |
| user6 | 555 |
+----------+----------+
6 rows in set (0.00 sec)
(2)半同步成功
server1:
这时待从库开启IO线程后,在插入数据,查看是否同步
mysql> insert into usertb values ('user7','555');
Query OK, 1 row affected (0.01 sec)
mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 4566 |
| Rpl_semi_sync_master_tx_wait_time | 4566 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |##这时查看半同步成功次数变为1
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> show variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
在server2中查看
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
| user4 | 123 |
| user5 | 555 |
| user6 | 555 |
| user7 | 555 |
+----------+----------+
7 rows in set (0.00 sec)
四.组复制
组复制模型:
- MySQL组复制是MySQL
5.7.17开始引入的新功能,为主从复制实现高可用功能。它支持单主模型和多主模型两种工作方式(默认是单主模型)。 - 单主模型:从复制组中众多个MySQL节点中自动选举一个master节点,只有master节点可以写,其他节点自动设置为read
only。当master节点故障时,会自动选举一个新的master节点,选举成功后,它将设置为可写,其他slave将指向这个新的master。 - 多主模型:复制组中的任何一个节点都可以写,因此没有master和slave的概念,只要突然故障的节点数量不太多,这个多主模型就能继续可用。
组复制原理:
- 复制组由多个 server成员构成,并且组中的每个 server
成员可以独立地执行事务。但所有读写(RW)事务只有在冲突检测成功后才会提交。只读(RO)事务不需要在冲突检测,可以立即提交。 - 换句话说,对于任何 RW 事务,提交操作并不是由始发 server 单向决定的,而是由组来决定是否提交。准确地说,在始发 server
上,当事务准备好提交时,该 server
会广播写入值(已改变的行)和对应的写入集(已更新的行的唯一标识符)。然后会为该事务建立一个全局的顺序。最终,这意味着所有 server
成员以相同的顺序接收同一组事务。因此,所有 server 成员以相同的顺序应用相同的更改,以确保组内一致。 - 组复制使您能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此,只要它不是全部或多数 server
发生故障,即使有一些 server 故障,系统仍然可用,最多只是性能和可伸缩性降低,但它仍然可用。server
故障是孤立并且独立的。它们由组成员服务来监控,组成员服务依赖于分布式故障检测系统,其能够在任何 server
自愿地或由于意外停止而离开组时发出信号。 - 总之,MySQL 组复制提供了高可用性,高弹性,可靠的 MySQL 服务。
在单主模式下部署组复制:
-
准备单台纯净的安装过mysql的虚拟机,mysql的安装
主机 IP 角色 server3 172.25.26.1 master server4 172.25.26.2 slave server5 172.25.26.3 slave
1.server1环境配置
1.server1:
(1)关闭数据库,删除以前的文件,重新配置
[root@server1 mysql]# systemctl stop mysqld
[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf ib_buffer_pool mysql-bin.000001 private_key.pem westos
ca-key.pem ibdata1 mysql-bin.000002 public_key.pem
ca.pem ib_logfile0 mysql-bin.000003 server-cert.pem
client-cert.pem ib_logfile1 mysql-bin.index server-key.pem
client-key.pem mysql performance_schema sys
[root@server1 mysql]# cat auto.cnf ##查询uuid
[auto]
server-uuid=0633a285-7156-11e9-8edd-525400402603
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# ls
(2)编辑配置文件
[root@server3 mysql]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64 ###指示Server必须为每个事务收集写集合,并使用XXHASH64哈希算法将其编码为散列
loose-group_replication_group_name="0633a285-7156-11e9-8edd-525400402603" ##告知插件,正在加入或创建的组要命名,一般写UUID
loose-group_replication_start_on_boot=off ##指示插件在 server 启动时不自动启动组复制。
loose-group_replication_local_address="172.25.26.1:24901" ##告诉插件使用 IP 地址 本地主机,端口 24901 用于接受来自组中其他成
员的传入连接。
loose-group_replication_group_seeds="172.25.26.1:24901,172.25.26.2:24901,172.25.26.3:24901"
loose-group_replication_bootstrap_group=off #配置是否自动引导组
loose-group_replication_ip_whitelist="127.0.0.1,172.25.26.0/24" # 用户白名单
loose-group_replication_enforce_update_everywhere_checks=ON #多主模式下为多主更新启用或禁用严格一致性检查。
loose-group_replication_single_primary_mode=OFF #设置组自动选择一个 server 来处理读/写工作。
(3)开启服务后查看新密码
[root@server1 mysql]# systemctl start mysqld
[root@server1 mysql]# grep password /var/log/mysqld.log
2019-05-08T12:54:00.063606Z 1 [Note] A temporary password is generated for root@localhost: gtsk=B3-BU&i
(4)初始化数据库
[root@server1 mysql]# mysql -p'gtsk=B3-BU&i'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-log
Copyright (c) 2000, 2018, 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 clear the current input statement.
mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
(5)启动组复制
mysql> SET SQL_LOG_BIN=0; #禁用二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec) #创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; ##加用户权限
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES; ##刷洗数据
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1; #启用二进制日志
Query OK, 0 rows affected (0.00 sec)
##一旦配置了用户,使用CHANGE MASTER TO语句将服务器配置为在下一次需要从其他成员恢复状态时使用group_replication_recovery复制通道的给定凭证。发出以下命令,用创建用户时使用的值替换rpl_user和密码。
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.23 sec)
mysql> INSTALL PLUGIN group_replication SONAME #安装组复制插件'group_replication.so';
Query OK, 0 rows affected (0.85 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##master上要先打开,等打开组复制之后再开启(slave上不用进行)
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION; ##打开组复制
Query OK, 0 rows affected (2.88 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
##查看组成员,查看状态,显示ONLINE则开启成功
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 595fc1a4-7190-11e9-9c92-525400402603 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
2.server2配置:
[root@server2 mysql]# systemctl stop mysqld
[root@server2 mysql]# cd /var/lib/mysql
[root@server2 mysql]# rm -fr *
[root@server2 mysql]# vim /etc/my.cnf
30 server_id=2
31 gtid_mode=ON
32 enforce_gtid_consistency=ON
33 master_info_repository=TABLE
34 relay_log_info_repository=TABLE
35 binlog_checksum=NONE
36 log_slave_updates=ON
37 log_bin=binlog
38 binlog_format=ROW
39
40 transaction_write_set_extraction=XXHASH64
41 loose-group_replication_group_name="0633a285-7156-11e9-8edd-525400402603"
42 loose-group_replication_start_on_boot=off
43 loose-group_replication_local_address= "172.25.26.2:24901"
44 loose-group_replication_group_seeds= "172.25.26.1:24901,172.25.26.2:24901,17 2.25.26.3:24901"
45 loose-group_replication_bootstrap_group=off
46 loose-group_replication_ip_whitelist="127.0.0.1,172.25.26.0/24"
47 loose-group_replication_enforce_update_everywhere_checks=ON
48 loose-group_replication_single_primary_mode=OFF
[root@server2 mysql]# systemctl start mysqld
[root@server2 mysql]# grep password /var/log/mysqld.log
2019-05-02T07:37:31.463140Z 1 [Note] A temporary password is generated for root@localhost: s#oqUBDgj6gf
[root@server2 mysql]# mysql -p's#oqUBDgj6gf'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log
Copyright (c) 2000, 2018, 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 clear the current input statement.
mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.26 sec)
3.server3配置:
[root@server3 mnt]# vim /etc/my.cnf
[root@server3 mnt]# systemctl start mysqld
[root@server3 mnt]# grep password /var/log/mysqld.log
2019-05-02T07:44:49.584904Z 1 [Note] A temporary password is generated for root@localhost: 7-rKw9vu8w,B
[root@server3 mnt]# mysql -p'7-rKw9vu8w,B'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log
Copyright (c) 2000, 2018, 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 clear the current input statement.
mysql> alter user root@localhost identified by 'Aa316256.';
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Aa316256.';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Aa316256.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.08 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.06 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.25 sec)
4.server1中查看server2和server3是否添加到组中
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 051792d9-7194-11e9-8080-525400f87789 | server2 | 3306 | ONLINE |
| group_replication_applier | 595fc1a4-7190-11e9-9c92-525400402603 | server1 | 3306 | ONLINE |
| group_replication_applier | ad3b36a9-7194-11e9-bbc4-52540042c7d6 | server3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
5.测试
在server1中向数据库添加数据,在2和3中查看是否同步过来
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.01 sec)
在server3中给数据库添加数据
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO t1 VALUES (2,'hui');
Query OK, 1 row affected (0.07 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | hui |
+----+------+
2 rows in set (0.00 sec)
在server2查看
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | hui |
+----+------+
2 rows in set (0.00 sec)
五.mysql的读写分离
1.什么是读写分离?
- MySQL读写分离是指让master处理写操作,让slave处理读操作,非常适用于读操作量比较大的场景,可减轻master的压力。
- 使用mysql-proxy实现mysql的读写分离,mysql-proxy实际上是作为后端mysql主从服务器的代理,它直接接受客户端的请求,对SQL语句进行分析,判断出是读操作还是写操作,然后分发至对应的mysql服务器上。
2.为什么要读写分离?
- 因为数据库的写操作相对读操作是比较耗时的,所以数据库的读写分离,解决的是数据库的写入,影响了查询的效率。
3.实验环境:
- rhel7.3 selinux and firewalld disabled
server1:172.25.26.1(master)
server2:172.25.26.2(slave)
server3:172.25.26.3(mysql-proxy)
4.搭建步骤
(1)[server1和server2]: 实现主从复制
测试:主从复制
在server1(主)上创建库,进入库,创建表,插入表信息
mysql> insert into userlist values('user1','123');
Query OK, 1 row affected (0.10 sec)
在server2(从)查看
mysql> select * from westos.userlist;
+----------+----------+ | username | password |
+----------+----------+ | user1 | 123 |
+----------+----------+ 1 row in set (0.00 sec)
在master(server1)上授权(授予全部权限,但是仅仅是实验环境所与不太在意安全)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Aa316256.';
Query OK, 0 rows affected, 1 warning (0.15 sec)
(2)server3 proxy环境配置
[root@server3 mysql-proxy]# systemctl stop mysqld ##停止mysqld服务,防止影响
下载安装包并解压到指定目录
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
建立目录存放读写分离的配置文件和日志
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir logs
将mysql-proxy的二进制命令放进系统环境变量中
[root@server3 mysql-proxy]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 mysql-proxy]# source ~/.bash_profile
修改数据库发生读写分离时的最大最小值
[root@server3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy
[root@server3 mysql-proxy]# vim rw-splitting.lua
40 min_idle_connections = 1, ##最小连接数
41 max_idle_connections = 2, ##最大连接数,最大连接数大于2时发生读写分离
实现读写分离是有lua脚本实现的,现在mysql-proxy里面已经集成,无需再安装
创建配置文件
[root@server3 ~]# cd /usr/local/mysql-proxy/conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root ##运行mysql-proxy用户
proxy-address=0.0.0.0:3306 ##mysql-proxy运行ip和端口
proxy-read-only-backend-addresses=172.25.26.2:3306 ##slave用户:只读
proxy-backend-addresses=172.25.26.1:3306 ##master用户:可读写
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua ##lua脚本地址
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log ##日志位置
log-level=debug #定义log日志级别,由高到低分别有(error|warning|info|message|debug)
daemon=true ##打入后台
keepalive=true ##mysql-proxy崩溃时,尝试重启(持续连接)
给文件设置权限,再启动mysql-proxy(否则会启动失败)
[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
启动:
[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
此时查看端口3306是否打开
[root@server3 conf]# netstat -anltp
(3)测试
在物理机上安装mysql客户端
在物理机上打开三个shell,都通过server3连接数据库进行如下操作:
[root@foundation26 ~]# mysql -h 172.25.26.3 -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> select * from westos.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user1 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
测试1:
(1)master和slave状态都开启
(2)在物理机上通过server3连接数据库,向数据库的表中插入新的数据信息
MySQL [(none)]> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [westos]> insert into userlist values('user2','222');
Query OK, 1 row affected (0.08 sec)
(3)在server1(主)和server2(从)上都可以看到添加的信息
注意:
在master端可以看到数据,说明写操作是在server1(master)上,而在server2上看到数据,是因为server1和server2是主从复制关系。(不能说明server2可以进行写操作)
测试2
(1)关闭server2(slave)
mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)
(2)物理机添加新的信息,在master上可以查看到,slave上无法看到
MySQL [westos]> insert into userlist values('user3','333');
Query OK, 1 row affected (0.37 sec)
master上查看
slave上不能查看到
说明:读写分离了,读操作是在server2(slave)上
测试3
1.关掉server1(master)的mysql,开启server2的slave
server1
server2
2.在物理机上连接数据库,发现插入数据失败
说明:读写分离了,写操作在server1(master)上
测试4:通过tcpdump抓取数据读写分离
(1).server3上安装tcpdump抓包工具
[root@server3 conf]# yum install -y tcpdump
(2).server3上执行以下命令:可以看到客户端登陆mysql-proxy的操作
[root@server3 conf]# tcpdump -i eth0 port 3306
(3)执行上一条命令后不要关掉,然后在物理机上连接数据库,然后给表中插入一条新的信息后,在server3上看到相应的抓包信息
发现当连接3个物理机后,执行写操作是通过server3写入到server2(msater)中的