mysql服务器管理_MySQL服务器的管理与维护

最新推荐文章于 2022-04-16 18:37:31 发布

苒子

最新推荐文章于 2022-04-16 18:37:31 发布

阅读量239

点赞数

文章标签： mysql服务器管理

本文链接：https://blog.csdn.net/weixin_30658759/article/details/113331161

版权

MySQL服务器的管理与维护

1、监控复制

1)、查看主服务器状态：SHOW

MASTER STATUS

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File

| Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000011 |

106 |

+-------------------+----------+--------------+------------------+

1 row in set (0.04 sec)

2)、显示二进制日志中的事件：SHOW

BINLOG EVENTS

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT

[offset,] row_count]

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

FROM pos：从哪个位置开始

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

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

mysql> show binlog events limit 10,3;

+-------------------+------+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Log_name

| Pos |

Event_type | Server_id | End_log_pos | Info

| master-bin.000001 | 9264 | Query

1 |

9615 | use `mysql`; CREATE TABLE IF NOT EXISTS help_category (

help_category_id smallint unsigned not 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 utf8 comment='help categories' |

| master-bin.000001 | 9615 | Query

1 |

9957 | use `mysql`; CREATE TABLE IF NOT EXISTS help_relation (

help_topic_id int unsigned not null references 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-topic relation'

| master-bin.000001 | 9957 | Query

1 |

10238 | use `mysql`; CREATE TABLE IF NOT EXISTS help_keyword

(

help_keyword_id int unsigned not null,

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;

+-------------------+-----+------------+-----------+-------------+---------------------+

| Log_name

+-------------------+-----+------------+-----------+-------------+---------------------+

| master-bin.000010 | 550 | Xid

1 |

577 | COMMIT |

| 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;

+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| Log_name

| Pos |

Event_type | Server_id | End_log_pos | Info

+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| 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

| 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;

+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| Log_name

| Pos |

Event_type | Server_id | End_log_pos | Info

+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

| 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

+-------------------+------+------------+-----------+-------------+----------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

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

SHOW BINARY

LOGS

SHOW MASTER

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 for master 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

Relay_Log_File: relay-log.000055

Relay_Log_Pos: 252

Relay_Master_Log_File: master-bin.000011

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: 106

Relay_Log_Space: 18331

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;

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

| Id |

User

| Host

| db

| Command

| Time | State

| Info

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

| 2

| repluser | 192.168.88.130:45975 | NULL

| Binlog Dump | 678 | Has sent all

binlog to slave; waiting for binlog to be updated |

NULL

| 3

| root

| localhost

ibdatax | Query

0 | NULL

| show full processlist |

+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

repluser状态为：Has sent all binlog to slave; waiting

for binlog to be updated

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

2 rows in

set (0.00 sec)

查看从库进程状态

mysql>

show full processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

| Id |

User

| Host

| db |

Command | Time | State

| Info

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

| 2

| system user |

| NULL | Connect | 1332 | Waiting for master to send

event

| NULL

| 1

| system user |

| NULL | Connect | 248 | Has read all

relay log; waiting for the slave I/O thread to update it |

NULL

| 4

| root

| localhost | NULL | Query

0 | NULL

| show full processlist |

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

3 rows in

set (0.00 sec)

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

event

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

另一个system user状态为：Has read all relay log; waiting for

the 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 REPLICATION SLAVE,REPLICATION CLIENT ON

*.* TO 'repluser'@'192.168.%.%'IDENTIFIED BY 'testpass';

mysql> CHANGE MASTER

TOMASTER_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

1740

192.168.88.131

repluser

replpass

3306

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

./relay-log.000046

1886

master-bin.000010

1740

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=

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=

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

sync_master_info =

sync_relay_log =

sync_relay_log_info

= 1

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

苒子

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫