mysql active_MYSQL ACTIVE DG 配置

半同步模式类似于ORACLE的最大可用模式，另外还有个异步复制模式，类似于最大性能模式。

MYSQL ADG配置相对简单些，比ORACLE简单多了。而且MYSQL这里是丛库主动连主库，并且把主库的BINGLOG拉回来。

开启主库的二进制日志(归档日志)

My.cnf

log_bin=/u01/mysql_instance/mysql_master/mysql_log/mysql_bin_log

server-id=2

说明：必需配置，server-id指定服务器唯一id，不可重复，log-bin开启binlog日志

service mysqld restart

主库添加复制账号

mysql> GRANT REPLICATIONSLAVE ON *.* TO 'rpl'@'%' IDENTIFIED BY '123456';

说明：添加一个rpl 账号在任何机器上使用 123456 这个密码对任何数据库行使 replication slave 权限

当然我们可以使用root

mysql >GRANT FILEON*.*TO'root'@'192.168.1.2'IDENTIFIEDBY'mysql password';

mysql >GRANTREPLICATION SLAVEON*.*TO'root'@'192.168.1.2'IDENTIFIEDBY'mysql password';

mysql >FLUSH PRIVILEGES

也可以先创建用户在赋权

mysql> insert intomysql.user(Host,User,Password) values("192.168.2.200","rpl",password("123456"));

具体如下

mysql> GRANTREPLICATION SLAVE ON *.* TO 'rpl'@'192.168.2.200' IDENTIFIED BY '123456';

mysql> GRANT fileON *.* TO 'rpl'@'192.168.2.200' IDENTIFIED BY '123456';

mysql> flushprivileges;

mysql> selectuser,host from user;

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

| user          | host          |

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

| mysql.session |%             |

| mysql.sys     | %             |

| root          | %             |

| rpl           | 192.168.2.200 |

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

开启从库的二进制日志

[mysqld]

#changes to the binary log between backups.

# These are commonly set, remove the # and setas required.

basedir = /u01/mysql_soft/mysql5.7.21/

datadir =/u01/mysql_instance/mysql_slave/mysql_data

port = 3306

socket =/tmp/mysql.sock

user=mysqldba

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/mysql_instance/mysql_slave/mysql_log/mysql_bin_log

server-id=3

#binlog-ignore-db=information_schema

#binlog-ignore-db=cluster

#binlog-ignore-db=mysql

#replicate-do-db=ufind_db

#replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60

被屏蔽掉的是特色参数，主要是可以忽悠复制某些SCHMA。不过我们还是按ORACLE DG模式，全库一摸一样复制。

启动丛库DG模式

mysql>stopslave;#关闭Slave

mysql> change masterto master_host='192.168.2.119',master_user='rpl',master_password='123456',master_log_file='mysql_bin_log.000005', master_log_pos=880;

mysql> start slave;#开启Slave

其中主库日志和日志位置可以通过主库下面命令获得

mysql> showmaster status;

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

| File                 | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |

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

|mysql_bin_log.000005 |      880 |              |                  |                   |

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

1 row in set (0.00sec)

执行结果

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to

master_host='192.168.1.119',master_user='rpl',master_password='123456',master_log_file='mysql-bin.000005', master_log_pos=880;

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

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G;

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.1.119

Master_User: rpl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 880

Relay_Log_File: mysql-slave-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Connecting

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: 880

Relay_Log_Space: 154

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'rpl@192.168.1.119:3306' - retry-time: 60  retries: 1

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: /u01/mysql_instance/mysql_slave/mysql_data/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: 180420 16:53:14

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

检查点

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

不是也是的话就表示有问题

FAQ

Last_IO_Error:  error connecting to master'rpl@192.168.1.119:3306' - retry-time: 60 retries: 1

更改后再报下面错误

The slave I/Othread stops because master and slave have equal MySQL server UUIDs; theseUUIDs must be different for replication to work

这个问题是虚拟机克隆造成的

[mysqldba@mysql-mastermysql_data]cat auto.cnf

[auto]

server-uuid=c785211b-276e-11e8-9c16-0800271ee1f5

对从库mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk  ###重命名该文件

然后重启下

Last_IO_Error: Gotfatal error 1236 from master when reading data from binary log: 'Could not findfirst log file name in binary log index file'

主库日志名写错了 改过来

成功无错误后的信息：

mysql> showslave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.2.119

Master_User: rpl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:mysql_bin_log.000005

Read_Master_Log_Pos: 880

Relay_Log_File:mysql-slave-relay-bin.000002

Relay_Log_Pos: 324

Relay_Master_Log_File:mysql_bin_log.000005

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: 880

Relay_Log_Space: 537

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: 2

Master_UUID:c785211b-276e-11e8-9c16-0800271ee1f5

Master_Info_File:/u01/mysql_instance/mysql_slave/mysql_data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall 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.00sec)

ERROR:

No query specified

你可以建库，建表测试是否同步过去了

其他

只同步某个库的时候

Master锁定库，使只能读取不能写入

mysql > flush tables with read lock;

2、Master导出备份

~$ mysqldump --master-data -uroot -p db_test>db_test.sql

说明：--master-data参数在生成的dump 文件中产生一条 CHANGE MASTER TO 命令，查看可知master当前使用的binlog文件名

3、Slave导入备份

~$ mysql -uroot -p db_test

4、最后配置好同步以后，Master解除写锁定

mysql > unlock tables;

主库特色参数

#给从机同步的库binlog-do-db=shenxianyun_consolebinlog-do-db=shenxianyun_portalbinlog-do-db=test#自动清理1天前的log文件expire_logs_days=1

这里的数据库类似于ORACLE SHCMA

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

binlog-do-db=ufind_db