PMM出问题排查

看各种日志
monitoring service

1
2
3
4
[root@node4 ~]# ll /var/log/pmm-*
-rw-r--r--. 1 root root 1880 Sep 27 18:02 /var/log/pmm-linux-metrics-42000.log
-rw-r--r--. 1 root root 783 Sep 27 18:02 /var/log/pmm-mysql-metrics-42002.log
-rw-r--r--. 1 root root 7143 Sep 27 18:08 /var/log/pmm-mysql-queries-0.log

docker里的日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@node4 log]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e4916410b314 percona/pmm-server:latest "/opt/entrypoint.sh" 2 hours ago Up 31 minutes 0.0.0.0:80->80/tcp, 443/tcp pmm-server
948a9aeb047e percona/pmm-server:latest "/bin/true" 2 hours ago Created pmm-data
[root@node4 log]# docker logs e4916410b314
2017-09-27 08:39:47,175 CRIT Supervisor running as root (no user in config file)
2017-09-27 08:39:47,175 WARN Included extra file "/etc/supervisord.d/pmm.ini" during parsing
Unlinking stale socket /var/run/supervisor/supervisor.sock
2017-09-27 08:39:47,527 INFO RPC interface 'supervisor' initialized
2017-09-27 08:39:47,528 INFO supervisord started with pid 1
2017-09-27 08:39:48,536 INFO spawned: 'mysql' with pid 15
2017-09-27 08:39:48,543 INFO spawned: 'consul' with pid 16
2017-09-27 08:39:48,552 INFO spawned: 'grafana' with pid 17
2017-09-27 08:39:48,563 INFO spawned: 'nginx' with pid 18
2017-09-27 08:39:48,610 INFO spawned: 'cron' with pid 19
2017-09-27 08:39:48,612 INFO spawned: 'qan-api' with pid 20

进去容器看

1
2
3
4
docker exec -it e4916410b314 /bin/bash
/var/log 下面各种日志
/var/log/grafana/grafana.log
/var/log/prometheus.log

pmm-admin list是yes不代表没问题,check-network看看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[root@node4 ~]# pmm-admin check-network
PMM Network Status
Server Address | 172.16.83.103
Client Address | 172.16.83.103
* System Time
NTP Server (0.pool.ntp.org) | 2017-09-27 17:13:58 +0800 CST
PMM Server | 2017-09-27 09:13:58 +0000 GMT
PMM Client | 2017-09-27 17:13:58 +0800 CST
PMM Server Time Drift | OK
PMM Client Time Drift | OK
PMM Client to PMM Server Time Drift | OK
* Connection: Client --> Server
-------------------- -------
SERVER SERVICE STATUS
-------------------- -------
Consul API OK
Prometheus API OK
Query Analytics API OK
Connection duration | 551.133µs
Request duration | 2.467879ms
Full round trip | 3.019012ms
* Connection: Client <-- Server
-------------- ------ -------------------- ------- ---------- ---------
SERVICE TYPE NAME REMOTE ENDPOINT STATUS HTTPS/TLS PASSWORD
-------------- ------ -------------------- ------- ---------- ---------
linux:metrics node4 172.16.83.103:42000 OK YES -
mysql:metrics node4 172.16.83.103:42002 OK YES -

MySQL忘记密码处理方法,无需重启

源库这边跑一个sysbench,为了测试之后kill -HUP是不是会有影响

1
2
3
4
5
6
7
8
9
10
11
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=30 \
--events=5000000 --report-interval=5 --db-driver=mysql prepare
sysbench \
/usr/share/sysbench/oltp_read_write.lua \
--mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysqldata/3306/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 \
--threads=30 --report-interval=5 --time=7000 --db-driver=mysql run > binlog_off.txt

把忘记密码的库的mysql.user表传到一个知道密码的测试库

1
2
3
4
5
[root@uz22199 mysql]# scp -p user.* 10.4.3.100:/data/mysqldata/3306/data/fandb/
root@10.4.3.100's password:
user.frm 100% 11KB 10.6KB/s 00:00
user.MYD 100% 736 0.7KB/s 00:00
user.MYI 100% 4096 4.0KB/s 00:00

生成一下insert语句,后面用

1
2
3
4
[root@test43100 ~]# mysqldump --user=root --password='mysql' fandb user --where="host='localhost' and user='root'" |grep INSERT
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
INSERT INTO `user` VALUES ('localhost','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B','N','2017-08-04 08:12:53',NULL,'N');

阅读全文

MySQL不完全恢复

不完全恢复

通过做Slave恢复

找到问题语句的位置,这里是1315

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#170911 14:42:19 server id 330641 end_log_pos 2508 CRC32 0xf697b7bb Xid = 215
COMMIT/*!*/;
# at 2508
#170911 14:50:56 server id 330641 end_log_pos 2573 CRC32 0x8309dea0 GTID last_committed=9 sequence_number=10
SET @@SESSION.GTID_NEXT= '5c351518-78ec-11e7-8e7a-005056a610c3:1315'/*!*/;
# at 2573
#170911 14:50:56 server id 330641 end_log_pos 2693 CRC32 0xb06b9074 Query thread_id=25 exec_time=1 error_code=0
use `fandb`/*!*/;
SET TIMESTAMP=1505112656/*!*/;
DROP TABLE `users` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

那一份备份,恢复出来,启动,reset slave all;然后change master

1
2
3
4
5
6
7
8
9
10
change master to
master_host='10.4.3.100',
master_user='repl',
master_password='repl',
master_port=3306,
master_auto_position=1;
重点
start slave sql_thread until SQL_BEFORE_GTIDS='5c351518-78ec-11e7-8e7a-005056a610c3:1315';
start slave io_thread;

阅读全文

通过mysqlbinlog --skip-gtids恢复后再备份可能造成的坑

通过mysqlbinlog –skip-gtids恢复后再备份可能造成的坑

版本

1
2
3
4
5
6
7
8
[root@uz22199 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@uz22199 backup]# mysql -e"select @@version"
+------------+
| @@version |
+------------+
| 5.7.18-log |
+------------+

源库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
表结构与数据
root@mysqldb 21:51: [fandb]> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`email` varchar(10) DEFAULT NULL,
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@mysqldb 18:43: [fandb]> select* from users;
+-------+
| email |
+-------+
| 1 |
| 10 |
| 20 |
| 30 |
| 5 |
+-------+
插入一条数据
insert into users values(50); --GTID=1297
再删掉
delete from users where email=50; ----GTID=1298
当前Executed_Gtid_Set
root@mysqldb 18:35: [fandb]> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000005 | 495 | | | 5c351518-78ec-11e7-8e7a-005056a610c3:1-1298 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

源库再次应用一下已经执行过得binlog, 再次应用insert into users values(50); 这一条

这里先不考虑有没有可能这样子去恢复数据,只做实验

阅读全文

在datadir外创建InnoDB表

在datadir外创建表

要在MySQL datadir外的特定位置创建新的InnoDB file-per-table tablespace,请在create table时指定DATA DIRECTORY = absolute_path_to_directory子句

提前规划好位置,因为无法使用alter语句修改一个表的DATA DIRECTORY属性.

MySQL会在目标目录中创建一个对应于数据库名称的子目录,并在改目录中创建表的.ibd文件用于存储表数据.在MySQL datadir目录下的数据库目录中,MySQL创建一个包含表的路径名称的table_name.isl文件. .isl文件由MySQL处理,像一个符号链接(不过InnoDB表不支持实际的符号链接)

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
版本
[root@test43100 ~]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test43100 ~]# mysql -e "\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
root@mysqldb 11:00: [(none)]> use fandb
Database changed
root@mysqldb 11:00: [fandb]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
root@mysqldb 11:00: [fandb]> create table t_out(id int auto_increment primary key) data directory='/data/outdir';
Query OK, 0 rows affected (0.37 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 412
-rw-r----- 1 mysql mysql 28 Sep 6 11:03 t_out.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:03 t_out.frm
包含.isl和.frm文件

您还可以将CREATE TABLE … TABLESPACE与DATA DIRECTORY子句结合使用,以便在MySQL数据目录之外创建一个file-per-table tablespace。 为此,您必须指定innodb_file_per_table作为表空间名称。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@mysqldb 11:03: [fandb]> create table t_out2(id int auto_increment primary key) TABLESPACE = innodb_file_per_table data directory='/data/outdir';
Query OK, 0 rows affected (0.42 sec)
查看目标目录
[mysql@test43100 data]$ tree outdir/
outdir/
└── fandb
├── t_out2.ibd
└── t_out.ibd
查看datadir
[root@test43100 fandb]# ls -lt
total 428
-rw-r----- 1 mysql mysql 29 Sep 6 11:08 t_out2.isl
-rw-r----- 1 mysql mysql 8556 Sep 6 11:08 t_out2.frm

使用第二章方法无需启用innodb_file_per_table

使用说明

  • MySQL最初保持.ibd文件打开,阻止您卸载设备,但如果服务器正忙,最终可能会关闭该表。 当MySQL运行时,请注意不要意外卸载外部设备,或者在设备断开连接时启动MySQL。 当相关的.ibd文件丢失时尝试访问表会导致严重的错误,需要重新启动服务器。
    如果.ibd文件仍然不在预期路径,服务器重新启动可能会失败。 在这种情况下,请手动删除数据库目录中的table_name.isl文件,并在重新启动后执行DROP TABLE以删除.frm文件,并从数据字典中删除有关该表的信息。

  • Before tables on an NFS-mounted volume, review potential issues outlined in Using NFS with MySQL.

  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES … FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.

  • DATA DIRECTORY子句是使用符号链接的一个支持的替代方法.InnoDB直接使用符号链接是不支持的

innobackupex遇到的坑

stream选择使用tar方式压缩到时slave_info信息不完整

版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@test1 backup]# innobackupex --version
innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
[root@test1 backup]# cat /etc/redhat-release
CentOS release 6.4 (Final)
[root@test1 backup]# gzip --version
gzip 1.3.12
Copyright (C) 2007 Free Software Foundation, Inc.
Copyright (C) 1993 Jean-loup Gailly.
This is free software. You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.
Written by Jean-loup Gailly.
[root@test1 backup]# mysql -e"\s"
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /data/mysqldata/3306/mysql.sock
Uptime: 15 min 52 sec
Threads: 3 Questions: 61 Slow queries: 0 Opens: 116 Flush tables: 5 Open tables: 0 Queries per second avg: 0.064
--------------

备份命令

1
innobackupex --user=backup --password='backup' --slave-info --stream=tar /tmp | gzip -> /data/mysqldata/backup/xtra_full.tar.gz

期望得到的完整信息

1
2
3
[root@test1 slave_info]# more xtrabackup_slave_info
SET GLOBAL gtid_purged='5c351518-78ec-11e7-8e7a-005056a610c3:1-1164';
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2019 Fan() All Rights Reserved.

访客数 : | 访问量 :

#