彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > MySQL高可用之PXC安装部署(续)

MySQL高可用之PXC安装部署(续)

发布时间:2019-10-05 09:46编辑:工作委员会浏览(99)

     

     

    Preface

    Preface

     

     

        Today,I'm gonna implement a PXC,Let's see the procedure.

        Yesterday I implemented a three-nodes PXC,but there were some errors when proceeding it.So I decide to research what's wrong with it.The operations and evidences shows below.

     

     

    Framework

    Procedure

     

     

    Hostname IP Port OS Version MySQL Version Xtrabackup version
    zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
    zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
    zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)

    Fisrt of all,I find out that yesterday's error is due to lack of "socat" package on first node zlm2.

     

     1 //Here's the error log of it.
     2 2018-08-08T08:48:18.198487+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21')
     3 2018-08-08T08:48:18.200272+01:00 2 [Note] WSREP: DONOR thread signaled with 0
     4 which: no socat in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
     5     2018-08-08T06:48:18.782344Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
     6     2018-08-08T06:48:18.784043Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
     7     2018-08-08T06:48:18.785926Z WSREP_SST: [ERROR] ****************************************************** 
     8 2018-08-08T08:48:18.786496+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21': 2 (No such file or directory)
     9 2018-08-08T08:48:18.786587+01:00 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21'
    10 2018-08-08T08:48:18.787610+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
    11 
    12 [root@zlm2 08:53:31 /data/mysql/mysql3308/data]
    13 #rpm -qa|grep socat
    14 
    15 [root@zlm2 09:11:08 /data/mysql/mysql3308/data]
    16 #yum install socat
    17 ... //Omitted.
    18 
    19 Installed:
    20   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
    21 
    22 Complete!
    

    Procedure

     

     

    Restart the PXC mysqld on zlm2.

        The official website of PXC:

     1 [root@zlm2 09:21:38 /data/mysql/mysql3308/data]
     2 #mysqladmin -P3308 shutdown
     3 
     4 [root@zlm2 09:22:10 /data/mysql/mysql3308/data]
     5 #ps aux|grep mysqld
     6 mysql     6295  0.1 22.4 1565212 227920 pts/0  Sl   08:47   0:02 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
     7 root      6835  0.0  0.0 112640   956 pts/3    R+   09:22   0:00 grep --color=auto mysqld
     8 
     9 [root@zlm2 09:22:15 /data/mysql/mysql3308/data]
    10 #ps aux|grep mysqld
    11 root      6841  0.0  0.0 112640   960 pts/3    R+   09:22   0:00 grep --color=auto mysqld
    12 
    13 [root@zlm2 09:30:04 /data/mysql/mysql3308/data]
    14 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
    15 [1] 7055
    16 
    17 [root@zlm2 09:30:20 /data/mysql/mysql3308/data]
    18 #ps aux|grep mysqld
    19 mysql     7055  0.4 22.1 1564952 224900 pts/3  Sl   09:30   0:02 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
    20 root      7583  0.0  0.0 112640   956 pts/3    R+   09:39   0:00 grep --color=auto mysqld
    

        

     

     

    It still show the ERROR output when node zlm3 startup.

        Choose the Linux-Generic software.There're three version relevant with openssl(100,101,102),I choose the ssl102 version.

     1 2018-08-08T09:32:14.789691+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') connection established to 2bcb8dba tcp://192.168.1.102:4567
     2 2018-08-08T09:32:14.790669+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
     3 2018-08-08T09:32:15.289238+01:00 0 [Note] WSREP: declaring 2bcb8dba at tcp://192.168.1.102:4567 stable
     4 2018-08-08T09:32:15.290161+01:00 0 [Note] WSREP: Node e783b5a9 state primary
     5 2018-08-08T09:32:15.291214+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
     6 view (view_id(PRIM,2bcb8dba,2)
     7 memb {
     8     2bcb8dba,0
     9     e783b5a9,0
    10     }
    11 joined {
    12     }
    13 left {
    14     }
    15 partitioned {
    16     }
    17 )
    18 2018-08-08T09:32:15.291254+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
    19 2018-08-08T09:32:15.291707+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
    20 2018-08-08T09:32:15.291726+01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
    21 2018-08-08T09:32:15.791844+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b
    22 2018-08-08T09:32:15.793169+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b from 0 (zlm3)
    23 2018-08-08T09:32:15.793198+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2c64cc25-9add-11e8-bdeb-67bbb141911b from 1 (zlm2)
    24 2018-08-08T09:32:15.793205+01:00 0 [Note] WSREP: Quorum results:
    25     version    = 4,
    26     component  = PRIMARY,
    27     conf_id    = 1,
    28     members    = 1/2 (primary/total),
    29     act_id     = 21,
    30     last_appl. = 0,
    31     protocols  = 0/8/3 (gcs/repl/appl),
    32     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
    33 2018-08-08T09:32:15.793211+01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
    34 2018-08-08T09:32:15.793213+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
    35 2018-08-08T09:32:15.793314+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
    36 2018-08-08T09:32:15.793324+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
    37 2018-08-08T09:32:15.793327+01:00 2 [Note] WSREP: Setting wsrep_ready to true
    38 2018-08-08T09:32:15.793330+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 2) (Increment: 1 -> 2)
    39 2018-08-08T09:32:15.793332+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
    40 2018-08-08T09:32:15.793337+01:00 2 [Note] WSREP: Assign initial position for certification: 21, protocol version: 3
    41 2018-08-08T09:32:15.793351+01:00 0 [Note] WSREP: Service thread queue flushed.
    42 2018-08-08T09:32:16.392574+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
    43 2018-08-08T09:32:16.392599+01:00 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 21)
    44 2018-08-08T09:32:16.392659+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
    45 2018-08-08T09:32:16.392879+01:00 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21')
    46 2018-08-08T09:32:16.396838+01:00 2 [Note] WSREP: DONOR thread signaled with 0
    47 2018-08-08T09:32:17.917352+01:00 0 [Note] WSREP: (e783b5a9, 'tcp://0.0.0.0:4567') turning message relay requesting off
    48     2018-08-08T07:32:27.003790Z WSREP_SST: [INFO] Streaming the backup to joiner at 192.168.1.102 4444
    49 2018-08-08T09:32:27.124206+01:00 4 [Note] Access denied for user 'sst'@'192.168.1.101' (using password: YES)
    50 2018-08-08T09:32:27.138281+01:00 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'sst' host: 'localhost' (Got an error reading communication packets)
    51     2018-08-08T07:32:27.139336Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
    52     2018-08-08T07:32:27.140923Z WSREP_SST: [ERROR] xtrabackup finished with error: 1.  Check /data/mysql/mysql3308/data//innobackup.backup.log
    53 --------------- innobackup.backup.log (START) --------------------
    54 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1013308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_group_home_dir=./ --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT --defaults_group=mysqld --parallel=4 
    55 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1013308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_group_home_dir=./ --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT --defaults_group=mysqld --parallel=4 --port=3308 --user=sst --password=* --socket=/tmp/mysql3308.sock --lock-ddl=1 --backup=1 --galera-info=1 --binlog-info=ON --stream=xbstream --target-dir=/tmp/pxc_sst_sn2p/donor_xb_qh5x 
    56 180808 09:32:27  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3308;mysql_socket=/tmp/mysql3308.sock' as 'sst'  (using password: YES).
    57 Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3308;mysql_socket=/tmp/mysql3308.sock','sst',...) failed: Access denied for user 'sst'@'192.168.1.101' (using password: YES) at - line 1314.
    58 180808 09:32:27 Connecting to MySQL server host: localhost, user: sst, password: set, port: 3308, socket: /tmp/mysql3308.sock
    59 Using server version 5.7.22-22-29.26-log
    60 180808 09:32:27 Executing LOCK TABLES FOR BACKUP...
    61 Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
    62 --------------- innobackup.backup.log (END) ----------------------
    63     2018-08-08T07:32:27.143691Z WSREP_SST: [ERROR] ****************************************************** 
    64     2018-08-08T07:32:27.145779Z WSREP_SST: [ERROR] Cleanup after exit with status:22
    65 2018-08-08T09:32:27.158932+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21': 22 (Invalid argument)
    66 2018-08-08T09:32:27.159041+01:00 0 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.1.102:4444/xtrabackup_sst//1' --socket '/tmp/mysql3308.sock' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix ''  --binlog '/data/mysql/mysql3308/logs/mysql-bin' --gtid 'bd5525ab-9a15-11e8-aa0f-4b830c783fc7:21'
    67 2018-08-08T09:32:27.160430+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -22 (Invalid argument)
    68 
    69 //Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation.
    70 //It seams the user sst@localhost is lack of privilege of "PROCESS".
    

     

     

    Install Percona-XtraDB-Cluster software on three nodes.

    Modify the privilege of user sst@localhost.

     1 [root@zlm2 07:56:03 ~]
     2 #mkdir -p /opt/mysql
     3 
     4 [root@zlm2 07:56:51 ~]
     5 #ls -l|grep Percona
     6 -rwxr-xr-x   1 root root 252395830 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz
     7 
     8 [root@zlm2 07:57:01 ~]
     9 #gzip -d Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz 
    10 
    11 [root@zlm2 07:57:59 ~]
    12 #ls -l|grep Percona
    13 -rwxr-xr-x   1 root root 1035939840 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar
    14 
    15 [root@zlm2 07:59:36 ~]
    16 #tar xf Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar -C /opt/mysql
    17 
    18 [root@zlm2 08:02:29 ~]
    19 #cd /usr/local
    20 
    21 [root@zlm2 08:02:22 /usr/local]
    22 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
    23 
    24 [root@zlm2 08:02:28 /usr/local]
    25 #ls -l
    26 total 4
    27 drwxr-xr-x. 2 root   root    105 May  7 10:44 bin
    28 drwxr-xr-x. 2 root   root      6 Jun 10  2014 etc
    29 drwxr-xr-x. 2 root   root      6 Jun 10  2014 games
    30 drwxr-xr-x. 2 root   root      6 Jun 10  2014 include
    31 drwxr-xr-x. 2 root   root      6 Jun 10  2014 lib
    32 drwxr-xr-x. 3 root   root     18 May  7 09:37 lib64
    33 drwxr-xr-x. 2 root   root      6 Jun 10  2014 libexec
    34 lrwxrwxrwx  1 root   root     36 Apr 28 14:30 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64/
    35 drwxr-xr-x  9 root   root   4096 Aug  7 10:00 mysql-5.7.21-linux-glibc2.12-x86_64
    36 drwxrwxr-x  5 root   root     94 May  7 09:28 mysql_performance_monitor-agent
    37 drwxr-xr-x. 2 root   root     18 Apr 28 16:06 sbin
    38 drwxr-xr-x. 6 root   root     58 May  7 09:37 share
    39 drwxr-xr-x. 2 root   root      6 Jun 10  2014 src
    40 drwxr-xr-x  8 zabbix zabbix   70 Apr 28 16:45 zabbix
    41 
    42 [root@zlm2 08:02:32 /usr/local]
    43 #rm -f mysql
    44 
    45 [root@zlm2 08:02:37 /usr/local]
    46 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
    47 
    48 [root@zlm2 08:03:04 /usr/local]
    49 #ls -l
    50 total 0
    51 drwxr-xr-x. 2 root root  21 Jul  4 03:21 bin
    52 drwxr-xr-x. 2 root root   6 Jun 10  2014 etc
    53 drwxr-xr-x. 2 root root   6 Jun 10  2014 games
    54 drwxr-xr-x. 2 root root   6 Jun 10  2014 include
    55 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib
    56 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib64
    57 drwxr-xr-x. 2 root root   6 Jun 10  2014 libexec
    58 lrwxrwxrwx  1 root root  75 Aug  7 08:23 mysql -> /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/
    59 drwxr-xr-x  9 root root 120 Aug  7 08:22 mysql-5.7.21-linux-glibc2.12-x86_64
    60 drwxr-xr-x. 2 root root   6 Jun 10  2014 sbin
    61 drwxr-xr-x. 7 root root  71 Jul  4 03:21 share
    62 drwxr-xr-x. 2 root root   6 Jun 10  2014 src
    63 
    64 [root@zlm2 08:32:56 /usr/local]
    65 #ldd mysql/bin/mysqld
    66     linux-vdso.so.1 =>  (0x00007fff96f3f000)
    67     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1119136000)
    68     libaio.so.1 => /lib64/libaio.so.1 (0x00007f1118f34000)
    69     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f1118d27000)
    70     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f1118af0000)
    71     libssl.so.10 => /lib64/libssl.so.10 (0x00007f1118883000)
    72     libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f111849b000)
    73     libdl.so.2 => /lib64/libdl.so.2 (0x00007f1118297000)
    74     libz.so.1 => /lib64/libz.so.1 (0x00007f1118081000)
    75     librt.so.1 => /lib64/librt.so.1 (0x00007f1117e78000)
    76     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f1117b71000)
    77     libm.so.6 => /lib64/libm.so.6 (0x00007f111786f000)
    78     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1117658000)
    79     libc.so.6 => /lib64/libc.so.6 (0x00007f1117297000)
    80     /lib64/ld-linux-x86-64.so.2 (0x00007f1119359000)
    81     libfreebl3.so => /lib64/libfreebl3.so (0x00007f1117094000)
    82     libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f1116e47000)
    83     libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f1116b64000)
    84     libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f1116960000)
    85     libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f111672d000)
    86     libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f111651e000)
    87     libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f1116319000)
    88     libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f11160ff000)
    89     libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f1115eda000)
    90     libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f1115c78000)
    91     liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f1115a53000)
    92     
    93 ... //Omitted the procedure of installation on the other nodes.
    
     1 (zlm@192.168.1.101 3308)[(none)]>select user,host from mysql.user;
     2 +---------------+-------------+
     3 | user          | host        |
     4 +---------------+-------------+
     5 | zlm           | 192.168.1.% |
     6 | mysql.session | localhost   |
     7 | mysql.sys     | localhost   |
     8 | root          | localhost   |
     9 | sst           | localhost   |
    10 +---------------+-------------+
    11 5 rows in set (0.02 sec)
    12 
    13 (zlm@192.168.1.101 3308)[(none)]>show grants for sst@localhost;
    14 +---------------------------------------------------------------------------+
    15 | Grants for sst@localhost                                                  |
    16 +---------------------------------------------------------------------------+
    17 | GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' |
    18 +---------------------------------------------------------------------------+
    19 1 row in set (0.00 sec)
    20 
    21 (zlm@192.168.1.101 3308)[(none)]>grant process on *.* to 'sst'@'localhost';
    22 ERROR 1045 (28000): Access denied for user 'zlm'@'192.168.1.%' (using password: YES)
    23 (zlm@192.168.1.101 3308)[(none)]>exit
    24 Bye
    25 
    26 [root@zlm2 10:07:32 ~]
    27 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3308.sock
    28 mysql: [Warning] Using a password on the command line interface can be insecure.
    29 Welcome to the MySQL monitor.  Commands end with ; or g.
    30 Your MySQL connection id is 9
    31 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
    32 
    33 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
    34 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    35 
    36 Oracle is a registered trademark of Oracle Corporation and/or its
    37 affiliates. Other names may be trademarks of their respective
    38 owners.
    39 
    40 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    41 
    42 (root@localhost mysql3308.sock)[(none)]>grant process on *.* to sst@localhost;
    43 Query OK, 0 rows affected, 1 warning (0.01 sec)
    44 
    45 (root@localhost mysql3308.sock)[(none)]>show grants for sst@localhost;
    46 +------------------------------------------------------------------------------------+
    47 | Grants for sst@localhost                                                           |
    48 +------------------------------------------------------------------------------------+
    49 | GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' |
    50 +------------------------------------------------------------------------------------+
    51 1 row in set (0.00 sec)
    

     

     

    **Initialized a PXC node with proper pxc relevant parameters below.**

    Restart PXC again on zlm2.

     1 [root@zlm2 08:51:49 ~]
     2 #mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
     3 
     4 [root@zlm2 08:52:44 ~]
     5 #chown -R mysql.mysql /data
     6 
     7 [root@zlm2 08:53:27 ~]
     8 #cp /vagrant/my3308.cnf /data/mysql/mysql3308
     9 
    10 [root@zlm2 08:54:09 ~]
    11 #cd /data/mysql/mysql3308
    12 
    13 [root@zlm2 08:54:17 /data/mysql/mysql3308]
    14 #ls -l
    15 total 8
    16 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 data
    17 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 logs
    18 -rwxr-xr-x 1 root  root  4522 Aug  7 08:54 my3308.cnf
    19 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 tmp
    20 
    21 [root@zlm2 08:54:30 /data/mysql/mysql3308]
    22 #tail -20 my3308.cnf 
    23 ...
    24 #pxc
    25 innodb_locks_unsafe_for_binlog=1
    26 innodb_autoinc_lock_mode=2
    27 loose-wsrep_cluster_name=pxc_wubx 
    28 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.103
    29 loose-wsrep_node_address=192.168.1.101
    30 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
    31 loose-wsrep_sst_method=xtrabackup-v2
    32 #loose-wsrep_sst_method=rsync
    33 loose-wsrep_sst_auth=sst:zlmzlm
    34 #loose-wsrep_debug=on
    35 #loose-wsrep_provider_options="debug=on"
    36 [root@zlm2 08:54:38 /data/mysql/mysql3308]
    37 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
    38 
    39 [root@zlm2 09:00:56 /data/mysql/mysql3308]
    40 #tail ./data/error.log 
    41 2018-08-07T09:00:47.560965+01:00 0 [Warning] CA certificate ca.pem is self signed.
    42 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM //This is the password we need when first login.
    43 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
    44 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
    45 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
    46 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
    47 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
    48 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
    49 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
    50 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
    51 
    52 [root@zlm2 09:02:45 /data/mysql/mysql3308]
    53 #cd data
    54 
    55 [root@zlm2 09:02:57 /data/mysql/mysql3308/data]
    56 #ls -l
    57 total 409676
    58 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
    59 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
    60 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
    61 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
    62 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
    63 -rw-r----- 1 mysql mysql      2354 Aug  7 09:00 error.log
    64 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
    65 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ibdata1
    66 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile0
    67 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
    68 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
    69 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
    70 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
    71 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
    72 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
    73 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
    74 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
    75 -rw-r----- 1 mysql mysql       227 Aug  7 09:00 slow.log
    76 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
    77 
    78 //Omitted the operations on the other nodes.Notice that there's no need to initialize the other node.
    
    [root@zlm2 10:11:10 ~]
    #ps aux|grep mysqld
    mysql     7055  0.1 22.2 1564952 225952 pts/3  Sl   09:30   0:04 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
    root      7661  0.0  0.0 112640   960 pts/0    R+   10:11   0:00 grep --color=auto mysqld
    
    [root@zlm2 10:11:28 ~]
    #mysqladmin -uzlm -pzlmzlm -h192.168.1.101 -P3308 shutdown
    mysqladmin: [Warning] Using a password on the command line interface can be insecure.
    
    [root@zlm2 10:11:50 ~]
    #ps aux|grep mysqld
    root      7672  0.0  0.0 112640   960 pts/0    R+   10:12   0:00 grep --color=auto mysqld
    
    [root@zlm2 09:39:30 /data/mysql/mysql3308/data]
    #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
    [2] 7690
    [1]   Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
    
    [root@zlm2 10:12:44 /data/mysql/mysql3308/data]
    #ps aux|grep mysqld
    mysql     7690  5.8 20.6 1556488 210296 pts/3  Sl   10:12   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
    root      7729  0.0  0.0 112640   960 pts/3    R+   10:13   0:00 grep --color=auto mysqld
    
    //No ERROR messages were found in error log.
    

     

     

    本文由彩世界平台发布于工作委员会,转载请注明出处:MySQL高可用之PXC安装部署(续)

    关键词:

上一篇:Java之JDBC

下一篇:没有了