目录
一、使用一键安装脚本，在两台机器上分别部署好二进制的MySQL，并设置好my.cnf的内容（一键安装脚本中包含了）
二、在master服务器上开启bin_log并配置server_id,在从服务器上配置server_id
三、在master上创建给slave过来复制二进制日志的用户并授权
四、在master上做一个全备，导入数据到slave上，保持master和slave上的数据一致
1.先导出master所有数据库数据 导出为alldb.sql
2.slave上的databases（以下四个数据库是自带的）
六、在master和slave上都关闭防火墙和selinux
七、在slave服务器上启动slave服务，查看IO线程和SQL线程是否成功启动
九、为主从复制集群做高可用，进行Keepalived的双VIP配置
1.在router3(master)和router(backup)都安装 keepalived；
一、使用一键安装脚本，在两台机器上分别部署好二进制的MySQL，并设置好my.cnf的内容（一键安装脚本中包含了）
one_key_install.sh
#!/bin/bash
#解决软件的依赖关系
yum install cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs -y
#解压mysql二进制安装包
tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
#移动mysql解压后的文件到/usr/local下改名叫mysql
mv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd mysql
#mysql这个用户的shell 是/bin/false 属于mysql组
useradd -r -g mysql -s /bin/false mysql
#关闭firewalld防火墙服务，并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config
#新建存放数据的目录
mkdir /data/mysql -p
#修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户可以对这个文件夹进行读写了
chown mysql:mysql /data/mysql/
#只是允许mysql这个用户和mysql组可以访问，其他人都不能访问
chmod 750 /data/mysql/
#进入/usr/local/mysql/bin目录
cd /usr/local/mysql/bin/
#初始化mysql
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt
#让mysql支持ssl方式登录的设置
./mysql_ssl_rsa_setup --datadir=/data/mysql/
#获得临时密码
tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')
#$NF表示最后一个字段
# abc=$(命令) 优先执行命令，然后将结果赋值给abc
# 修改PATH变量，加入mysql bin目录的路径
#临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
#重新启动linux系统后也生效，永久修改
echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc
#复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld
#修改/etc/init.d/mysqld脚本文件里的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld
#生成/etc/my.cnf配置文件
cat >/etc/my.cnf <<EOF
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m mysql>
EOF
#修改内核的open file的数量
ulimit -n 1000000
#设置开机启动的时候也配置生效
echo "ulimit -n 1000000" >>/etc/rc.local
chmod +x /etc/rc.d/rc.local
#启动mysqld进程
service mysqld start
#将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on
#初次修改密码需要使用--connect-expired-password 选项
#-e 后面接的表示是在mysql里需要执行命令 execute 执行
#set password='123456'; 修改root用户的密码为123456
mysql -uroot -p$tem_passwd --connect-expired-password -e "set password='123456';"
#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
mysql -uroot -p'123456' -e "show databases;"
二、在master服务器上开启bin_log并配置server_id,在从服务器上配置server_id
1.开启bing_log
查看是否开启
root@(none) 10:11 mysql>show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.00 sec)
其中log_bin 显示OFF，表示未开启，需要手动开启
查找linux系统上的mysql可执行程序所在目录
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql我这里显示是在/usr/local/mysql/bin/mysql目录下
[root@localhost mysql]# /usr/local/mysql/bin/mysql --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf根据刚刚查找到的mysql可执行程序目录，查找mysql配置文件所在目录，查找后会输出多个目录，mysql按照顺序中这些文件中读取配置，如果上一个配置文件不存在则会去读取下一个，依次类推。
尝试查看第一个配置文件/etc/my.cnf的内容，刚刚使用一键安装脚本安装的时候创建了，因此就在这个文件中设置log_bin，设置之前要先查看MySQL版本（我这里是5.7.37）
查看数据库版本
root@(none) 10:20 mysql>select version();
+-----------+
| version() |
+-----------+
| 5.7.37 |
+-----------+
1 row in set (0.00 sec)
2.配置binlog参数并配置server_id
vim /etc/my.cnf在[mysqld]下添加
#开启并指定二进制日志前缀
log_bin=mysql-bin
#唯一id
server_id=11234
#二进制日志保存时间
expire_logs_days=7
binlog_format=ROW修改之后的文件
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#开启并指定二进制日志前缀
log_bin=mysql-bin
#唯一id
server_id=11234
#二进制日志保存时间
expire_logs_days=7
binlog_format=ROW
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
mysql8.0开启binlog的配置与5.7略有区别，这里不做详细讲解。
3.重启mysql
service mysqld restart
执行后进入mysql查看配置是否生效
root@(none) 10:25 mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql-bin |
| log_bin_index | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)
log_bin 显示为ON，表示开启成功
master配置完毕后，接下来就要配置slave的server_id了
这里修改一下my.cnf即可，slave的my.cnf配置如下
[root@localhost mysql]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
server_id = 11235
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
三、在master上创建给slave过来复制二进制日志的用户并授权
这里直接使用grant授权同时创建，grant授权如果没有这个用户的话会进行创建用户。
root@(none) 10:44 mysql>grant replication slave on *.* to rep1 identified by 'rep123456';
Query OK, 0 rows affected, 1 warning (0.09 sec)四、在master上做一个全备，导入数据到slave上，保持master和slave上的数据一致
1.先导出master所有数据库数据 导出为alldb.sql
mysqldump -u root -p123456 --all-databases > alldb.sql
[root@localhost mysql]# mysqldump -u root -p123456 --all-databases > alldb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# ls
alldb.sql include man support-files
bin lib README
docs LICENSE share
这里有报警，不影响，只是系统提醒我们直接把密码输入到命令行不安全，我这里只是做实验就直接输入了，在工作中就不要把密码直接输入到命令行中了。
2.将导出的数据拷贝到slave，这里使用的scp命令
scp alldb.sql root@192.168.174.139:/usr/local/mysql
命令解释：将当前目录下面的alldb.sql 拷贝到192.168.174.139服务器的/usr/local/mysql目录下使用root用户登陆（要知道对应密码才可以拷贝）
[root@localhost mysql]# scp alldb.sql root@192.168.174.139:/usr/local/mysql
The authenticity of host '192.168.174.139 (192.168.174.139)' can't be established.
ECDSA key fingerprint is SHA256:uEg1xjL7rZlunaIWRaVYq7C5VoKsjcbVueZpIx0uGQ8.
ECDSA key fingerprint is MD5:1f:fe:8a:80:90:eb:22:64:ea:76:a8:fe:a8:73:1f:88.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.174.139' (ECDSA) to the list of known hosts.
root@192.168.174.139's password:
alldb.sql 100% 862KB 102.3MB/s 00:00 slave 的/usr/local/mysql目录下
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# ls
alldb.sql docs lib man share
bin include LICENSE README support-files
3.将alldb.sql导入slave使用命令
mysql -u root -p < alldb.sql
[root@localhost mysql]# mysql -u root -p < alldb.sql
Enter password:
[root@localhost mysql]# mysql -uroot -p123456
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 6
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.
root@(none) 11:52 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| log |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
这里全备就做好了，然后再接着执行上面未全备的操作就可以了（注意因为我这里还做了一些操作，所以位置号发生了改变，slave到master拉取二进制日志一定要先show master status;看一下位置号）
五、在slave上配置去master上拉取二进制日志文件
为什么要先做主备，因为如果我们的master不是全新的机器，不先做全备那么slave就只会同步你做主从复制之后所做的操作
如果已经做了全备，那就只需要做第3步
不先做主备
1.master原有databases
root@(none) 10:55 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| log |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
2.slave上的databases（以下四个数据库是自带的）
root@(none) 11:14 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
3.从机上配置需要复制的主机
change master to master_host='192.168.174.137',master_port=3306,master_user='rep1',master_password='rep123456',master_log_file='mysql-bin.000001',master_log_pos=437;
Query OK, 0 rows affected, 2 warnings (0.06 sec)六、在master和slave上都关闭防火墙和selinux
在master和slave都要执行
关闭防火墙
#临时关闭防火墙
[root@localhost mysql]# systemctl stop firewalld
#设置开机不启动防火前
[root@localhost mysql]# systemctl disable firewalld关闭selinux
临时关闭
setenforce 0
永久关闭
vim /etc/selinux/config 修改SELINUX=disabled
七、在slave服务器上启动slave服务，查看IO线程和SQL线程是否成功启动
首先进入mysql：mysql-uroot -p123456
开启slave
root@(none) 12:01 mysql>start slave;
Query OK, 0 rows affected (0.01 sec)
使用命令show slave \G查看状态,【\G是为了以键值的形式显示，好看一些】
看到Slave_IO_Running和Slave_SQL_Running是YES就表示主从复制成功了
八、验证主从复制效果
master上创建数据库（只是演示一下修改操作，其它修改操作也是可以的，这里为了直观一点就直接创建数据库了）
root@(none) 11:14 mysql>create database log1;
root@(none) 11:14 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| log |
| log1 |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
slave
root@(none) 11:14 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| log1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)可以看到，slave上只同步了新建的log1数据库，但是master上的test并没有出现在slave上
至此，我们的主从复制就做好了
九、为主从复制集群做高可用，进行Keepalived的双VIP配置
1.在router3(master)和router(backup)都安装 keepalived；
yum install keepalived -y2.修改配置文件；
vim /etc/keepalived/keepalived.conf
master
vrrp_instance VI_1 {
state SLAVE
interface ens33
virtual_router_id 52
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.174.112
}
}
#双VIP的第二组，和上一组不同，代表这个服务器在52这个组里面是SLAVE
vrrp_instance VI_2 {
state MASTER #指定主服务器为主节点
interface ens33 #指定虚拟IP的接口
virtual_router_id 51 #VRRP组名，两个节点的组名要一样，表示在同一VRRP组
priority 200 #优先级，1-255，数字越大优先级越高
advert_int 1 #组播信息发送间隔，两个节点必须一样
slave
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 52
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.174.112
}
}
vrrp_instance VI_2 {
state SLAVE
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
检测是否配置成功
输入ip a查看
master
slave
当其中有一台机器宕机之后
如果有以上效果就代表成功了
十、添加GTID，配置mysql-router、读写分离
添加GTID配置
master和slave都要添加如下图的两行配置
vim /etc/my.cnf
检查GTID是否已经开启
这里显示gtid_mode为ON就代表配置好了
slave连接到master
这里要修改成GTID
CHANGE MASTER TO
MASTER_HOST='master的IP',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_PORT=端口号,
# 1 代表采用GTID协议复制
# 0 代表采用老的binlog复制
MASTER_AUTO_POSITION = 1;配置mysql-router
下载
[root@Hardy ~]# cd /data/
[root@Hardy data]# wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@Hardy data]# tar -Jxvf mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@Hardy data]# ln -s /data/mysql-router-8.0.18-linux-glibc2.12-x86_64 /usr/local/mysql-router配置
[root@Hardy data]# mkdir /etc/mysql-route/
[root@Hardy data]# cp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.conf修改后完整的内容，配置主可读可写，配置从只读
[DEFAULT]
# 日志存放目录
logging_folder = /data/log/mysql-route
# 插件存放目录
plugin_folder = /usr/local/mysql-router/lib/mysqlrouter
# 配置文件存放目录
config_folder = /etc/mysql-route
# 运行目录
runtime_folder = /var/run
#
[logger]
# 日志运行级别
level = debug
#
#主节点故障转移配置
[routing:basic_failover]
# 写节点地址
bind_address=192.168.174.160
# 写节点端口
bind_port = 7001
# 模式，读写
mode = read-write
# 主节点地址：默认情况下第一台主数据库为写主库，当第一台主数据库DOWN机后，第二台数据库被提升为主库
destinations = 192.168.174.111:3306,192.168.174.112:3306
# 从节点负载均衡配置
[routing:balancing]
# 绑定的IP地址
bind_address=192.168.174.160
# 监听的端口
bind_port = 7002
# 连接超时时间
connect_timeout = 3
# 最大连接数
max_connections = 1024
# 后端服务器地址
destinations = 192.168.174.112:3306
# 模式:读还是写
mode = read-only
[keepalive]
interval = 60
创建目录
mkdir -p /data/log/mysql-route
chown root:root /data/log/mysql-route/启动
/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &查看日志
tail -f /data/log/mysql-route/mysqlrouter.log
2022-08-14 16:13:37 main DEBUG [7f58512e5780] Starting all plugins.
2022-08-14 16:13:37 main DEBUG [7f584dda7700] plugin 'keepalive:' starting
2022-08-14 16:13:37 keepalive INFO [7f584dda7700] keepalive started with interval 60
2022-08-14 16:13:37 keepalive INFO [7f584dda7700] keepalive
2022-08-14 16:13:37 main DEBUG [7f58512e5780] plugin 'logger:' doesn't implement start()
2022-08-14 16:13:37 main DEBUG [7f584d5a6700] plugin 'routing:balancing' starting
2022-08-14 16:13:37 routing INFO [7f584d5a6700] [routing:balancing] started: listening on 192.168.174.160:7002, routing strategy = round-robin
登录验证
这里要注意，不要用root登陆，因为root默认是不允许远程登陆的。
[root@localhost bin]# mysql -h 192.168.174.160 -uroot -p'123456' -P7002
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.174.160' (using password: YES)
[root@localhost bin]# mysql -h 192.168.174.160 -ua -p'123456' -P7001
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 8
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.
a@(none) 17:12 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| log |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
a@(none) 17:22 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.174.111
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 586
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 799
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 586
Relay_Log_Space: 1010
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11234
Master_UUID: 7d81324d-fe5f-11ec-b5b0-000c29993ee7
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7d81324d-fe5f-11ec-b5b0-000c29993ee7:1-2
Executed_Gtid_Set: 7d81324d-fe5f-11ec-b5b0-000c29993ee7:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
参考：
扫一扫
3438
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
