系统信息
[root@cache1 ~]# uname -a
Linux cache1 3.10.0-1160.11.1.el7.x86_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
[root@cache1 ~]# cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)
[root@cache1 ~]# mysqld --version
/usr/sbin/mysqld Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)
[root@cache1 ~]# mysql --version
mysql Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)
1 主服务器
1.1 修改配置文件
配置文件my.cnf(或者my.ini)，通常在/etc/my.cnf
[mysqld]
#开启二进制日志
log-bin=mysql-bin
#设置server-id
server-id=1
不同步的数据库 （排除）
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
只同步指定数据库
binlog-do-db = game
修改配置文件后，要重启mysql
1.2 连接 Master
通过 mysql 命令连接 Master 的 MySQL Server
[root@cache1 ~]# mysql -uroot -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7969
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
查看 master 状态
MariaDB> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000001 | 12551 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
1.3 binlog日志
binlog 日志即二进制日志，Master
默认情况下 日志文件以1G一个文件滚动，如下：
binlog.000001
binlog.000002
…
binlog.xxxxxx
在实际应用环境下会有一个小问题，因为缺少运维管理，磁盘空间爆了，原因是binlog日志会一直增加。
1.3.1 查询日志
1.3.1.1 日志文件列表
show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000005 | 1073742020 | No |
| binlog.000006 | 1073742051 | No |
| binlog.000007 | 1073745295 | No |
| binlog.000008 | 1073742375 | No |
| binlog.000009 | 1073742026 | No |
| binlog.000010 | 1001631295 | No |
+---------------+------------+-----------+
6 rows in set
log_name 二进制日志文件名
File_size 文件大小
1.3.1.1 查看日志内容
最好加上 limit ，否则你懂的
show binlog events in 'binlog.000005' limit 10;
+---------------+------+----------------+------------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+------------+-------------+---------------------------------------+
| binlog.000010 | 4 | Format_desc | 1609812814 | 125 | Server ver: 8.0.22, Binlog ver: 4 |
| binlog.000010 | 125 | Previous_gtids | 1609812814 | 156 | |
| binlog.000010 | 156 | Anonymous_Gtid | 2172834 | 246 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 | 246 | Query | 2172834 | 333 | BEGIN |
| binlog.000010 | 333 | Table_map | 2172834 | 404 | table_id: 156 (ems.frame_data) |
| binlog.000010 | 404 | Write_rows | 2172834 | 542 | table_id: 156 flags: STMT_END_F |
| binlog.000010 | 542 | Xid | 2172834 | 573 | COMMIT /* xid=25342041 */ |
| binlog.000010 | 573 | Anonymous_Gtid | 2172834 | 663 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000010 | 663 | Query | 2172834 | 759 | BEGIN |
| binlog.000010 | 759 | Table_map | 2172834 | 825 | table_id: 109 (ems.raw_data) |
+---------------+------+----------------+------------+-------------+---------------------------------------+
10 rows in set
也可以同mysqlbinlog命令，具体还没用过，用过后再记录。
1.3.2 限制binlog日志
有2个参数可以限制 binlog
expire_logs_days 日志保存期限
默认值为 0 ，即不限制，值为保留的日志天数
可以修改配置文件 如 my.cnf 修改为7天
[mysqld]
expire_logs_days=7
或者使用SQL语句，设置全局参数，临时生效，重启失效
set global expire_logs_days=7
max_binlog_size 单个日志文件的大小
默认值是 1G，即每个文件1G
可以修改配置文件 如 my.cnf 修改为500M
[mysqld]
max_binlog_size = 500M
或者使用SQL语句，设置全局参数，临时生效，重启失效
set global max_binlog_size = 500M
注：当使用大事务时，二进制日志文件可能会超过日志文件大小的限制。因为一个事务的所有操作都要写入一个二进制日志文件中。
1.3.3 手动刷新日志
mysql 支持使用 flush logs 语句刷新 binlog 日志，其作用为 生成一个新的日志文件，后续日志写入到新文件中。
flush logs;
Query OK, 0 rows affected
1.3.4 手动删除binlog日志
mysql 支持使用 purge logs 语句删除 binlog 日志
语法如下
指定日志文件
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
指定日期以前的日志
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
范例
# 删除文件名为 mysql-bin.010 的日志文件
PURGE MASTER LOGS TO 'mysql-bin.010';
# 删除给定日期 2020-01-01 之前的所有binlog 日志文件
PURGE MASTER LOGS BEFORE '2020-01-01 00:00:00';
# 删除 3 天前日志文件
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
2 从服务器
2.1 修改配置文件
[mysqld]
#设置server-id，必须唯一
server-id=2
2.2 连接 Slave
通过 mysql 命令连接 Slave 的 MySQL Server
[root@cache1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
....
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.3 定义 master
master_host 主服务器地址
master_user 拥有 REPLICATION SLAVE 权限的帐户
master_password 密码
master_log_file binlog 文件，从哪个binlog 文件开始复制
master_log_pos 上面指定文件的起始位置（可以从中间开始）
参数之间使用 逗号 相隔。
mysql> change master to master_host='10.1.1.1',
-> master_user='root',
-> master_password='password',
-> master_log_file='binlog.000001',
-> master_log_pos=12551;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
2.4 启动 slave
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看 slave 状态，重点是
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 12551
Relay_Log_File: cache1-relay-bin.000002
Relay_Log_Pos: 1751
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12551
Relay_Log_Space: 1953
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2172834
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
同步完成，空闲状态时，有一个进程 状态为 Slave has read all relay log; waiting for more updates
3 故障排除
3.1 The server is not configured as slave
启动 slave
MariaDB [(none)]> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
- server-id 未配置或者配置错误
查询 server_id 变量，发现是0
MariaDB [(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
1 row in set (0.00 sec)
检查 my.cnf 中配置，发现 server-id 定义在 mysqld_safe 中了，改到 mysqld 下。
[mysqld_safe]
server-id=2
3.2 Slave同步失败 Errno: 13121
mysql> show slave status \G; 命令查看到错误信息
Last_Errno: 13121
Last_Error: Relay log read failure: Could not parse relay log event entry.
The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master's or slave's MySQL code.
If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
另外我还看到下面2个，后面恢复的时候要用到
Master_Log_File: binlog.000035
Exec_Master_Log_Pos: 24808935
检查一下磁盘空间，看到没剩余空间了
[root@cache2 ~]# df -h
文件系统 容量 已用 可用 已用% 挂载点
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 8.9M 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/mapper/centos-root 247G 247G 0 100% /
/dev/sda1 1014M 278M 737M 28% /boot
tmpfs 379M 0 379M 0% /run/user/0
这个一条虚拟机，SO直接改，物理机可以删除写文件或者加块硬盘
扩充磁盘空间
加好空间后，重新定义Master，把 show slave status \G; 里看到的 Master_Log_File 写到 master_log_file，Exec_Master_Log_Pos 写到 master_log_pos。
mysql> change master to master_host='10.1.1.1',
-> master_user='root',
-> master_password='password',
-> master_log_file='binlog.000035',
-> master_log_pos=24808935;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
尝试启动 Slave，并且查看Slave状态，如果成功，重启MySQL服务再试。
扫一扫
2036
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
