Centos7.5 MySQL双主复制

安装mysql

mysql仓库：https://repo.mysql.com/

获取rpm包

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

安装rpm包

sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

安装mysql服务

sudo yum install mysql-server

登陆mysql更改密码

mysql -u root -p

报错

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

更改用户组权限，问题解决

chown -R root /var/lib/mysql/

登入更改用户密码

mysql -u root -p

默认密码应该是root，切换到mysql数据库，然后更改密码

use mysql;
update user set password=PASSWORD("your password") where user='root';

远程访问赋权

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "your password";

master1的基本配置

修改my.cnf

vim /etc/my.cnf

在[mysqld]结点下添加以下内容，而不是[mysqld_safe]结点

server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log 
log_bin_index       = /var/log/mysql/mysql-bin.log.index 
relay_log           = /var/log/mysql/mysql-relay-bin 
relay_log_index     = /var/log/mysql/mysql-relay-bin.index 
expire_logs_days    = 10 
max_binlog_size     = 100M 
log_slave_updates   = 1

另外，移除这一行

bind-address = 127.0.0.1

接着添加下面这三行

# 递增步长
auto_increment_increment = 1
# 起始偏移
auto_increment_offset = 1
# 用0.0.0.0代替任何ip
bind-address = 0.0.0.0

然后重启mysql

service mysqld restart

重启mysql卡住 

[root@VM-0-12-centos log]# service mysqld restart;
Redirecting to /bin/systemctl restart mysqld.service

查看一下mysql日志

cat /var/log/mysqld.log 

翻到启动mysql的时间点查看

可以看到指定的文件 /var/log/mysql/mysql-bin.log.index 找不到，这个文件有在my.cnf中配置，盲猜权限问题

查看一下用户组和用户

cut -d : -f 1 /etc/group
cut -d : -f 1 /etc/passwd

能看到mysql组和mysql用户，更改一下创建配置文件夹饼更改的用户所有者

mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql

然后重启mysql服务，启动成功，然后查看一下master的status

创建用户

GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'ip' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'ip'   IDENTIFIED BY 'agent_password';
GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'ip' IDENTIFIED BY 'replication_password';

数据库之间的数据同步

打开一个终端登录mysql，然后上锁确保数据库不会被修改

FLUSH TABLES WITH READ LOCK;

打开另一个终端，查看master status，记录下file和position

导出当前数据库的备份

mysqldump -u root -p --all-databases > /tmp/database-backup.sql

把sql文件导入到服务器

scp /tmp/database-backup.sql <user>@ip:/tmp

在其他服务器上到人文件

mysql -u root -p < /tmp/database-backup.sql

登陆mysql然后刷新

FLUSH PRIVILEGES;

配置主从

继续在其他服务器上登陆mysql，执行下面指令

CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

这里的master_log_file和master_log_pos都是上面show master status记录下来值

修改完后，在mysql终端启动slave

START SLAVE;

参考脚本

安装 MySQL

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum -y  install mysql-server
chown -R root /var/lib/mysql/
chmod -R 777 /var/lib/mysql/
if [ ! -d "/var/log/mysql" ]; then
    mkdir /var/log/mysql
fi
chown mysql.mysql /var/log/mysql
myconf=$(cat<< EOF
skip-grant-tables\nserver_id           = $1\nlog_bin             = \/var\/log\/mysql\/mysql\-bin.log\nlog_bin_index       = \/var\/log\/mysql\/mysql\-bin.log.index\nrelay_log           = \/var\/log\/mysql\/mysql\-relay\-bin\nrelay_log_index     = \/var\/log\/mysql\/mysql\-relay\-bin.index\nexpire_logs_days    = 10\nmax_binlog_size     = 100M\nlog_slave_updates   = 1\nauto_increment_increment = 1\nauto_increment_offset = 1\nbind\-address = 0.0.0.0
EOF
)
echo $myconf
sed -i "/\[mysqld\]/a$myconf" /etc/my.cnf
service mysqld restart
mysql -e"use mysql;update user set password=PASSWORD(\"密码\") where user='root';"
sed -i '/skip-grant-tables/d' /etc/my.cnf
service mysqld restart
mysql -uroot -p密码 -e"GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码';GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY '密码';GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY '密码';GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY '密码';flush privileges;"

配置主从

#查看master表
master_status=`mysql -h $1 -uroot -p密码 -e "show master status;"`
#打印master表
echo "$master_status"
file=`echo "$master_status" | grep "bin" | awk '{print $1}'`
echo "$file"
pos=`echo "$master_status" | grep "bin" | awk '{print $2}'`
echo "$pos"
mysql -h $2 -uroot -pReborn2020 -e "stop slave;"
mysql -h $2 -uroot -pReborn2020 -e "change master to master_host='$1',master_user='replication',master_password='Reborn2020',master_log_file='$file',master_log_pos=$pos;"
mysql -h $2 -uroot -pReborn2020 -e "start slave;"

导入表

mysql -uroot -p密码 < /tmp/database-backup.sql
mysql -uroot -p密码 -e "flush privileges;"

最后效果，双主中任意一个添加、修改数据，其他三台机器都会相应改动