(DB2.1)MySQL主从同步、主从同步模式

一、mysql 主从同步

1.1 什么是mysql 主从同步

实现数据主从同步的服务结构，结构中分为两种角色
主服务器：接收客户端访问的数据库服务器
从服务器：自动从主库服务器同步到本机的数据库服务器

1.2 mysql 主从同步工作原理

• Mster，记录数据更改操作
  -启用binlog日志
  -设置binlog日志格式
  -设置server_id
• Slave 运行2个线程
  Slave_IO 复制master主机 binlog日志文件里的SQL到本机的relay-log文件里
  Slave_sql 执行本机的relay-log 文件里的SQL语句，重现Master的数据操作

1.3 配置mysql主从同步

1.3.1 服务器角色

192.168.4.50 客户端（client）
192.168.4.51 主（master） 运行数据库服务器 管理员root本机能够登陆
192.168.4.52 从（slave） 运行数据库服务器 管理员root本机能够登陆1.3.2 配置主服务器 192.168.4.51

1 启用binlog日志文件

vim /etc/my.conf	
[mysq]
server_id=51
log-bin=master51		
:wq
[root@51 ~]# systemctl restart mysqld
[root@51 ~]# ls /var/lib/mysql/master51.*
/var/lib/mysql/master51.000001  /var/lib/mysql/master51.index
[root@51 ~]# mysql -uroot -p123456 -e "show master status"

2 用户授权

[root@51 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

3 查看binlog日志信息（master状态）

mysql> show master status;

1.3.3 配置从服务器 192.168.4.52

1 指定server_id（不允许与主库server_id相同）

vim /etc/mysql
[mysq]server_id=52

2 指定主服务器信息

mysql> change master to 		
    —> master_host="192.168.4.51",         //指定主库IP
    —> master_user="repluser",             //指定主库授权用户
    —> master_password="123qqq...A",       //指定主库授权密码
    —> master_log_file="master51.000002",  //日志文件
    —> master_log_pos=441;               //偏移位置

3 启动slave 进程

mysql> start slave;

Master信息会自动保存到 /var/lib/mysql/master.info
若要更改master信息，应先停止stop slave；
4 查看进程信息(查看slave状态，确认IO线程、SQL线程是否运行)

mysql> show  slave status\G;
Slave_IO_Running: Yes          //查看IO线程是否出错
Slave_SQL_Running: Yes         //查看SQL线程是否出错

从库相关文件

[root@52 ~]# cat /var/lib/mysql/
                    master.info         //主库信息
                    relay-log.info      //中继日志信息
                    主机名-rely-bin.xxxx //中继日志
                    主机名-rely-bin.index//索引文件

1.4 验证配置

1 在主服务器上添加访问数据连接的用户

mysql> create database db7;
mysql> grant all on db7.* to webuser@"%" identified by "123qqq...A";

2 在客户端连接主服务器 对数据做操作

[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq...A"
mysql> show grants;
mysql> use db7;
mysql> create table db7.t1(id int);
mysql> insert into db7.t1 values(10101);
mysql> insert into db7.t1 values(10101);

3 在从服务器本机上查看是否和主服务器的数据一致

[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> show databases;
mysql> select * from db7.t1;

二、 mysql主从同步模式

MYSQL 主从同步结构模式

一主一从

一主多从

给当前主服务器51再配置1个从数据库服务器53

1 在服务器53 上运行mysql服务且管理员能够本机登陆

2 在没有配置为从服务器之前，要有主服务器上的数据

[root@51 ~]# mysqldump -uroot -p123456 db7 > /root/db7.sql
[root@51 ~]# scp /root/db7.sql root@192.168.4.53:/root
        
[root@53 ~]# mysql -uroot -pTaren1.com
mysql> create database db7;
mysql> use db7;
mysql> source /root/db7.sql;

3 设置主机53的server_id

      vim /etc/my.conf
      [mysqld]
      server_id=53
      [root@53 ~]# systemctl restart mysqld

4 指定主服务器信息

      [root@53 ~]# mysql -uroot -pTaren1.com
      mysql> change master to 		
            master_host="192.168.4.51",    
            master_user="repluser",          
            master_password="123qqq...A",    
            master_log_file="master51.000002",  
            master_log_pos=1821；

5 启动slave进程

 mysql> start slave;

6 查看进程信息

mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

7 客户端测试

1 在客户端50主机连接和数据库服务访问数据

[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq...A"
mysql> insert into db7.t1 values(8888888);
mysql> create table db7.t2(name char(10));
mysql> select * from db7.t1;
mysql> select * from db7.t2;

2 分别在2个服务器本机查看数据（可以看到和主服务器一样
的数据）

mysql> select * from db7.t1;
mysql> select * from db7.t2;

主从从

给当前的从服务器192.168.4.52，配置一个从服务器192.168.4.54

1 配置52

1.1 启用binlog日志

vim /etc/my.cnf
log-bin=master52
log_slave_updates
[root@52 ~]# systemctl restart mysqld
[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> show slave status\G;

1.2 用户授权

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

1.3 查看binlog日志信息

mysql> show master status;

2配置54

52备份数据
[root@52 ~]# mysqldump -uroot -pTaren1.com db7 > /root/db7.sql
[root@52 ~]# scp /root/db7.sql root@192.168.4.54:/root/
54使用备份文件恢复数据
[root@54 ~]# mysql -uroot -pTaren1.com
mysql> create database db7;
mysql> use db7;
mysql> source /root/db7.sql
mysql> show tables;

2.1 指定server_id
vim /etc/my.cnf
server_id=54
[root@54 ~]# systemctl restart mysqld

2.2 指定主服务器信息

[root@52 ~]# mysql -uroot -p"Taren1.com"
mysql> grant all on db7.* to webuser@"%" identified by "123qqq...A";

[root@54 ~]# mysql -uroot -pTaren1.com
mysql>  change master to master_host="192.168.4.52",master_user="repluser",master_password="123qqq...A",master_log_file="master52.000002",master_log_pos=732;

2.3 启动slave进程

mysql> start slave;

2.4 查看进程信息

mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3 测试配置

3.1 在客户端连接主服务器51存数据

[root@50 ~]# mysql -h192.168.4.51 -uwebuser -p"123qqq…A"
mysql> insert into db7.t1 values(123456789);

3.2 在从服务器52 和 54 主机上都可以看到和主服务器一致的数据

[root@52 ~]# mysql -uroot -p"Taren1.com" -e “select * from db7.t1;”
[root@54~]# mysql -uroot -p"Taren1.com" -e “select * from db7.t1;”

主主结构

MYSQL 主从同步复制模式

异同步模式
主库执行完一次事务后，立即将结果返回给客户端，并不关心从库是否已经接收并处理
全同步复制
主库执行完一次事务后，且所有从库都执行了该事务后才返回给客户端
半同步复制
介于异步复制和全同步复制之间
主库执行完一次事务后，等待至少一个从库接收到并写到relay-log中才返回给客户端
案例：启用数据库服务器52的半同步复制模式
步骤：
1 查看是否允许动态加载模块
mysql> show variables like “have_dynamic_loading”;
2 加载模块
用户需要有super权限
mysql> install plugin rpl_sync_master soname “semisync_master.so”; //主库执行
mysql> install plugin rpl_sync_slave soname “semisync_slave.so”; //从库上执行
3 查看加载信息
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like “%semi%”;
4 启用模块
mysql> set global rpl_semi_sync_master_enabled = 1; //主库上执行
mysql> set global rpl_semi_sync_slave_enabled = 1; //从库上执行
5 查看是否启用
mysql> show variables like “rpl_semi_sync_%_enabled”; //查看是否启用
6 修改配置文件
[root@52 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1 //主库上的配置
plugin-load=rpl_semi_sync_slave=semisync=slave.so
rpl_semi_sync_slave_enabled=1 //从库上的配置
[root@52 ~]# systemctl restart mysqld

主库配置选项( 适用于Master服务器)

/etc/my.conf

选项	用途
binlog_do_db=数据库名	只允许同步的库
binlog_ignore_db=数据库名	不允许同步的库

从库配置选项(适用于Slave服务器)

选项	用途
log_slave_updates	记录从库更新，允许链式复制(a-b-c)
relay_log=dbsvr2-relay-bin	指定中继日志文件名
replicate_do_db=数据库名	仅复制指定库，其他库将被忽略，此选项可设置多条(省略时复制所有库)
replicate_ignore_db=test	不复制那些库，其他库将被省略，ignore-db与do-db只需选择其中一种

故障分析
问题现象1
Slave_IO 线程没有运行
-报错：Slave_IO_Running:No
mysql > show slave status\G;
Slave_IO_Running:No
Last_IO_Error: 报错信息
原因分析
-连接不上 master数据库服务器
解决办法
-检查物理连接（ping）、检查授权用户
-禁用防火墙、关闭SElinux
-binlog日志文件指定错误（日志名或pos节点）
mysql> stop slave;
mysql> change master to 选项=值；
mysql> start slave;
问题现象2
Slave_SQL 线程没有运行
-报错：Slave_SQL_Running:No
mysql> show slave status\G;
Slave_SQL_Running:No
Last_IO_Error: 报错信息
故障分析及排除

• 原因分析
  -执行本机中继日志里的sql命令时，sql命令适用的库、表或记录在本机不存在
  解决办法
  mysql> stop slave;
  mysql> … //创建或恢复需要用到的库或表
  mysql> start slave;