手把手带你配置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/
#把安装目录的目录的权限所有者改为rootchown -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同步更新了
主备有了,要是能够故障自动切换就完美了,这正是下一篇内容。
引用请注明出处!