mysql 免密码备份_mysql 多库不同主机免密码定时备份

设置mysql免密码登陆配置(前提当前主机安装了"mysql-client")

可以避免 Warning: Using a password on the command line interface can be insecure.

[root@xxxx-zabbx-12l ~]# mysql_config_editor set --login-path=confdb --user=xxxx --socket=/tmp/

dingding.log mysql.sock .X11-unix/

.font-unix/ sess_vutauod8dd2etpnugs0peb9845 .XIM-unix/

hsperfdata_root/ systemd-private-4fe3e89c1d0d4336b0f8eb906b922e7a-vgauthd.service-WuBPGJ/ zabbix_server_5297.pinger

.ICE-unix/ systemd-private-4fe3e89c1d0d4336b0f8eb906b922e7a-vmtoolsd.service-6bsXLG/ zabbix_server_alerter.sock

jetty-0.0.0.0-8080-xwiki-_xwiki-any-2760222951236920400.dir/ .Test-unix/ zabbix_server_preprocessing.sock

[root@xxxx-zabbx-12l ~]# mysql_config_editor set --login-path=confdb --host=192.168.0.68 --port=3306 --user=xxxx --socket=/tmp/mysql.sock --password

Enter password:

[root@xxxx-zabbx-12l ~]# mysql --login-path=confdb

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| config |

+--------------------+

2 rows in set (0.00 sec)

mysql> use config;

mysql> show tables;

+---------------------------+

| Tables_in_config |

+---------------------------+

| app_info |

+---------------------------+

[root@xxxx-zabbx-12l ~]# mysql_config_editor print --all ##查看配置信息

[confdb]

user = xxxx

password = *****

host = 192.168.0.68

socket = /tmp/mysql.sock

port = 3306

设置定时任务&& 备份脚本编写

[root@uat-mysqlredis-214l ~]# crontab -l

30 1 * * * /usr/sbin/ntpdate 192.168.0.76 >> /var/log/ntpdate.log

10 8,11,15,18,22 * * * /usr/bin/sh /root/mysql-rdb_backup.sh

[root@uat-mysqlredis-214l ~]# cat mysql-rdb_backup.sh

#!/bin/bash

# 数据库备份脚本

# author:sean-litx 2019-11-29

PATH=/data/mysql/bin:/data/xxxx-java/bin:/data/xxxx-java/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin

date=$(date +%Y%m%d%H)

#mysql_config_editor set --login-path=rdb_home --host=192.168.0.214 --port=3306 --user=xxxx --socket=/tmp/mysql.sock --password

#mysql_config_editor set --login-path=61-rdb_tech --host=192.168.0.61 --port=3306 --user=xxxx --socket=/tmp/mysql.sock --password

#mysql_config_editor set --login-path=72-rdb_tech --host=192.168.0.72 --port=3306 --user=xxxx --socket=/tmp/mysql.sock --password

backupdir=/data/mysql-home_backup

mkdir -pv $backupdir

mysqldump --login-path=rdb_home --database -B xxxx_dt_home_rdb xxxx_dt_business_rdb | gzip > $backupdir/rdb-home-multi-${date}.sql.gz

mysqldump --login-path=72-rdb_tech --database xxxx_library_rdb | gzip > $backupdir/rdb-tech-library-${date}.sql.gz

find ${backupdir} -mtime +7 -name 'rdb-*' -exec rm -rf {} \; ##保留7天备份

gzip -d xxxxx-${date}.sql.gz #解压