中继链路
这样的可以减轻master的压力，不管后面有多少台slave,都是中继来读取一次数据，后面的slave服务全部到中继去拿数据
环境
10.10.100.22 -------master
10.10.100.28---------slave中继
10.10.100.30---------slave
在主服务上授权用户：
mysql> grant replication slave on *.* to slave@'10.10.100.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
建库，建表，插入数据
mysql> create database MSS;
Query OK, 1 row affected (0.00 sec)
mysql> use MSS;
Database changed
mysql> create table test(id int,name varchar(8),sex varchar(8));
Query OK, 0 rows affected (0.00 sec)
mysql> create table pt(id int,name varchar(8),sex varchar(8));
Query OK, 0 rows affected (0.31 sec)
mysql> insert into test values (8,'lj','M');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (9,'jj','W');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (9,'jj','W');
Query OK, 1 row affected (0.01 sec)
mysql> exit
导出数据结构和数据
[root@localhost ~]# mysqldump -root -p123456 -B MSS > MSS.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
将导出的数据远程复制到其他服务器上
[root@localhost ~]# scp MSS.sql 10.10.100.28:/root/
root@10.10.100.28's password:
MSS.sql 100% 2564 56.1KB/s 00:00
[root@localhost ~]# scp MSS.sql 10.10.100.30:/root/
root@10.10.100.30's password:
MSS.sql 100% 2564 2.1MB/s 00:00
数据还原
[root@localhost ~]# mysql -uroot -p123456 < MSS.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
修改配置文件
[root@localhost ~]# vi /etc/my.cnf
server-id=1
binlog-do-db=MSS #需要同步的库
binlog-ignore-db=mysql #不需要同步的库
log-bin=mysql-bin-master #启动二进制日志
sync-binlog=1 #启用二进制日志同步功能
binlog-format=row #二进制日志格式化，以行的方式
重启服务
[root@localhost ~]# systemctl restart mysqld
登录数据库查看主库的状态
[root@localhost ~]# mysql -uroot -p123456
mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000005 | 680 | MSS | mysql | |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
修改中继服务的主配置文件28
[root@localhost ~]# vim /etc/my.cnf
添加下面的内容
log-bin=mysql-bin-slave1
log-slave-updates=1 #把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化
binlog-format=row
server-id=2
重启服务
[root@localhost ~]# systemctl restart mysqld
登录数据库
[root@localhost ~]# mysql -uroot -p123456
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='10.10.100.22',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.100.22
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000005
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 381
Relay_Master_Log_File: mysql-bin-master.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
看到这样的状态说明中继服务器已经正常去主上拿去二进制日志
给最后一个从授权
mysql> grant replication slave on *.* to 'slave'@'10.10.100.30' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置slave服务30
修改配置文件
[root@localhost ~]# vim /etc/my.cnf
添加下面的内容
log-bin=mysql-bin-slave2
server-id=3
binlog-format=row
重启服务
[root@localhost ~]# systemctl restart mysqld
登录数据库
[root@localhost ~]# mysql -uroot -p123456
mysql> stop slave ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='10.10.100.28',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.10.100.28
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试数据同步
在主库上
查看pt表
mysql> select * from pt;
Empty set (0.00 sec)
插入一条数据
mysql> insert into pt values (11,'kk','M');
Query OK, 1 row affected (0.11 sec)
在另外的两台服务器上查询
28
mysql> select * from pt;
+------+------+------+
| id | name | sex |
+------+------+------+
| 11 | kk | M |
+------+------+------+
1 row in set (0.00 sec)
30
mysql> select * from pt;
+------+------+------+
| id | name | sex |
+------+------+------+
| 11 | kk | M |
+------+------+------+
1 row in set (0.01 sec)
说明数据都已经同步
mms已经搭建完成
优化修改配置，让中继服务器不存储数据，只抓取二进制日志，真正的只做桥梁的作用，只需要修改中继服务器的引擎修改成黑洞引擎就可以实现
操作步骤如下
修改之前需要先将二进制的同步功能关闭
登录数据库
mysql> set sql_log_bin=off; #暂时关闭二进制日志功能，这样修改的内容就不会同步到30上面
mysql> alter table pt engine=blackhole; #修改pt表的存储引擎为黑洞引擎
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
查看是否修改成功
mysql> show create table pt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pt | CREATE TABLE `pt` (
`id` int(11) DEFAULT NULL,
`name` varchar(8) DEFAULT NULL,
`sex` varchar(8) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
开启二进制日志功能
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
正常来说现在这台服务器是已经没有数据
mysql> select * from pt; 这个表已经空了，但是不影响数据的同步
Empty set (0.00 sec)
在主数据库22，插入一条数据，测试
mysql> insert into pt values (12,'test','W');
Query OK, 1 row affected (0.05 sec)
在中继服务，slave分别查看
中继
mysql> select * from pt;
Empty set (0.00 sec)
从
mysql> select * from pt;
+------+------+------+
| id | name | sex |
+------+------+------+
| 11 | kk | M |
| 12 | test | W |
+------+------+------+
2 rows in set (0.00 sec)
这样就搭建完成。。
排错
错误1
mysql> show slave status\G
Read_Master_Log_Pos: 288
Exec_Master_Log_Pos: 107
Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: 'Table 'MSS.student' doesn't exist'
因为只对MSS记录了binlog,当在mydb库操作其它数据库的表，但该表在slave上又不存在时就出错了。
到master上查看事件记录
这个报错主要是没有这个表的原因，可以在主库上把这个表导出来，在导入到从库
mysql> show binlog events in 'mysql-bin-master.000002' from 107\G
由上面的结果可知，我们需要跳过两个事务173，288
然后到salve中继操作：
mysql> slave stop;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; 跳过一个事务
mysql> slave start;
这个搞定
或者：
它提供了一个session粒度的选项，通过关闭这个选项可以不让主库将打开这个选项或关闭连接前的SQL语句写入binlog。
set sql_log_bin=off;
mysql>alter table aaa add column xxx int default 1 after yyy;
这个搞定
或者：
它提供了一个session粒度的选项，通过关闭这个选项可以不让主库将打开这个选项或关闭连接前的SQL语句写入binlog。
set sql_log_bin=off;
mysql>alter table aaa add column xxx int default 1 after yyy;
模拟故障：
由于历史遗留问题，MySQL主从库的表结构不一致，主库的某个表tableA比从库表tableA少了一个字段
当尝试在主库上更改表结构时，这行alter语句会随着binlog同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停止，那样只能人为手动处理错误，然后再启动slave上的主从同步线程。场景大概是下面这个样子：
先在从库添加这个字段：
mysql> alter table student add age int default 0 after name;
再在主库添加这个字段：
mysql> alter table student add age int default 0 after name;修改主库上的表结构，添加一个字段
从库会同步主库的，但是从库已经存在了这个字段
查看slave状态
解决方法1：
跳过错误的事物
从库上执行：
mysql> stop slave;
set global sql_slave_skip_counter=1;
mysql> start slave;
很多slave数据库的时候这样改太麻烦了
slave比较少的时候还可以，但是当从库有几十台时，逐台去处理既费时又容易出错，怎样在主库这一侧一劳永逸地避免呢？
那很简单，我们不要让主库将alter语句记录到binlog中就行
我们直接这主库中关闭binlog记录
mysql> set sql_log_bin=off;
然后我们再执行alter语句
mysql> alter table student add age int default 0 after name;
再开启bin-log
mysql> set sql_log_bin=on;
错误2：
这种要求对齐binlog
先到作为它的主上查看binlog
Slave上不对应
Slave上操作：
mysql> stop slave;
mysql> change master to master_host='10.10.100.22',master_user='repl',master_password='123456',master_log_file='mysql-bin-slave1.000002',master_log_pos=415;
mysql> start slave;
查看从库的状态，有两个yes
Ok,恢复正常
扫一扫
1357
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
