注册

手把手带你配置MySQL主备环境



为了保障生产数据的安全性，我们往往需要对数据库做备份，而通过MySQL主备配置，则是一种MySQL数据库备份的好的实现方案。本文将一步步带你搭建MySQL主备环境。

MySQL主备搭建

现有两台虚拟机，192.168.56.11（主）和192.168.56.12（备）

1 MySQL安装

离线安装解压版MySQL

1 上传压缩包

  • 以root用户上传mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz到/root/Downloads/下

2 解压

  • cd /root/Downloads

  • tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

  • mv mysql-5.7.29-linux-glibc2.12-x86_64 mysql5.7

  • mkdir /usr/database

  • mv mysql5.7 /usr/database

3 创建系统用户组和用户

  • groupadd mysql

  • useradd -r -g mysql mysql

  • id mysql

4 创建mysql data目录

  • cd /usr/database/mysql5.7

  • mkdir data

4 设置data目录权限

  • chown -R mysql:mysql /usr/database/mysql5.7/

  • ll /usr/database

5 修改my.cnf文件

删除并重新创建/etc/my.cnf：

rm -rf /etc/my.cnf
vim /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
init-connect='SET NAMES utf8'
basedir=/usr/database/mysql5.7      #根据自己的安装目录填写 
datadir=/usr/database/mysql5.7/data #根据自己的mysql数据目录填写
socket=/tmp/mysql.sock
max_connections=200                 # 允许最大连接数
character-set-server=utf8           # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB       # 创建新表时将使用的默认存储引擎

最大连接数

  • max_connections<=16384

  • 管理员(SUPER)登录的连接，不计其中

  • mysql会为每个连接提供连接缓冲区，连接越多内存开销越大

  • 查询：show variables like '%_connections'; ，show status like '%_connections';

  • max_used_connections / max_connections * 100% （理想值≈ 85%）

  • 数值过小会经常出现ERROR 1040: Too many connections错误

  • 修改方法：

    • 永久：在配置文件my.cnf中设置max_connections的值

    • 临时：以root登录mysql：set GLOBAL max_connections=xxx;->flush privileges;

mysql存储引擎

  • InnoDB：5.5及之后版本的默认引擎

    • 支持事务

    • 聚集索引，文件存放在主键索引的叶子节点上，必须要有主键。通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

    • 不支持全文类型索引

    • 支持外键

    • 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。

    • 最小锁粒度是行锁

    • 适用场景：支持事物、较多写操作、系统崩溃后相对易恢复

  • MyISAM：5.5版本之前的默认引擎

    • 不支持事务

    • 非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

    • 保存了整个表的行数，执行select count(*) 时只需要读出该变量即可，速度很快；

    • 支持全文类型索引

    • 不支持外键

    • 最小锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都被阻塞，因此并发访问受限

    • 表以文件形式保存，跨平台使用较方便

    • 适用场景：非事物型、读操作、小型应用

6 mysql初始化

  • /usr/database/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/database/mysql5.7 --datadir=/usr/database/mysql5.7/data

#注意：mysqld --initialize-insecure初始化后的mysql是没有密码的

  • chown -R root:root /usr/database/mysql5.7/ #把安装目录的目录的权限所有者改为root

  • chown -R mysql:mysql /usr/database/mysql5.7/data/ #把data目录的权限所有者改为mysql

7 启动mysql
/usr/database/mysql5.7/bin/mysqld_safe --user=mysql &
8 修改root密码

  • cd /usr/database/mysql5.7/bin

  • ./mysql -u root -p # 默认没有密码，直接回车就行

  • use mysql;

  • update user set authentication_string=password('``rootpasswd``') where user='root';

  • flush privileges;

  • exit;

9 登录测试

  • /usr/database/mysql5.7/bin/mysql mysql -u root -p

  • (输入密码)

  • show databases;

  • exit;

10 启动设置

  • cp /usr/database/mysql5.7/support-files/mysql.server /etc/init.d/mysql

  • chkconfig --add mysql # 添加服务

  • chkconfig --list # 查看服务列表

  • chkconfig --level 345 mysql on # 设置开机启动

11 测试服务命令是否可用

  • systemctl status mysql

  • systemctl start mysql

  • systemctl stop mysql

12 设置远程访问

  • 登录数据库：mysql -uroot -p[password]

  • use mysql;

  • select host,user from user;

  • update user set host='%' where user='root';

  • flush privileges;

  • 如果还是无法访问，检查防火墙

13 创建新用户

  • 创建用户app：create user 'app'@'%' identified by 'password';

  • 用户赋权(具有数据库appdb的所有权限，并可远程不限ip访问)：grant all on appdb.* to 'app'@'%';

  • flush privilegesl;

14 其他问题

  • -bash: mysql: command not found

    • 临时方案，重新登录后失效：alias mysql=/usr/database/mysql5.7/bin/mysql

    • 永久方案，将命令路径添加到PATH中：

      • vim /etc/profile

      • PATH="$PATH:/usr/database/mysql5.7/bin"

      • source /etc/profile

2 主备配置

1 生产环境为什么需要MySQL集群

  • 高可用性，在主节点失效时自动切换，不需要技术人员紧急处理

  • 高吞吐，可以多个节点同时提供读取数据服务，降低主节点负载，实现高吞吐

  • 可扩展性强，支持在线扩容

  • 无影响备份，在备节点进行备份操作不会对业务产生影响

MySQL集群的缺点：

  • 架构复杂，在部署、管理方面对技术人员要求高

  • 备节点拉取主节点日志时会对主节点服务器性能有一定影响

  • 如果配置了半同步复制，会对事物提交有一定影响

2 修改my.cnf

  • 主备服务器均创建日志文件路径：mkdir /data/mysql_log

  • 修改master下的/etc/my.cnf:

[client]
port = 3306
default-character-set=utf8mb4
socket = /usr/database/mysql5.7/mysql.sock

[mysqld]
basedir = /usr/database/mysql5.7
datadir = /usr/database/mysql5.7/data
tmpdir = /tmp
socket = /tmp/mysql.sock
# pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server-id = 113306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql-bin
log_bin_index = /data/mysql_log/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql-relay-bin.index
log_error = /data/mysql_log/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/database/mysql5.7/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

  • 修改slave下的/etc/my.cnf:

[client]
port = 3306
default-character-set=utf8mb4
socket = /usr/database/mysql5.7/mysql.sock

[mysqld]
basedir = /usr/database/mysql5.7
datadir = /usr/database/mysql5.7/data
tmpdir = /tmp
socket = /tmp/mysql.sock
# pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server-id = 123306
read-only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql-bin
log_bin_index = /data/mysql_log/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql-relay-bin.index
log_error = /data/mysql_log/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/database/mysql5.7/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

  • utf8：最大unicode字符是0xffff，仅支持Unicode 中的基本多文种平面(BMP)，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。

  • utf8mb4（most bytes 4）专门用来兼容四字节的unicode，是utf8的超集，包含很多不常用的汉字、Emoji表情，以及任何新增的 Unicode 字符等等

3 master上创建同步用户

  • mysql -uroot -proot

  • use mysql;

  • create user 'repl'@'%' identified by 'repl';

  • grant replication slave on . to 'repl'@'%';

  • flush privileges;

4 备份master数据库
/usr/database/mysql5.7/bin/mysqldump -S /tmp/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

上述命令报错，1045，增加登录信息：

/usr/database/mysql5.7/bin/mysqldump -uroot -proot -S /tmp/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

导出指定数据库（仅表结构）：

/usr/database/mysql5.7/bin/mysqldump -uroot -proot -S /tmp/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -d testdb1 testdb2 > mysql_backup_test.sql

导出指定数据库（表结构+数据）：

**/usr/database/mysql5.7/bin/mysqldump -uroot -proot -S /tmp/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 testdb1 testdb2 > mysql_backup_testdatas.sql**
5 slave上恢复master数据库

master推送sql：rsync -avzP mysql_backup_test.sql 192.168.56.12:/root/Downloads/

slave导入sql：/usr/database/mysql5.7/bin/mysqldump -S /tmp/mysql.sock < mysql_backup_test.sql

上述命令不成功，需先创建数据库，改为下述操作：

**mysql -uroot -proot**
**use test;**
**source /root/Downloads/**``**mysql_backup_test.sql**
6 开启同步

  • mysql命令行中查看master status中的File和Position参数：show master status;

    • 查看进程：show processlist\G

  • slave的mysql命令行执行：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.83',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=154;
mysql> start slave;
mysql> show slave status\G

状态中注意这几项：

Slave_IO_Running：取 Master 日志的线程, Yes 为正在运行

Slave_SQL_Running：从日志恢复数据的线程, Yes 为正在运行

Seconds_Behind_Master：当前数据库相对于主库的数据延迟， 这个值是根据二进制日志的时间戳计算得到的(秒)

7 同步测试

master数据库插入一条数据，可用看到slave同步更新了

主备有了，要是能够故障自动切换就完美了，这正是下一篇内容。

引用请注明出处！

0
分享
2021-12-30

0 个评论

要回复文章请先登录或注册

一个开放、互助、协作、创意的社区

关于imGeek
关于专职工程师值守
社区财富榜
赞助商
友情链接
热门标签
京ICP备2023000793号