云主机云服务器

VPS服务器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} 如果 你是 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%

购买使用一诺网络日本vps，可以极大降低初创企业、中小企业以及个人开发者等用户群体的整体IT使用成本，无需亲自搭建基础设施、简化了运维和管理的日常工作量，使用户能够更专注于自身的业务发展和创新。日本vps低至129元/月，购买链接：https://www.enuoidc.com/vpszq.html?typeid=10