Purpose:

The 192.168.100.20 server backs up the MySQL database on the 192.168.100.10 server.

1. Configure MySQL server

1) 100.10 MySQL server log in to MYSQL to create two databases

[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> create database liyanxin;
mysql> create database wangzhaojun;

2) liyanxin database creates tables and writes data

mysql> create table liyanxin.liyanxin (full name char(6),Gender char(5),Age tinyint,Telephone char(11));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into liyanxin.liyanxin values ('bob','male','18','11111111111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into liyanxin.liyanxin values ('tom','female','20','22222222222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from liyanxin.liyanxin;
+--------+--------+--------+-------------+
| full name   | Gender   | Age   | Telephone        |
+--------+--------+--------+-------------+
| bob    | male     |     18 | 11111111111 |
| tom    | female     |     20 | 22222222222 |
+--------+--------+--------+-------------+
2 rows in set (0.00 sec)

3) Create tables and write data in the database

mysql> create table wangzhaojun.wangzhaojun (full name char(6),Gender char(5),Age tinyint,Telephone char(11));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into wangzhaojun.wangzhaojun values ('bob','male','18','11111111111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into wangzhaojun.wangzhaojun values ('tom','female','20','22222222222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from wangzhaojun.wangzhaojun;
+--------+--------+--------+-------------+
| full name   | Gender   | Age   | Telephone        |
+--------+--------+--------+-------------+
| bob    | male     |     18 | 11111111111 |
| tom    | female     |     20 | 22222222222 |
+--------+--------+--------+-------------+
2 rows in set (0.00 sec)

4) On the 100.10 server, authorize the account specially used to back up the database, and give corresponding permissions

mysql> grant select,lock tables on *.* to 'bob'@'192.168.100.%' identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
        <!--The backup database requires that the account has the right to view and lock the table-->

2. Configure backup server

1) Install MySQL client and test whether manual backup is successful

[root@centos02 ~]# yum -y install mysql
[root@centos02 ~]# mysqldump -u bob -ppwd@123 -h 192.168.100.10 --databases liyanxin > liyanxin.sql
[root@centos02 ~]# ls
liyanxin.sql

2) Script backup

[root@centos02 ~]# MKDIR - P / opt / backup <! -- create the storage directory after backup -- >
[root@centos02 ~]# vim  mysql.sh          <! -- script shell -- >
#!/bin/bash
<!--Define database connection, target information base and other information:-->
user="bob"      <!--Authorized account-->
pass="pwd@123"       <!--Password of authorized account-->
host="192.168.100.10"    <!--Target server IP address-->
conn="-u $user -p$pass -h $host"
data1="liyanxin"             <!--Backup database name-->
data2="wangzhaojun"    <!--Backup database name-->
bak="/opt/backup"          <!--Specify backup directory-->
cmd="/usr/bin/mysqldump"      <!--Specify command tools-->
time=`date +%Y-%m-%d-%H-%M`      <!--Define time variables-->
name_1="$data1-$time"        <!--Define the name after backup-->
name_2="$data2-$time"        <!--Define the backup name-->
cd $bak           <!--Switch to backup directory-->
$cmd $conn --databases $data1 > $name_1.sql      <!--Backup as.sql file-->
$cmd $conn --databases $data2 > $name_2.sql      <!--Backup as.sql file-->
/bin/tar zcf $name_1.tar.gz $name_1.sql --remove > /dev/null    <!--Delete source files after packaging-->
/bin/tar zcf $name_2.tar.gz $name_2.sql --remove > /dev/null    <!--Delete source files after packaging-->

3) Test whether the script backup is successful

[root@centos02 ~]# ./mysql.sh
[root@centos02 ~]# cd /opt/backup/
[root@centos02 backup]# ls
liyanxin-2020-05-20-02-10.tar.gz  wangzhaojun-2020-05-20-02-10.tar.gz

4) Set up scheduled tasks for automatic backup

[root@centos02 ~]# MV mysql.sh  /Opt / backup / <! -- move script to backup directory too -- >
[root@centos02 ~]# Crontab - e <! -- edit scheduled task -- >
*/5       *       *       *       *       /opt/backup/mysql.sh     <!--Back up every five minutes-->
[root@centos02 ~]# Systemctl status crond <! -- make sure the crond service is running -- >
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since III. 2020-05-20 01:20:56 CST; 37min ago
 Main PID: 829 (crond)
   CGroup: /system.slice/crond.service
           └─829 /usr/sbin/crond -n

————————————Thank you for watching——————————————