一、环境说明:
本次准备了两台机器搭建mysql主从复制。只需要安装好操作系统,挂载系统镜像和上传所需的安装包和脚本即可。另外,脚本部分位置需要修改IP地址。
操作系统版本 | 数据库版本 | IP地址 | 主机名 |
centos7 | 8.0.33 | 192.168.59.249 | db1 |
centos7 | 8.0.33 | 192.168.59.250 | db2 |
网盘地址:
链接: https://pan.baidu.com/s/1PUcGP0qSBphCfxCsFFhFSg
提取码: pvdv
上传安装包和脚本至任意目录下,赋予执行权限,执行mysql_ms_install.sh即可。整个过程中只需要输入数据库的版本号即可。
[root@db1 ~]#sh -x mysql_ms_install.sh
二、安装过程部分截图:
三、脚本内容:
[root@db1 ~]#cat mysql_ms_install.sh
#!/bin/bash
echo "-----------------------------开始MYSQL主从搭建--------------------------------------"
start_time=$(date +%s)
#关闭Selinux
setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld
#配置yum源
cd /etc/yum.repos.d/
rm -rf ./*
cat >> /etc/yum.repos.d/centos.repo <<-EOF
[centos]
name=oracle
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF
cd
mount /dev/sr0 /mnt
yum clean all|wc -l
yum makecache
yum install expect* -y
yum install wget -y
#yum install gcc build-essential -y
#tar -xvf sshpass.tar.gz
#cd /root/sshpass-1.10
#./configure
#make
#make install
os_name=${node_name}
if [[ "$os_name" =~ ^localhost ]];then
echo "ERROR: hostname: $os_name is invalid,pls check"
exit -1
else
echo "hostname check passed"
hostnamectl set-hostname ${node_name}db1
fi
# 变量定义
groupadd mysql
useradd -r -g mysql mysql
# 定义变量
REMOTE_USER="root"
REMOTE_HOST="192.168.59.250"
PASSWORD="123123"
# 本地生成SSH密钥对(如果不存在)
if [ ! -f ~/.ssh/id_rsa ]; then
ssh-keygen -t rsa -b 2048 -N "" -f ~/.ssh/id_rsa
fi
# 使用Expect脚本将公钥复制到远程主机
/usr/bin/expect <<EOF
set timeout 10
# 启动ssh-copy-id命令
spawn ssh-copy-id $REMOTE_USER@$REMOTE_HOST
# 处理首次连接提示的 "yes/no" 和密码输入
expect {
"yes/no" { send "yes\r"; exp_continue }
"password:" { send "$PASSWORD\r" }
}
expect eof
EOF
# 验证是否成功
ssh $REMOTE_USER@$REMOTE_HOST "echo '免密登录设置成功'"
REMOTE_COMMANDS=$(cat << EOF
echo "hostname:"
os_name=${node_name}
if [[ "$os_name" =~ ^localhost ]];then
echo "ERROR: hostname: $os_name is invalid,pls check"
exit -1
else
echo " hostname check passed"
hostnamectl set-hostname ${node_name}db2
fi
EOF
)
ssh $REMOTE_USER@$REMOTE_HOST "$REMOTE_COMMANDS"
#添加主机名与IP对应记录
public_ip1=$(hostname -I| grep -o -e '[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}' |head -n 1)
node_name1=$(hostname)
sleep 3
public_ip2=$(ssh $REMOTE_USER@$REMOTE_HOST "hostname -I | grep -o -e '[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}' | head -n 1")
node_name2=$(ssh $REMOTE_USER@$REMOTE_HOST "hostname")
sleep 3
echo -e "${public_ip1} ${node_name1}\n${public_ip2} ${node_name2}" >> /etc/hosts
cat /etc/hosts
cd ~
echo "-----------------------------开始MYSQL MASTER安装--------------------------------------"
start_time=$(date +%s)
MYSQL_ROOT_PASSWORD="123456"
REPLICA_USER="repl"
REPLICA_PASSWORD="repl"
echo -e "\e[31m***************一键安装mysql任何版本数据库******************\e[0m"
echo -e "输入安装版本,如\e[31m8.0.27\e[0m"
read version
find / -name mysql | xargs rm -rf
port=$(netstat -antup|grep mysql|wc -l)
if [ $port != 0 ]
then echo "mysql进程存在,请先杀掉进程"
ps -ef |grep mysqld
exit 1
fi
echo "-----------------创建所需目录及用户并上传安装包----------------------------"
# 获取当前所在目录位置
current_dir=$(pwd)
echo "当前所在目录位置: $current_dir"
# 目标路径
target_dir="/opt"
# 检查目标路径是否存在,如果不存在则创建
if [ ! -d "$target_dir" ]; then
mkdir -p "$target_dir"
echo "已创建目录: $target_dir"
fi
# 移动当前目录下的所有文件到目标路径
mv $current_dir/* $target_dir
cp mysql_install.sh /opt
echo "已将当前目录下所有文件移动至 $target_dir"
mkdir -p /data/mysql
groupadd mysql
useradd -r -g mysql mysql
cd /opt/
tar -xvf mysql-$version-linux-glibc2.12-x86_64.tar.xz
mv mysql-$version-linux-glibc2.12-x86_64/ /usr/local/
cd /usr/local/
mv mysql-$version-linux-glibc2.12-x86_64/ mysql
chown -R mysql.mysql /usr/local/mysql/
echo "-----------------------------卸载原有的mysql组件--------------------------"
yum list installed | grep mariadb
yum -y remove mariadb* || true
chown mysql:mysql -R /data/mysql
touch /etc/my.cnf
chmod 644 /etc/my.cnf
MYSQL_ROOT_PASSWORD=123456
cat <<EOF >/etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
default-character-set = utf8mb4
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
log-error=/data/mysql/mysql.err
socket = /tmp/mysql.sock
pid-file = /data/mysql/mysql.pid
innodb_buffer_pool_size = 8G # 配置为内存的75%
innodb_buffer_pool_instances = 8 # 配置buffer_pool为8个
innodb_log_file_size = 2G # 调整日志文件大小
innodb_log_buffer_size = 128M # InnoDB用于写入磁盘日志文件的缓冲区大小
innodb_flush_log_at_trx_commit = 1 # 保证数据安全
innodb_file_per_table = 1 # 每张表独立表空间
innodb_io_capacity = 2000 # I/O操作数
innodb_read_io_threads = 4 # 读I/O线程
innodb_write_io_threads = 4 # 写I/O线程
innodb_flush_method = O_DIRECT # 防止双写
default_storage_engine = InnoDB # 存储引擎默认InnoDB
max_connections = 500 # 最大连接数
max_connect_errors = 1000 # 最大错误连接次数
skip_name_resolve = 1 # 只能用IP地址检查客户端的登录
server-id = 1 # 主库ID
gtid_mode = ON # 启用GTID
enforce-gtid-consistency = ON # 强制GTID一致性
log-bin = mysql-bin # 启用二进制日志
binlog_format = ROW # 二进制日志格式
binlog_row_image = FULL # 行级日志模式
master_info_repository = TABLE # 主库信息表存储
relay_log_info_repository = TABLE # 中继日志信息表存储
sync-binlog = 1 # 保证数据安全
log-slave-updates = 1 # 允许从库更新记录到binlog
binlog_expire_logs_seconds = 604800 # binlog保留时间
slave_parallel_workers = 4 # 从库并行复制线程数
slave_parallel_type = LOGICAL_CLOCK # 并行复制类型
table_open_cache = 4096 # 程打开的表的数量
table_definition_cache = 2048 # 可以存储在定义缓存中的表定义数量
tmp_table_size = 128M # 内部内存临时表大小
max_heap_table_size = 256M # 内部内存临时表的最大值
thread_cache_size = 128 # 连接使用缓存线程
open_files_limit = 65535 # mysqld进程能使用的最大文件描述(FD)符数量
EOF
echo "-----------------------------------初始化-----------------------------------"
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
path=$(grep 'basedir' /etc/profile|wc -l)
if [ $path != 0 ]
then
echo -e "\e[31m MYSQL_HOME路径存在\e[0m"
else
echo "export basedir=/usr/local/mysql/bin" >> /etc/profile
echo "export PATH=\$PATH:\$basedir" >> /etc/profile
source /etc/profile
fi
echo "---------------------------------启动MYSQL服务---------------------------------------"
service mysql start
echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib'>>/etc/profile
sleep 3
source /etc/profile
cat /data/mysql/mysql.err|grep password
chkconfig --add mysql
chkconfig mysql on
chkconfig --list mysql
echo "-----------------------------恭喜!MYSQL 主库安装成功--------------------------------------"
end_time=$(date +%s)
execution_time=$((end_time - start_time))
echo "脚本执行时间:${execution_time} 秒"
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.err`
mysqladmin -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD}
mysql -uroot -p123456 -e "update mysql.user set host ='%' where user ='root';"
mysql -uroot -p123456 -e "flush privileges;"
mysql -uroot -p123456 << EOF
exit
EOF
sleep 10
#EOF
#)
#ssh $REMOTE_USER@$REMOTE_HOST "$REMOTE_COMMANDS1"
echo "-----------------------------开始安装MYSQL SLAVE--------------------------------------"
source /etc/profile
scp /opt/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql_install.sh root@192.168.59.250:/opt
scp /opt/mysql_install.sh root@192.168.59.250:/opt/
ssh -tt -p 22 root@192.168.59.250 "source /opt/mysql_install.sh"
#REMOTE_USER="root"
ln -fs /usr/local/mysql/bin/mysql /usr/bin/
REMOTE_HOST="192.168.59.250"
#REMOTE_SCRIPT_PATH="/opt/mysql_install.sh"
#ssh 192.168.59.250 "source /opt/mysql_install.sh"
ssh =$REMOTE_HOST <<EOF
if [ $? -eq 0 ]; then
echo "MySQL 安装已成功完成."
else
echo "MySQL 安装失败,请检查日志或手动排查问题."
fi
# 添加其他命令,如清理临时文件或重启服务
# 例如: rm /tmp/temp_file
exit
EOF
sleep 10
/usr/bin/expect <<EOF
set timeout 60
# 启动SSH命令
spawn ssh root@192.168.59.249
# 处理首次连接提示的 "yes/no" 和密码输入
expect {
"Are you sure you want to continue connecting (yes/no)?" { send "yes\r"; exp_continue }
"password:" { send "123123\r"; exp_continue }
eof
}
EOF
sleep 10
source /etc/profile
ln -fs /usr/local/mysql/bin/mysql /usr/bin/
MASTER_IP=192.168.59.249
SLAVE_IP=192.168.59.250
# 配置主服务器
function configure_master {
REMOTE_USER="root"
REMOTE_HOST="192.168.59.249"
MYSQL_ROOT_PASSWORD="123456"
REPLICA_USER="repl"
REPLICA_PASSWORD="repl"
SSH_PASSWORD="123123"
/usr/bin/expect <<EOF
set timeout -1
set REMOTE_USER "root"
set REMOTE_HOST "192.168.59.249"
set MYSQL_ROOT_PASSWORD "123456"
set REPLICA_USER "repl1"
set REPLICA_PASSWORD "repl"
set SSH_PASSWORD "123123"
spawn mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h$REMOTE_HOST -e "DROP USER IF EXISTS '$REPLICA_USER'@'%'; CREATE USER '$REPLICA_USER'@'%' IDENTIFIED BY '$REPLICA_PASSWORD'; USE mysql; GRANT REPLICATION SLAVE ON *.* TO '$REPLICA_USER'@'%'; FLUSH PRIVILEGES; SHOW MASTER STATUS;"
set output ""
expect {
"*password:" {
send "$MYSQL_ROOT_PASSWORD\r"
exp_continue
}
eof {
append output $expect_out(buffer)
}
}
puts "Complete Output:\n$output\n"
set master_status [split $output "\n"]
puts "DEBUG: Master Status List:\n$master_status\n" ; # 添加调试信息,查看分割后的列表
set file ""
set position ""
foreach line $master_status {
if {[regexp {\| (\S+) \| (\d+) \|} $line -> file_temp position_temp]} {
set file $file_temp
set position $position_temp
break
}
}
puts "FILE: $file"
puts "POSITION: $position"
EOF
REMOTE_USER="root"
REMOTE_HOST="192.168.59.249"
MYSQL_ROOT_PASSWORD="123456"
OUTPUT_FILE="/opt/output.txt"
output=$(mysql -u"$REMOTE_USER" -p"$MYSQL_ROOT_PASSWORD" -h"$REMOTE_HOST" -e "SHOW MASTER STATUS\G")
echo "$output" > "$OUTPUT_FILE"
file=$(echo "$output" | grep "File:" | awk '{print $2}')
position=$(echo "$output" | grep "Position:" | awk '{print $2}')
echo "FILE: $file"
echo "POSITION: $position"
scp /opt/output.txt 192.168.59.250:/opt
file=$(cat "/opt/output.txt" | grep "File:" | awk '{print $2}')
position=$(cat "/opt/output.txt" | grep "Position:" | awk '{print $2}')
export FILE="$file"
export POSITION="$position"
}
# 配置从服务器
function configure_slave {
file=$(cat "/opt/output.txt" | grep "File:" | awk '{print $2}')
position=$(cat "/opt/output.txt" | grep "Position:" | awk '{print $2}')
export FILE="$file"
export POSITION="$position"
REMOTE_USER="root"
REMOTE_HOST="192.168.59.250"
MYSQL_ROOT_PASSWORD="123456"
REPLICA_USER="repl"
REPLICA_PASSWORD="repl"
SSH_PASSWORD="123123"
MASTER_HOST="192.168.59.249"
/usr/bin/expect <<EOF
set timeout -1
set REMOTE_USER "root"
set REMOTE_HOST "192.168.59.250"
set MASTER_IP "192.168.59.249"
set MYSQL_ROOT_PASSWORD "123456"
set REPLICA_USER "repl"
set REPLICA_PASSWORD "repl"
set SSH_PASSWORD "123123"
spawn mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h192.168.59.250 -e "CHANGE MASTER TO MASTER_HOST='192.168.59.249', MASTER_USER='$REPLICA_USER', MASTER_PASSWORD='$REPLICA_PASSWORD', MASTER_LOG_FILE='$FILE', MASTER_LOG_POS=$POSITION;CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;START SLAVE;"
expect {
"*password:" {
send "$MYSQL_ROOT_PASSWORD\r"
exp_continue
}
eof
}
#puts "\nSlave Status:\n$slave_output"
set show_slave_status_cmd "mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h$REMOTE_HOST -e \"SHOW SLAVE STATUS\\\\G\""
spawn sh -c $show_slave_status_cmd
expect {
"*password:" {
send "$MYSQL_ROOT_PASSWORD\r"
exp_continue
}
eof {
set slave_output $expect_out(buffer)
puts "SHOW SLAVE STATUS completed."
}
}
puts "\nSlave Status:\n$slave_output"
EOF
configure_master
configure_slave
此脚本为mysql从库安装脚本:
cat mysql_install.sh
#!/bin/bash
echo "-----------------------------开始MYSQL SLAVE安装--------------------------------------"
#关闭Selinux
setenforce 0
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld
#配置yum源
cd /etc/yum.repos.d/
rm -rf ./*
cat >> /etc/yum.repos.d/centos.repo <<-EOF
[centos]
name=oracle
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF
cd
mount /dev/sr0 /mnt
yum clean all|wc -l
yum makecache
yum install expect* telent* -y
start_time=$(date +%s)
echo -e "\e[31m***************一键安装mysql任何版本数据库******************\e[0m"
echo -e "输入安装版本,如\e[31m8.0.27\e[0m"
read version
find / -name mysql | xargs rm -rf
port=$(netstat -antup|grep mysql|wc -l)
if [ $port != 0 ]
then echo "mysql进程存在,请先杀掉进程"
ps -ef |grep mysqld
exit 1
fi
echo "-----------------创建所需目录及用户并上传安装包----------------------------"
# 获取当前所在目录位置
current_dir=$(pwd)
echo "当前所在目录位置: $current_dir"
# 目标路径
target_dir="/opt"
# 检查目标路径是否存在,如果不存在则创建
if [ ! -d "$target_dir" ]; then
mkdir -p "$target_dir"
echo "已创建目录: $target_dir"
fi
# 移动当前目录下的所有文件到目标路径
mv $current_dir/* $target_dir
echo "已将当前目录下所有文件移动至 $target_dir"
mkdir -p /data/mysql
groupadd mysql
useradd -r -g mysql mysql
cd /opt/
tar -xvf mysql-$version-linux-glibc2.12-x86_64.tar.xz
mv mysql-$version-linux-glibc2.12-x86_64/ /usr/local/
cd /usr/local/
mv mysql-$version-linux-glibc2.12-x86_64/ mysql
chown -R mysql.mysql /usr/local/mysql/
echo "-----------------------------卸载原有的mysql组件--------------------------"
yum list installed | grep mariadb
yum remove mariadb* -y || true
chown mysql:mysql -R /data/mysql
touch /etc/my.cnf
chmod 644 /etc/my.cnf
MYSQL_ROOT_PASSWORD=123456
cat <<EOF >/etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
default-character-set = utf8mb4
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
log-error=/data/mysql/mysql.err
socket = /tmp/mysql.sock
pid-file = /data/mysql/mysql.pid
# 性能优化
innodb_buffer_pool_size = 8G # 配置为内存的75%
innodb_buffer_pool_instances = 8 # 配置buffer_pool为8个
innodb_log_file_size = 2G # 调整日志文件大小
innodb_log_buffer_size = 128M # InnoDB用于写入磁盘日志文件的缓冲区大小
innodb_flush_log_at_trx_commit = 1 # 保证数据安全
innodb_file_per_table = 1 # 每张表独立表空间
innodb_io_capacity = 2000 # I/O操作数
innodb_read_io_threads = 4 # 读I/O线程
innodb_write_io_threads = 4 # 写I/O线程
innodb_flush_method = O_DIRECT # 防止双写
default_storage_engine = InnoDB # 存储引擎默认InnoDB
# 连接优化
max_connections = 500 # 最大连接数
max_connect_errors = 1000 # 最大错误连接次数
skip_name_resolve = 1 # 只能用IP地址检查客户端的登录
# 复制配置
server-id = 2 # 从库ID
gtid_mode = ON # 启用GTID
enforce-gtid-consistency = ON # 强制GTID一致性
log-bin = mysql-bin # 启用二进制日志
binlog_format = ROW # 二进制日志格式
binlog_row_image = FULL # 行级日志模式
master_info_repository = TABLE # 主库信息表存储
relay_log_info_repository = TABLE # 中继日志信息表存储
sync-binlog = 1 # 保证数据安全
log-slave-updates = 1 # 允许从库更新记录到binlog
binlog_expire_logs_seconds = 604800 # binlog保留时间
# 多线程复制
slave_parallel_workers = 4 # 从库并行复制线程数
slave_parallel_type = LOGICAL_CLOCK # 并行复制类型
# 缓存优化
table_open_cache = 4096 # 程打开的表的数量
table_definition_cache = 2048 # 可以存储在定义缓存中的表定义数量
tmp_table_size = 128M # 内部内存临时表大小
max_heap_table_size = 256M # 内部内存临时表的最大值
thread_cache_size = 128 # 连接使用缓存线程
open_files_limit = 65535 # mysqld进程能使用的最大文件描述(FD)符数量
EOF
echo "-----------------------------------初始化-----------------------------------"
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
path=$(grep 'basedir' /etc/profile|wc -l)
if [ $path != 0 ]
then
echo -e "\e[31m MYSQL_HOME路径存在\e[0m"
else
echo "export basedir=/usr/local/mysql/bin" >> /etc/profile
echo "export PATH=\$PATH:\$basedir" >> /etc/profile
source /etc/profile
fi
echo "---------------------------------启动MYSQL服务---------------------------------------"
service mysql start
echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib'>>/etc/profile
sleep 3
source /etc/profile
cat /data/mysql/mysql.err|grep password
chkconfig --add mysql
chkconfig mysql on
chkconfig --list mysql
ln -fs /usr/local/mysql/bin/mysql /usr/bin/
echo "-----------------------------恭喜!MYSQL 从库安装成功--------------------------------------"
end_time=$(date +%s)
execution_time=$((end_time - start_time))
echo "脚本执行时间:${execution_time} 秒"
MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.err`
mysqladmin -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD}
mysql -uroot -p123456 -e "update mysql.user set host ='%' where user ='root';"
mysql -uroot -p123456 -e "flush privileges;"
mysql -uroot -p123456 << EOF
exit
EOF
sleep 10
特别说明:此脚本为1.0版本,还有很多问题,希望大家多提意见,我们继续优化迭代。另外,要感谢国内知名MySQL数据库架构师李伟的鼎力支持,后续我们还会继续合作分享MySQL相关干货。