Shulou(Shulou.com)06/01 Report--

I don't know if you have any knowledge about the specific operation of using shell script to back up MySQL database automatically. Today, I'm here to tell you briefly. If you are interested, let's take a look at the body. I believe you will gain something after reading the specific operations of automatically backing up the MySQL database with shell scripts.

Objective:

The 192.168.1.2 server backs up the MySQL database on the 192.168.1.1 server.

Conditions that must be met:

1. Create a database account specially used for backup on the 192.168.1.1 server, and grant the corresponding permissions:

Mysql > grant select,lock tables on *. * to 'operator'@'192.168.%.%' identified by' 123456; # backup database requires the account to have the permission to view and lock tables

2. Use this account for manual backup on the 192.168.1.2 server to test whether the backup can be successful:

[root@localhost] # mysqldump-u operator-p123456-h 192.168.1.1-- databases test1 > test1.sql

Write a shell script:

[root@localhost ~] # mkdir-p / opt/backup # create backup storage directory [root@localhost ~] # vim mysqlbak.sh # write shell script #! / bin/bash# define database connection, Target information base and other information: user= "operator" # define user name account pass= "123456" # password host= "192.168.1.1" # destination host conn= "u $user-p$pass-h $host" data1= "test1" # backup target library data2= "test2" bak= "/ opt/backup" # specify backup directory cmd= "/ usr/ Local/mysql/bin/mysqldump "# specify command tool time= `date +% Y% m% dmury% H% M` # define time variable name_1=" $data1-$time "# define the name after backup name_2=" $data2-$time "cd $bak # switch to backup directory $cmd $date-- databases $data1 > $name_1.sql # backup to .sql file $cmd $conn-- databases $data2 > $name_2.sql/bin/tar zcf $name_1.tar.gz $name_1.sql-- remove > / dev/null # Delete the source file / bin/tar zcf $name_2.tar.gz $name_2.sql-- remove > / dev/null after packaging

Test whether the backup was successful:

[root@localhost ~] # chmod + x mysqlbak.sh # gives the script execution permission [root@localhost ~] #. / mysqlbak.sh # execute the script to test whether the backup is successful Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure. [root@localhost ~] # ls / opt/backup/ # View the backed up file, OK! Test1-20190521-2254.tar.gz test2-20190521-2254.tar.gz

Set up scheduled tasks:

[root@localhost ~] # mv mysqlbak.sh / opt/backup/ # also move the script to the backup directory [root@localhost backup] # crontab-e # Edit scheduled task 00 22 * / opt/backup/mysqlbak.sh [root@localhost backup] # systemctl status crond # ensure that 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 II 2019-05-21 22:15:30 CST; 49min ago Main PID: 1493 (crond) Tasks: 1 CGroup: / system.slice/crond.service └─ 1493 / usr/sbin/crond-n

What do you think of this article after reading the specific operation of using shell script to automatically back up MySQL database? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.