【MariaDB主从搭建（线上业务不停机）】

原理讲解

主从同步的核心：二进制日志

实现主从同步（主从复制）：
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  -u'backup' -p'backup' --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' doesn't 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 status\G;