MySQL服务器的管理与维护

叁歲伎倆 2022-05-23 13:21 287阅读 0赞

**MySQL服务器的管理与维护**

**1、监控复制**

**1）、查看主服务器状态：SHOW MASTER STATUS**

mysql> show master status;

\+-------------------+----------+--------------+------------------+

\+-------------------+----------+--------------+------------------+

| master-bin.000011 |      106 |              |                  |

\+-------------------+----------+--------------+------------------+

1 row in set (0.04 sec)

**2）、显示二进制日志中的事件：SHOW BINLOG EVENTS**

SHOW BINLOG EVENTS \[IN 'log\_name'\] \[FROMpos\] \[LIMIT \[offset,\] row\_count\]

如果不指定'log\_name'，则显示第一个二进制日志。

FROMpos：从哪个位置开始

LIMIT\[offset,\] row\_count：显示显示条目，用法同SELECT语句

**例：第一个二进制日志，从第十个条目起，取3条目      **

mysql> show binlog events limit 10,3;

\+-------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| master-bin.000001 | 9264 | Query      |        1 |        9615 | use \`mysql\`;CREATE TABLE IF NOT EXISTS help\_category ( help\_category\_id smallint unsignednot null, name  char(64) not null,parent\_category\_id smallint unsigned null, url text not null, primary key(help\_category\_id), unique index (name) ) engine=MyISAM CHARACTER SET utf8comment='help categories' |

| master-bin.000001 | 9615 | Query      |        1 |        9957 | use \`mysql\`;CREATE TABLE IF NOT EXISTS help\_relation ( help\_topic\_id int unsigned not nullreferences help\_topic, help\_keyword\_id int unsigned not null references help\_keyword, primary key(help\_keyword\_id, help\_topic\_id) ) engine=MyISAM CHARACTER SET utf8 comment='keyword-topicrelation'          |

| master-bin.000001 | 9957 | Query      |        1 |       10238 | use \`mysql\`;CREATE TABLE IF NOT EXISTS help\_keyword (  help\_keyword\_id  int unsigned notnull, name char(64) not null, primary key (help\_keyword\_id), unique index(name) ) engine=MyISAM CHARACTER SET utf8 comment='help keywords'                                                                      |

3 rows in set (0.00 sec)

**例：查看master-bin.000010，从第5条起，取3条目**

mysql> show binlog events in'master-bin.000010' LIMIT 5,3;

\+-------------------+-----+------------+-----------+-------------+---------------------+

\+-------------------+-----+------------+-----------+-------------+---------------------+

| master-bin.000010 | 550 | Xid        |         1 |         577 | COMMIT /\* xid=28 \*/ |

| master-bin.000010 | 577 | Query      |        1 |         648 | BEGIN               |

| master-bin.000010 | 648 | Intvar     |        1 |         676 | INSERT\_ID=2         |

\+-------------------+-----+------------+-----------+-------------+---------------------+

3 rows in set (0.00 sec)

**例：从位置1582开始查看master-bin.000010记录的event**

mysql> show binlog events in'master-bin.000010' from 1582;

\+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| master-bin.000010 | 1582 | Query      |        1 |        1713 | use \`ibdatax\`;insert into tb\_emp value('3','Kin','Man','35','FS','kin@126.com') |

| master-bin.000010 | 1713 | Xid        |         1 |        1740 | COMMIT /\* xid=38 \*/                                                             |

| master-bin.000010 | 1740 | Stop       |        1 |        1759 |                                                                                 |

\+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

**例：从位置1582开始查看master-bin.000010记录的event，取2个条目**

mysql> show binlog events in'master-bin.000010' from 1582 limit 2;

\+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| master-bin.000010 | 1582 | Query      |        1 |        1713 | use \`ibdatax\`;insert into tb\_emp value('3','Kin','Man','35','FS','kin@126.com') |

| master-bin.000010 | 1713 | Xid        |         1 |        1740 | COMMIT /\* xid=38 \*/                                                              |

\+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

**3）、查看服务器二进制日志文件：**

**SHOWBINARY LOGS**

**SHOWMASTER LOGS**

常用于确定确定哪些日志可以被清除（PURGE BINARY LOGS）。

mysql> show binary logs;

\+-------------------+-----------+

| Log\_name          | File\_size |

\+-------------------+-----------+

| master-bin.000001 |     19742 |

| master-bin.000002 |    765307 |

| master-bin.000003 |       764 |

| master-bin.000004 |       599 |

| master-bin.000005 |     10270 |

| master-bin.000006 |       125 |

| master-bin.000007 |       596 |

| master-bin.000008 |       367 |

| master-bin.000009 |      2893 |

| master-bin.000010 |      1759 |

| master-bin.000011 |       106 |

\+-------------------+-----------+

11 rows in set (0.02 sec)

**4）、查看从服务器状态：SHOW SLAVE STATUS**

从服务器状态中，重点监控slave同步状态中的：

Slave\_IO\_Running、Slave\_SQL\_Running状态值，如果都为YES，则表示主从同步开启，状态正常。

Seconds\_Behind\_Master的值，如果为0，则表示主从同步不延时，反之同步延时。

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event \-->指示从服务器的当前状态

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File:master-bin.000011 <--同步的主服务器二进制文件

Read\_Master\_Log\_Pos: 106     <--相对于主库,从库IO线程读取到的主库的二进制日志的位置

Relay\_Log\_File: relay-log.000055 <--相对于从库，sql线程用于同步的中继日志文件

Relay\_Log\_Pos: 252     <--相对于从库,从库的sql线程同步数据时执行到的位置

Relay\_Master\_Log\_File: master-bin.000011 <--相对于主库,是从库的SQL线程执行到的二进制日志

Slave\_IO\_Running: Yes     <--从服务器I/O线程运行状态

Slave\_SQL\_Running: Yes     <--从服务器SQL线程运行状态

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: 106     <--相对于主库,是从库的sql线程执行到的位置

Relay\_Log\_Space: 18331   <--当前relay-log中继日志文件的大小

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:

1 row in set (0.00 sec)

未同步完成时,read\_master\_log\_pos 始终会大于exec\_master\_log\_pos的值(也有可能相等)

因为一个值是代表I/O线程,一个值代表SQL线程;SQL线程肯定在I/O线程之后；

此外，Seconds\_Behind\_Master值大于0

**5）、查看进程状态：SHOW  FULL  PROCCESSLIST**

必要时可以kill掉某些进程

**查看主库进程状态**

mysql>show full processlist;

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

repluser状态为：Has sent all binlog to slave; waitingfor binlog to be updated

意为二进制日志文件以通过Binlog Dump线程传递给从服务器的I/O线程，等待数据库操作后更新binlog

2 rows inset (0.00 sec)

**查看从库进程状态**

mysql>show full processlist;

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

3 rows inset (0.00 sec)

从服务器system user状态一为：Waiting for master to sendevent

意为等待主服务器传递事件，即等待主服务器传递binlog，然后经由I/O线程更新到relay log中；

另一个system user状态为：Has read all relay log; waiting forthe slave I/O thread to update it

意为二进制日志文件relay log中事件已重做完毕，等待I/O线程接收主服务器binlog后更新relay log

**2、slave是否发了复制延迟**

mysql> SHOW SLAVE STATUS\\G查看以下项：

Seconds\_Behind\_Master: 0

**3、确定master/slave节点数据是否一致**

**(1)表自身的checksum**

mysql> help checksum table

**(2)使用percona-tools中的pt-table-checksum**

**4、数据不一致时的修改方法**

重复设定复制机制

使用mysqldump从master导出slave不同的数据

参考《mysqldump和mysqlbinlog实现完全备份和增量备份》

https://blog.csdn.net/field\_yang/article/details/78641047

**5、为从库设定新的主库**

参考《mysql主从复制配置实现及其监控与维护》

https://blog.csdn.net/field\_yang/article/details/78639405

在从库停止复制线程，而后重新设定CHANGE MASTER TO命令即可；

mysql> stop slave;

Query OK, 0 rows affected (0.10 sec)

mysql> GRANT REPLICATIONSLAVE,REPLICATION CLIENT ON \*.\* TO 'repluser'@'192.168.%.%'IDENTIFIED BY'testpass';

mysql> CHANGE MASTERTOMASTER\_HOST='192.168.88.131',MASTER\_USER='repluser',MASTER\_PASSWORD='testpass',MASTER\_LOG\_FILE='master-bin.000003',MASTER\_LOG\_POS=358;

**6、跟复制功能相关的文件：**

**master.info：保存slave连接master时所需要信息；纯文本文件；**

**relay-log.info：保存了当前slave节点上（主节点上的）二进制日志和当前节点中继日志的对应关系；**

\[root@test mysql\]\# cat master.info

master-bin.000010   <-- 主服务器binlog dump线程传递的给从服务器binlog文件

1740              <-- 主服务器Position

192.168.88.131     <-- 主服务地址

repluser           <-- 提供复制的账户

replpass           <-- 提供复制的账户密码

3306              <-- 主服务地址端口

\[root@test mysql\]\# cat relay-log.info

./relay-log.000046     <-- 从服务器I/O线程更新event的从服务器relay-log文件

1886                <-- 从服务器Relay\_Log\_Pos

master-bin.000010     <-- 从服务器I/O线程传递的主服务器binlog文件

1740                <-- 主服务器Position

**7、清理二进制日志：PURGE命令**

**实例：**

mysql> show binary logs;

\+-------------------+-----------------+

|Log\_name           | File\_size       |

\+-------------------+-----------------+

|master-bin.000001  |    19924       |

|master-bin.000002  |   775387       |

|master-bin.000003  |      387       |

|master-bin.000004  |      527       |

\+-------------------+-----------------+

4rows in set (0.00 sec)

mysql>purge binary logs to'master-bin.000002';

QueryOK, 0 rows affected (0.08 sec)

mysql>show binary logs;

\+-------------------+-----------------+

|Log\_name           | File\_size       |

\+-------------------+-----------------+

|master-bin.000002  |   775387       |

|master-bin.000003  |      387       |

|master-bin.000004  |      527       |

\+-------------------+-----------------+

3rows in set (0.00 sec)

mysql>quit

Bye

\[root@testmysql\]\# ls

ibdata1     master-bin.000002 master-bin.index  mysql             relay-log.000006  test ib\_logfile0  master-bin.000003  master.info       mysql.sock        relay-log.index ib\_logfile1  master-bin.000004  mydb  relay-log.000005  relay-log.info

\[root@test mysql\]\# catmaster-bin.index

./master-bin.000002

./master-bin.000003

./master-bin.000004

**8、提升从服务器为主服务器**

主从服务器配置参考《mysql主从复制配置实现及其监控与维护》

https://blog.csdn.net/field\_yang/article/details/78639405

**1）、计划内提升一个从库为主库：**

(1) 停止向老的主库写入数据；

(2) 让计划提升为主库的从库赶上主库；

(3) 提升从库为主库

(4) 修改其它从库的指向