MySQL安装-多主复制（6）

环境

192.168.1.81 master1

192.168.1.82 master2

Centos7.4

下载yum安装包

登录mysql网站下载yum源包




列出rpm包中文件

rpm -qpl mysql57-community-release-el7-11.noarch.rpm

rpm -ivh mysql57-community-release-el7-11.noarch.rpm







安装MySQL

查看已经安装的mysql




安装

yum -y install mysql-client mysql-server




启动mysql

systemctl enable mysqld.service

systemctl start mysqld.service

systemctl status mysqld.service




初始化MySQL

查看密码

密码存放位置/var/log/mysqld.log

grep ‘password‘ /var/log/mysqld.log |head -n 1




设置配置文件

配置文件位置/etc/my.cnf

Master1 配置

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

server-id=1
log-bin=mysql-bin

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重新启动数据库
systemctl restart mysqld
systemctl status mysqld

建立同步账号
mysql> grant replication slave on . to tongbu@‘%‘ identified by ‘P@ssw0rd‘;
mysql> flush privileges;

锁住数据库并查看bin-log状态
mysql> flush tables with read lock;
mysql> show master status\G;

设置第二台master
Master2配置
[root@master2 ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

server-id=2
log-bin=mysql-bin

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重新启动数据库
systemctl restart mysqld
systemctl status mysqld

执行change master
mysql> change master to master_host=‘192.168.1.81‘,master_user=‘tongbu‘,master_password=‘P@ssw0rd‘,master_log_file=‘mysql-bin.000001‘,MASTER_LOG_POS=591;

启动slave服务，并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

查看操作清单
mysql> show processlist\G;

查看2台master之前的运作
netstat -natp |grep -i established.mysql

解析master2地址
此步骤要和dns做反向解析
yum -y install bind-utils
host 192.168.1.82

在master2上建立同步账号
mysql> grant replication slave on .* to tongbu@‘%‘ identified by ‘P@ssw0rd‘;
mysql> flush privileges;

执行查看语句
必须在本地建立应答脚本 .my.cnf
[root@master1 ~]# vi .my.cnf[client]
user=root
password=P@ssw0rd
br/>[client]
user=root
password=P@ssw0rd

mysql -e “show processlist\G;”

数据库解锁
mysql -e “unlock tables”

执行change master，实现多主复制
在master上执行show master status
在master2上执行show master status 查看bin-log
mysql> show master status;

在master1上执行change master
mysql> change master to master_host=‘192.168.1.82‘,master_user=‘tongbu‘,master_password=‘P@ssw0rd‘,master_log_file=‘mysql-bin.000001‘,MASTER_LOG_POS=154;

在master1上开启slave服务，并查看状态
mysql>start slave;
mysql> show slave status\G;

在master1上查看操作清单
mysql> show processlist;

[client]
user=root
password=P@ssw0rd