暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

mysql数据库之安装、主从搭建、备份恢复

云时代IT运维 2021-02-09
437

[toc]   在本篇博文中,主要讲解如下几个知识点和实践经验,供大家参考:

  1. mysql-5.7.29数据库安装:

  2. mysql-5.7.29数据库主从环境搭建:

  3. 使用xtrabackup备份和恢复数据库:

1. mysql-5.7.29数据库安装:

  mysql数据库安装有多种方式,可以使用yum安装,也可以编译安装,还有一种就是二进制安装,不需要编译。本文介绍的就是二进制的安装方式。主要安装过程包含如下:

1.1 下载mysql并解压安装:


# 删除centos7系统自带的mariadb
rpm -qa | grep mariadb
yum remove mariadb-libs.x86_64 -y

# 安装依赖 libaio
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.myhuaweicloud.com/repo/CentOS-Base-7.repo
yum install -y libaio

# 解压安装mysql5.7.29

cd /tmp
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/
cd /usr/local/
ln -sv mysql-5.7.29-linux-glibc2.12-x86_64/ mysql
useradd -s /sbin/nologin mysql

# 配置环境变量

vim /etc/profile.d/mysql.sh

MYSQL_HOME=/usr/local/mysql
PATH=$MYSQL_HOME/bin:$PATH
export MYSQL_HOME PATH

source /etc/profile.d/mysql.sh

# 创建mysql数据目录并授权

chown -R mysql.mysql /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64/ #更改mysql安装目录权限
mkdir -p /data/mysql/data # 创建mysql数据目录
chown -R mysql.mysql /data/ #更改mysql数据目录权限


复制

1.2 初始化mysql数据并启动服务:

#  初始化mysql数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

# 增加服务配置文件,服务启动文件

vim /etc/my.cnf

[client]
default-character-set=utf8
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
port=3306
character-set-server=utf8
lower_case_table_names=1
innodb_strict_mode=0
default-storage-engine=INNODB
max_allowed_packet = 100M
symbolic-links=0
[mysql]
socket=/tmp/mysql.sock

# 增加服务启动文件

vim /etc/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

# 启动mysql服务

systemctl daemon-reload
systemctl start mysqld.service
systemctl status mysqld.service

# 查看mysql端口

netstat -lnp | grep 3306 #通过端口查看是否启动
tcp6 0 0 :::3306 :::* LISTEN 4982/mysqld

# 修改mysql数据库root密码


mysqladmin -uroot -p password

Enter password: #输入旧密码,第一次使用密码为空
New password: #输入新密码
Confirm new password: #再次确认新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

复制

1.3 mysql故障排查案例:


# mysql连接报错

ERROR 1130 (HY000): Host '192.168.5.191' is not allowed to connect to this MySQL server

# 这个原因是因为不允许通过ip连接,只能通过127.0.0.1连接,修改root访问权限就可以了

# 查看root用户权限
SELECT host FROM mysql.user WHERE User = 'root';

# 修改root登录权限,允许登录的主机为%,表示任何主机

CREATE USER 'root'@'%' IDENTIFIED BY 'CloudEasy@2020';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

# mysql字符集报错

mysql> INSERT INTO tb_emp1 (name,sex,age,salary) VALUES ("张涵","女",40,5000);
ERROR 1366 (HY000): Incorrect string value: '\xE5\x91\xA8\xE9\xA2\x96' for column 'name' at row 1

# 原因数据库设置字符集的问题,解决方案如下:

# 修改数据库enterprises的字符集:
alter database gaoyang character set utf8;

# 修改数据表employees的字符集:
alter table tb_emp1 character set utf8;


复制

2. mysql数据库主从环境搭建:

  上面主要讲解了如何安装一个单机版的mysql数据库,但是实际在生产环境中,我们一般都是要安装主从架构的数据库。第一是考虑到如果主数据库服务器down机,从数据库可以升级为主数据库提供服务;第二是考虑到互联网系统的访问量比较大,如果能够坐到读写分离。读请求访问从数据库,写请求访问主数据库,可以解决数据库性能问题;当然这里的从数据库可以配置多个,比如主-从-从架构。也有人使用mycat类似的proxy中间件来实现读写分离。也就是业务连接的是mycat服务器地址,所有的select、update、delete等请求,由mycat来分析然后自动的发给后端的主数据库服务器和从数据库服务器。目前只介绍一下主从环境的搭建;

# 先按照上面的操作,在两台ECS上面分别搭建mysql 5.7.29的数据库。搭建完成之后,配置master mysql的配置文件

[root@ecs-e5b8-0002 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
#replicate
server_id = 1 #每一台指定唯一的一个server-id标识,master的优先级要比slave的高
#replicate database
#binlog-do-db = test # 如果只同步指定的数据库,就写一个固定的数据库的名称
log_bin = mysql-bin #开启binlog,mysql-bin只是二进制日志名称,可以自行指定
[mysql]
socket=/tmp/mysql.sock
[root@ecs-e5b8-0002 ~]#

# 创建一个账户,用来做主从同步, 这里的ip一般指定从库的服务器IP

create user 'repl'@'%' identified by 'test2020';
grant replication slave on * . * to ‘repl’@’slave_ip‘;
flush privileges; # 刷新权限

# 查看master同步binlog文件名称和postition

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000008 #这个日志文件名称要记住
Position: 1825 # 这个点要记住,日志所在位置
Binlog_Do_DB: test #如果只同步这个库,my.cnf配置了,这里就显示这个数据库
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

#replicate
server_id = 2
#replicate database
replicate-do-db = gaoyang

# 接着是配置从服务器:

# 配置my.cnf

[root@ecs-d377-0002 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
#replicate
server_id = 2 # 每一台指定唯一的一个server-id标识,slave库的id优先级小于主库
#replicate database
#replicate-do-db = gaoyang
log-bin=mysql-bin #mysql-bin只是二进制日志名称,可以自行指定
read_only=1 # 设置从库为只读,但是superuser权限的用户可以写,比如root
[mysql]
socket=/tmp/mysql.sock


# 配置slave指向master;

change master to master_host='192.168.5.191',master_port=3306,master_user='repl',master_password='test2020',master_log_file='mysql-bin.000001',master_log_pos=154;

master的日志文件名称和日志位置可以在master数据库使用show master status\G;查看到

# 在slave服务器上面查看主从同步状态

mysql> start slave;
查看slave运行状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.5.191
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 3896
Relay_Log_File: ecs-d377-0002-relay-bin.000006
Relay_Log_Pos: 684
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes #只要这两个是yes就可以
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3896
Relay_Log_Space: 899
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 5fab1ce2-65fa-11eb-821b-fa163e1988de
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

1 Slave_IO_Running: Yes #表示slave的日志读取线程开启
2 Slave_SQL_Running: Yes #表示SQL执行线程开启


复制

3. xtrabackup备份数据库:

  通常我们备份数据库分为两种,一种是逻辑备份,一种是物理备份;逻辑备份用的是mysqldump命令备份数据库,物理备份使用的是xtrabackup工具备份数据库;本文主要讲解如何使用xtrabackup备份数据库;

  Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份工具InnoDB Hotbackup的一个很好的替代品。它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁)。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle

  xtrabackup有两个主要的工具:xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB 两种数据表

innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表 Innobackupex完整备份后生成了几个重要的文件:

xtrabackup_binlog_info:记录当前最新的LOG Position

xtrabackup_binlog_pos_innodb: innodb log postion

xtrabackup_checkpoints: 存放备份的起始位置beginlsn和结束位置endlsn,增量备份需要这个lsn[增量备份可以在这里面看from和to两个值的变化]




3.1 xtrabackup的安装:

yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel
yum -y install perl perl-devel perl-Time-HiRes perl-DBD-MySQL
yum -y install perl-Digest-MD5
yum -y install perl

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/tarball/percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12.tar.gz
tar xzvf percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12.tar.gz -C /usr/local

ln -sv percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12 xtrabackup

vim /etc/profile.d/xtrabackup
XTRABACKUP_HOME=/usr/local/xtrabackup
PATH=$XTRABACKUP_HOME/bin:$PATH
export XTRABACKUP_HOME PATH
source /etc/profile.d/xtrabackup

复制

3.2 使用xtrabackup进行备份:

# 备份


innobackupex --defaults-file=/etc/my.cnf --user=pxb --password=123456 --socket=/tmp/mysql.sock /data/pxb

#利用 --apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
innobackupex --apply-log /data/pxb/2021-02-05_16-49-55/

复制

3.3 使用xtrabackup进行恢复:

# 恢复
3.1 停止mysqld
systemctl stop mysqld
3.2 删除mysql数据库存储文件目录
rm -rf /data/mysql/data
chown -R mysql.mysql /data/mysql/data/


innobackupex --defaults-file=/etc/my.cnf --copy-back /data/pxb/2021-02-05_16-49-55/
chown -R mysql.mysql /data/mysql/data/
systemctl start mysqld


复制

说明一点,通过xtrabackup恢复数据库之后,主从同步的配置需要重新配置

  好的,今天的博文就写到这里了。马上要过年了,在这里也祝大家2021年新年快乐,工作顺利。年后再继续更新博文了,感谢大家的持续关注;


文章转载自云时代IT运维,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论