发布日期 2016/07/14
MYSQL集群 多主服务器 部署文档 [CentOS7]
部署文档 实现mysql集群,可以往集群中的任意一台服务器写入 读取
所有集群自动同步.
约定
DB1 192.168.207.160
DB2 192.168.163.238
DB3 其他可以追加
更改hostname
|
1 2 |
hostnamectl set-hostname db1.servs.net --static hostnamectl set-hostname db2.servs.net --static |
设置解析服务器
|
1 2 3 4 |
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
|
1 |
date +%s | sha256sum | base64 | head -c 32 ; echo |
关闭SeLinux
|
1 |
setenforce 0 |
防火墙端口开放
|
1 2 3 |
firewall-cmd --add-service=mysql --permanent firewall-cmd --add-port={3306/tcp,4567/tcp,4568/tcp,4444/tcp} --permanent firewall-cmd --reload |
也可以直接关闭
|
1 2 |
systemctl stop iptables systemctl stop firewalld |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
加大系统文件系统限制 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 |
更新系统和安装依赖包
|
1 2 3 4 5 6 |
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软件 包括集群
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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和其他的 都不需要
|
1 2 3 4 5 6 |
mysql DELETE FROM mysql.user WHERE user=''; GRANT USAGE ON *.* to sync@'%' IDENTIFIED BY '同步密码'; GRANT ALL PRIVILEGES on *.* to sync@'%'; FLUSH PRIVILEGES; exit |
DB1 配置文件
============================
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 配置文件
============================
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
|
1 2 |
/etc/init.d/mysql stop /etc/init.d/mysql start --wsrep-new-cluster |
其次 启动DB2 和其他的
|
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个启动都没错误的话 测试测试
|
1 2 3 4 5 6 7 |
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 |
读写测试
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
#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了
下一步就是优化了
|
1 2 3 4 5 |
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 |