MariaDB主从配置及备份恢复管理
1、主节点配置：
yum install mariadb-server.x86_64 -y#安装数据库
service mariadb start#启动数据库
mysql -uroot -p123456#登录数据库
创建从节点连接登录用户
create user 'repl'@'192.168.2.%' identified by 'repl';
给用户授权
grant replication slave on *.* to 'repl'@'192.168.2.%' identified by 'repl';
vim /etc/my.cnf
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=/var/lib/mysql/mysql-bin
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
重启数据库
service mariadb restart
进入数据库
mysql -uroot -p123456
查看服务器id是否生效
MariaDB [(none)]> show variables like 'server_id';
2、从节点配置：
yum install mariadb-server.x86_64 -y#安装数据库
service mariadb start#启动数据库
mysql -uroot -p123456#登录数据库
--------------------------------------------------------------
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
relay-log=/var/lib/mysql/relay-bin
relay-log-index=/var/lib/mysql/relay-bin.index
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
-------------------------------------------------------------------------
重启数据库
service mariadb restart
进入数据库
mysql -uroot -p123456
查看服务器id是否生效
MariaDB [(none)]> show variables like 'server_id';
重置从节点设置
MariaDB [(none)]> reset slave;
配置从节点连接信息
MariaDB [(none)]> change master to master_host='192.168.2.121',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=0;
启动从节点连接
MariaDB [(none)]> start slave;
查看从节点运行状态
MariaDB [(none)]> show slave status\G
-------------------------------------------------------------------------
数据库备份：
备份所有数据库
mysqldump -uroot -p'root' --all-databases > /home/mysqlbak/mysql_bak_$(date "+%Y-%m-%d").sql
备份指定数据库：
mysqldump -uroot -p'root' --databases mytest mytest1 > /home/dblogs/mysqlbak/mysql_bak_$(date "+%Y-%m-%d").sql
导出指定表数据
mysqldump -uroot -p'root' --databases mytest --tables test1 > /home/dblogs/mysqlbak/mysql_bak_$(date "+%Y-%m-%d").sql
mysqldump -uroot -p'root' --databases mytest --tables test1 --where='id=1' > /home/dblogs/mysqlbak/mysql_bak_$(date "+%Y-%m-%d").sql
导出表结构
mysqldump -uroot -p'root' --no-data --databases mytest > /home/dblogs/mysqlbak/mysql_bak_$(date "+%Y-%m-%d").sql
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错,-C压缩传输
mysqldump --host=h1 -uroot -proot -C --databases db1 |mysql --host=h2 -uroot -proot db2
-----------------------------------------------------------------------------
备份数据恢复：
全量恢复
cd /home/dblogs/backup/full/
tar zxvf /home/dblogs/backup/20180726.sql.tgz -C .
mysql -uroot -p -v < 20180726.sql
在全量恢复基础上做增量恢复
mysqlbinlog --no-defaults mysql-bin.000036 mysql-bin.000038 |mysql -uroot -plove1990
查看二进制文件，查找恢复点
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000036
结束恢复点
mysqlbinlog --no-defaults --stop-position='344' mysql-bin.000039 |mysql -uroot -plove1990
开始恢复点
mysqlbinlog --no-defaults --stop-position='852' mysql-bin.000039 |mysql -uroot -plove1990
--------------------------------------------------------------------
数据库自动备份
创建目录
mkdir -p /home/dblogs/backup/{daily,full}
chown -R mysql.mysql /home/dblogs
修改配置文件，开启binlog
vi /etc/my.cnf
log_bin = "/home/dblogs/mysql-bin"
binlog_format = ROW
expire_logs_days = 30 #日志保存时间
制作每天备份脚本
vi backup/dailybak.sh
#!/bin/bash export LANG=en_US.UTF-8 BakDir=/home/dblogs/backup/daily BinDir=/home/dblogs LogFile=/home/dblogs/backup/daily/back.log BinFile=/home/dblogs/mysql-bin.index mysqladmin -uroot -plove1990 flush-logs #这个是用于产生新的mysql-bin.00000*文件 Counter=`wc -l $BinFile |awk '{print $1}'` NextNum=0 #这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的。 for file in `cat $BinFile` do base=$(basename $file) #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./ NextNum=`expr $NextNum + 1` if [ $NextNum -gt $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if test -e $dest then echo $base exit! >> $LogFile else cp $BinDir/$base $BakDir echo $base copying >> $LogFile fi fi done echo `date +"%Y年%m月%d日 %H:%M:%S"` Bakup succ! >> $LogFile
制作全量备份脚本
vi backup/fullbak.sh
#!/bin/bash export LANG=en_US.UTF-8 Dailydir=/home/dblogs/backup/daily BakDir=/home/dblogs/backup/full LogFile=/home/dblogs/backup/full/back.log Date=`date +%Y%m%d` Begin=`date +"%Y年%m月%d日_%H:%M:%S"` cd $BakDir DumpFile=$Date.sql GZDumpFile=$Date.sql.tgz mysqldump -uroot -plove1990 --all-databases --flush-logs --delete-master-logs --single-transaction > $DumpFile tar -czf $GZDumpFile $DumpFile rm $DumpFile count=$(ls -l *.tgz |wc -l) if [ $count -ge 5 ] then file=$(ls -l *.tgz |awk '{print $9}'|awk 'NR==1') rm -f $file fi #保留过去四周全量备份数据 Last=`date +"%Y年%m月%d日 %H:%M:%S"` echo 开始:$Begin 结束:$Last $GZDumpFile succ >> $LogFile cd $Dailydir rm -f *
增加定时任务,周一到周六凌晨2点增量备份，周日凌晨2点全量备份
crontab -e
0 2 * * 1-6 /home/dblogs/backup/dailybak.sh > /dev/null 2>&1
0 2 * * 0 /home/dblogs/backup/fullbak.sh > /dev/null 2>&1
常用命令：
查看库启动日志
cat /var/log/mariadb/mariadb.log
mysql -V#查看版本
grant all privileges on *.* to zabbix@localhost identified by 'zabbix';#为用户授权
rpm -ql mariadb-server#查看安装文件
ss -ntl#查看端口运行状态，3306端口
ss -utlnp
service mariadb start#启动mysql
mysql -u [用户名] -p -h [远程主机地址]#连接到数据
mysql -urepl -prepl -h192.168.2.121#远程登录数据库
show databases；#查看数据库
use mysql#进入数据库
mysql_secure_installation#执行安全加固脚本
mysql -uroot -p12345678 < test.sql#执行脚本
MariaDB [(none)]> source /root/test.sql#执行脚本
show databases;#显示数据库
create table user(id int,name varchar(20),age int);#创建表
insert into user(id,name,age) values(1,'trent',20);#向表中插入数据
create table test(id int) engine=myisam;#创建表指定引擎
show create table user;#查看数据表用的引擎
cat /etc/my.cnf#查看数据库配置
MariaDB [mysql]> select user,host from user;#用户及登录范围设置
grant replication slave on *.* to 'repl'@'192.168.2.%' identified by 'repl'; #配置用户远程连接数据库
show variables like 'max_connections';
set global max_connections=1000;
flush privileges;#刷新权限设置
