万普插件库

jQuery插件大全与特效教程

2025年08月28日

MySql 8.0.33 主从实战配置及迁移企业生产数据

由于最近有项目需要升级数据库由mysql 5.7.29 升级至mysql 8.0.33 ，并且迁移数据。由于数据库版本跨度大，需使用导出导入的数据方式进行迁移。数据采用主从架构，本文详细记录一下整个过程。

一、操作系统基础环境安装（2台主从服务器都操作）

1、系统安装，系统选用centos7.9（最小化安装，过程略）,CPU16,内存48G，磁盘850G（50G系统盘，800G数据盘），数据库数据70G。

2、配置主机名，IP地址

3、配置yum源，使用阿里云

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum clean all
yum makecache
yum -y install wget epel-release rsync telnet zip unzip net-tools lrzsz  //安装常用工具
netstat -natp   //查看有没非必要的服务端口，如25端口
rpm -e mariadb-libs-5.5.68-1.el7.x86_64  //默认安装，卸载
yum -y remove postfix   //卸载非必要软件

3、关闭selinux ，编辑配置文件

SELINUX=disabled

4、添加数据盘

fdisk /dev/sdb   //分区
pvcreate /dev/sdb1
vgcreate data /dev/sdb1
lvcreate -l +100%FREE -n app data
mkfs.ext4 /dev/data/app
mount /dev/data/app /data/
 echo "/dev/data/app /data	ext4 defaults 0 0"  >> /etc/fstab

5、防火墙设置

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

二、安装数据库软件

1、主库

安装软件

yum -y install mysql-community-common-8.0.33-1.el7.x86_64.rpm mysql-community-server-8.0.33-1.el7.x86_64.rpm mysql-community-client-8.0.33-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm mysql-community-libs-8.0.33-1.el7.x86_64.rpm

编辑配置文件：

# cat /etc/my.cnf
[client]
port = 3306
[mysqld]
character-set-server = utf8mb4  
collation-server = utf8mb4_unicode_ci
datadir=/data/mysql
port = 3306
default_storage_engine = InnoDB 
socket=/var/lib/mysql/mysql.sock
ft_min_word_len=1
skip-external-locking
skip-name-resolve
max_allowed_packet=256M
back_log = 500
max_user_connections = 500
max_connect_errors = 10000
slow_query_log
long_query_time = 2
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16  
log_bin_trust_function_creators=1
max_connections=5000
transaction_isolation=READ-COMMITTED
thread_cache_size = 500  
table_open_cache = 4096  
open_files_limit = 65535  
back_log = 1000
tmp_table_size = 512M  
max_heap_table_size = 512M  
sort_buffer_size = 2M  
join_buffer_size = 8M
log-bin=/data/mysql-bin/mysql-bin
binlog_format=ROW 
server-id=1
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema
binlog-ignore-db=sys
group_concat_max_len=102400
log-error=/var/log/mysqld.log
lower_case_table_names = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout=3600
interactive_timeout=3600
sync_binlog=1

创建数据库目录及binlog日志目录

mkdir -p /data/mysql /data/mysql-bin
chown -R mysql:mysql /data/mysql /data/mysql-bin

启动：

systemctl start mysqld
chkconfig mysqld on  //开机启动

查看日志，获取密码：

cat /var/log/mysqld.log

首次登录需要修改root密码

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'passwd';

创建业务系统所需要的库，并且授权：

create database  db1 CHARACTER SET utf8 COLLATE utf8_general_ci;
create database  db2 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
create database  db3 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

为了确保升级后旧客户端仍能正常连接，创建用户时必须显式指定 mysql_native_password，例如：

CREATE USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';
flush privileges;

修改已有用户的密码

ALTER USER 'user1'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd';
flush privileges;

2、从库

安装软件

yum -y install mysql-community-common-8.0.33-1.el7.x86_64.rpm mysql-community-server-8.0.33-1.el7.x86_64.rpm mysql-community-client-8.0.33-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm mysql-community-libs-8.0.33-1.el7.x86_64.rpm

编辑配置文件：

# cat /etc/my.cnf
[client]
port = 3306

[mysqld]
character-set-server = utf8mb4  
collation-server = utf8mb4_unicode_ci
datadir=/data/mysql
port = 3306
default_storage_engine = InnoDB 
socket=/var/lib/mysql/mysql.sock
ft_min_word_len=1
skip-external-locking
skip-name-resolve
max_allowed_packet=256M
back_log = 500
max_user_connections = 500
max_connect_errors = 10000
slow_query_log
long_query_time = 2
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 16  
log_bin_trust_function_creators=1
max_connections=5000
transaction_isolation=READ-COMMITTED
thread_cache_size = 500  
table_open_cache = 4096  
open_files_limit = 65535  
back_log = 1000
tmp_table_size = 512M  
max_heap_table_size = 512M  
sort_buffer_size = 2M  
join_buffer_size = 8M
#log-bin=/data/mysql-bin/mysql-bin
#binlog_format=ROW 
server-id=2
relay-log = /data/mysql-relay/relay-bin
read_only = ON
replicate_do_db=db1
replicate_do_db=db2
replicate_do_db=db3
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=sys
group_concat_max_len=102400
log-error=/var/log/mysqld.log
lower_case_table_names = 1
#KB测试过程中开发要求添加的参数
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wait_timeout=3600
interactive_timeout=3600

创建数据库目录及binlog日志目录

mkdir -p /data/mysql /data/mysql-bin
chown -R mysql:mysql /data/mysql /data/mysql-bin

启动：

systemctl start mysqld
chkconfig mysqld on  //开机启动

查看日志，获取密码 ; 首次登录需要修改root密码 ;创建业务系统所需要的库，并且授权 ;为了确保升级后旧客户端仍能正常连接，创建用户时必须显式指定 mysql_native_password 等步骤跟主库操作一样。

三、数据迁移

登录从库， 停止现有复制（首次配置可跳过）

STOP SLAVE;

停止所有业务后，开始导出数据：

time=`date '+%F'`
mysqldump -u${mysql_user} -p${mysql_passwd} -hlocalhost --single-transaction --master-data=2 --default-character-set=utf8mb4 --triggers --events -R --databases dbname > dbname-${time}.sql

生产环境我是使用备份脚本进行数据库备份,有需要可以参考，以下是mysql5.7.29的备份计划脚本：

# cat /opt/dbbak.sh
#!/bin/bash
time=`date '+%F'`
nowtime=`date +"%F %T"`
cd /data/dbbak
mysql_user=root
mysql_passwd=passwd
dbs=(db1 db2 db3)
dbnum=${#dbs[*]}
n=$((dbnum-1))
#echo $n
startbaktime=`date +"%Y-%m-%d %H:%M:%S"`
echo "################ 开始备份，时间为：${startbaktime} ###########" >> log/$time-backup.log
for i in `seq 0 $n`
do
	echo $i
	echo "$nowtime  begin backup database ${dbs[$i]} ..." >> log/$time-backup.log
        /usr/local/mysql/bin/mysql -u${mysql_user} -p${mysql_passwd} -e "select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema=\"${dbs[$i]}\" order by data_length desc,index_length desc;" >> log/$time-backup.log
	/usr/local/mysql/bin/mysqldump -u${mysql_user} -p${mysql_passwd} -hlocalhost --single-transaction --master-data=2 --default-character-set=utf8mb4 --triggers --events -R --databases ${dbs[$i]} > ${dbs[$i]}-${time}.sql
	tar czvf ${dbs[$i]}-${time}.sql.tar.gz ${dbs[$i]}-${time}.sql
	rm -rf ${dbs[$i]}-${time}.sql
	endtime=`date +"%F %T"`
	echo "$endtime  finish backup database ${dbs[$i]} ..." >> log/$time-backup.log
done
endbaktime=`date +"%Y-%m-%d %H:%M:%S"`
echo "################ 结束备份，时间为：${endbaktime} ###########" >> log/$time-backup.log

说明：注意由于数据库中有使用编码utf8mb4，所以建议导出数据库的时候也要使用该编码导出。

数据库备份完成，传输到新的主从服务器，开始导入，可以同时进行导入：

mysql -uroot -p --default-character-set=utf8mb4 < dbbacksqlfile.sql

四、主从配置

数据导入完成后，注意在主库查看binlog位置：

show master status;

主库创建主从同步账户

CREATE USER 'slave'@'从库IP' IDENTIFIED WITH mysql_native_password BY 'passwd';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'从库IP';
FLUSH PRIVILEGES;

从库,其中MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 157根据实际填写，也就是主库数据导入完成后show master status查看的位置。

CHANGE MASTER TO MASTER_HOST = '主库IP', MASTER_PORT = 3306,MASTER_USER = 'slave',MASTER_PASSWORD = 'passwd',MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 157; 
start slave;

五、其他

备份计划任务

# crontab -l 
0 22 * * * /bin/bash /opt/dbbak.sh &> /dev/null

备份脚本

# cat /opt/dbbak.sh 
#!/bin/bash
time=`date '+%F'`
nowtime=`date +"%F %T"`
cd /data/dbbak
mysql_user=root
mysql_passwd=passwd
dbs=(db1 db2 db3)
dbnum=${#dbs[*]}
n=$((dbnum-1))
#echo $n
startbaktime=`date +"%Y-%m-%d %H:%M:%S"`
echo "################ 开始备份，时间为：${startbaktime} ###########" >> log/$time-backup.log
for i in `seq 0 $n`
do
	echo $i
	echo "$nowtime  begin backup database ${dbs[$i]} ..." >> log/$time-backup.log
        /usr/bin/mysql -u${mysql_user} -p${mysql_passwd} -e "select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema=\"${dbs[$i]}\" order by data_length desc,index_length desc;" >> log/$time-backup.log
	/usr/bin/mysqldump -u${mysql_user} -p${mysql_passwd} -hlocalhost --single-transaction --source-data --default-character-set=utf8mb4 --triggers --events -R --databases ${dbs[$i]} > ${dbs[$i]}-${time}.sql
	tar czvf ${dbs[$i]}-${time}.sql.tar.gz ${dbs[$i]}-${time}.sql
	rm -rf ${dbs[$i]}-${time}.sql
	endtime=`date +"%F %T"`
	echo "$endtime  finish backup database ${dbs[$i]} ..." >> log/$time-backup.log
done
endbaktime=`date +"%Y-%m-%d %H:%M:%S"`
echo "################ 结束备份，时间为：${endbaktime} ###########" >> log/$time-backup.log

至此mysql配置完成及数据迁移完成，生产环境一般还需要添加监控，监控数据库的性能等情况。

标签：el-select 默认选中 

作者:wanpu88 , 分类:插件库 , 浏览:5 , 评论:0

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言