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
| Pos | Event_type | Server_id | End_log_pos | Info
|
+-------------------+-----+------------+-----------+-------------+---------------------+
| 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实现完全备份和增量备份》
5、为从库设定新的主库
参考《mysql主从复制配置实现及其监控与维护》
在从库停止复制线程，而后重新设定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
15
master-bin.000010
1740
192.168.88.131
repluser
replpass
3306
60
0
0
[root@test mysql]# cat relay-log.info
./relay-log.000046
1886
master-bin.000010
17407、清理二进制日志：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.0000048、提升从服务器为主服务器
主从服务器配置参考《mysql主从复制配置实现及其监控与维护》
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以确保不会丢失信息
