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
半同步复制
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