mysql-集群-二进制部署

所有主机上操作
一、环境清理
yum erase $(rpm -qa | grep mysql) -y ; yum erase $(rpm -qa | grep mariadb) -y
##安装epel拓展源
yum install epel-release -y

然后开始搞信任进行相互传输
ssh root@192.168.171.18

##二进制安装包上传到这里
mkdir /soft
cd /soft

含有wsrep补丁mysql的二进制包下载地址
http://releases.galeracluster.com/mysql-wsrep-5.7/binary/mysql-wsrep-5.7.43-25.35-linux-x86_64.tar.gz

mysql-wsrep-相关包下载地址
http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/

galera下载地址
http://releases.galeracluster.com/galera-3/centos/7/x86_64/
要下载的文件列表
galera-3-25.3.37-1.el7.x86_64.rpm

同步安装包至其他主机
rsync -avz /soft root@192.168.241.25:/
rsync -avz /soft root@192.168.241.26:/

在全部主机上操作，进行二进制包安装(含有wsrep补丁)
开始部署
cd /soft
INSTALLDIR=‘/usr/local/mysql’
INIT_PASSWD_MODE=‘initialize-insecure’

##解包建账号授权
tar xzvf mysql-wsrep-5.7.43-25.35-linux-x86_64.tar.gz
mv mysql-wsrep-5.7.43-25.35-linux-x86_64 ${INSTALLDIR}
id mysql &>/dev/null || useradd -M -s /sbin/nologin mysql
chown -R mysql:mysql ${INSTALLDIR}

安装相关包
yum install epel-release
yum install galera lsof -y
rpm -qa|egrep ‘galera|rsync|lsof’

#检查一下是否有mysql
cd /usr/local

开始配置集群
在第一台主机上操作,创建新集群

##查看一下路径 find / -name libgalera_smm.so
跟wsrep_provider路径是否一致
/usr/lib64/galera/libgalera_smm.so

cat >/etc/my.cnf< [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
port=3306
default-storage-engine = innodb
innodb_large_prefix=on
innodb_file_per_table = on
innodb_file_per_table
max_connections = 10000
collation-server = utf8_general_ci
character_set_server=utf8
user=mysql
log-error=/usr/local/mysql/mysql.log
##galera_conf
server-id=1
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name=‘galera’
wsrep_cluster_address=‘gcomm://’
wsrep_node_name=‘galera1’
wsrep_node_address=‘192.168.171.17’
wsrep_sst_auth=syncuser:‘QianFeng@123’
wsrep_sst_method=rsync

[client]
port = 3306
socket =/usr/local/mysql/data/mysql.sock
default-character-set = utf8
EOF

第二台主机
cat >/etc/my.cnf< [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
port=3306
default-storage-engine = innodb
innodb_large_prefix=on
innodb_file_per_table = on
innodb_file_per_table
max_connections = 10000
collation-server = utf8_general_ci
character_set_server=utf8
user=mysql
log-error=/usr/local/mysql/mysql.log
##galera_conf
server-id=2
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name=‘galera’
wsrep_cluster_address=‘gcomm://192.168.171.17,192.168.171.18,192.168.171.19’
wsrep_node_name=‘galera2’
wsrep_node_address=‘192.168.171.18’
wsrep_sst_auth=syncuser:‘QianFeng@123’
wsrep_sst_method=rsync

[client]
port = 3306
socket =/usr/local/mysql/data/mysql.sock
default-character-set = utf8
EOF

第三台主机
cat >/etc/my.cnf< [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
port=3306
default-storage-engine = innodb
innodb_large_prefix=on
innodb_file_per_table = on
innodb_file_per_table
max_connections = 10000
collation-server = utf8_general_ci
character_set_server=utf8
user=mysql
log-error=/usr/local/mysql/mysql.log
##galera_conf
server-id=3
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name=‘galera’
wsrep_cluster_address=‘gcomm://192.168.171.17,192.168.171.18,192.168.171.19’
wsrep_node_name=‘galera3’
wsrep_node_address=‘192.168.171.19’
wsrep_sst_auth=syncuser:‘QianFeng@123’
wsrep_sst_method=rsync

[client]
port = 3306
socket =/usr/local/mysql/data/mysql.sock
default-character-set = utf8
EOF

全部主机上操作
初始化mysql
INSTALLDIR=‘/usr/local/mysql’
INIT_PASSWD_MODE=‘initialize-insecure’
$INSTALLDIR/bin/mysqld--${INIT_PASSWD_MODE}
–basedir=$INSTALLDIR--datadir=${INSTALLDIR}/data
–user=mysql

全部主机上操作配置开机自启动服务
cat >/usr/lib/systemd/system/mysqld.service< [Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=${INSTALLDIR}/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

##开始配置环境变量 加载生效
cat >/etc/profile.d/mysql.sh< export PATH=$PATH:${INSTALLDIR}/bin
EOF

##全部主机上操作然后服务加载启动
systemctl daemon-reload
systemctl enable mysqld.service
systemctl start mysqld

##检查端口是否是4567 3306
ss -ntpl
手工启动mysql方式
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql &

查看日志/usr/local/mysql/mysql.log是否有错误输出

查看工作端口
ss -ntpl | grep mysqld
LISTEN 0 128 :4567 : users:((“mysqld”,pid=5780,fd=12))
LISTEN 0 128 [::]:3306 [::]: users:((“mysqld”,pid=5780,fd=29))

##添加同步账号
设置root口令以后会同步至其他主机
#source /etc/profile
#mysql -u root -p
mysql>set password=‘Qianfeng123!’;
mysql>grant all on . to ‘syncuser’@‘%’ identified by ‘QianFeng@123’;
mysql>flush privileges;
mysql>exit

在其他主机上查看同步用户的生成情况
#mysql -u root -p
#show grants for syncuser;

查看工作端口
ss -ntpl | grep mysqld
LISTEN 0 128 :4567 : users:((“mysqld”,pid=5780,fd=12))
LISTEN 0 128 [::]:3306 [::]: users:((“mysqld”,pid=5780,fd=29))

如果报错，只有4567端口则杀死该进程并将galera.cache、grastate.dat**两个文件删除
再将galera1中的/etc/my.cnf配置文件中的wsrep_cluster_address改成wsrep_cluster_address=‘gcomm://’
ps aux | grep mysqld | grep -v grep | awk ‘{print $2}’ | xargs kill
rm -f /usr/local/mysql/data/{galera.cache,grastate.dat}
systemctl start mysqld

#所有节点查看集群地址
mysql> SHOW VARIABLES LIKE ‘wsrep_cluster_address’;

#所有节点查看Galera集群状态，观察wsrep_cluster_size、wsrep_incoming_addresses、wsrep_ready是否都一致
mysql> show status like ‘wsrep%’;

群集创建成功以后，最后修改第一台主机的my.cnf
cat >/etc/my.cnf< [mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
port=3306
default-storage-engine = innodb
innodb_large_prefix=on
innodb_file_per_table = on
innodb_file_per_table
max_connections = 10000
collation-server = utf8_general_ci
character_set_server=utf8
user=mysql
log-error=/usr/local/mysql/mysql.log
##galera_conf
server-id=1
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name=‘galera’
wsrep_cluster_address=‘gcomm://192.168.241.24,192.168.241.25,192.168.241.26’
wsrep_node_name=‘galera1’
wsrep_node_address=‘192.168.241.24’
wsrep_sst_auth=syncuser:‘QianFeng@123’
wsrep_sst_method=rsync

[client]
port = 3306
socket =/usr/local/mysql/data/mysql.sock
default-character-set = utf8
EOF

systemctl restart mysqld

ss- ntpl

当所有节点的mysqld都停止后或者服务器断电了需要重启galera集群，想要重启集群：

• 需要先把开机自启的myqld停止掉，保证当前系统没有mysqld进程，服务器重启后会有mysqld进程，有4567端口，但是没有3306端口，因为开机自启的mysqld启动不成功,杀死4567端口对应的进程
  ps aux | grep mysqld | grep -v grep | awk ‘{print $2}’ | xargs kill
• 再将galera.cache、grastate.dat**两个文件删除
  rm -f /usr/local/mysql/data/{galera.cache,grastate.dat}
• 再将galera1中的/etc/my.cnf配置文件中的`wsrep_cluster_address改成wsrep_cluster_address=‘gcomm://’
• 依次启动galera1、galera2、galera3的mysqld服务即可使集群恢复正常
• 最后所有节点的galera服务都正常后，再将`wsrep_cluster_address改成wsrep_cluster_address=‘gcomm://192.168.241.23,192.168.241.24,192.168.241.25’，最后重启mysqld