Mysql主从部署和配置调优

登峰客
243 阅读3分钟

一、单节点部署Mysql5.7

1、下载libaio依赖包

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-devel-0.3.109-13.el7.x86_64.rpm
wget http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm

2、下载最新5.7二进制安装包

wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

3、创建用户和用户组

groupadd mysql
useradd -M -g mysql -s /sbin/nologin mysql

4、创建数据目录和日志目录

mkdir -p /opt/mysql/data
mkdir -p /opt/mysql/logs/{binlogs,relaylogs}

5、安装依赖包

rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm

6、解压5.7二进制安装包

tar -xzvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
mv ./mysql-5.7.35-linux-glibc2.12-x86_64  /usr/local/mysql5.7

7、创建配置文件

cat << EOF >  /usr/local/mysql5.7/my.cnf
[mysqld_safe]
log-error=/opt/mysql/logs//mysql-error.log
[mysqld]
default_authentication_plugin=mysql_native_password
default-time-zone='+8:00'
user=mysql
port=3310

# 做主从同步时需要添加如下配置，server-id不能重复。
server-id=$(date +%s)
binlog_format = ROW
binlog_row_image = full
max_binlog_size = 1G
expire_logs_days = 7
log-bin=/opt/mysql/logs/binlogs/mysql-bin
relay-log=/opt/mysql/logs/relaylogs/slave-relay-bin
# end

# 免密登录，用于安装后设置密码时使用
skip-grant-tables
# end

log-error=/opt/mysql/logs/mysql-error.logdatadir=/opt/mysql/data
basedir=/usr/local/mysql5.7
socket=/usr/local/mysql5.7/mysql.sockpid-file=/usr/local/mysql5.7/mysql.pid
skip-name-resolve
symbolic-links = 0
lower_case_table_names = 1
character-set-server = utf8mb4
default-storage-engine = InnoDB
innodb_file_per_table = 1
max_connections = 5000
max_allowed_packet = 1G
interactive_timeout = 120
wait_timeout = 864000
sort_buffer_size=2097152
sql_mode=NO_AUTO_VALUE_ON_ZERO
[mysql]
socket=/usr/local/mysql5.7/mysql.sockdefault-character-set=utf8mb4
EOF

8、授予运行用户目录权限

chown -R mysql:mysql /usr/local/mysql5.7
chown -R mysql:mysql /opt/mysql
chmod -R 770 /usr/local/mysql5.7
chmod -R 770 /opt/mysql

9、初始化mysql数据库

/usr/local/mysql5.7/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/opt/mysql/data

10、支持SSL安全连接

yum -y install openssl
/usr/local/mysql5.7/bin/mysql_ssl_rsa_setup --user=mysql --datadir=/opt/mysql/data

11、修改启动脚本

cd /usr/local/mysql5.7/support-files
sed   -i   "/^basedir/c\basedir=/usr/local/mysql5.7"   mysql.server
sed   -i   "/^datadir/c\datadir=/opt/mysql/data"   mysql.server

12、设置开机自启

ln -s /usr/local/mysql5.7/support-files/mysql.server  /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list

13、启动mysql数据库

systemctl restart mysqld && systemctl status mysqld

14、设置root用户密码

/usr/local/mysql5.7/bin/mysql -S /usr/local/mysql5.7/mysql.sock
update mysql.user set authentication_string=password('WuAi@3030') where user='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'WuAi@3030';exit

15、去除免密登录

sed -i 's/^skip-grant-tables/#&/'  /usr/local/mysql5.7/my.cnf
systemctl restart mysqld

16、设置允许远程连接

/usr/local/mysql5.7/bin/mysql -S /usr/local/mysql5.7/mysql.sock -uroot -p
grant all privileges on *.* to 'root'@'%' identified by 'WuAi@3030' with grant option;
flush privileges;
select host,user from mysql.user;
exit

17、添加环境变量

cat << EOF >> /etc/profile
export MYSQL_HOME=/usr/local/mysql5.7/mysql
export PATH=\$MYSQL_HOME/bin:\$PATH
EOF
source /etc/profile

18、防火墙放行mysql端口

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

二、配置Mysql主从关系

1、设置从库为只读模式

/usr/local/mysql5.7/bin/mysql -S /usr/local/mysql5.7/mysql.sock -uroot -p
set global read_only=off;
show variables like 'read_only';
exit

2、重启从库

systemctl restart mysqld

3、在主库上创建同步账号

CREATE USER 'repl'@'%' IDENTIFIED BY 'iN@EhGm@xk9B';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'
FLUSH PRIVILEGES;

4、在主库上查看binlog文件名和位置

SHOW MASTER STATUS;

比如看到的是：mysql-bin.000002 | 1465

5、在从库上清除主从关系

STOP SLAVE;
RESET SLAVE ALL;

6、在从库上添加主从关系

CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_PORT=3310,
MASTER_USER='repl',
MASTER_PASSWORD='iN@EhGm@xk9B',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1465;

7、在从库上开启主从同步

START SLAVE;

8、在从库上查看同步状态

SHOW SLAVE STATUS\G

如果为双yes表示主从同步状态正常。

三、Mysql配置调优

1、InnoDB缓存命中率的核心优化参数

InnoDB需要在innodb buffer pool中处理缓存，所以非常需要有足够的InnoDB buffer pool空间。mysql数据库的pool可以缓存索引和行数据，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置到服务器物理内存的80%，但是也要根据服务器综合使用情况进行合理设置。

Mysql的InnoDB缓存命中率的核心优化参数有三个：innodb_buffer_pool_instances、innodb_buffer_pool_chunk_size、innodb_buffer_pool_size。

从MySQL 5.7开始，innodb_buffer_pool_size必须等于innodb_buffer_pool_chunk_size *innodb_buffer_pool_instances的整数倍才行。

设置innodb_buffer_pool_instances值大于等于 2时，必须在 innodb_buffer_pool_size 大于等于 1G 时才生效，同理，若要调整innodb_buffer_pool_size小于1G，需要确保innodb_buffer_pool_instances的值不能大于1。

当 innodb_buffer_pool_size 的值不高于 8GB时，没必要设置 innodb_buffer_pool_instances 的值大于 1，比较合理的调整方法是，当innodb_buffer_pool_size每增加8G，innodb_buffer_pool_instances的值相应增加1，但不要超过服务器的cpu核数，建议可以先设置为服务器CPU核数的一半。

为避免潜在的性能问题，块数（缓冲池的大小innodb_buffer_pool_size/块的大小 innodb_buffer_pool_chunk_size）不应超过1000。innodb_buffer_pool_chunk_size默认值为128M。

执行下面的命令查看当前innodb buffer pool值：

show global variables like 'innodb_buffer_pool%';

执行下面的命令查看innodb_buffer_pool系列参数：

show status like '%innodb_buffer_pool_%';

验证innodb_buffer_pool_size大小是否合适：

当前配置的innodb_buffer_pool_size是否合适，可以通过分析InnoDB缓冲池的性能和命中率来验证。 

 （1）计算InnoDB缓冲池性能： 

 Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

 innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数，需要从磁盘中读取。

 innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。 

（2） 计算InnoDB缓冲池命中率： 

 InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests /

 (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100 

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

2、IOPS优化

充分利用多核CPU，两个参数加起来等于CPU核数即可。

偏于读的就将innodb_read_io_threads设置高一些。

调整完成后，可以用命令“show engine innodb status\G;”来查看调整结果。

innodb_read_io_threads = 20 

innodb_write_io_threads = 12 

四、Mysql5.7升级到Mysql8.0

本文采用全量导出Mysql5.7的数据后，再导入到Mysql8.0的升级方式。

升级步骤是：先升级从库，升级完成后，切换VIP，再升级主库。

1、导出全量数据

参考我之前写的文章：juejin.cn/post/728000…

mysql -S /usr/local/mysql5.7/mysql.sock -uroot -p123456 -e "show databases" | grep -Ev "Database|information_schema|performance_schema|sys" |\
xargs mysqldump -S /usr/local/mysql5.7/mysql.sock -uroot -p123456 -F -E -R --triggers --force --master-data=2 --single-transaction --databases > ./iam_$(date +%F).sql

2、下载Mysql8.0安装包

https://downloads.mysql.com/archives/community/
# 当前最新版本是mysql-8.0.33-linux-glibc2.28-x86_64.tar.gz

3、创建用户和用户组

由于之前安装mysql5.7已经创建。

4、创建数据目录和日志目录

mkdir -p /foura/mysql/data2
mkdir -p /foura/mysql/logs2/{binlogs,relaylogs}

5、安装依赖包

由于之前安装mysql5.7已经安装。

6、解压8.0二进制安装包

tar -xzvf mysql-8.0.33-linux-glibc2.28-x86_64.tar.gz
mv ./mysql-8.0.33-linux-glibc2.28-x86_64  /usr/local/mysql8.0

7、添加配置文件

cat << EOF >  /usr/local/mysql8.0/my.cnf
[mysqld_safe]
log-error=/foura/mysql/logs2/mysql-error.log
[mysqld]
default_authentication_plugin=mysql_native_password
default-time-zone='+8:00'
user=mysql
port=3310

# 做主从同步时需要添加如下配置，server-id不能重复。
server-id=$(date +%s)
binlog_format = ROW
binlog_row_image = full
max_binlog_size = 1G
expire_logs_days = 7
log-bin=/foura/mysql/logs2/binlogs/mysql-bin
relay-log=/foura/mysql/logs2/relaylogs/slave-relay-bin# end

# 免密登录，用于安装后设置密码时使用
skip-grant-tables
# end

log-error=/foura/mysql/logs2/mysql-error.log
datadir=/foura/mysql/data2basedir=/usr/local/mysql8.0
socket=/usr/local/mysql8.0/mysql.sock
pid-file=/usr/local/mysql8.0/mysql.pid
skip-name-resolve
symbolic-links = 0
lower_case_table_names = 1
character-set-server = utf8mb4
default-storage-engine = InnoDB
innodb_file_per_table = 1
max_connections = 5000
max_allowed_packet = 1G
interactive_timeout = 120
wait_timeout = 864000
sort_buffer_size=2097152
sql_mode=NO_AUTO_VALUE_ON_ZERO
[mysql]
socket=/usr/local/mysql8.0/mysql.sock
default-character-set=utf8mb4
EOF

8、授予运行用户目录权限

chown -R mysql:mysql /usr/local/mysql8.0
chown -R mysql:mysql /foura/mysql
chmod -R 770 /usr/local/mysql8.0
chmod -R 770 /foura/mysql

9、添加环境变量

在做初始化的时候，如果命令执行后没有任何日志打印出来，是因为MYSQL_HOME这个环境变量没有配置导致的。配置上后，mysql可以在安装目录下查找动态链接库，比如需要使用到这个库/usr/local/mysql8/lib/plugin/component_reference_cache.so。

echo "export MYSQL_HOME=/usr/local/mysql8.0" >> /etc/profile
source /etc/profile

10、初始化mysql数据库

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql8.0 --datadir=/foura/mysql/data2

11、支持SSL安全连接

yum -y install openssl
/usr/local/mysql5.7/bin/mysql_ssl_rsa_setup --user=mysql --datadir=/opt/mysql/data

12、修改启动脚本

cd /usr/local/mysql5.7/support-files
sed   -i   "/^basedir/c\basedir=/usr/local/mysql5.7"   mysql.server
sed   -i   "/^datadir/c\datadir=/opt/mysql/data"   mysql.server

13、设置开机自启

ln -s /usr/local/mysql5.7/support-files/mysql.server  /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list

14、启动mysql数据库

systemctl restart mysqld && systemctl status mysqld
avatar
文章
阅读
粉丝