CentOS 7.4通过rpm包离线安装 Mysql8.0并部署主从复制（附从库备份脚本）

一. 部署MySQL

(两个节点都做)

下载 rpm包

wget https://goodrain-pkg.oss-cn-shanghai.aliyuncs.com/mysql8.rpm
tar xvf mysql8.rpm
cd mysql/

卸载mariadb

rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.65-1.el7.x86_64 --nodeps

安装 MySQL

rpm -ivh mysql-community-server-8.0.20-1.el7.x86_64.rpm mysql-community-client-8.0.20-1.el7.x86_64.rpm mysql-community-common-8.0.20-1.el7.x86_64.rpm mysql-community-libs-8.0.20-1.el7.x86_64.rpm net-tools-2.0-0.25.20131004git.el7.x86_64.rpm

启动 MySQL

systemctl restart mysqld
systemctl enable mysqld

获取密码

$ grep "A temporary password is generated for root@localhost" /var/log/mysqld.log
2020-06-07T12:51:10.889926Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 7Pq+r7v5H:d1

登录并修改密码

mysql -uroot -p7Pq+r7v5H:d1
### 初始化时已更改root密码，这里可以省略
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Www.1.com';

附（创建用户并授权操作）

### 为了避免SQLyog连接8.0报：Error No.2058 Plugin caching_sha2_password could not be loaded，后续工具更新后，再使用8.0新的加密方式
mysql> CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'Www.1.com';
### 授予用户所有权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;

二. 部署主从复制

修改配置文件

主

$ vi /etc/my.cnf
[mysqld]

# 修改为utf8编码
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

# 默认存储引擎
default-storage-engine=INNODB

# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve


# 主从配置
log-bin=binlog
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14
max_binlog_size=100M

# Logging
log_warnings  = 2
slow_query_log= 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
log-queries-not-using-indexes
log_slow_admin_statements

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/

从

$ vi /etc/my.cnf
[mysqld]

# 修改为utf8编码
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
# 默认存储引擎
default-storage-engine=INNODB

# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve

# 主从配置
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14
max_binlog_size=100M

# Logging
log_warnings  = 2
slow_query_log= 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
log-queries-not-using-indexes
log_slow_admin_statements

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/

重启数据库

systemctl restart mysql

主数据库

查看数据库字符编码（可选）,并创建用户授权

# 进入数据库
docker exec -it mysql_master bash
# 查看字符编码
mysql>  show global variables like'%character_set%';
# 创建用户授权
mysql>  CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
mysql>  GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql>  flush privileges;

获取主节点当前binary log文件名和位置（position）

mysql>  SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      868 |              |                  | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

从数据库

# 主从配置
mysql>  CHANGE MASTER TO
mysql>  MASTER_HOST='192.168.0.162',
mysql>  MASTER_USER='slave',
mysql>  MASTER_PASSWORD='slave',
mysql>  MASTER_PORT=3306,
mysql>  MASTER_LOG_FILE='binlog.000003',
mysql>  MASTER_LOG_POS=868;

# 开启主从同步
mysql>  start slave;
# 再查看主从同步状态
mysql>  show slave status;

这里只要看到两个参数Slave_IO_Running和Slave_SQL_Running都为true且Error字段都为空则代码主从正常复制

三. 测试验证

通过在主服务器创建数据库建表插入数据的方式来进行测试，查看从服务器是否同步更新了数据

在主库创建库

mysql>  create database console;

在从库查看

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

数据同步成功，主从复制部署完成

四. 从节点配置备份计划

• 安装并配置计划任务

$ crontab -e
0 3 * * * /var/lib/mysql-backup/mysql-backup.sh

• 备份脚本内容

$  vim /var/lib/mysql-backup/mysql-backup.sh
#!/bin/bash
DATE=`date +%Y%m%d%H%M`                 #every minute
DB_USER=root                            #database username
DB_PASS="mysqlpass"                     #database password
BACKUP=/var/lib/mysql-backup            #backup path
      
#backup command
      
/usr/bin/mysqldump -u$DB_USER -p$DB_PASS -h 127.0.0.1 |gzip > ${BACKUP}\/rainbond_${DATE}.sql.gz
      
#just backup the latest 30 days 
      
find ${BACKUP} -name "rainbond_*.sql.gz" -type f -mtime +30 -exec rm {} \; > /dev/null 2>&1

• 赋予执行权限

chmod +x /var/lib/mysql-backup/mysql-backup.sh