Mariadb 的主从配置记录

为了给站点的数据库做多 Mysql 备份,想了想干脆就直接主从备份最方便。


1、Mariadb 主从配置说明

首先每个 Mysql 数据库的操作都对应着一条命令,而二进制方式的主从同步就是同步这些命令,所以在主从操作开始前请先锁表,并且保证双方同步的数据库完全一致


2、Mariadb 主 配置

2.1、修改 Mariadb-Master 配置文件

vim /etc/my.cnf.d/server.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = "/var/log/mariadb/mariadb-slow.log"
long_query_time = 1
skip-networking = 0
bind-address=0.0.0.0
skip-name-resolve = 1
port = 3306
datadir = "/var/lib/mysql"
socket = "/var/lib/mysql/mysql.sock"
symbolic-links=0
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-do-table=mysql.%
#replicate-wild-ignore-table=other.%
#binlog_format=row
expire_logs_days = 3
#read_only=on

其中前三条内容是延迟存放日志(无关)

第四条 skip-networking=1 这个必须关闭,否则不会联网

第五条 bind-address=0.0.0.0 一般建议全部绑定,因为 mysql 和 mariadb 不允许手动绑定指定 IP 地址
(如果你想限制外部设备访问,你可以通过防火墙控制)

第六条 skip-name-resolve = 1 是防止 mysql 默认对 IP 地址反向查询影响行能(无关)

第七条 port = 3306 是默认值

第八条 datadir = 是默认值,后期同步的二进制修改信息会放在这里

第九条 socket = 是默认值,用于支持本地访问的 socket 文件(无关)

第十条 symbolic-links 关闭后会阻止 mysql 访问符号链接文件(快捷方式)为了安全推荐关闭(无关)

第十一条 server-id=1 是配置本机 mysql 的唯一 ID,多台 mysql 不能存在相同的 ID

第十二条 log-bin=mysql-bin 设置”主服务器需要同步的二进制日志”文件名前缀

第十三条 relay-log=mysql-relay-bin 设置”从服务器需要执行的中继日志”文件名前缀(主从身份可以同时存在一台实例上)

第十四条 replicate-wild-do-table 是设置同步的数据库(多个就复制多行)
(你也可以通过配置 replicate-wild-ignore-table 反过来设置排除哪些数据库)
(这里不用 binlog-do-db 和 binlog-ignore-db 是为了避免用户进行跨库更新时导致的不同步问题)

第十五条 binlog_format=mixed 是修改了复制格式为 mixed,mysql 一共有三种格式 mixed 和 statement 和 row ,默认是 row
(ROW 记录数据操作上下文保证数据同步的准确性,statement 记录命令操作上下文间接实现数据同步,但个别命令执行错误就会存在差异,mixed 则是两者都使用)

第十六条 expire_logs_days 是设置”二进制修改信息”的保存时间,过期(三天后)清理

第十七条 read_only=on 是设置数据库为只读状态(在从服务器开启主要为了保证从服务器和主服务器的数据一致性)


2.2、查看当前可以同步的二进制修改信息 File 和 Position

先登陆 mysql

mysql -uroot -p

创建一个用于同步的用户 mysql_sync 密码是 passwordabc 并允许服务器数据库的从权限

CREATE USER 'mysql_sync'@'clientipadress' IDENTIFIED BY 'passwordabc';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'mysql_sync'@'ipadress';

最后输入命令 show master status 查看状态。

MariaDB [(none)]> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000010 | 74878    |              |                                             |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.000 sec)

3、Mariadb 从 配置

3.1、修改 Mariadb-Master 配置文件

vim /etc/my.cnf.d/server.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = "/var/log/mariadb/mariadb-slow.log"
long_query_time = 1
skip-networking = 0
bind-address=0.0.0.0
skip-name-resolve = 1
port = 3306
datadir = "/var/lib/mysql"
socket = "/var/lib/mysql/mysql.sock"
symbolic-links=0

server-id=2
relay-log=mysql-relay-bin
replicate-wild-do-table=mysql.%
#replicate-wild-ignore-table=other.%
binlog_format=row
expire_logs_days = 3
read_only=on

3.2、查看当前可以同步的二进制修改信息 File 和 Position

先登陆 mysql

mysql -uroot -p

然后你需要设置 slave 同步的主服务器信息,注意要参照主服务器的信息修改最后两个选项的值(指定出从哪条开始同步)

change master to \
master_host='192.168.1.100',
master_port=3306,
master_user='mysql_sync',
master_password='passwordabc',
master_log_file='mysql-bin.000010',
master_log_pos=74878;

最后输入命令 show slave status\G 查看状态。(写 \G 是换行)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: mysql_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 74878
               Relay_Log_File: mysql-relay-bin.000011
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主要观察的 Slave_IO_State 是连接成功的等待状态 和两个 Running 必须是 Yes


4、其他命令

4.1、锁定数据库

锁定数据库所有表仅可以只读

flush tables with read lock;

解锁数据库

unlock tables;

4.2、远程传输命令

如果你需要完整的保证数据传输到对端设备,也就是强制同步一次,你就需要导出数据库并导入了(下列参数请自动替换)(注意需要在 slave 端创建一个用户支持远程访问)

# Backup
mysql" -h${master_host} -P${master_port} --hex-blob -u${master_user} -p${master_pass} ${master_database} > ${backup_dir_sync}/${output_database}
# Update
mysql" -h${slave_host} -P${slave_port} --default-character-set=utf8 -u${slave_user} -p${slave_pass} ${slave_database} < ${backup_dir_sync}/${output_database}

如果 你是 Windows 的客户端,你可以使用这两条命令(下列参数请自动替换)

REM 备份
"%MYSQL_HOME%\bin\mysqldump" -h%MASTER_HOST% -p%MASTER_PORT% --hex-blob -u%MASTER_USER% -p%MASTER_PASS% -R %MASTER_DATABSE% > "%BACKUP_DIR_SYNC%\%OUTPUT_DATABSE%.sql"
REM 更新
"%MYSQL_HOME%\bin\mysql" -h%SLAVE_HOST% --default-character-set=utf8 -P%SLAVE_PORT% -u%SLAVE_USER% -p%SLAVE_PASS% %SLAVE_DATABASE% < "%BACKUP_DIR_SYNC%\%OUTPUT_DATABSE%.sql"

5、参考文章

MariaDB 主从复制搭建 [ 链接 ]

MySQL 主从复制的配置 [ 链接 ]

MySQL Binlog【ROW】和【STATEMENT】选择 [ 链接 ]

点赞