安装MySQL5.7
约 5945 字大约 20 分钟
安装MySQL5.7
单机版安装
Yum安装
下载Rpm
由于CentOS 的yum源中没有mysql，需要到mysql的官网下载yum repo配置文件。下载命令：
[root@localhost ~]# wget -i -c http://dev.mysql.com/get/~-community-release-el7-10.noarch.rpm
安装Repo
rpm -ivh ~-community-release-el7-10.noarch.rpm
执行完成后会在 /etc/yum.repos.d/ 目录下生成两个repo文件 mysql-community.repo mysql-community-source.repo
安装mysql
必须进入到 /etc/yum.repos.d/ 目录后再执行以下脚本, 然后就可以直接yum安装了。
[root@localhost ~]# yum install mysql-server
这步可能会花些时间，安装完成后就会覆盖掉之前的mariadb。
临时密码
获取安装时的临时密码（在第一次登录时就是用这个密码）：grep 'temporary password' /var/log/mysqld.log
[root@localhost yum.repos.d]# grep 'temporary password' /var/log/mysqld.log
2020-12-27T02:34:04.063826Z 1 [Note] A temporary password is generated for root@localhost: LXwH6NSf*kVh
解压安装
创建用户和组
#卸载系统自带的Mariadb和mysql
[root@hdp265dnsnfs ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@hdp265dnsnfs ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@hdp265dnsnfs ~]# rpm -qa | grep mysql
#检查mysql组和用户是否存在，如无创建
[root@hdp265dnsnfs ~]# cat /etc/group | grep mysql
[root@hdp265dnsnfs ~]# cat /etc/passwd | grep mysql
#创建mysql用户组
[root@hdp265dnsnfs ~]# groupadd mysql
#创建一个用户名为mysql的用户并加入mysql用户组
[root@hdp265dnsnfs ~]# useradd -g mysql mysql
#指定password 为111111
[root@hdp265dnsnfs ~]# passwd mysql
Changing password for user mysql.
New password:
BAD PASSWORD: The password is a palindrome
Retype new password:
passwd: all authentication tokens updated successfully.
# 安装到 /user/local 目录
[root@hdp265dnsnfs ~]#]# tar -zxvf mysql-5.7.26-linux-glibc2.5-x86_64.tar.gz -C /usr/local
#更改所属的组和用户
[root@hdp265dnsnfs ~]#]# mv /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.26-linux
[root@hdp265dnsnfs ~]#]# mkdir -p /usr/local/mysql-5.7.26-linux/data
[root@hdp265dnsnfs ~]#]# chown -R mysql:mysql /usr/local/mysql-5.7.26-linux
配置my.cnf
在etc下新建配置文件 my.cnf，并在该文件内添加以下配置
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/mysql-5.7.26-linux
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql-5.7.26-linux/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
安装和初始化
# basedir和datadir与 my.cnf中保持一致
[root@hdp265dnsnfs ~]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql-5.7.26-linux/ --datadir=/usr/local/mysql-5.7.26-linux/data/
2017-04-17 17:40:02 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2017-04-17 17:40:05 [WARNING] The bootstrap log isn't empty:
2017-04-17 17:40:05 [WARNING] 2017-04-17T09:40:02.728710Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
2017-04-17T09:40:02.729161Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-04-17T09:40:02.729167Z 0 [Warning] Changed limits: table_open_cache: 407 (requested 2000)
[root@hdp265dnsnfs ~]# cp ./support-files/mysql.server /etc/init.d/mysqld
[root@hdp265dnsnfs ~]# chown 777 /etc/my.cnf
[root@hdp265dnsnfs ~]# chmod +x /etc/init.d/mysqld
[root@hdp265dnsnfs ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
#设置开机启动
[root@hdp265dnsnfs ~]# chkconfig --level 35 mysqld on
[root@hdp265dnsnfs ~]# chkconfig --list mysqld
[root@hdp265dnsnfs ~]# chmod +x /etc/rc.d/init.d/mysqld
[root@hdp265dnsnfs ~]# chkconfig --add mysqld
[root@hdp265dnsnfs ~]# chkconfig --list mysqld
[root@hdp265dnsnfs ~]# service mysqld status
SUCCESS! MySQL running (4475)
/etc/profile/
export PATH=$PATH:/usr/local/mysql-5.7.26-linux/bin
[root@hdp265dnsnfs ~]# source /etc/profile
初始密码
[root@hdp265dnsnfs bin]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2017-04-17 17:40:02
_pB*3VZl5T<6
权限设置
修改登录密码
[root@hdp265dnsnfs bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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.
# 设置密码为 123456
mysql> set PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
添加远程访问权限
# 切换数据库
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 添加远程访问权限
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 刷新权限，否则不生效
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 查询修改后的结果
mysql> select host,user from user;
+-----------+-----------+
| host | user |
+-----------+-----------+
| % | root |
| localhost | mysql.sys |
+-----------+-----------+
2 rows in set (0.00 sec)
create user 'xxx'@'%' identified by '123456'; 这里 @‘%’ 表示在任何主机都可以登录
重启生效
/bin/systemctl restart mysql.service
[root@hdp265dnsnfs bin]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
为了在任何目录下可以登录mysql
ln -s /usr/local/mysql-5.7.26-linux/bin/mysql /usr/bin/mysql
一键安装脚本
需将 mysql-5.7.26-linux.tar.gz 放在 /root 目录下，才能正确执行脚本
#!/bin/bash
function stopOld() {
pid=$(ps -ef | grep -v 'grep' | egrep mysqld_safe| awk '{printf $2 " "}')
if [ "$pid" != "" ]; then
echo -n $"Shutting down mysqld_safe: "
kill -9 "$pid"
else
echo "mysqld_safe is stopped"
fi
pid=$(ps -ef | grep -v 'grep' | egrep mysqld| awk '{printf $2 " "}')
if [ "$pid" != "" ]; then
echo -n $"Shutting down my5.7.26sqld: "
kill -9 "$pid"
else
echo "mysqld is stopped"
fi
}
echo '尝试停止mysql';
stopOld
#sleep 10s
##define mysql directory configuration variable
Datadir=/usr/local/mysql-5.7.26-linux/data
Binlogdir=/usr/local/mysql-5.7.26-linux/binlog
Logdir=/usr/local/mysql-5.7.26-linux/logs
rm -rf /data/mysql
rm -rf /var/lock/subsys/mysql
rm -rf /usr/sbin/mysqladmin
rm -rf /usr/sbin/mysql
rm -rf /var/log/mysqld.log
clear
echo "========================================================================="
echo "A tool to auto-compile & install MySQL 5.7.26 on Redhat/CentOS Linux "
echo "========================================================================="
#pkill -9 mysql
#date +%Y-%m-%d-%H-%M
#卸载系统自带的Mysql
#/bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps
#/bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps
#/bin/rm -f /etc/my.cnf
#set mysql root password
echo "==========================="
mysqlrootpwd="$1"
if [ "$1" = "" ]; then
mysqlrootpwd="123456"
fi
# Initialize the installation related content.
#Delete Old Mysql program
rpm -qa|grep mysql
rpm -e mysql
# cat >>/etc/security/limits.conf<<EOF
# * soft nproc 65535
#* hard nproc 65535
#* soft nofile 6553500
#* hard nofile 6553500
#EOF
# echo "fs.file-max=6553500" >> /etc/sysctl.conf
echo "============================Install MySQL 5.7.26=================================="
#Backup old my.cnf
#rm -f /etc/my.cnf
if [ -s /etc/my.cnf ]; then
mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%M%S`.bak
fi
echo "============================MySQL 5.7.26 installing…………========================="
rm -rf /usr/local/mysql
rm -rf /usr/local/mysql-5.7.26-linux
tar -zxvf /root/mysql-5.7.26-linux.tar.gz -C /usr/local/
mv /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.26-linux
# ln -s /usr/local/mysql-5.7.26-linux /usr/local/mysql
grep mysql /etc/passwd
RETVAL=$?
if [ $RETVAL -ne 0 ];then
useradd mysql -s /sbin/nologin -M
action "mysql user added successfully" /bin/true
else
action " $(echo -e "\e[31;47;5m mysql user already exists\e[0m")" /bin/false
fi
if [ ! -d "$Datadir" ]
then
mkdir -p /usr/local/mysql-5.7.26-linux/data
# cd /usr/local/mysql-5.7.26-linux/data
# touch bogon.pid
#echo " bogon.pid ok "
# chmod -R 775 /var/run/mysqld/
fi
if [ ! -d "$Binlogdir" ]
then
mkdir -p /usr/local/mysql-5.7.26-linux/binlog
fi
if [ ! -d "$Logdir" ]
then
mkdir -p /usr/local/mysql-5.7.26-linux/logs
fi
sudo chown -R mysql:mysql /usr/local/mysql-5.7.26-linux
# sudo chown -R mysql:mysql /data/mysql
# sudo chown -R mysql:mysql /var
#edit /etc/my.cnf
#SERVERID=`ifconfig eth0 | grep "inet addr" | awk '{ print $2}'| awk -F. '{ print $3$4}'`
cat >>/etc/my.cnf<<EOF
[client]
port = 3306
[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
[mysqld]
####: for global
user =mysql
basedir =/usr/local/mysql-5.7.26-linux
datadir =/usr/local/mysql-5.7.26-linux/data
server_id =2333306
port =3306
character_set_server =utf8
explicit_defaults_for_timestamp =off
log_timestamps =system
socket =/tmp/mysql.sock
read_only =0
skip_name_resolve =1
auto_increment_increment =1
auto_increment_offset =1
lower_case_table_names =1
secure_file_priv =
open_files_limit =65536
max_connections =1000
thread_cache_size =64
table_open_cache =81920
table_definition_cache =4096
table_open_cache_instances =64
max_prepared_stmt_count =1048576
####: for binlog
binlog_format =row
log_bin =/usr/local/mysql-5.7.26-linux/binlog/mysql-bin
binlog_rows_query_log_events =on
log_slave_updates =on
expire_logs_days =7
binlog_cache_size =65536
#binlog_checksum =none
sync_binlog =1
slave-preserve-commit-order =ON
####: for error-log
log_error =/usr/local/mysql-5.7.26-linux/logs/error.log
general_log =off
general_log_file =/usr/local/mysql-5.7.26-linux/logs/general.log
####: for slow query log
slow_query_log =on
slow_query_log_file =/usr/local/mysql-5.7.26-linux/logs/slow.log
#log_queries_not_using_indexes =on
long_query_time =1.000000
####: for gtid
#gtid_executed_compression_period =1000
gtid_mode =on
enforce_gtid_consistency =on
####: for replication
skip_slave_start =1
#master_info_repository =table
#relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =4
#rpl_semi_sync_master_enabled =1
#rpl_semi_sync_slave_enabled =1
#rpl_semi_sync_master_timeout =1000
#plugin_load_add =semisync_master.so
#plugin_load_add =semisync_slave.so
binlog_group_commit_sync_delay =100
binlog_group_commit_sync_no_delay_count = 10
####: for innodb
default_storage_engine =innodb
default_tmp_storage_engine =innodb
innodb_data_file_path =ibdata1:1024M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool
innodb_log_group_home_dir =/usr/local/mysql-5.7.26-linux/data
innodb_log_files_in_group =3
innodb_log_file_size =1024M
innodb_file_per_table =on
innodb_online_alter_log_max_size =128M
innodb_open_files =65535
innodb_page_size =16k
innodb_thread_concurrency =0
innodb_read_io_threads =4
innodb_write_io_threads =4
innodb_purge_threads =4
innodb_page_cleaners =4
# 4(刷新lru脏页)
innodb_print_all_deadlocks =on
innodb_deadlock_detect =on
innodb_lock_wait_timeout =20
innodb_spin_wait_delay =128
innodb_autoinc_lock_mode =2
innodb_io_capacity =200
innodb_io_capacity_max =2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc =on
innodb_stats_persistent =on
innodb_stats_persistent_sample_pages =20
innodb_adaptive_hash_index =on
innodb_change_buffering =all
innodb_change_buffer_max_size =25
innodb_flush_neighbors =1
#innodb_flush_method =
innodb_doublewrite =on
innodb_log_buffer_size =128M
innodb_flush_log_at_timeout =1
innodb_flush_log_at_trx_commit =1
innodb_buffer_pool_size =4096M
innodb_buffer_pool_instances =4
autocommit =1
#--------innodb scan resistant
innodb_old_blocks_pct =37
innodb_old_blocks_time =1000
#--------innodb read ahead
innodb_read_ahead_threshold =56
innodb_random_read_ahead =OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct =25
innodb_buffer_pool_dump_at_shutdown =ON
innodb_buffer_pool_load_at_startup =ON
EOF
echo '初始化开始.....';
/usr/local/mysql-5.7.26-linux/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-5.7.26-linux --datadir=/usr/local/mysql-5.7.26-linux/data
Pass=$(grep 'A temporary password' /usr/local/mysql-5.7.26-linux/logs/error.log | awk '{print $NF}')
#temppasswd=$(cat /usr/local/mysql-5.7.26-linux/logs/error.log | grep 'temporary password' | awk '{print $NF}')
echo '修改mysql配置文件完成,正在将mysql加入服务中';
cp /usr/local/mysql-5.7.26-linux/support-files/mysql.server /etc/init.d/mysqld
echo 'mysql加入服务完成，正在添加开机自启动';
chkconfig --add mysqld
chkconfig mysqld on
echo '添加开机自启动成功';
/etc/init.d/mysqld start
echo "export PATH=$PATH:/usr/local/mysql-5.7.26-linux/bin" > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
echo "============================MySQL 5.7.26 install completed========================="
ps -eo start,cmd,pid|grep mysql
#/usr/local/mysql-5.7.26-linux/bin/mysqladmin -uroot -p"$Pass" password $mysqlrootpwd
echo "----mysql已经安装成功，请在/home/mysql.log查看安装日志详情---------"
/usr/local/mysql-5.7.26-linux/bin/mysql -uroot -p"$Pass" --connect-expired-password <<EOF
SET PASSWORD = PASSWORD('123456');
flush privileges;
use mysql;
update user set host = '%' where user = 'root';
grant all privileges on *.* to root@'%' identified by '123456';
flush privileges;
EOF
##
echo "----mysql密码已经修改成功---------"
# 创建软链接方便使用
ln -s /usr/local/mysql-5.7.26-linux/bin/mysql /usr/bin/mysql
MySQL主从复制
环境说明
两台安装好mysql数据库的服务器如下：
| 序号 | 域名 | 服务器IP | 端口 | 主/从 | mysql版本 |
|---|---|---|---|---|---|
| 1 | cdh1 | 172.18.11.111 | 3306 | 主 | 5.7.26 |
| 2 | cdh2 | 172.18.11.112 | 3306 | 从 | 5.7.26 |
主从配置需要注意的点
- 主从服务器操作系统版本和位数一致；
- Master和Slave数据库的版本要一致；
- Master和Slave数据库中的数据要一致；
- Master开启二进制日志，Master和Slave的server_id在局域网内必须唯一；
- 如果用clone的方式得到两个MySQL服务，注意
auto.cnf中的 UUID 不能一样，否则IO线程不启动；
find / -name auto.cnfvim /usr/local/mysql-5.7.26-linux/data/auto.cnfservice mysqld restart
- 注意配置文件中
_和-，如 server_id 使用下划线； - 注意拷贝配置如
binlog-ignore-db是中横线，不是其他。
配置主数据库
配置文件开启binlog
修改 MySQL 配置文档 /etc/my.cnf，在 [mysqld] 段添加以下配置：
[mysqld]
server_id=1
log-bin=mysql‐bin #开启日志，启用二进制日志
binlog-format=ROW
expire_logs_days=7 # binlog过期清理时间
max_binlog_size=1G # binlog每个日志文件大小
binlog-do-db=user_db #设置需要同步的数据库(也可不配置)
#屏蔽不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
创建同步账号
登录主库
systemctl restart mysqld
mysql ‐uroot ‐p123456
创建库和表
-- 创建用户用于同步
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
-- 只允许从库连接
-- CREATE USER 'repl'@'cdh2' IDENTIFIED BY '123456';
-- 创建同步数据库
CREATE DATABASE user_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
use user_db;
create table t_user_0(id bigint , name varchar(40));
-- 给用户设置权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
锁定主库
刷新表然后锁表（只允许查数据不允许写数据），防止binlog、position变动
FLUSH TABLES WITH READ LOCK;
获取binlog名字和position
mysql> show master status;
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql‐bin.000002 | 154 | user_db | mysql,information_schema,performance_schema,sys | |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
配置从数据库
配置文件开启binlog
修改 MySQL 配置文档 /etc/my.cnf，在 [mysqld] 段添加以下配置：
[mysqld]
server_id=101 #设置服务器标识ID，主从不能一致 ，每台服务器唯一
log-bin=mysql‐bin #开启日志，启用二进制日志
binlog-format=ROW
expire_logs_days=7 #binlog过期清理时间
max_binlog_size=100m #binlog每个日志文件大小
#设置需要同步的数据库
replicate_wild_do_table=user_db.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=dolphinscheduler.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=store.%
开启主从同步
systemctl restart mysqld
mysql ‐uroot ‐p123456
file和pos是从主节点获取的
change master to master_host='cdh1', master_user='repl', master_log_file='mysql‐bin.000002', master_log_pos=154, master_port=3306, master_password='123456';
start slave;
查看从节点状态
-- 登录MySQL查看
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: cdh1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql‐bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: cdh2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql‐bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Wild_Do_Table: user_db.%
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,dolphinscheduler.%,sys.%,store.%
...
1 row in set (0.00 sec)
注意，主从同步成功的标志：
IO线程和SQL线程都是成功运行的：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果IO线程 not running，可以尝试：
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status\G
解锁主库
在MySQL中执行如下命令：
unlock tables;
MySQL双主模式
Keepalived+mysql双主来实现MySQL-HA，必须保证两台MySQL数据库的数据完全一样，基本思路是两台MySQL互为主从关系，通过Keepalived配置虚拟IP，实现当其中的一台MySQL数据库宕机后，应用能够自动切换到另外一台MySQL数据库，保证系统的高可用。
| 序号 | 域名 | 服务器IP | 端口 | mysql版本 | keepalived |
|---|---|---|---|---|---|
| 1 | cdh1 | 172.18.11.111 | 3306 | 5.7.26 | keepalived-2.1.5 |
| 2 | cdh2 | 172.18.11.112 | 3306 | 5.7.26 | keepalived-2.1.5 |
| 3 | VIP | 172.18.11.100 |
配置主主同步
该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务写入二进制日志。在事件写入二进制日志完成后，master通知存储引擎提交事务。 下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经同步了master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。 SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步。 若mysql主机开启了防火墙，需要关闭防火墙或创建规则。
修改MySQL配置文件
两台MySQL均要开启 binlog日志功能，两台 MySQL 的 server-ID 不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可。
cdh1中有关复制的配置如下：
[root@cdh1 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=mysql‐bin #开启日志，启用二进制日志
binlog-format=ROW
expire_logs_days = 3 # binlog过期清理时间
max_binlog_size = 1G # binlog每个日志文件大小
#binlog-do-db=user_db #设置需要同步的数据库(也可不配置)
relay-log=relay-bin
relay_log_index = slave-relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 1
#屏蔽不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
[root@cdh1 ~]# systemctl restart mysqld #重启mysqld服务
cdh2中有关复制的配置如下：
[root@cdh2 ~]# vim /etc/my.cnf
[mysqld]
server_id=101
log-bin=mysql‐bin #开启日志，启用二进制日志
binlog-format=ROW
expire_logs_days = 3 # binlog过期清理时间
max_binlog_size = 1G # binlog每个日志文件大小
#binlog-do-db=user_db #设置需要同步的数据库(也可不配置)
relay-log=relay-bin
relay_log_index = slave-relay-bin.index
auto_increment_increment = 2
auto_increment_offset = 2
#屏蔽不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
[root@cdh2 ~]# systemctl restart mysqld #重启mysqld服务
mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。
- auto-increment-increment 表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2。
- auto-increment-offset 是用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突
将cdh1设为cdh2的主服务器
在cdh1主机上创建授权账户，允许在cdh2(172.18.11.112)主机上连接
[root@cdh1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'172.18.11.112' identified by '123456';
mysql> flush privileges;
mysql> show master status; #查看cdh1的当前binlog状态信息
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql‐bin.000001 | 154 | | mysql,information_schema,performance_schema,sys | |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
在cdh2上将cdh1设为自已的主服务器并开启slave功能
[root@cdh2 ~]# mysql -uroot -p123456
mysql> change master to master_host='172.18.11.111',master_user='repl',master_password='123456',master_log_file='mysql‐bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G; # 查看从的状态
只有 Slave_IO_Running 和 Slave_SQL_Running 两个值都为yes, 才代表从服务器能正常连接主服务器
将cdh2设为cdh1的主服务器
在cdh2主机上创建授权账户，允许在cdh1(172.18.11.111)主机上连接
[root@cdh2 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'172.18.11.111' identified by '123456';
mysql> flush privileges;
mysql> show master status; # 查看cdh2的当前binlog状态信息
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql‐bin.000001 | 154 | | mysql,information_schema,performance_schema,sys | |
+--------------------+----------+--------------+-------------------------------------------------+-------------------+
在cdh1上将cdh2设为自已的主服务器并开启slave功能
mysql> change master to master_host='172.18.11.112',master_user='repl',master_password='123456',master_log_file='mysql‐bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G; # 查看从的状态
只有 Slave_IO_Running 和 Slave_SQL_Running 两个值都为yes, 才代表从服务器能正常连接主服务器
测试主主同步
在cdh1上创建要同步的数据库如 test_db, 并在test_db中创建一张测试表如tab1
mysql> create database aaa;
mysql> use aaa;
mysql> create table a(id int primary key auto_increment,name varchar(20));
mysql> insert into a(name) values('a'),('b');
查看cdh2主机是否同步了cdh1上的数据变化
mysql> select * from aaa.a;
+----+------+
| id | name | # 可以看出cdh2同步了master的数据变化
+----+------+
| 1 | a |
| 3 | b |
+----+------+
mysql> use aaa
mysql> insert into a(name) values('c'),('d');
mysql> select * from aaa.a;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | b |
| 4 | c |
| 6 | d |
+----+------+
查看cdh1主机是否同步了cdh2上的数据变化
mysql> select * from aaa.a;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | b |
| 4 | c |
| 6 | d |
+----+------+
现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步完成。
配置Keepalived
keepalived是集群管理中保证集群高可用的一个软件解决方案，其功能类似于heartbeat，用来防止单点故障 keepalived是以VRRP协议为实现基础的，VRRP全称Virtual Router Redundancy Protocol，即虚拟路由冗余协议。 虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N台提供相同功能的路由器组成一个路由器组，这个组里面有一个master和多个backup，master上面有一个对外提供服务的vip，master会发组播（组播地址为224.0.0.18），当backup收不到vrrp包时就认为master宕掉了，这时就需要根据VRRP的优先级来选举一个backup当master。这样的话就可以保证路由器的高可用了。keepalived主要有三个模块，分别是core 、check和vrrp。core模块为keepalived的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。check负责健康检查，包括常见的各种检查方式。vrrp模块是来实现 VRRP协议的。
cdh1配置Keepalived
[root@cdh1 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_`date '+%Y%m%d%H%M%S'`
[root@cdh1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id cdh1 #当前节点名
}
vrrp_script restart_mysql {
script "/usr/local/script/restart_mysql.sh" #重启 mysql 容器
interval 2
weight 2
}
vrrp_instance VI_11 {
state BACKUP #两台配置节点均为BACKUP
interface enp0s8 #绑定虚拟IP的网络接口
virtual_router_id 51 #VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
priority 101 #节点的优先级,另一台优先级改低一点
advert_int 1 #组播信息发送间隔，两个节点设置必须一样
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
authentication { #设置验证信息，两个节点必须一致
auth_type PASS
auth_pass 123456
}
track_script {
restart_mysql #检测 mysql 状态，如果失败，则重启 mysql 容器
}
virtual_ipaddress { #指定虚拟IP,两个节点设置必须一样
172.18.11.100
}
}
virtual_server 172.18.11.100 3306 { #linux虚拟服务器(LVS)配置
delay_loop 2 #每个2秒检查一次real_server状态
lb_algo wrr #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh
lb_kind DR #LVS集群模式 ,NAT|DR|TUN
persistence_timeout 60 #会话保持时间
protocol TCP #使用的协议是TCP还是UDP
real_server 172.18.11.111 3306 {
weight 3 #权重
TCP_CHECK {
connect_port 3306 #健康检查端口
connect_timeout 10 #连接超时时间
retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
}
}
}
[root@cdh1 ~]# scp /etc/keepalived/keepalived.conf root@172.18.11.112:/etc/keepalived/
cdh2配置Keepalived
[root@cdh2 ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_`date '+%Y%m%d%H%M%S'`
[root@cdh2 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id cdh2 #当前节点名
}
vrrp_script restart_mysql {
script "/usr/local/script/restart_mysql.sh" #重启 mysql 容器
interval 2
weight 2
}
vrrp_instance VI_12 {
state BACKUP #两台配置节点均为BACKUP
interface enp0s8 #绑定虚拟IP的网络接口
virtual_router_id 51 #VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
priority 50 #节点的优先级,另一台优先级改低一点
advert_int 1 #组播信息发送间隔，两个节点设置必须一样
authentication { #设置验证信息，两个节点必须一致
auth_type PASS
auth_pass 123456
}
track_script {
restart_mysql #检测 mysql 状态，如果失败，则重启 mysql 容器
}
virtual_ipaddress { #指定虚拟IP,两个节点设置必须一样
172.18.11.100
}
}
virtual_server 172.18.11.100 3306 { #linux虚拟服务器(LVS)配置
delay_loop 2 #每个2秒检查一次real_server状态
lb_algo wrr #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh
lb_kind DR #LVS集群模式 ,NAT|DR|TUN
persistence_timeout 60 #会话保持时间
protocol TCP #使用的协议是TCP还是UDP
real_server 172.18.11.112 3306 {
weight 3 #权重
TCP_CHECK {
connect_port 3306 #健康检查端口
connect_timeout 10 #连接超时时间
retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
}
}
}
重启MySQL脚本
[root@cdh1 ~]# mkdir -p /usr/local/script/
[root@cdh1 ~]# vim /usr/local/script/restart_mysql.sh
#!/bin/bash
# 定义变量，日志文件路径
LOG_FILE="/usr/local/keepalived/logs/mysql-check.log"
# 定义变量，检查 mysql 服务是否正常的命令
HAPS=`ps -C mysqld --no-header |wc -l`
# 打印当前时间到日志文件
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
# 打印提示信息到日志文件
echo "check mysql status" >> $LOG_FILE
# 检查数据库状态，如何返回 0，则重启 mysql 容器，然后休眠 3s 后，再次检测 mysql 状态，如果还是返回 0，则停止 keepalived。
if [[ $HAPS -eq 0 ]] ;then
echo "START MYSQL" >> $LOG_FILE
systemctl start mysql.service
# 等待一小会再次检查 mysqld，如果没有启动成功，则停止keepalived，使其启动备用机
sleep 3s
if [ `ps -C mysqld --no-header |wc -l` -eq 0 ] ;then
echo "start mysql failed, killall keepalived" >> $LOG_FILE
# service keepalived stop
killall keepalived
fi
fi
[root@cdh1 ~]# chmod +x /usr/local/script/restart_mysql.sh
[root@cdh1 ~]# scp -r /usr/local/script/restart_mysql.sh root@172.18.11.112:/usr/local/script/restart_mysql.sh
[root@cdh1 ~]# systemctl restart keepalived
[root@cdh2 ~]# systemctl restart keepalived # 启动keepalived 服务
测试高可用
在cdh1和cdh2分别执行 ip addr show dev enp0s8 命令查看cdh1和cdh2对VIP（群集虚拟IP）的控制权。
[root@cdh1 ~]# ip a show dev enp0s8 #cdh1的查看结果
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:29:90:d5 brd ff:ff:ff:ff:ff:ff
inet 172.18.11.111/24 brd 172.18.11.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet 172.18.11.100/32 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe29:90d5/64 scope link
valid_lft forever preferred_lft forever
[root@cdh2 ~]# ip a show dev enp0s8 # cdh2的查看结果
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:51:d4:37 brd ff:ff:ff:ff:ff:ff
inet 172.18.11.112/24 brd 172.18.11.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe51:d437/64 scope link
valid_lft forever preferred_lft forever
从上边可以看出cdh1是主服务器，cdh2为备用服务器
停止MySQL服务，看keepalived健康检查程序是否会触发编写的脚本
[root@cdh1 ~]# systemctl stop mysqld # 停止cdh1主机的mysql服务
cdh2主的查看结果：
[root@cdh2 ~]# ip a show dev enp0s8
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:51:d4:37 brd ff:ff:ff:ff:ff:ff
inet 172.18.11.112/24 brd 172.18.11.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet 172.18.11.100/32 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe51:d437/64 scope link
valid_lft forever preferred_lft forever
[root@cdh2 ~]#
# 这说明在主服务上停止MySQL服务，触发了编写的脚本，进行自动故障切换
在 172.18.11.113 上访问 172.18.11.100 数据库
[root@cdh1 ~]# systemctl start mysqld
[root@cdh1 ~]# systemctl start keepalived
[root@cdh3 ~]# mysql -uroot -p123456 -h172.18.11.100
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 101 |
+---------------+-------+
1 row in set (0.00 sec)
[root@cdh2 ~]# systemctl stop mysqld
[root@cdh3 ~]# mysql -uroot -p123456 -h172.18.11.100
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
MGR集群
PXC集群
参考：
- centos7 + mysql5.7 tar包解压安装 - 大大的橙子 - 博客园 (cnblogs.com)
- centos mysql 笔记（内含vagrant mysql 镜像） - 疯狂创客圈 - 博客园 (cnblogs.com)
- Linux下MySQL的配置文件（my.cnf）的存放路径_linux my.cnf 位置_李阿昀的博客-CSDN博客
- MySQL主从复制配置 (gper.club)
- mysql主从同步详细教程 - myIvan - 博客园 (cnblogs.com)
- mysql锁库与解锁 FLUSH TABLES WITH READ LOCK 和 UNLOCK TABLES_人生匆匆的博客-CSDN博客
- Mysql主从同步时Slave_IO_Running：Connecting ； Slave_SQL_Running：Yes的情况故障排除_奶瓶还给我的博客-CSDN博客
- 实战 MySQL 高可用架构 - 掘金 (juejin.cn)
- MySQL——MySQL高可用—双主_mysql 主主 轮询机制_KFC质检员的博客-CSDN博客
- mysql 双主配置(互为主从)_mysql 双主模式 后端怎么配置jdbc_Lin_Miao_09的博客-CSDN博客
- shell 中的[: -eq: unary operator expected_hzzyu的博客-CSDN博客
- Mysql-MGR集群搭建_mysql gmr_秦杨的博客-CSDN博客