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;#刷新权限设置