手把手带你配置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 个评论

要回复文章请先登录注册