MySQL主从复制配置

---

CentOS 7 配置 阿里云 yum 源

sudo mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
sudo wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
sudo yum clean all
sudo yum makecache

---

静态IP设置

vim /etc/sysconfig/network-scripts/ifcfg-ens33
 
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
IPADDR=192.168.15.150
NETMASK=255.255.255.0
GATEWAY=192.168.15.2
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
UUID=2c2371f1-ef29-4514-a568-c4904bd11150
DEVICE=ens33
ONBOOT=true
DNS1=114.114.114.114
 
systemctl restart network

---

一键部署MySQL

cd /usr/local/src
wget http://soft.vpser.net/lnmp/lnmp1.6.tar.gz -cO lnmp1.6.tar.gz && tar zxf lnmp1.6.tar.gz && cd lnmp1.6 && ./install.sh db

# 开放服务器端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=3306/udp --permanent

# 配置MySQL远程登陆所允许的IP
mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.62-log Source distribution
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Database changed
mysql> update user set host = '%' where user = 'root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit
Bye

---

配置MySQL集群

# 分布部署2台服务器,阿里源,固定IP,MySQL
192.168.15.150
192.168.15.151

固定IP个性化配置并重启

IPADDR=192.168.15.150
UUID=2c2371f1-ef29-4514-a568-c4904bd11150

IPADDR=192.168.15.151
UUID=2c2371f1-ef29-4514-a568-c4904bd11151

systemctl restart network

MySQL个性化配置并重启

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin   #启用二进制日志
server-id=150		#服务器唯一ID,比如linux的ip地址最后三位为150,就可以取150

vim /usr/local/mysql/var/auto.cnf 

[auto]
server-uuid=206e96bf-7d4f-11ee-93ce-000c29fd9150

service mysql restart

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin   #启用二进制日志
server-id=151		#服务器唯一ID,比如linux的ip地址最后三位为151,就可以取151

vim /usr/local/mysql/var/auto.cnf 

[auto]
server-uuid=206e96bf-7d4f-11ee-93ce-000c29fd9151

service mysql restart

登录MySQL主库,查看主库状态

mysql -h 192.168.15.150 -u root -p

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

登录MySQL主库,创建新账户并授予相关权限

mysql -h 192.168.15.150 -u root -p

mysql> grant replication slave on *.* to 'sharding-jdbc'@'%' identified by 'sharding-jdbc';
mysql> FLUSH PRIVILEGES;

登录MySQL从库,配置连接的主库信息

mysql -h 192.168.15.151 -u root -p

mysql> change master to master_host='192.168.15.150',master_user='sharding-jdbc',master_password='sharding-jdbc',master_log_file='mysql-bin.000010',master_log_pos=154;
mysql> FLUSH PRIVILEGES;
mysql> start slave;

查看从库状态

show slave status\G;        #推荐使用
show slave status;            #使用这一行命令，结果复制到notepad++更方便阅读