MySQL主从同步

一、mysql主从同步

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

1.2mysql主从同步工作原理
主库必须开binlog日志
从库I/O线程读取(复制)主库的binlog日志的SQL命令到本机的中继日志
从库SQL线程执行本机的中继日志里的SQL命令(重现主库数据操作)

1.3配置mysql主从同步
1)服务器角色
client 50
master 51
slave  52

注：本次实验需192.168.4.50～56共七台虚拟机

```````````````````````````````````````````````````````````````````````````
2）配置主服务器 51
启用binlog日志文件
]#vim /etc/my.cnf

[mysqld]
log_bin=master51(日志文件名)
server_id=51

binlog_format="mixed"  #指定日志格式，本次实验没有用，不用加
...
]#重启
]#mysql -uroot -p888888 -e "show master status"

]#ls /var/lib/mysql/master51.*

用户授权
mysql>grant replication slave on *.* to 用户名@"%" identified by "密码";

查看binlog日志信息
mysql>show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 |      440 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

-------------------------------------------------------------------------------------------------------------------

3）配置从服务器 52
指定server_id
]#vim /etc/my.cnf
[mysqld]
server_id=52     
]#重启

指定主服务器信息
mysql>change master to master_host="192.168.4.51(主服务器ip)",master_user="用户名(主服务器授权的用户)",master_password="密码(主服务器授权的密码)",master_log_file="master51.000001(binlog日志文件名)",master_log_pos=440(偏移量);

启动slave进程
mysql>show slave status;
mysql>start slave;
mysql>show slave status\G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

##############################################################

[从库]排错：进去先看mysql>show slave status;     
 Slave_IO_Running: Connecting
 Slave_SQL_Running: Yes
Last_IO_Error:  报错信息....
Last_SQL_Error: 报错信息...

查主服务器是否有该用户
1.密码可能不对:
]#cat /var/lib/mysql/master.info(里面包含用户密码)
mysql>set password for 用户名@"%"=password("新密码");

mysql>stop slave;先关从服务
哪部分错了就改哪部分
mysql>cahnge master to master_user="用户"，master_password="密码";
mysql>start slave;

mysql>show slave status;

 Slave_IO_Running: Yes
            Slave_SQL_Running: No

分析：从库执行中继日志文件中的改密码时用户不存在则报错，需在从库增该用户

-------------------------------------------------------------------
2.用户不存在：
 Slave_IO_Running: Connecting
 Slave_SQL_Running: Yes
Last_IO_Error:  报错信息....
Last_SQL_Error: 报错信息...
原因：在主库授权在从库指定的用户
再从库刷新，stop slave;  start slave;  show slave status;

3.用户名写错了
把进程停了，修改用户信息，再启进程
stop slave;
change master to master_user="",master_password="";
start slave;
show slave status\G

4.没有重置初始密码

5.binlog日志文件写错了(或文件前面多写了个空格)
stop slave 
change master to master_file="binlog日志文件";

############################################################

-------从库相关文件：
中继日志文件：/var/lib/mysql/host52(主机名)-relay-bin.index #索引文件
            /var/lib/mysql/host52(主机名)-relay-bin.编号
中继日志信息文件：/var/lib/mysql/relay-log.info
主库信息     ：/var/lib/mysql/master.info
删了这类文件=主从还原

1.4验证配置
1)在主服务器添加访问数据的连接用户
create database db7;
grant all on db7.* to 用户名@"%" identitfied by "密码";

2)在客户端连接主服务器，对数据进行操作
mysql>mysql -u用户 -p密码 
3）在从服务器本机查看是否和主服务器的数据一致

二、mysql主从同步模式

2.1 mysql主从同步结构模式
一主一从
一主多从
主从从、主主结构 #通常不单用，和其他软件用做高可用集群

一主多从：1.给当前主服务器51   再配合置一台从服务器53
    2.在没有配置为从服务器之前，要有主服务器上的数据

mysql>source /root/db7.sql  #要在库里操作
    3.设置新增从库主机的server_id
    4.指定主服务器信息
]#mysql -h192.168.4.51 -u主库授权从库的用户名 -p密码 (仅验证是否可以登陆)
本地指定主库信息mysql>change master to masater_host="",master_user="",master_password="",
master_log_file="",master_log_pos=; 参考主服务器的binlog日志
    5.启动slave进程
mysql>start slave;
    6.查看进程信息
mysql>show slave status\G;
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

    7.客户端测试
客户端连接主数据库访问数据，执行建库建表插入等操作
分别在从服务器本机查看数据
 

主从从:  给当前的从服务器52 配置1台从服务器54
    配置52（主兼从）
     启用binlog日志
]#vim /etc/my.cnf
log_bin=master52
server_id=52
log_slave_updates 允许链式复制
]#重启

     用户授权
mysql>show slave status\G; 查看原来的从服务状态Yes
mysql>grant replication slave on *.* to 用户名@"%" identified by "密码";
    查看binlog日志信息
mysql>show master status;
     

准备同步：
    完全备份库mysqldump -u -p 库> 文件
    拷贝到从服务器

    配置54（从）
    使用备份文件恢复数据
建库（与主库的完全备份的库名一致）
进库 source 备份数据的文件
    指定server_id
]#vim /etc/my.cnf
server_id=54
]#重启
    指定主库信息

mysql>change master to master_host="",master_user="",master_password="",
master_log_file="",master_log_pos= ;#可参考主库binlog日志信息

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

    客户端测试
连接第一个主服务器执行操作
查看所有从服务器上的数据变化

主主结构  ： 把数据库服务器55和56配为主主

2.2mysql主从复制模式
异步复制模式？
默认的
]主库接受到客户请求后，处理完事务后，立即将结果返给客户端，不去关系从库是否已经接收并处理
客户体验度高，访问从库可能没有数据

全同步复制模式？
]当主库执行完一次事务，且所有从库都接受并处理之后，才返回给客户端
客户体验度差，数据同步好

半同步模式？
]介于两种模式之间，主库执行一次事务后，等待至少一个从库接受并写到relay log中才返回给客户端

2.3 Mysql主从同步常用选项（/etc/my.cnf）
    2.3.1 用于主服务器的配置选项
        binlog_do_db=数据库名 [只允许同步的库]
        binlog_ignore_db=数据库名 [不允许同步的库]
    2.3.2 用于从服务器的配置选项
        log_slave_updates    [记录从库更新,允许链式复制]
        replicate_do_db=库    [仅同步的库]
        replicate_ignore_db=库    [仅不同步的库]
        relay_log=日志名    [自定义中继日志文件名]

 

主库配置选项，适用于master服务器

从库配置选项，适用于slave服务器