mysql bootstrap pxc_MYSQL PXC

下载ssl101的安装包上传服务器对应安装目录/usr/local 目录。

[root@localhost ~]# yum -y install  openssl openssl-devel  perl-Time-HiRes   perl-DBD-MySQL.x86_64  perl-IO-Socket-SSL.noarch

[root@pxc2 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

warning: libev-4.15-1.el6.rf.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY

Preparing...                ########################################### [100%]

1:libev                  ########################################### [100%]

[root@pxc2 local]# yum install  percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm  -y

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

base                                                                                                                                                                 | 4.0 kB     00:00 ...

base/primary_db                                                                                                                                                      | 4.4 MB     00:00 ...

Setting up Install Process

在PXC其他节点加入集群过程中发现有报错，日志提示xtrabackup版本过低。

[root@pxc2 ~]# yum list | grep percona-xtrabackup

percona-xtrabackup-24.x86_64             2.4.9-1.el6                       @/percona-xtrabackup-24-2.4.9-1.el6.x86_64

[root@pxc2 ~]# rpm -e percona-xtrabackup-24.x86_64

[root@pxc2 ~]# yum list | grep percona-xtrabackup

[root@pxc2 ~]# yum  install  percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm  -y

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

Setting up Install Process

Examining percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm: percona-xtrabackup-24-2.4.19-1.el6.x86_64

Marking percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package percona-xtrabackup-24.x86_64 0:2.4.19-1.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================================================================================

Package                                            Arch                                Version                                    Repository                                                               Size

=================================================================================================================================================================================================================

Installing:

percona-xtrabackup-24                              x86_64                              2.4.19-1.el6                               /percona-xtrabackup-24-2.4.19-1.el6.x86_64                               32 M

Transaction Summary

=================================================================================================================================================================================================================

Install       1 Package(s)

Total size: 32 M

Installed size: 32 M

Downloading Packages:

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

Installing : percona-xtrabackup-24-2.4.19-1.el6.x86_64                                                                                                                                                     1/1

Verifying  : percona-xtrabackup-24-2.4.19-1.el6.x86_64                                                                                                                                                     1/1

Installed:

percona-xtrabackup-24.x86_64 0:2.4.19-1.el6

Complete!

1.1.1 socat 安装

[root@pxc2 local]# tar -zxvf socat-1.7.3.2.tar.gz

./configure

make

make install

解压软件包并赋予权限

# cd /usr/local/

# unlink mysql

# tar -zxvf  Percona-XtraDB-Cluster-5.7.28-rel31-31.41.1.Linux.x86_64.ssl101.tar.gz

# ln  -s    Percona-XtraDB-Cluster-5.7.28-rel31-31.41.1.Linux.x86_64.ssl101  mysql

修改PXC配置文件：

修改/etc/my.cnf配置文件如下：

server-id=1313306

pxc_strict_mode=PERMISSIVE

default_storage_engine=Innodb

innodb_autoinc_lock_mode=2

wsrep_node_name=PXC1

wsrep_cluster_name=pxc_cluster

wsrep_cluster_address=gcomm://192.168.56.121,192.168.56.130,192.168.56.122

wsrep_node_address=192.168.56.121

wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

wsrep_provider_options="gcache.size=1G"

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth="sst:kk"

初始化数据库：

[root@PXC2bin]# ./mysqld   --defaults-file=/etc/my.cnf  --basedir=/usr/local/mysql  --datadir=/data/mysql/  --user=mysql  --initialize

[root@PXC2bin]# cd  /usr/local/mysql/support-files/

启动第一个节点：

root@PXC1 support-files]# cp  mysql.server  /etc/init.d/mysqld

[root@PXC1 support-files]# /etc/init.d/mysqld --help

Usage: mysqld {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc}  [ MySQL (Percona XtraDB Cluster) options ]

[root@PXC1 support-files]#

[root@PXC1 support-files]#

[root@PXC1 support-files]# /etc/init.d/mysqld  bootstrap-pxc

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (P[  OK  ]traDB Cluster).

[root@PXC1 support-files]# ps -ef|grep mysql

root      81433  79865  0 01:19 pts/1    00:00:00 tail -fn100 /data/mysql/mysqld.log

root      83678      1  0 02:34 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysqld.pid --wsrep-new-cluster

mysql     84011  83678 18 02:34 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-new-cluster --log-error=/data/mysql/mysqld.log --pid-file=/data/mysql/mysqld.pid --socket=/data/mysql/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1

root      84045  75479  0 02:34 pts/2    00:00:00 grep mysql

[root@PXC1 support-files]#

因为初始root 密码，以为safe模式启动。

[root@PXC1 support-files]# /etc/init.d/mysqld  stop  bootstrap-pxc

Shutting down MySQL (Percona XtraDB Cluster)...            [  OK  ]

[root@PXC1support-files]#

[root@PXC1support-files]#

[root@PXC1support-files]# /usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf  --skip-grant-tables  --ledir=/usr/local/mysql/bin --wsrep_new_cluster &

[1] 84431

mysql>update mysql.user set authentication_string=password('mysql123') where user='root' and Host = 'localhost';

mysql> flush privileges;

重置好密码后，正常启动PXC

[root@pxc1 ~]# service mysqld bootstrap-pxc stop

[root@pxc1 ~]#service mysqld bootstrap-pxc start

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (P[  OK  ]traDB Cluster)..

创建SST传输账号：

mysql> CREATE USER 'sst'@'localhost' IDENTIFIED BY 'kk';

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'sst'@'%' IDENTIFIED BY 'kk';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'sst'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS ON *.* TO 'sst'@'%';

Query OK, 0 rows affected (0.00 sec)

Mysql>flush privileges;

PXC其他节点配置：

pxc其他节点配置安装与第一个节点配置基本完全一致，除了如下几个地方需要主要修改一下。

1) 配置文件里的本机IP和本节点名

以下验证说明PXC配置完成:

PXC数据同步验证：

在三个节点的PXC已经启动成功，这样在任意一个节点上执行一序列DML语句看是否能及时同步到其他节点。

从上面截图可以看出，121节点上插入数据库，在130、122其他2个节点上都能及时同步数据。