Centos 6.x 搭建 PXC 集群及在线添加节点

目录 MySQL, 数据库

[TOC]


Centos 6.x 搭建 PXC 集群及在线添加节点


By:liups@liups.com

1.环境介绍

1.1操作系统版本

CentOS release 6.8 (Final)

1.2 默认端口

端口 用途
3306 数据库端口
4444 SST 全量传输端口
4567 组内成员通信端口
4568 IST增量传输端口

1.3 主机信息

IP hostname 用途 base datadir
192.168.51.101 cet701 pxc01 /usr/local/mysql /data/mysql
192.168.51.102 cet702 pxc02 /usr/local/mysql /data/mysql
192.168.51.103 cet703 pxc02 /usr/local/mysql /data/mysql

2. 软件下载

2.1 关于软件包的说明

Percona provides generic tarballs with all required files and binaries for manual installation.

You can download the appropriate tarball package from https://www.percona.com/downloads/Percona-XtraDB-Cluster-57

There are multiple tarballs in the Linux – Generic section depending on the OpenSSL library available in your distribution:

  • ssl100: for Debian prior to 9 and Ubuntu prior to 14.04 versions
  • ssl101: for CentOS 6 and CentOS 7
  • ssl102: for Debian 9 and Ubuntu versions starting from 14.04

这里的环境是 CentOS7 所以选择 ssl101 的软件包。

2.2 获取二进制软件包

cd /usr/local/src
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.25-31.35/binary/tarball/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt153.tar.gz

3.安装前的准备

3.1 安装依赖软件

yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat scons nc make libaio libaio-devel boost-devel rsync asio-devel readline-devel libev bison gcc gcc-c++  -y

3.2 防火墙关闭或者开放端口

service iptables stop

或者开放端口:ports 3306, 4444, 4567 and 4568

vi /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4444 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4567 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT
service iptables reload

3.3 关闭selinux

临时关闭

setenforce 0

永久关闭:

vi /etc/sysconfig/selinux
SELINUX=disabled

确认已经关闭

[root@mydbt001 local]# getenforce
Disabled

3.4 创建MySQL 用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

4 安装Percona-XtraDB-Cluster软件

采用二进制软件包的方式按照,直接复制软件包到 basedir,并创建软连

以下在3个节点都执行

cp /usr/local/src/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz /usr/local/

4.1 解压软件包

4.2 创建软连

cd /usr/local/
ln -s Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101 mysql

4.3 授与basedir和datadir权限

mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

5 搭建pxc环境

5.1 配置参数文件

pxc01

server-id = 1003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.101
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

pxc02

server-id = 2003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.102
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

pxc03

server-id = 3003306
log-bin = /data/mysql/mysql-bin
binlog_format=ROW
#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://192.168.56.103,192.168.56.101,192.168.56.102
wsrep_node_address=192.168.56.103
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=pxcuser:Password123

5.2 节点1初始化MySQL

cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
[root@cet701 mysql]# cd /usr/local/mysql/bin
[root@cet701 bin]# ./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️ 通过二进制安装的pxc软件,修改 /etc/init.d/mysql 中的basedir和datadir,否则会出现以下错误

sh: wsrep_sst_xtrabackup-v2: command not found

5.3 启动第一个节点

启动第一个节点的时候需要 采用 bootstrap-pxc 选项。

[root@cet701 data]# service mysql bootstrap-pxc  
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (P[  确定  ]aDB Cluster).
[root@cet701 data]# ps -ef |grep mysql
root     15705     1  0 16:17 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid --wsrep-new-cluster
mysql    16704 15705 17 16:17 pts/2    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/local/mysql/lib/libgalera_smm.so --wsrep-new-cluster --log-error=/data/mysql/error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root     16745  8991  0 16:17 pts/2    00:00:00 grep --color=auto mysql

5.4 修改密码

[root@cet701 bin]# cat  /data/mysql/error.log|grep password
2019-06-30T08:09:14.665946Z 1 [Note] A temporary password is generated for root@localhost: 1uZPZS1gou&y
[root@cet701 data]# mysqladmin -uroot -p password        
Enter password: 
New password: 
Confirm new password: 

新密码为:Password123

5.5 创建sst用户

CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'pxcuser'@'localhost';
mysql> CREATE USER 'pxcuser'@'localhost' IDENTIFIED BY 'Pxcuser123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'pxcuser'@'localhost';
Query OK, 0 rows affected (0.09 sec)

5.6 查看pxc状态

mysql> show status like 'wsrep%st%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| wsrep_local_state_uuid          | 868f27cb-9b0f-11e9-81ec-ebbaefc4a7ba |
| wsrep_last_applied              | 3                                    |
| wsrep_last_committed            | 3                                    |
| wsrep_flow_control_status       | OFF                                  |
| wsrep_cert_deps_distance        | 1.000000                             |
| wsrep_local_state               | 4                                    |
| wsrep_local_state_comment       | Synced                               |
| wsrep_ist_receive_status        |                                      |
| wsrep_ist_receive_seqno_start   | 0                                    |
| wsrep_ist_receive_seqno_current | 0                                    |
| wsrep_ist_receive_seqno_end     | 0                                    |
| wsrep_cluster_weight            | 1                                    |
| wsrep_evs_evict_list            |                                      |
| wsrep_evs_state                 | OPERATIONAL                          |
| wsrep_cluster_conf_id           | 1                                    |
| wsrep_cluster_size              | 1                                    |
| wsrep_cluster_state_uuid        | 868f27cb-9b0f-11e9-81ec-ebbaefc4a7ba |
| wsrep_cluster_status            | Primary                              |
+---------------------------------+--------------------------------------+

5.7 初始化节点2

cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️ :需要同样修改 /etc/init.d/mysql 中的basedir和datadir

5.8 启动节点2

/etc/init.d/mysql start

5.9 初始化节点3

cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/data/mysql --user = mysql
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql

⚠️:需要同样修改 /etc/init.d/mysql 中的basedir和datadir

5.10 启动节点3

/etc/init.d/mysql start

5.11 查看查看pxc状态

mysql> show status like 'wsrep%'
    -> ;
+----------------------------------+--------------------------------------------------------+
| Variable_name                    | Value                                                  |
+----------------------------------+--------------------------------------------------------+
| wsrep_local_state_uuid           | b25b348e-9c06-11e9-84f5-ce5173cbcbb4                   |
| wsrep_protocol_version           | 8                                                      |
| wsrep_last_applied               | 54                                                     |
| wsrep_last_committed             | 54                                                     |
| wsrep_replicated                 | 0                                                      |
| wsrep_replicated_bytes           | 0                                                      |
| wsrep_repl_keys                  | 0                                                      |
| wsrep_repl_keys_bytes            | 0                                                      |
| wsrep_repl_data_bytes            | 0                                                      |
| wsrep_repl_other_bytes           | 0                                                      |
| wsrep_received                   | 77                                                     |
| wsrep_received_bytes             | 19487                                                  |
| wsrep_local_commits              | 0                                                      |
| wsrep_local_cert_failures        | 0                                                      |
| wsrep_local_replays              | 0                                                      |
| wsrep_local_send_queue           | 0                                                      |
| wsrep_local_send_queue_max       | 1                                                      |
| wsrep_local_send_queue_min       | 0                                                      |
| wsrep_local_send_queue_avg       | 0.000000                                               |
| wsrep_local_recv_queue           | 0                                                      |
| wsrep_local_recv_queue_max       | 2                                                      |
| wsrep_local_recv_queue_min       | 0                                                      |
| wsrep_local_recv_queue_avg       | 0.012987                                               |
| wsrep_local_cached_downto        | 4                                                      |
| wsrep_flow_control_paused_ns     | 0                                                      |
| wsrep_flow_control_paused        | 0.000000                                               |
| wsrep_flow_control_sent          | 0                                                      |
| wsrep_flow_control_recv          | 0                                                      |
| wsrep_flow_control_interval      | [ 173, 173 ]                                           |
| wsrep_flow_control_interval_low  | 173                                                    |
| wsrep_flow_control_interval_high | 173                                                    |
| wsrep_flow_control_status        | OFF                                                    |
| wsrep_cert_deps_distance         | 15.882353                                              |
| wsrep_apply_oooe                 | 0.000000                                               |
| wsrep_apply_oool                 | 0.000000                                               |
| wsrep_apply_window               | 1.000000                                               |
| wsrep_commit_oooe                | 0.000000                                               |
| wsrep_commit_oool                | 0.000000                                               |
| wsrep_commit_window              | 1.000000                                               |
| wsrep_local_state                | 4                                                      |
| wsrep_local_state_comment        | Synced                                                 |
| wsrep_cert_index_size            | 6                                                      |
| wsrep_cert_bucket_count          | 58                                                     |
| wsrep_gcache_pool_size           | 18312                                                  |
| wsrep_causal_reads               | 0                                                      |
| wsrep_cert_interval              | 0.000000                                               |
| wsrep_ist_receive_status         |                                                        |
| wsrep_ist_receive_seqno_start    | 0                                                      |
| wsrep_ist_receive_seqno_current  | 0                                                      |
| wsrep_ist_receive_seqno_end      | 0                                                      |
| wsrep_incoming_addresses       | 192.168.56.101:3306,192.168.56.102:3306,192.168.56.103:3306|
| wsrep_desync_count               | 0                                                      |
| wsrep_evs_delayed                |                                                        |
| wsrep_evs_evict_list             |                                                        |
| wsrep_evs_repl_latency           | 0/0/0/0/0                                              |
| wsrep_evs_state                  | OPERATIONAL                                            |
| wsrep_gcomm_uuid                 | eaa2ae94-9c61-11e9-8403-1a605ed6ff1d                   |
| wsrep_cluster_conf_id            | 21                                                     |
| wsrep_cluster_size               | 3                                                      |
| wsrep_cluster_state_uuid         | b25b348e-9c06-11e9-84f5-ce5173cbcbb4                   |
| wsrep_cluster_status             | Primary                                                |
| wsrep_connected                  | ON                                                     |
| wsrep_local_bf_aborts            | 0                                                      |
| wsrep_local_index                | 1                                                      |
| wsrep_provider_name              | Galera                                                 |
| wsrep_provider_vendor            | Codership Oy <info@codership.com>                      |
| wsrep_provider_version           | 3.26(r)                                                |
| wsrep_ready                      | ON                                                     |
+----------------------------------+--------------------------------------------------------+
68 rows in set (0.00 sec)

6 添加节点

6.1 环境说明

源pxc集群环境

IP hostname 用途 base wsrep_cluster_name
10.30.105.73 mydbt001 pxc01 /usr/local/mysql pxc_enmo
10.30.104.245 mydbt002 pxc02 /usr/local/mysql pxc_enmo

新增节点环境

IP hostname 用途 base wsrep_cluster_name
10.31.91.204 fengshuo pxc03 /usr/local/mysql pxc_enmo

添加节点支持全量和增量,本次使用增量的方式,先搭建一个slave,然后将slave加入到pxc集群。

6.2 参数配置

在pxc原集群节点上修改参数

在 pxc01和pxc02节点上配置参数,并修改参数文件。此参数可以在线修改,立即生效,不需要重启数据库。

 set global wsrep_cluster_address='gcomm://10.30.105.74,10.30.104.245,10.31.91.204';
mysql> set global wsrep_cluster_address='gcomm://10.30.105.74,10.30.104.245,10.31.91.204';
Query OK, 0 rows affected (8.00 sec)

mysql> show variables like '%wsrep_cluster_address%'
    -> ;
+-----------------------+-------------------------------------------------+
| Variable_name         | Value                                           |
+-----------------------+-------------------------------------------------+
| wsrep_cluster_address | gcomm://10.30.105.74,10.30.104.245,10.31.91.204 |
+-----------------------+-------------------------------------------------+
1 row in set (0.00 sec)
wsrep_cluster_address=gcomm://10.30.105.74,10.30.104.245,10.31.91.204

6.3 搭建SLAVE

搭建slave的时候,模拟数据库有数据插入。

在添加节点之前创建一些临时数据。

mysql> show create table e;
+-------+------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                         |
+-------+------------------------------------------------------------------------------------------------------+
| e     | CREATE TABLE `e` (
  `i` datetime NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

通过xtrabackup 备份数据库,在备份的时候模拟pxc集群有插入数据。


    | 2019-07-02 09:42:42 |
    +---------------------+
    23 rows in set (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    ERROR 1062 (23000): Duplicate entry '2019-07-02 09:42:57' for key 'PRIMARY'
    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.02 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    ERROR 1062 (23000): Duplicate entry '2019-07-02 09:43:00' for key 'PRIMARY'
    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into e values(now());
    Query OK, 1 row affected (0.00 sec)

6.3.1 xtrabackup进行备份

备份开始时间是 190702 09:42:52

[root@mydbt001 pxc]# xtrabackup --defaults-file=/etc/my.cnf -uroot -pPassword123 --backup --target-dir=/pxc/
xtrabackup: recognized server arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --port=3306 --socket=/tmp/mysql.sock --user=root --password=* --backup=1 --target-dir=/pxc/
190702 09:42:52  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
190702 09:42:52  version_check Connected to MySQL server
190702 09:42:52  version_check Executing a version check against the server...
190702 09:42:52  version_check Done.
190702 09:42:52 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.7.22-22-29.26-log
xtrabackup version 2.4.14 based on MySQL server 5.7.19 Linux (x86_64) (revision id: ef675d4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1024M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
190702 09:42:52 >> log scanned up to (2638564)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
190702 09:42:52 [01] Copying ./ibdata1 to /pxc/ibdata1
190702 09:42:53 >> log scanned up to (2638564)

……

备份完成

xtrabackup: The latest check point (for incremental): '2647383'
xtrabackup: Stopping log copying thread.
.190702 09:43:11 >> log scanned up to (2647392)

190702 09:43:11 Executing UNLOCK TABLES
190702 09:43:11 All tables unlocked
190702 09:43:11 [00] Copying ib_buffer_pool to /pxc/ib_buffer_pool
190702 09:43:11 [00]        ...done
190702 09:43:11 Backup created in directory '/pxc/'
190702 09:43:11 [00] Writing /pxc/backup-my.cnf
190702 09:43:11 [00]        ...done
190702 09:43:11 [00] Writing /pxc/xtrabackup_info
190702 09:43:11 [00]        ...done
xtrabackup: Transaction log of lsn (2638555) to (2647392) was copied.
190702 09:43:11 completed OK!
[root@mydbt001 pxc]#

备份完成时间是 190702 09:43:43

6.3.2 传输备份到目标主机

scp -r pxc 10.31.91.204:/

6.3.3 目标节点 prepare 数据

[root@fengshuo local]# xtrabackup  --defaults-file=/etc/my.cnf --prepare target-dir=/pxc/
xtrabackup: recognized server arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT
xtrabackup: recognized client arguments: --datadir=/data/mysql --open_files_limit=65535 --server-id=1003306 --log_bin=/data/mysql/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=64M --innodb_log_file_size=256M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=90 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --port=3306 --socket=/tmp/mysql.sock --prepare=1
xtrabackup: Error: unknown argument: 'target-dir=/pxc/'
[root@fengshuo local]# xtrabackup  --defaults-file=/etc/my.cnf --prepare --target-dir=/pxc/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1
xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:1024M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1003306 --redo-log-version=1 --prepare=1 --target-dir=/pxc/
xtrabackup version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
xtrabackup: cd to /pxc/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2638555)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:1024M:autoextend
xtrabackup:   innodb_log_group_home_dir = .

……

InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: page_cleaner: 1000ms intended loop took 7793ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.19 started; log sequence number 2648597
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2648616
190702 10:06:08 completed OK!

6.4 安装 pxc 软件

采用二进制软件包的方式按照,直接复制软件包到 basedir,并创建软连。

cp /usr/local/src/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101.tar.gz /
/usr/local/
ln -s Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl101 mysql

6.4.1 授与basedir和datadir权限

mkdir -p /data/mysql
chown -R mysql:mysql  /data/mysql
chown -R mysql:mysql  /usr/local/mysql

6.4.2 将prepare后的数据文件复制到datadir

mv /pxc/* /data/mysql/
chown -R mysql:mysql /data/mysql

6.4.3 授权并启动数据库服务

[root@fengshuo data]# chown  -R mysql:mysql mysql
[root@fengshuo data]# /etc/init.d/mysql  start
Starting MySQL (Percona XtraDB Cluster).Logging to '/data/mysql/error.log'.
. SUCCESS!

登录数据库,查看在备份期间的数据 09:42到9:43 已经存在。

| 2019-07-02 09:42:59 |
| 2019-07-02 09:43:00 |
| 2019-07-02 09:43:01 |
| 2019-07-02 09:43:05 |
| 2019-07-02 09:43:06 |
| 2019-07-02 09:43:07 |
+---------------------+
33 rows in set (0.01 sec)

6.5 配置slave

6.5.1 找post点

[root@fengshuo mysql]# cat xtrabackup_binlog_info
mysql-bin.000002    10747

6.5.2 创建复制用户

mysql> create user repl@'%' identified by 'Repl1234';
Query OK, 0 rows affected (0.04 sec)
 grant replication slave, replication client on *.* to repl@'%';

6.5.3 change master

mysql>  CHANGE MASTER TO MASTER_HOST='10.30.105.74',MASTER_USER='repl',MASTER_PASSWORD='Repl1234',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=10747;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

6.5.4 查看复制状态

   Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.105.74
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 11208
               Relay_Log_File: fengshuo-relay-bin.000003
                Relay_Log_Pos: 781
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:

随便插入一些数据。

| 2019-07-02 09:43:07 |
| 2019-07-02 10:21:25 |
| 2019-07-02 10:21:27 |
| 2019-07-02 10:21:28 |
| 2019-07-02 10:21:29 |
| 2019-07-02 10:21:31 |
| 2019-07-02 10:21:32 |
+---------------------+
39 rows in set (0.00 sec)

6.6 slave 节点转换成pxc节点

slave节点通过 show slave status 找到 Exec_Master_Log_Pos,并stop slave。

          Master_Log_File: mysql-bin.000002
          Exec_Master_Log_Pos: 12798

6.6.1 在pxc01节点上通过Exec_Master_Log_Pos 找到 Xid

[root@mydbt001 pxc]# mysqlbinlog -vv /data/mysql/mysql-bin.000002 |grep 12798
#190702 10:21:32 server id 1003306  end_log_pos 12798 CRC32 0x5be4942f  Xid = 50
# at 12798
[root@mydbt001 pxc]#

6.6.2 pxc01节点添加参数

#pxc
default_storage_engine=Innodb
innodb_autoinc_lock_mode=2
wsrep_cluster_name=pxc_enmo
wsrep_cluster_address=gcomm://10.30.105.74,10.30.104.245,10.31.91.204
wsrep_node_address=10.31.91.204
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="pxcuser:Pxcuser123"

6.6.3 创建grastate.dat文件

将 pxc01节点的grastate.dat拷贝新节点,修改seqno为上面对象的 Xid值即 50

# GALERA saved state
version: 2.1
uuid:    b25b348e-9c06-11e9-84f5-ce5173cbcbb4
seqno:   50
safe_to_bootstrap: 0

6.6.4 启动新节点

2019-07-03T12:27:34.666497Z 0 [Warning] WSREP: Fail to access the file (/data/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2019-07-03T12:27:34.669345Z 0 [Warning] WSREP: (ef903445, 'tcp://0.0.0.0:4567') address 'tcp://10.31.91.204:4567' points to own listening address, blacklisting
2019-07-03T12:27:35.669742Z 1 [Warning] WSREP: Gap in state sequence. Need state transfer.
        2019-07-03T12:27:36.832833Z WSREP_SST: [INFO] xtrabackup_ist received from donor: Running IST
        2019-07-03T12:27:36.834756Z WSREP_SST: [INFO] Galera co-ords from recovery: b25b348e-9c06-11e9-84f5-ce5173cbcbb4:50
2019-07-03T12:27:37.405886Z 0 [Warning] CA certificate ca.pem is self signed.
2019-07-03T12:27:37.458437Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=fengshuo-relay-bin' to avoid this problem.
2019-07-03T12:27:37.470574Z 0 [Warning] Recovery from master pos 12798 and file mysql-bin.000002 for channel ''. Previous relay log pos and relay log file had been set to 320, ./fengshuo-relay-bin.000005 respectively.

加入集群成功

6.6.5 检查pxc集群状态

mysql> show status like '%wsrep_cluster%'
    -> ;
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 21                                   |
| wsrep_cluster_size       | 3                                    |
| wsrep_cluster_state_uuid | b25b348e-9c06-11e9-84f5-ce5173cbcbb4 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+
4 rows in set (0.00 sec)

mysql>

其状态为 Primary。

6.6.6 检查grastate.dat

[root@fengshuo mysql]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid:    b25b348e-9c06-11e9-84f5-ce5173cbcbb4
seqno:   -1
safe_to_bootstrap: 0

seqno 的值变为-1.

6.6.7 清除 SLAVE 信息

检查slave的状态,仍然有slave的信息。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.30.105.74
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 12798
               Relay_Log_File: fengshuo-relay-bin.000006
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 12798
              Relay_Log_Space: 1767
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 8a79a560-9c06-11e9-b48f-00163e064c24
             Master_Info_File: /u01/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

这里手动测试start slave,会报主键冲突,因为数据增量已经同步过来了。

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.105.74
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 13858
               Relay_Log_File: fengshuo-relay-bin.000007
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table pxc.e; Duplicate entry '2019-07-02 10:38:45' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 13032
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 12798
              Relay_Log_Space: 1590
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table pxc.e; Duplicate entry '2019-07-02 10:38:45' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 13032
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1003306
                  Master_UUID: 8a79a560-9c06-11e9-b48f-00163e064c24
             Master_Info_File: /u01/data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 190704 05:17:32
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

可以看到 Exec_Master_Log_Pos: 12798 仍然是 在 12798的位置。所以slave的信息已经没有用处了,进行slave信息清除。

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
Empty set (0.00 sec)

mysqldump 单表备份引发的性能问题分析

目录 MySQL, 数据库

在通过mysqldump进行MySQL数据库(innodb引擎)逻辑备份的时候经常使用的参数组合就是 –single-transaction –master-data=2,今天的案例就是来分析下通过–single-transaction –master-data=2参数组合进行单表备份而引发的性能问题。

  • 问题描述:
  • 某业务系统(数据库采用的MySQL数据库)上午10点左右部分业务业务反应系统缓慢,用户登陆出现超时的现象。

  • 问题分析与处理
  • 登录数据库服务器查看top,iostat –x 1等查看操作系统负载。
    top发现:CPU:load average 超过10且是mysqld进程占用,确定是MySQL等待。
    通过show processlist查看:发现大量的Waiting for global read lock 。(信息已经脱敏处理)

    lock
    这里第一感觉是正在进行mysqldump备份数据,然后show processlit查看数据库的进程信息,果然发现了backup 用户正在 执行 FLUSH TABLES WITH READ LOCK 。

    back
    通过操作系统 ps –ef|grep mysqldump 查看,发现mysqldump进程。

    mysqldump

    mysqldump进程是root发起的,仔细查看mysqldump 进程的pid是不同的,且dbname和tablename也是不同的,很显然这是一个 for循环在mysqldump备份单个表。
    切换到root用户,查看定时任务通过crontab –l发现有一个每天上午 4点30分开始执行的mbak.sh脚本,查看脚本是mysqldump备份数据库,通过分析备份脚本发现这个脚本的逻辑是这样的:先整库备份一次使用的是–all-database参数,然后分别备份每个数据库为一个备份文件,最后是单表备份一次,即一个表备份成一个文件。

    部分脚本节选如下:
    所有的数据库备份一个文件的脚本

    cd ${BACKDIR}/alldb_${TIME}
    $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction --master-data=2  --all-databases --log-error=alldb_${TIME}.err >alldb.${TIME}.sql
    echo "`date +"%F %T"` BACKUP all  DATABASE  one file FINISH" |tee -a $logF
    

    每个库一个备份文件的脚本

    echo "DB HOST  $IP IS BACKUP DATABASE per database one file " |tee -a $logF
    for dbname in `$MYSQL -B -N -e "show databases"|grep -vE "information_schema|performance_schema"`
    do
    mkdir -p ${BACKDIR}/${dbname}_${TIME}
    cd ${BACKDIR}/${dbname}_${TIME}
    $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction --master-data=2  $dbname --log-error=${dbname}_${TIME}.err >${dbname}.${TIME}.sql
    echo "`date +"%F %T"` BACKUP $dbname  one fie finish" |tee -a $logF
    done
    

    每个表一个备份文件的脚本

    ### per table per database
    echo "DB host $IP is backing" |tee -a $logF
    for dbname in `$MYSQL -B -N -e "show databases"|grep -vE "information_schema|performance_schema"`
    do
    mkdir -p ${BACKDIR}/${dbname}_per_${TIME}
    cd ${BACKDIR}/${dbname}_per_${TIME}
    for table in `$MYSQL $dbname -e "show tables"|sed '1d'|grep -vE "yxxxxxxx"`
    do
    echo "`date +"%F %T"` begin backup  ${dbname}.${table}  TABLE" |tee -a $logF
    $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction  --master-data=2  $dbname $table --log-error=${dbname}_${TIME}.err >$dbname_$table.sql
    echo "`date +"%F %T"` BACKUP ${dbname}.$table FINISH" |tee -a $logF
    done
    echo "`date +"%F %T"` BACKUP $dbname DATABASE FINISH" |tee -a $logF
    done
    

    很显然出问题的时候是在备份单个表,通过mbak.sh脚本的逻辑来看,是先全库备份,全库完成再单库备份,单库备份完成之后再单表备份。现在卡在单表备份的  FLUSH TABLES WITH READ LOCK,这是一个全库级别的锁,单表备份为什么会锁整个库呢?仔细查看上面的mysqldump备份命令,可以发现每次mysqldump都添加了 –single-transaction –master-data=2,这是问题的关键,下面通过开启general log来分析问题。
    开启general_log。

    root@hostname 13:14:  [test]> show variables like '%gen%';
    +------------------+------------------------------------------------+
    | Variable_name    | Value                                          |
    +------------------+------------------------------------------------+
    | general_log      | ON                                             |
    | general_log_file | /usr/local/mysql/data/BertLiudeMacBook-Pro.log |
    +------------------+------------------------------------------------+
    

    备份test 库下面的 t表,使用–single-transaction –master-data=2 参数

    mysqldump  -uroot -ppassword  --default-character-set=utf8 --single-transaction --master-data=2   test t    >test_t.sql
    

    查看general log:

    2017-08-28T15:14:15.960100Z     106 Connect    root@localhost on  using Socket
    2017-08-28T15:14:15.960860Z     106 Query        /*!40100 SET @@SQL_MODE='' */
    2017-08-28T15:14:15.962606Z     106 Query        /*!40103 SET TIME_ZONE='+00:00' */
    2017-08-28T15:14:15.962793Z     106 Query        FLUSH /*!40101 LOCAL */ TABLES
    2017-08-28T15:14:15.962887Z     106 Query        FLUSH TABLES WITH READ LOCK
    2017-08-28T15:14:15.962943Z     106 Query        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
    2017-08-28T15:14:15.962997Z     106 Query        START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
    2017-08-28T15:14:15.963094Z     106 Query        SHOW VARIABLES LIKE 'gtid\_mode'
    2017-08-28T15:14:15.965326Z     106 Query        SELECT @@GLOBAL.GTID_EXECUTED\
    2017-08-28T15:14:15.965458Z     106 Query        SHOW MASTER STATUS
    2017-08-28T15:14:15.965554Z     106 Query        UNLOCK TABLES
    …… …...
    2017-08-28T15:14:15.970349Z     106 Init DB       test
    2017-08-28T15:14:15.970438Z     106 Query        SHOW TABLES LIKE 't'
    2017-08-28T15:14:15.971500Z     106 Query        SAVEPOINT sp
    2017-08-28T15:14:15.971790Z     106 Query        show table status like 't'
    2017-08-28T15:14:15.973180Z     106 Query        SET SQL_QUOTE_SHOW_CREATE=1
    2017-08-28T15:14:15.973483Z     106 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:14:15.973668Z     106 Query        show create table `t`
    2017-08-28T15:14:15.973989Z     106 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:14:15.974788Z     106 Query        show fields from `t`
    2017-08-28T15:14:15.975367Z     106 Query        show fields from `t`
    2017-08-28T15:14:15.975627Z     106 Query        SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
    2017-08-28T15:14:15.975779Z     106 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:14:15.975881Z     106 Query        use `test`
    2017-08-28T15:14:15.975994Z     106 Query        select @@collation_database
    2017-08-28T15:14:15.976158Z     106 Query        SHOW TRIGGERS LIKE 't'
    2017-08-28T15:14:15.976556Z     106 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:14:15.976611Z     106 Query        ROLLBACK TO SAVEPOINT sp
    2017-08-28T15:14:15.976652Z     106 Query        RELEASE SAVEPOINT sp
    2017-08-28T15:14:15.994479Z     106 Quit
    

    发现其执行了

    FLUSH /*!40101 LOCAL */ TABLES
    FLUSH TABLES WITH READ LOCK
    

    备份test 库下面的 t 表,不使用–single-transaction –master-data=2 参数

    mysqldump -uroot –ppassword --default-character-set=utf8 test t  >test_t.sql
    

    查看general log:

    2017-08-28T15:18:00.613324Z     109 Connect    root@localhost on  using Socket
    2017-08-28T15:18:00.614229Z     109 Query        /*!40100 SET @@SQL_MODE='' */
    2017-08-28T15:18:00.615737Z     109 Query        /*!40103 SET TIME_ZONE='+00:00' */
    2017-08-28T15:18:00.615939Z     109 Query        SHOW VARIABLES LIKE 'gtid\_mode'
    2017-08-28T15:18:00.618627Z     109 Query        SELECT @@GLOBAL.GTID_EXECUTED
    ………… 省略
    2017-08-28T15:18:00.624351Z     109 Init DB       test
    2017-08-28T15:18:00.624422Z     109 Query        SHOW TABLES LIKE 't'
    2017-08-28T15:18:00.624843Z     109 Query        LOCK TABLES `t` READ /*!32311 LOCAL */《--这里只锁定了 t 表。
    2017-08-28T15:18:00.624967Z     109 Query        show table status like 't'
    2017-08-28T15:18:00.625452Z     109 Query        SET SQL_QUOTE_SHOW_CREATE=1
    2017-08-28T15:18:00.625514Z     109 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:18:00.625566Z     109 Query        show create table `t`
    2017-08-28T15:18:00.625624Z     109 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:18:00.626177Z     109 Query        show fields from `t`
    2017-08-28T15:18:00.626474Z     109 Query        show fields from `t`
    2017-08-28T15:18:00.626725Z     109 Query        SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
    2017-08-28T15:18:00.626896Z     109 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:18:00.626978Z     109 Query        use `test`
    2017-08-28T15:18:00.627034Z     109 Query        select @@collation_database
    2017-08-28T15:18:00.627106Z     109 Query        SHOW TRIGGERS LIKE 't'
    2017-08-28T15:18:00.627399Z     109 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:18:00.627451Z     109 Query        UNLOCK TABLES
    2017-08-28T15:18:00.645805Z     109 Quit
    

    发现:没有执行 FLUSH /*!40101 LOCAL */ TABLES、FLUSH TABLES WITH READ LOCK 。而是执行的LOCK TABLES `t ` READ,对备份的 t 表进行一个lock。
    备份全库使用–all-databases及–single-transaction –master-data=2 参数

    mysqldump -uroot -ppassword --default-character-set=utf8  --single-transaction --master-data=2 --all-databases   >all.singel-2.sql
    查看general log:
    
    2017-08-28T15:22:22.002783Z     110 Connect    root@localhost on  using Socket
    2017-08-28T15:22:22.003739Z     110 Query        /*!40100 SET @@SQL_MODE='' */
    2017-08-28T15:22:22.005306Z     110 Query        /*!40103 SET TIME_ZONE='+00:00' */
    2017-08-28T15:22:22.005487Z     110 Query        FLUSH /*!40101 LOCAL */ TABLES
    2017-08-28T15:22:22.005606Z     110 Query        FLUSH TABLES WITH READ LOCK
    2017-08-28T15:22:22.005684Z     110 Query        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
    2017-08-28T15:22:22.005721Z     110 Query        START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
    2017-08-28T15:22:22.005803Z     110 Query        SHOW VARIABLES LIKE 'gtid\_mode'
    2017-08-28T15:22:22.008043Z     110 Query        SELECT @@GLOBAL.GTID_EXECUTED
    2017-08-28T15:22:22.008193Z     110 Query        SHOW MASTER STATUS
    2017-08-28T15:22:22.008256Z     110 Query        UNLOCK TABLES
    ………… 省略
    2017-08-28T15:22:22.022739Z     110 Init DB       b
    2017-08-28T15:22:22.022825Z     110 Query        SHOW CREATE DATABASE IF NOT EXISTS `b`
    2017-08-28T15:22:22.022890Z     110 Query        SAVEPOINT sp
    2017-08-28T15:22:22.022956Z     110 Query        show tables
    2017-08-28T15:22:22.023106Z     110 Query        RELEASE SAVEPOINT sp
    2017-08-28T15:22:22.023157Z     110 Init DB       e
    2017-08-28T15:22:22.023200Z     110 Query        SHOW CREATE DATABASE IF NOT EXISTS `e`
    2017-08-28T15:22:22.023277Z     110 Query        SAVEPOINT sp
    2017-08-28T15:22:22.023353Z     110 Query        show tables
    

    结论:不管是全库备份还是单表备份使用了–single-transaction –master-data=2 参数会执行FLUSH /*!40101 LOCAL */ TABLES、FLUSH TABLES WITH READ LOCK 来获取 show master status的一致性。
    通过进一步测试发现mysqldump备份的时候只使用 –single-transaction 不使用–master-data=2参数是不会进行锁表的。只使用 –master-data=2参数为了获取show master status,会执行 FLUSH TABLES WITH READ LOCK  的全局锁。
    只使用 –single-transaction 备份单表的general log

    2017-08-28T15:24:59.149626Z     111 Connect    root@localhost on  using Socket
    2017-08-28T15:24:59.150404Z     111 Query        /*!40100 SET @@SQL_MODE='' */
    2017-08-28T15:24:59.151672Z     111 Query        /*!40103 SET TIME_ZONE='+00:00' */
    2017-08-28T15:24:59.151944Z     111 Query        SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
    2017-08-28T15:24:59.152007Z     111 Query        START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
    2017-08-28T15:24:59.152178Z     111 Query        SHOW VARIABLES LIKE 'gtid\_mode'
    2017-08-28T15:24:59.154385Z     111 Query        SELECT @@GLOBAL.GTID_EXECUTED
    2017-08-28T15:24:59.154538Z     111 Query        UNLOCK TABLES
    ……省略
    2017-08-28T15:24:59.160621Z     111 Init DB       test
    2017-08-28T15:24:59.160790Z     111 Query        SHOW TABLES LIKE 't'
    2017-08-28T15:24:59.161636Z     111 Query        SAVEPOINT sp
    2017-08-28T15:24:59.161832Z     111 Query        show table status like 't'
    2017-08-28T15:24:59.162886Z     111 Query        SET SQL_QUOTE_SHOW_CREATE=1
    2017-08-28T15:24:59.163046Z     111 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:24:59.163226Z     111 Query        show create table `t`
    2017-08-28T15:24:59.163401Z     111 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:24:59.163994Z     111 Query        show fields from `t`
    2017-08-28T15:24:59.164420Z     111 Query        show fields from `t`
    2017-08-28T15:24:59.164831Z     111 Query        SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
    2017-08-28T15:24:59.165083Z     111 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:24:59.165263Z     111 Query        use `test`
    2017-08-28T15:24:59.165378Z     111 Query        select @@collation_database
    2017-08-28T15:24:59.165540Z     111 Query        SHOW TRIGGERS LIKE 't'
    2017-08-28T15:24:59.167415Z     111 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:24:59.167585Z     111 Query        ROLLBACK TO SAVEPOINT sp
    2017-08-28T15:24:59.167707Z     111 Query        RELEASE SAVEPOINT spt
    

    结论:只使用–single-transaction 不执行任何lock table,也就是说可以进行热备份。

    只使用 –master-data=2参数备份单表的general log

    22017-08-28T15:27:20.891774Z   112 Connect    root@localhost on  using Socket
    2017-08-28T15:27:20.891932Z     112 Query        /*!40100 SET @@SQL_MODE='' */
    2017-08-28T15:27:20.892941Z     112 Query        /*!40103 SET TIME_ZONE='+00:00' */
    2017-08-28T15:27:20.893122Z     112 Query        FLUSH /*!40101 LOCAL */ TABLES
    2017-08-28T15:27:20.893404Z     112 Query        FLUSH TABLES WITH READ LOCK
    2017-08-28T15:27:20.893508Z     112 Query        SHOW VARIABLES LIKE 'gtid\_mode'
    2017-08-28T15:27:20.897696Z     112 Query        SELECT @@GLOBAL.GTID_EXECUTED
    2017-08-28T15:27:20.897990Z     112 Query        SHOW MASTER STATUS
    2017-08-28T15:27:20.906499Z     112 Init DB       test
    …………省略
    2017-08-28T15:27:20.906595Z     112 Query        SHOW TABLES LIKE 't'
    2017-08-28T15:27:20.907204Z     112 Query        show table status like 't'
    2017-08-28T15:27:20.907865Z     112 Query        SET SQL_QUOTE_SHOW_CREATE=1
    2017-08-28T15:27:20.907973Z     112 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:27:20.908068Z     112 Query        show create table `t`
    2017-08-28T15:27:20.908182Z     112 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:27:20.908294Z     112 Query        show fields from `t`
    2017-08-28T15:27:20.908705Z     112 Query        show fields from `t`
    2017-08-28T15:27:20.909113Z     112 Query        SELECT /*!40001 SQL_NO_CACHE */ * FROM `t`
    2017-08-28T15:27:20.910751Z     112 Query        SET SESSION character_set_results = 'binary'
    2017-08-28T15:27:20.910859Z     112 Query        use `test`
    2017-08-28T15:27:20.910947Z     112 Query        select @@collation_database
    2017-08-28T15:27:20.911098Z     112 Query        SHOW TRIGGERS LIKE 't'
    2017-08-28T15:27:20.911609Z     112 Query        SET SESSION character_set_results = 'utf8'
    2017-08-28T15:27:20.928296Z     112 Quit
    
  • 结论:
  • 也就是说通过这几次实验发现只要添加了–master-data=2参数就会执行 FLUSH TABLES WITH READ LOCK全局锁,即使你是备份单个表也会锁整个库来获取show master status;从而导致整个库的一个等待。在业务低峰期,获取全局锁会很快,但是由于整个库有700多个表,有部分表在上午10点也就是业务高峰期还没备份完成,由于高峰期有大量的DML操作,从而获取全局锁更加困难,Waiting for global read lock 的时间会更长,从而影响了业务的正常运行。

  • 问题解决与改善
  • 问题解决:
    找到mbak.sh 脚本的pid号,进行kill操作,数据库恢复正常。
    改善:
    调整备份策略:
    1、取消备份每个单表为一个文件,减少全局锁(经过生产环境实际测试mysqldump全库(17G数据)备份一次不到5分钟);
    2、如果有必要进行单表备份的话,禁用–master-data=2参数,只使用–single-transaction 参数;
    3、采用xtraback 物理备份替换mysqldump的逻辑备份,来进行在线热备数据库(innodb引擎)。

  • 总结:
  • 单表备份:禁用–master-data=2参数
    全库备份:–master-data=2 和 –single-transaction 组合,实现innodb的在线热备。

    附:
    官档上的 — single-transaction
    https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_single-transaction
    –single-transaction
    This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
    When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
    While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLECREATE TABLE,DROP TABLERENAME TABLETRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqlpump to retrieve the table contents to obtain incorrect contents or fail.
    –add-locks and –single-transaction are mutually exclusive.
    官档上的 –master-data
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data
    –master-data[=value]
    Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.
    If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.
    This option requires the RELOAD privilege and the binary log must be enabled.
    The –master-data option automatically turns off –lock-tables. It also turns on –lock-all-tables, unless–single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for –single-transaction). In all cases, any action on logs happens at the exact moment of the dump.
    It is also possible to set up a slave by dumping an existing slave of the master, using the –dump-slave option, which overrides –master-data and causes it to be ignored if both options are used.
    官档上的 FLUSH TABLES WITH READ LOCK
    https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-with-read-lock

    Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLESto release the lock.
    FLUSH TABLES WITH READ LOCK acquires a global read lock rather than table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:

    FLUSH TABLES WITH READ LOCK is not compatible with XA transactions.
    FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables (see Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”).

    不重启解决 MySQL Too Many Connections

    目录 MySQL, 数据库

    当发生Too many connections时,即使是DBA也无法登录到数据库,一般的做法是修改配置文件的max_connections参数,然后重启数据库,这样业务就有几秒钟的中断。

    还有一个hack的方法,用过gdb直接修改mysqld内存中max_connections的值,具体做法如下:

    gdb -ex "set max_connections=5000" -batch -p `pgrep -x mysqld`


    MySQL忘记root密码–重启重置root密码

    目录 MySQL, 数据库

    1、skip-grant-tables
    我们常用的方法是使用skip-grant-tables选项,为了安全起见,通常加上skip-networking,mysqld不侦听任何TCP/IP连接请求。
    1)修改my.cnf配置文件,在mysqld选项中添加skip-grant-tables和skip-networking。
    2)再重启mysqld server。
    3)通过sql语句修改mysql.user表中存储密码。执行flush privileges,重新启用mysql权限系统。
    UPDATE mysql.USER SET Password=PASSWORD(“liups.com”)WHERE User=’root’;

    FLUSH PRIVILEGES;
    4)删除或者注释配置文件中skip-grant-tables和skip-networking的参数选项。然后重启生效。
    2、–init-file
    mysqld_safe可以使–init-file参数选项来执行重新设定密码的sql语句。
    1)新建一个初始化文件,如/tmp/initfile,文件内容为上面修改密码的sql语句。
    UPDATE mysql.user SET Password=PASSWORD(‘liups.com’) WHERE User=’root’;

    FLUSH PRIVILEGES;
    2)关闭mysqld服务进程。
    3)使用mysqld_safe启动mysqld;
    mysqld_safe –init-file=/tmp/initfile &

    虽然简单但是需要重启MySQL库。当然也有不重启MySQL方法。下篇详解