在以前环境基础上做这四项实验

所以要清理下环境

主库:192.168.2.11

datadir=/u01/mysqldatas

basedir =/u01/mysqlsoft/mysql

socket=/tmp/mysql.sock

port = 3306

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

innodb_buffer_pool_size = 128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

init_connect='SET NAMES utf8'

log_bin=/u01/mysqllogs/mylogbin.log

server-id=1

rpl_semi_sync_master_enabled = 1

binlog-do-db=ilike_org

default-character-set=utf8

mysql> show master status ;

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

| mylogbin. |      154| ilike_org    |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

备库1:192.168.2.12

datadir =/u01/mysqldatas

basedir =/u01/mysqlsoft/mysql

socket  =/tmp/mysql.sock

port = 3306

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

innodb_buffer_pool_size = 128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

init_connect='SET NAMES utf8'

log_bin=/u01/mysqllogs/mylogbin.log

server-id=2

rpl_semi_sync_slave_enabled = 1

log-slave-updates #从库做恢复的时候,同时把事务也写在自己的日志中.

slave-net-timeout=60

default-character-set=utf8

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.11

Master_User: d_org

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 154

Relay_Log_File: MYSQL-SLAVE2-relay-bin.

Relay_Log_Pos: 365

Relay_Master_Log_File: mylogbin.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_Table: ilike_org.ilike_school

Exec_Master_Log_Pos: 154

Relay_Log_Space: 744

Master_Server_Id: 1

Master_UUID: ad216d41-5c3a-11e8-d97c

Master_Info_File: /u01/mysqldatas/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

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

No query specified

备库2: 192.168.2.13

datadir =/u01/mysqldatas

basedir =/u01/mysqlsoft/mysql

socket  =/tmp/mysql.sock

port = 3306

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

innodb_buffer_pool_size = 128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

init_connect='SET NAMES utf8'

log_bin=/u01/mysqllogs/mylogbin.log

server-id=3

rpl_semi_sync_slave_enabled = 1

log-slave-updates

slave-net-timeout=60

replicate_do_table=ilike_org.ilike_school

binlog-do-db=ilike_worker

default-character-set=utf8

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.11

Master_User: d_org

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 154

Relay_Log_File: MYSQL-SLAVE2-relay-bin.

Relay_Log_Pos: 365

Relay_Master_Log_File: mylogbin.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_Table: ilike_org.ilike_school

Exec_Master_Log_Pos: 154

Relay_Log_Space: 744

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_Server_Id: 1

Master_UUID: ad216d41-5c3a-11e8-d97c

Master_Info_File: /u01/mysqldatas/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

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

1 row in set (0.00 sec)

No query specified

查看主库的奴隶角色

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.2.13

Master_User: d_worker

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 154

Relay_Log_File: MYSQL-MASTER-relay-bin.

Relay_Log_Pos: 365

Relay_Master_Log_File: mylogbin.

Slave_IO_Running: No

Slave_SQL_Running: No

Exec_Master_Log_Pos: 154

Relay_Log_Space: 744

Master_Server_Id: 3

Master_UUID: ae1c4db7-5f27-11e8-aee196

1 row in set (0.00 sec)

主库半同步

mysql> show variables like '%have_dynamic_loading';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| have_dynamic_loading | YES   |

+----------------------+-------+

mysql> 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 |

+-------------------------------------------+------------+

mysql> show status like '%rpl_semi_sync%';

+--------------------------------------------+-------+

| 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             | 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)

从库半同步:

mysql> show variables like '%semi%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled     | ON    |

| rpl_semi_sync_slave_trace_level | 32    |

+---------------------------------+-------+

mysql> show status like '%rpl_semi_sync%';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON    |

+----------------------------+-------+

1 row in set (0.00 sec)

从库和主库半同步状态良好!

目前的环境来说 是一主两从 分别是主库11,从库12,从库13; 不过呢 主库11和从库13互相之间也互为主从, 从库13的ilike_worke 传递给主库上.

目标是 1主 两从  开启GTID复制, 从库变成只读,复制所有的表,从库开启并发复制,主库开启组提交,从库2开启延迟复制.

主库关闭

1 stop slave;

2 RESET SLAVE ALL;#关掉主库上的奴隶角色(从库)

2.5 等一等从库应用最后日志

4 sevice mysqld stop;

两个从库关闭

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> exit;

[mysql@MYSQL-SLAVE1 u01]service mysqld stop;

Shutting down MySQL..                                      [确定]

rm: cannot remove `/var/lock/subsys/mysql': Permission denied

修改主库的参数:

[root@MYSQL-MASTER ~]# cat /etc/my.cnf

datadir=/u01/mysqldatas

basedir =/u01/mysqlsoft/mysql

socket=/tmp/mysql.sock

port = 3306

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

innodb_buffer_pool_size = 128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

init_connect='SET NAMES utf8'

log_bin=/u01/mysqllogs/mylogbin.log

server-id=1

rpl_semi_sync_master_enabled = 1

#binlog-do-db=ilike_org

gtid-mode=on

enforce-gtid-consistency=1

default-character-set=utf8

启动主库

[mysql@MYSQL-MASTER u01]$ service mysqld start

Starting MySQL..                                           [确定]

[mysql@MYSQL-MASTER u01]$ sh mysqlplus.sh

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ilike_org          |

| ilike_worker       |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

mysql> show master status;

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

| mylogbin. |      154 |              |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>  show variables like '%gtid%';

+----------------------------------+-----------+

| Variable_name                    | Value     |

+----------------------------------+-----------+

| binlog_gtid_simple_recovery      | ON        |

| enforce_gtid_consistency         | ON        |

| gtid_executed_compression_period | 1000      |

| gtid_mode                        | ON        |

| gtid_next                        | AUTOMATIC |

| gtid_owned                       |           |

| gtid_purged                      |           |

| session_track_gtids              | OFF       |

+----------------------------------+-----------+

8 rows in set (0.00 sec)

GTID 没有生效 ????

看下从库,先编辑参数,添加

gtid-mode=on

enforce-gtid-consistency=1

read_only=1

mysql> select * from mysql.gtid_executed;

Empty set (0.00 sec)

mysql> show variables like '%read_only%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| innodb_read_only | OFF   |

| read_only        | ON    |

| super_read_only  | OFF   |

| tx_read_only     | OFF   |

+------------------+-------+

4 rows in set (0.00 sec)

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to

-> master_host = '192.168.2.11',

-> master_port = 3306,

-> master_user = 'rpl',

-> master_auto_position = 1;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

MASTER_AUTO_POSITION:

该参数在mysql5.6.5版本引入,如果进行change master to时使用MASTER_AUTO_POSITION = 1,slave连接master将使用基于GTID的复制协议。

使用基于GTID协议的复制,slave会告诉master它已经接收到或执行了哪些事务。计算这个集,slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set。

结果集作为初次握手的一部分,发送到master,master发回它已经执行的且不在结果集这部分的所有事务。如果这些事务在master的binlog文件中已经被清除,master将会发送一个ER_MASTER_HAS_PURGED_REQUIRED_GTIDS错误信息到slave,复制将不会开启。

使用基于GTID的复制时(MASTER_AUTO_POSITION = 1),首先要开启gtid_mode(在my.cnf中设置gtid-mode = ON),MASTER_LOG_FILE 与 MASTER_LOG_POS也不能使用,否则会报错。

使用GTID后想要恢复到老的基于文件的复制协议,在change master to时需要指定MASTER_AUTO_POSITION = 0以及MASTER_LOG_FILE 或 MASTER_LOG_POSITION中至少一项。

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.11

Master_User: rpl

Master_Port: 3306

Connect_Retry: 60

Read_Master_Log_Pos: 154

Relay_Log_File: MYSQL-SLAVE1-relay-bin.

Relay_Log_Pos: 365

Relay_Master_Log_File: mylogbin.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Exec_Master_Log_Pos: 154

Relay_Log_Space: 579

Master_Server_Id: 1

Master_UUID: ad216d41-5c3a-11e8-d97c

Master_Info_File: /u01/mysqldatas/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

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 1

1 row in set (0.00 sec)

从库也没有?

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ilike_org          |

| ilike_worker       |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

mysql> use sharkdb

Database changed

mysql> show tables;

+-------------------+

| Tables_in_sharkdb |

+-------------------+

| studen_name       |

+-------------------+

1 row in set (0.00 sec)

mysql> desc studen_name;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| f_name | varchar(20) | YES  |     | NULL    |       |

| f_sex  | char(1)     | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show variables like '%commit%';

+-----------------------------------------+-------+

| Variable_name                           | Value |

+-----------------------------------------+-------+

| autocommit                              | ON    |

| binlog_group_commit_sync_delay          | 0     |

| binlog_group_commit_sync_no_delay_count | 0     |

| binlog_order_commits                    | ON    |

| innodb_api_bk_commit_interval           | 5     |

| innodb_commit_concurrency               | 0     |

| innodb_flush_log_at_trx_commit          | 1     |

| slave_preserve_commit_order             | OFF   |

+-----------------------------------------+-------+

8 rows in set (0.00 sec)

#设置手工提交

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%commit%';

+-----------------------------------------+-------+

| Variable_name                           | Value |

+-----------------------------------------+-------+

| autocommit                              | OFF   |

| binlog_group_commit_sync_delay          | 0     |

| binlog_group_commit_sync_no_delay_count | 0     |

| binlog_order_commits                    | ON    |

| innodb_api_bk_commit_interval           | 5     |

| innodb_commit_concurrency               | 0     |

| innodb_flush_log_at_trx_commit          | 1     |

| slave_preserve_commit_order             | OFF   |

+-----------------------------------------+-------+

8 rows in set (0.00 sec)

mysql> insert into studen_name values('zengmuansha','女');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.17 sec)

#GTID 来了

mysql> show master status;

+-----------------+----------+--------------+------------------+----------------------------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |

+-----------------+----------+--------------+------------------+----------------------------------------+

| mylogbin. |      439 |              |                  | ad216d41-5c3a-11e8-8720-08002791d97c:1 |

+-----------------+----------+--------------+------------------+----------------------------------------+

1 row in set (0.00 sec)

#再插入一笔 GTID没有变化

mysql> insert into studen_name values('shark','女');

Query OK, 1 row affected (0.01 sec)

mysql> show master status;

+-----------------+----------+--------------+------------------+----------------------------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |

+-----------------+----------+--------------+------------------+----------------------------------------+

| mylogbin. |      439 |              |                  | ad216d41-5c3a-11e8-8720-08002791d97c:1 |

+-----------------+----------+--------------+------------------+----------------------------------------+

1 row in set (0.00 sec)

#提交后才有变化