MySQL服务器的管理与维护-向日葵屋

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) 修改其它从库的指向

2）、计划外提升一个从库为主库：

(1) 确定哪个从库的数据为最新最全；

Master_Log_File: master1-bin.000002

Read_Master_Log_Pos:245

(2) 等待所有的从库执行从主库那复制而来的生成的中继日志；

(3) 在提升为主库的从库上STOP SLAVE；而后，让各从库指向新的主库；

(4) 再次比较主库和各从库上的两个参数：

Master\_Log\_File:master1-bin.000002
Read\_Master\_Log\_Pos:245

9、相对理想的主从复制配置：

(1)、master上配置:

sync_binlog=1

sync_binlog是MySQL的binlog写入方式。它可以设置0以上的值。最安全的设置是1，即写一个binlog，同步一次。确保每次事务提前之前都能将二进制日志同步磁盘上；

对于使用InnoDB存储引擎的场景：

innodb_flush_logs_at_trx_commit=1

控制innodb的redo的刷盘策略，可以设置的值是0，1，2。默认值为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

innodb_support_xa=1

设为ON时，会使用二阶段提交协议来保证binlog和innodb的一致。

(2)、slave上配置:

read_only=1

从库只读，但是有super权限的依然可以写入

sync_master_info= 1

sync_relay_log = 1

sync_relay_log_info= 1

设置为1以确保不会丢失信息

MySQL服务器的管理与维护

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL服务器稳定性挑战：实例与维护技巧

相关 MySQL服务器稳定性测试：发现与维护

相关服务器的维护维修,服务器维护维修

相关管理与维护DNS服务器,UNIX下DNS服务器之管理维护篇

相关我的世界服务器的维护,教程/服务器维护

相关数据切分——Mysql分区表的管理与维护

相关数据切分——Mysql分区表的管理与维护

相关 MySQL服务器的管理与维护

相关路由器的日常维护与管理

相关（11）Linux服务器管理维护注意事项

随便看看

计算机配置清单硬盘,2020年组装电脑配置清单列表

超实用：Vue 自定义指令合集

【忆当年】H5布局易筋经——一个模型教你画出90%的前端页面

下午带着几个同学打了两节课的牌~

GeekOS课程设计-Project 2

计算机配置设置重置,电脑怎么重置系统电脑重置系统方法【图文】

教程文章

热评文章

1江湖小白之一起学Python （二）爬取数据的保存

2Java Shiro：简化身份验证和授权的安全框架

3Java中try()catch{}的使用方法

4Swagger注解-@ApiModel 和 @ApiModelProperty

5windows下强制杀死tomcat进程

6uni-app 条形码(一维码)/二维码生成实现

标签列表