MySQL

mysql主从复制原理

主从复制安装过程

服务器规划

• 一主二从。三台服务器

• 服务器版本
  openeuler 22.04 LTS SP4.
  看版本号: cat /etc/os-release

• mysql版本 : 5.7.44 版本，安装方式 yum 安装

安装步骤

，修改服务器名称。

hostnamectl set-hostname master
hostnamectl set-hostname slave1
hostnamectl set-hostname slave2

同步服务器的时间，时间同步

安装ntpdate 命令
yum -y install ntpdate
在欧拉系统里，yum已经被dnf命令所取代。

ntpdate ntp1.aliyun.com

安装mysql5.7

在欧拉里面，默认安装mysql8.0,而且，mysql5.7 软件的名字叫
mysql-communti-server,而不是mysql-server,后者会安装8.0

首先安装5.7的yum仓库

cat > /etc/yum.repos.d/mysql.repo <<'EOF' 
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
EOF

安装mysql5.7 服务

yum -y install mysql-community-server

配置服务器，按照不同角色运行。

设置master

cat <<EOF >>/etc/my.cnf
log-bin=master
server-id=1
EOF

设置slave

在slave1上

cat <<EOF >>/etc/my.cnf
server-id=2
EOF

在slave2上

cat <<EOF >>/etc/my.cnf
server-id=3
EOF

启动服务器并关闭防火墙

systemctl start mysqld
systemctl disable firewalld && systemctl stop firewalld

初始化密码

[root@master ~]# mpass=`cat /var/log/mysql.log |grep password | head -1 | awk '{print $NF}'`
cat: /var/log/mysql.log: No such file or directory
[root@master ~]# mpass=`cat /var/log/mysqld.log |grep password | head -1 | awk '{print $NF}'`
[root@master ~]# echo $mpass
HI;s%nsg(3ZL
[root@master ~]# mysql -uroot -p"$mpass"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> alter user root@localhost identified by '1qaz!QAZ';
Query OK, 0 rows affected (0.01 sec)

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

master机器设置复制日志的用户

只在master机器上操作,为了slave1,slave2能够访问到master并获取数据。

mysql> create user repl@'%' identified by '1qaz!QAZ';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave , replication client on *.* to repl@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

`

在slave机器上，设置master的信息

只在slave1,slave2上操作.

mysql> change master to master_host='192.168.1.160',master_user='repl',master_password='1qaz!QAZ';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.1.160
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

如果IO线程和SQL线程如果是2个YES，表示同步成功.

测试，在master机器上创建数据库，不能在slave写数据

mysql> create database frommaster charset uft8;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| frommaster         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

在从机查看，也会得到这个数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| frommaster         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

注意点

如果change master to写错了，应该要做的是

1. stop slave
2. reset slave ,清除和master之间的关系。
3. change master to …

读写分离

规划下
| IP | 角色 | 主机名称 | 集群角色 |
| ———— | ———— | ———— | —- |
| 192.168.1.160 | master | master | read,write|
| 192.168.1.20 | slave | slave1 | read,write|
| 192.168.1.29 | slave | slave2 | read |

首先安装Nginx

安装centos yum -y install epel-release
在欧拉下面直接运行

yum -y install nginx

配置文件修改，增加/etc/nginx/stream.d/

mkdir -p /etc/nginx/stream.d
并且要启用stream模块，欧拉nginx有这个模块，如果没有
使用yum安装
yum -y install nginx_mod_stream.x86_64

修改nginx.conf让/etc/nginx/stream.d/*conf生效

events {
    worker_connections 1024;
}
stream {
        include /etc/nginx/stream.d/*conf;
}
http {
...
}

然后stream.d里面增加如下文件，比如mysql-proxy.conf

[root@slave1 stream.d]# cat *
upstream write-servers {
    server 192.168.1.20:3306;
    server 192.168.1.160:3306;
}
upstream read-servers {
    server 192.168.1.160:3306;
    server 192.168.1.29:3306;
}
server {
    listen 3301; # readonly cluster
    proxy_pass read-servers;
}
server {
    listen 3302; # write cluster
    proxy_pass write-servers;
}

测试nginx配置并启动.

nginx -t
nginx -s reload

测试

mysql -uroot -ppassword -h’nginx_ip’ -P 3301/3302
-e “select @@hostname ;”
是否在服务器之间切换。

[root@slave1 stream.d]# mysql -urepl -p'1qaz!QAZ' -h192.168.1.20 -P 3302 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+
[root@slave1 stream.d]# mysql -urepl -p'1qaz!QAZ' -h192.168.1.20 -P 3302 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| master     |
+------------+
[root@slave1 stream.d]# mysql -urepl -p'1qaz!QAZ' -h192.168.1.20 -P 3302 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| slave1     |
+------------+
[root@slave1 stream.d]# mysql -urepl -p'1qaz!QAZ' -h192.168.1.20 -P 3302 -e "select @@hostname;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| master     |
+------------+