原理讲解

主从同步的核心：二进制日志 实现主从同步（主从复制）： 1. master将改变的数据记录在本地的二进制日志中（binary log）；该过程称之为二进制日志事件 2. slave将master的binary log拷贝到自己的relay log（中继日志）中 3. 中继日志事件，将数据读取到自己的数据库之中

主要信息： 主数据库内网ip：10.0.3.22 从数据库内网ip：10.0.6.6 MariaDB版本为10.1.38

主数据库操作

1、主数据库配置文件修改

# id
#server-id=1
# 二进制日志文件
log-bin=mysql-bin

重启数据库

systemctl restart mariadb

2、创建备份数据库用户

create user backup@localhost identified by backup;
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO backup@localhost IDENTIFIED BY backup;
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO backup@10.0.6.10 IDENTIFIED BY backup;
flush privileges;

3、备份所有数据库

time mysqldump --all-databases  -ubackup -pbackup --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

总结：

要实现点对点的备份恢复，或者对于某点的数据完整备份，必须使用–single-transaction 和–master-data=2两个参数，比如主从搭建时候的，因为在–single-transaction参数中，没可以保证数据备份的备份时间点的事务一致性 –single-transaction备份对于数据库的影响时间比较长，影响范围比较大 ，但是加入该参数后，对于多张大表的备份速度肯定会加快。 –lock-all-tables并不能保持所有数据对于某个时间点的一致性。

4、确定bin_log位置

head -n100 2021-12-13-mysql-all.sql | grep MASTER

查看到的内容： – CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.0292’, MASTER_LOG_POS=366;

5、拷贝到从数据库服务器

scp  2021-12-13-mysql-all.sql root@10.0.6.6:~/

从数据库操作

1、导入备份库

方法一：命令行直接导入

mysql -uroot -p123456 2021-12-13-mysql-all.sql < kuaidaili.sql

方法二：进入数据库使用source命令导入，注意需要登入数据库

source ~/2021-12-13-mysql-all.sql;

2、从数据库授权

change master to master_host=10.0.3.22,master_port=3306,master_user=backup,master_password=backup,master_log_file=mysql-bin.0292,master_log_pos=366;

参数说明： CHANGE MASTER TO MASTER_HOST = ‘10.0.3.22’, #另一台机器的地址 MASTER_PORT = 3306, #另一台机器的端口 MASTER_USER = ‘backup’,#另一台机器上第一步分配的用户名 MASTER_PASSWORD = ‘backup1’, #另一台机器上第一步分配的密码 MASTER_LOG_FILE = ‘mysql-bin.0292’,#另一台机器上执行SHOW MASTER STATUS得到的文件名（File） MASTER_LOG_POS = 366; #另一台机器上执行SHOW MASTER STATUS得到的偏移量(Position)

3、 启动I/O 线程和SQL线程

start slave;

4、 查看从库属性状态

show slave status G;

确保：Slave_IO_Running和Slave_SQL_Running参数都是yes，如果不是yes需要查看下方Last_IO_Error和Last_SQL_Error错误日志

5、遇到的报错处理

Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table mysql.gtid_slave_pos doesnt exist

提示没有这个表：Table ‘mysql.gtid_slave_pos’ doesn’t exist

use mysql;
CREATE TABLE `gtid_slave_pos` (
       `domain_id` int(10) unsigned NOT NULL,
       `sub_id` bigint(20) unsigned NOT NULL,
       `server_id` int(10) unsigned NOT NULL,
       `seq_no` bigint(20) unsigned NOT NULL,
       PRIMARY KEY (`domain_id`,`sub_id`)
     ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=Replication slave GTID state;
stop slave;
start slave;
show slave statusG;