只粘实现的主要步骤，详细步骤见百度

主库A 172.21.25.71

创建一个用于slave和master通信的用户账号 打开mysql会话shell>mysql -hlocalhost -uname -ppassword 创建用户并授权：用户：slave密码：slave123456 mysql> CREATE USER ‘slave‘@‘%‘ IDENTIFIED BY ‘slave123456‘;#创建用户 mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave‘@‘%‘;#分配权限 mysql>flush privileges; #刷新权限

获得master二进制日志文件名及位置 查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：mysql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000041 | 741 | | | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

使用master分配的用户账号读取master二进制日志 mysql> CHANGE MASTER TO MASTER_HOST=‘172.21.25.172‘, MASTER_USER=‘slave‘, MASTER_PASSWORD=‘slave123456‘, MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=979; 启用slave服务 mysql>start slave; 查看状态 mysql> show slave status\G; 主库重启

主库B 172.21.25.72

操作步骤通上

mysql> SHOW MASTER STATUS;+---------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000019 | 741 | | | |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)使用master分配的用户账号读取master二进制日志 mysql> CHANGE MASTER TO MASTER_HOST=‘172.21.25.171‘, MASTER_USER=‘slave‘, MASTER_PASSWORD=‘slave123456‘, MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=979; 启用slave服务 mysql>start slave; 查看状态 mysql> show slave status\G;

其他配置解释

replicate-do-db 设定需要复制的数据库(多数据库使用逗号，隔开)replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号，隔开)replicate-do-table 设定需要复制的表replicate-ignore-table 设定需要忽略的复制表replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符replicate-wild-do-table=db_name.% 只复制哪个库的哪个表replicate-wild-ignore-table=mysql.% 忽略哪个库的哪个表问题处理整理 ====================================#跳过一个事务SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 

附录

配置文件

my-71.cnf

[client]socket=/home/mysql/mysql.sockport=3306[mysqld]user=mysqlport=3306server-id=1max_connections=2000datadir=/home/mysql/data#basedir=/home/mysqlsocket=/home/mysql/mysql.sockpid_file=/home/mysql/mysql.piddefault-storage-engine=INNODBcharacter_set_server=utf8mb4# 双主log-bin=binlog log-bin-index=binlog.indexrelay_log=relay-logreplicate-ignore-db=information_schemareplicate-ignore-db=mysqlreplicate-ignore-db=performance_schemareplicate-ignore-db=sysreplicate-ignore-db=otterreplicate-ignore-db=hh_positionreplicate-do-db=data_centerreplicate-do-db=hh_data_centerreplicate-do-db=test# INNODBinnodb_buffer_pool_size=60Ginnodb_log_file_size=1Ginnodb_file_per_table=1innodb_flush_method=O_DIRECTinnodb_open_files=1000# MyISAMkey_buffer_size=8M# logginglog_error ="error.log"log-output=FILEgeneral-log=1general_log_file="mysql.log"slow-query-log=1slow_query_log_file="mysql-slow.log"long_query_time=3# othertmp_table_size=32Mopen_files_limit=65535

my-72.cnf

[client]socket=/home/mysql/mysql.sockport=3306[mysqld]user=mysqlport=3306server-id=2max_connections=2000datadir=/home/mysql/data#basedir=/home/mysqlsocket=/home/mysql/mysql.sockpid_file=/home/mysql/mysql.piddefault-storage-engine=INNODBcharacter_set_server=utf8mb4# 双主log-bin=binlog log-bin-index=binlog.indexrelay_log=relay-logreplicate-ignore-db=information_schemareplicate-ignore-db=mysqlreplicate-ignore-db=performance_schemareplicate-ignore-db=sysreplicate-ignore-db=otterreplicate-ignore-db=hh_positionreplicate-do-db=data_centerreplicate-do-db=hh_data_centerreplicate-do-db=test# INNODBinnodb_buffer_pool_size=20Ginnodb_log_file_size=1Ginnodb_file_per_table=1innodb_flush_method=O_DIRECTinnodb_open_files=1000# MyISAMkey_buffer_size=8M# logginglog_error ="error.log"log-output=FILEgeneral-log=1general_log_file="mysql.log"slow-query-log=1slow_query_log_file="mysql-slow.log"long_query_time=3# othertmp_table_size=32Mopen_files_limit=65535