mysql主从复制,读写分离,半同步复制实现

时间:2022-09-20 15:24:47


mysql复制,主服务器能读能写,从服务器只读,以及半同步复制

mysql主从复制,读写分离,半同步复制实现


主服务器 192.168.81.132

从服务器 192.168.81.133



MySQL简单复制应用扩展:


1、主从服务器时间要同步(ntp服务器):

 yum -y install ntpdate apt-get -y install ntpdate
 [root@localhost ~]# crontab -e
 */5 * * * * /usr/sbin/ntpdate cn.pool.ntp.org > /dev/null


【如果报错】-bash: crontab: command not found

1. 确认crontab是否安装:执行 crontab 命令如果报 command not found,就表明没有安装2. 安装 crontab执行 yum install -y vixie-cron3. 确认是否安装成功:执行 crontab -l4. 看是否设置了开机自动启动chkconfig --list crond5. 启动crontabservice crond star




     2、如何限制从服务器只读?

read-only=ON注意:仅能限制那不具有SUPER权限用户无法执行写操作;[root@localhost ~]# vim /etc/my.cnf +62read-only = ON[root@localhost ~]# service mysqld restart查看read-only是否已经启动mysql> show global variables like '%read_only%';+---------------+-------+| Variable_name | Value |+---------------+-------+| read_only     | ON    |+---------------+-------+1 row in set (0.00 sec)在主服务器上把hellodb库的所有权限给一个用户mysql> grant all on hellodb.* to 'testuser'@'192.168.%.%' identified by 'testpass';在从服务器上看一下生成的新用户mysql> use mysql;Database changedmysql> select user,host from user;+----------+-----------------------+| user     | host                  |+----------+-----------------------+| root     | 127.0.0.1             || repluser | 192.168.%.%           || testuser | 192.168.%.%           || root     | ::1                   || root     | localhost             || root     | localhost.localdomain |+----------+-----------------------+6 rows in set (0.00 sec)随便连接到从服务器的mysql[root@localhost ~]# mysql -u testuser -h 192.168.81.133 -pmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || test               |+--------------------+3 rows in set (0.00 sec)mysql> use hellodb;Database changedmysql> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes           || coc               || courses           || scores            || students          || t1                || teachers          || toc               |+-------------------+8 rows in set (0.01 sec)mysql> create table t1 (id int);ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement可读,但是没有写操作!想限制所有用户,在从服务器锁定:mysql> FLUSH TABLES WITH READ LOCK;3、如何主从复制时的事务安全?mysql> show global variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in set (0.00 sec)提高I/O性能,建议在主服务器关闭autocommit,手动提交在主服务器上配置,立即同步到二进制日志文件:sync_binlog=1




半同步复制

mysql主从复制,读写分离,半同步复制实现

MySQL5.5 除了支持内置的异步复制机制,还提供了接口支持半同步复制的机制。


异步复制的缺点:


MySQL复制默认是异步复制,Master将事件写入binlog,但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下,如果Master宕机,事务在Master上已提交,但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制,此时Slave也可能会丢失事务。




半同步复制的概念:

i.

当Slave主机连接到Master时,能够查看其是否处于半同步复制的机制。


ii.

当Master上开启半同步复制的功能时,至少应该有一个Slave开启其功能。此时,一个线程在Master上提交事务将受到阻塞,直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件,或等待超时。


iii.

当一个事务的事件都已写入其relay-log中且已刷新到磁盘上,Slave才会告知已收到。


iv.

如果等待超时,也就是Master没被告知已收到,此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了,Master与其Slave自动转换为半同步复制的机制。


v.

半同步复制的功能要在Master,Slave都开启,半同步复制才会起作用;否则,只开启一边,它依然为异步复制。




同步,异步,半同步复制的比较:


同步复制:Master提交事务,直到事务在所有的Slave都已提交,此时才会返回客户端,事务执行完毕。缺点:完成一个事务可能会有很大的延迟。


异步复制:当Slave准备好才会向Master请求binlog。缺点:不能保证一些事件都能够被所有的Slave所接收。


半同步复制:半同步复制工作的机制处于同步和异步之间,Master的事务提交阻塞,只要一个Slave已收到该事务的事件且已记录。它不会等待所有的Slave都告知已收到,且它只是接收,并不用等其完全执行且提交。




查看次版本是否有半同步复制插件[root@localhost ~]# ll /usr/local/mysql/lib/plugin/semisync_*-rwxr-xr-x. 1 root root 172804 10月 13 03:28 /usr/local/mysql/lib/plugin/semisync_master.so-rwxr-xr-x. 1 root root  93658 10月 13 03:28 /usr/local/mysql/lib/plugin/semisync_slave.so主服务器:安装semisync_master.somysql> select user();+----------------+| user()         |+----------------+| root@localhost |+----------------+ 只有root用户才能执行安装插件mysql> help installmysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled       | OFF   |  主节点没有启动| rpl_semi_sync_master_timeout       | 10000 |   单位是毫秒 | rpl_semi_sync_master_trace_level   | 32    || rpl_semi_sync_master_wait_no_slave | ON    |+------------------------------------+-------+4 rows in set (0.01 sec)mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';+------------------------------------+-------+| Variable_name                      | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled       | ON    || rpl_semi_sync_master_timeout       | 2000  || rpl_semi_sync_master_trace_level   | 32    || rpl_semi_sync_master_wait_no_slave | ON    |+------------------------------------+-------+4 rows in set (0.01 sec从服务器上操作:找一个距离主服务器近,带宽足够的从服务器,安装semisync_slave.so就好了1 row in set (0.00 sec)mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | OFF   || rpl_semi_sync_slave_trace_level | 32    |+---------------------------------+-------+2 rows in set (0.05 sec)mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled     | ON    || rpl_semi_sync_slave_trace_level | 32    |+---------------------------------+-------+2 rows in set (0.03 sec)【测试,在主服务器上操作】mysql> use hellodb;Database changedmysql> create table t2 (Name char(10) );Query OK, 0 rows affected (2.04 sec) 看看等待时间,这是第一次的超时!再创建一次,因为上次超时,本次就不会在等待Slave了mysql> create table t3 (Name char(10) );Query OK, 0 rows affected (0.01 sec)从服务器操作,重启IO_THREAD就可以工作在半同步模式下了mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;【测试,在主服务器上操作】在主服务器验正半同步复制是否生效mysql> create table t4 (Name char(10) );Query OK, 0 rows affected (0.02 sec)mysql> SHOW GLOBAL STATUS LIKE '%semi%';+--------------------------------------------+-------+| Variable_name                              | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients               | 1     | 可以看到半同步的slave服务器个数| Rpl_semi_sync_master_net_avg_wait_time     | 924   || Rpl_semi_sync_master_net_wait_time         | 924   || Rpl_semi_sync_master_net_waits             | 1     || 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      | 1091  || Rpl_semi_sync_master_tx_wait_time          | 1091  || 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     |+--------------------------------------------+-------+14 rows in set (0.01 sec)一旦某次等待超时,会自动降级为异步;如果不想使用插件mysql> help uninstallmysql> help uninstallUNINSTALL PLUGIN plugin_name