50. 脚本：MySQL分库备份

7.6 mysql分库备份脚本

忘记mysql数据库密码怎么办？

#1.修改配置文件
vim /etc/my.cnf
[mysqld]
socket=/var/lib/mysql/mysql.sock

#2.重启mariadb
systemctl restart mariadb

#3.登录mysql

#4.修改密码
update mysql.user set authentication_string=password('cpu123456') where user='root' and host='localhost';
flush privileges;

mysql快捷使用命令

mysql -uroot -p123456 -e "show databases;"|sed "1d"

脚本

vim mysql_table_bak.sh

#!/bin/bash
MYUSER=root
MYPWD=123456
DBPATH=/mysql_db_backup/
MYCMD="mysql -u$MYUSER -p$MYPWD"
MYDUMP="mysqldump -u$MYUSER -p$MYPWD"

[ ! -d "$DBPATH" ] && mkdir $DBPATH

for dbname in `$MYCMD -e "show databases;"|sed '1d'|egrep -v "mysql|schema"`
do
    mkdir ${DBPATH}/${dbname}_$(date +%F) -p
    for table in `$MYCMD -e "show tables from $dbname;"|sed '1d'`
    do  
        $MYDUMP $dbname $table |gzip > $DBPATH/${dbname}_$(date +%F)/${dbname}_${table}.sql.gz
    done
done

---

最后更新: 2022-02-19 13:59:07