MYSQL集群多主服务器部署文档 [CentOS7]

发布日期 2016/07/14

MYSQL集群多主服务器部署文档 [CentOS7]
部署文档实现mysql集群,可以往集群中的任意一台服务器写入读取
所有集群自动同步.

约定

DB1 192.168.207.160
DB2 192.168.163.238
DB3 其他可以追加

更改hostname

hostnamectl set-hostname db1.servs.net --static
hostnamectl set-hostname db2.servs.net --static

1 2	hostnamectl set-hostname db1.servs.net --static hostnamectl set-hostname db2.servs.net --static

设置解析服务器

cat >/etc/resolv.conf <<EOF
nameserver 8.8.8.8
nameserver 8.8.4.4
EOF

cat >/etc/resolv.conf <<EOF

nameserver 8.8.8.8

nameserver 8.8.4.4

EOF

注意确认网络双向是通的…… 否则会异常
有些跨区域的内网,或者网络配置不当 ARP等问题
所以先测试好 A可以ping B B 可以Ping A

生成密码用于ROOT和MYSQL

date +%s | sha256sum | base64 | head -c 32 ; echo

1	date +%s \| sha256sum \| base64 \| head -c 32 ; echo

关闭SeLinux

setenforce 0

1	setenforce 0

防火墙端口开放

firewall-cmd --add-service=mysql --permanent 
firewall-cmd --add-port={3306/tcp,4567/tcp,4568/tcp,4444/tcp} --permanent 
firewall-cmd --reload

firewall-cmd --add-service=mysql --permanent

firewall-cmd --add-port={3306/tcp,4567/tcp,4568/tcp,4444/tcp} --permanent

firewall-cmd --reload

也可以直接关闭

systemctl stop iptables
systemctl stop firewalld

1 2	systemctl stop iptables systemctl stop firewalld

加大系统文件系统限制
ulimit -n
echo "* soft nofile 65535
* hard nofile 65535
">>/etc/security/limits.conf
cat /etc/security/limits.conf
echo "ulimit -SHn 65535">>/etc/rc.local
echo "ulimit -SHn 65535">>/etc/profile
ulimit -SHn 65535
sed -i -e 's/4096/unlimited/g' /etc/security/limits.d/20-nproc.conf
cat /etc/security/limits.d/20-nproc.conf
ulimit -n

加大系统文件系统限制

ulimit -n

echo "* soft nofile 65535

* hard nofile 65535

">>/etc/security/limits.conf

cat /etc/security/limits.conf

echo "ulimit -SHn 65535">>/etc/rc.local

echo "ulimit -SHn 65535">>/etc/profile

ulimit -SHn 65535

sed -i -e 's/4096/unlimited/g' /etc/security/limits.d/20-nproc.conf

cat /etc/security/limits.d/20-nproc.conf

ulimit -n

更新系统和安装依赖包

yum update -y
yum clean all
yum -y install nload nethogs iftop mtr whois net-tools traceroute net-tools \
wget unzip gd gd-devel gcc gcc-c++ lua-devel autoconf automake make \
zlib zlib-devel openssl openssl-devel pcre pcre-devel socat
yum erase *mysql*

yum update -y

yum clean all

yum -y install nload nethogs iftop mtr whois net-tools traceroute net-tools \

wget unzip gd gd-devel gcc gcc-c++ lua-devel autoconf automake make \

zlib zlib-devel openssl openssl-devel pcre pcre-devel socat

yum erase *mysql*

安装MYSQL软件包括集群

cat >/etc/yum.repos.d/MariaDB.repo <<EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
yum -y install MariaDB-Galera-server MariaDB-client galera rsync socat
/etc/init.d/mysql start
mysql_secure_installation
/etc/init.d/mysql stop
cat >/root/.my.cnf <<EOF
[client]
user=root
password=mysql的密码
EOF

cat >/etc/yum.repos.d/MariaDB.repo <<EOF

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.0/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

EOF

yum -y install MariaDB-Galera-server MariaDB-client galera rsync socat

/etc/init.d/mysql start

mysql_secure_installation

/etc/init.d/mysql stop

cat >/root/.my.cnf <<EOF

[client]

user=root

password=mysql的密码

EOF

DB1 主节点需要这样操作 DB2和其他的都不需要

mysql
DELETE FROM mysql.user WHERE user='';
GRANT USAGE ON *.* to sync@'%' IDENTIFIED BY '同步密码';
GRANT ALL PRIVILEGES on *.* to sync@'%';
FLUSH PRIVILEGES;
exit

mysql

DELETE FROM mysql.user WHERE user='';

GRANT USAGE ON *.* to sync@'%' IDENTIFIED BY '同步密码';

GRANT ALL PRIVILEGES on *.* to sync@'%';

FLUSH PRIVILEGES;

exit

DB1 配置文件
============================

cat >/etc/my.cnf.d/server.cnf <<EOF
[galera]
wsrep_on=ON
wsrep_cluster_name='cluster1'
wsrep_node_name='db1'
wsrep_node_address='192.168.207.160'
wsrep_cluster_address=gcomm://192.168.207.160,192.168.163.238
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_slave_threads=4
query_cache_size=0
query_cache_type=0
binlog_format=ROW
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
wsrep_replicate_myisam=1
wsrep_sst_method=rsync
wsrep_sst_auth=root:你在Node1设的密码
EOF

cat >/etc/my.cnf.d/server.cnf <<EOF

[galera]

wsrep_on=ON

wsrep_cluster_name='cluster1'

wsrep_node_name='db1'

wsrep_node_address='192.168.207.160'

wsrep_cluster_address=gcomm://192.168.207.160,192.168.163.238

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

#wsrep_slave_threads=4

query_cache_size=0

query_cache_type=0

binlog_format=ROW

bind-address=0.0.0.0

datadir=/var/lib/mysql

innodb_log_file_size=100M

innodb_file_per_table

default_storage_engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

innodb_doublewrite=1

innodb_flush_log_at_trx_commit=2

wsrep_replicate_myisam=1

wsrep_sst_method=rsync

wsrep_sst_auth=root:你在Node1设的密码

EOF

DB2和其他节点也这样
DB2 配置文件
============================

cat >/etc/my.cnf.d/server.cnf <<EOF
[galera]
wsrep_on=ON
wsrep_cluster_name='cluster1'
wsrep_node_name='db2'
wsrep_node_address='192.168.163.238'
wsrep_cluster_address=gcomm://192.168.207.160,192.168.163.238
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_slave_threads=4
query_cache_size=0
query_cache_type=0
binlog_format=ROW
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
innodb_flush_log_at_trx_commit=2
wsrep_replicate_myisam=1
wsrep_sst_method=rsync
wsrep_sst_auth=root:你在Node1设的密码
EOF

cat >/etc/my.cnf.d/server.cnf <<EOF

[galera]

wsrep_on=ON

wsrep_cluster_name='cluster1'

wsrep_node_name='db2'

wsrep_node_address='192.168.163.238'

wsrep_cluster_address=gcomm://192.168.207.160,192.168.163.238

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

#wsrep_slave_threads=4

query_cache_size=0

query_cache_type=0

binlog_format=ROW

bind-address=0.0.0.0

datadir=/var/lib/mysql

innodb_log_file_size=100M

innodb_file_per_table

default_storage_engine=innodb

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

innodb_doublewrite=1

innodb_flush_log_at_trx_commit=2

wsrep_replicate_myisam=1

wsrep_sst_method=rsync

wsrep_sst_auth=root:你在Node1设的密码

EOF

MYSQL启动命令和对应的启动顺序
首先启动DB1

/etc/init.d/mysql stop
/etc/init.d/mysql start --wsrep-new-cluster

1 2	/etc/init.d/mysql stop /etc/init.d/mysql start --wsrep-new-cluster

其次启动DB2 和其他的

/etc/init.d/mysql start

1	/etc/init.d/mysql start

如果返回
Starting MySQL…SST in progress, setting sleep higher.. SUCCESS!
代表启动成功

如果有错误查看日志
db1
cat /var/lib/mysql/db2.servs.net.err
db2
tail -f /var/lib/mysql/db1.servs.net.err

2个启动都没错误的话测试测试

mysql
show status like 'wsrep%';
显示结果 主要关注这几个参数
wsrep_local_state_comment | Synced         <-- 集群是不是已同步了?
wsrep_incoming_addresses     | 192.168.207.160:3306,192.168.163.238:3306 <-- DB1到DB2 或者DB2到DB1
wsrep_cluster_size        | 2                              <-- 集群数量 比如1
wsrep_ready               | ON                               <-- 读取OK

mysql

show status like 'wsrep%';

显示结果主要关注这几个参数

wsrep_local_state_comment | Synced <-- 集群是不是已同步了?

wsrep_incoming_addresses | 192.168.207.160:3306,192.168.163.238:3306 <-- DB1到DB2 或者DB2到DB1

wsrep_cluster_size | 2 <-- 集群数量比如1

wsrep_ready | ON <-- 读取OK

读写测试

#DB1运行
CREATE DATABASE cnlabs_db;
CREATE TABLE cnlabs_db.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));
INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES ("db1", "1.1.1.1");
SELECT * FROM cnlabs_db.mycluster;

MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;
+----+------+-----------+
| id | name | ipaddress |
+----+------+-----------+
|  3 | db1  | 1.1.1.1   |
+----+------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;
+----+------+-----------+
| id | name | ipaddress |
+----+------+-----------+
|  3 | db1  | 1.1.1.1   |
|  6 | db2  | 2.2.2.2   |
+----+------+-----------+
2 rows in set (0.00 sec)


#DB2运行
SELECT * FROM cnlabs_db.mycluster;
INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES ("db2", "2.2.2.2");

#DB1运行

CREATE DATABASE cnlabs_db;

CREATE TABLE cnlabs_db.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), ipaddress VARCHAR(20), PRIMARY KEY(id));

INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES ("db1", "1.1.1.1");

SELECT * FROM cnlabs_db.mycluster;

MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;

+----+------+-----------+

| id | name | ipaddress |

+----+------+-----------+

| 3 | db1 | 1.1.1.1 |

+----+------+-----------+

1 row in set (0.00 sec)

MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;

+----+------+-----------+

| id | name | ipaddress |

+----+------+-----------+

| 3 | db1 | 1.1.1.1 |

| 6 | db2 | 2.2.2.2 |

+----+------+-----------+

2 rows in set (0.00 sec)

#DB2运行

SELECT * FROM cnlabs_db.mycluster;

INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES ("db2", "2.2.2.2");

MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;
+—-+——+———–+
| id | name | ipaddress |
+—-+——+———–+
| 3 | db1 | 1.1.1.1 |
+—-+——+———–+
1 row in set (0.00 sec)

MariaDB [(none)]> INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES (“db2”, “2.2.2.2”);
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM cnlabs_db.mycluster;
+—-+——+———–+
| id | name | ipaddress |
+—-+——+———–+
| 3 | db1 | 1.1.1.1 |
| 6 | db2 | 2.2.2.2 |
+—-+——+———–+
2 rows in set (0.00 sec)

在DB1插入的记录
INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES (“db1”, “1.1.1.1”);
在DB2可以查询到
SELECT * FROM cnlabs_db.mycluster;

在DB2插入的
INSERT INTO cnlabs_db.mycluster (name, ipaddress) VALUES (“db2”, “2.2.2.2”);
在DB可以查询到

代表全部OK了
下一步就是优化了

cd /opt
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
perl mysqltuner.pl

cd /opt

wget http://mysqltuner.pl/ -O mysqltuner.pl

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

perl mysqltuner.pl