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

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

read-only=ON

注意：仅能限制那不具有SUPER权限用户无法执行写操作；
[root@localhost ~]# vim /etc/my.cnf +62
read-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 changed
mysql> 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 -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use hellodb;
Database changed
mysql> 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

半同步复制

查看次版本是否有半同步复制插件
[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.so
	mysql> select user();
	+----------------+
	| user()         |
	+----------------+
	| root@localhost |
	+----------------+ 只有root用户才能执行安装插件
	mysql> help install
	mysql> 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 changed
	mysql> 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 uninstall
mysql> help uninstall
UNINSTALL PLUGIN plugin_name

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

原文：http://990487026.blog.51cto.com/10133282/1702933