用一段符合要求的Shell脚本来实现整个备份过程的

作者: www.9159.com  发布:2019-09-25

Enter password:

复制代码 代码如下:

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> exit  ← 退出MySQL服务器
Bye

(eg:shell> mysql -h 127.0.0.1 -u root -P 3306 -p -A)

Option Description
c A counter that increments for each statement you issue
D The full current date
d The default database
h The server host
l The current delimiter (new in 5.1.12)
m Minutes of the current time
n A newline character
O The current month in three-letter format (Jan, Feb, …)
o The current month in numeric format
P am/pm
p The current TCP/IP port or socket file
R The current time, in 24-hour military time (0–23)
r The current time, standard 12-hour time (1–12)
S Semicolon
s Seconds of the current time
t A tab character
U

Your full user_name@host_name account name

u Your user name
v The server version
w The current day of the week in three-letter format (Mon, Tue, …)
Y The current year, four digits
y The current year, two digits
_ A space
  A space (a space follows the backslash)
' Single quote
" Double quote
\ A literal “” backslash character
x

x, for any “x” not listed above

Option Description
c A counter that increments for each statement you issue
D The full current date
d The default database
h The server host
l The current delimiter (new in 5.1.12)
m Minutes of the current time
n A newline character
O The current month in three-letter format (Jan, Feb, …)
o The current month in numeric format
P am/pm
p The current TCP/IP port or socket file
R The current time, in 24-hour military time (0–23)
r The current time, standard 12-hour time (1–12)
S Semicolon
s Seconds of the current time
t A tab character
U

Your full user_name@host_name account name

u Your user name
v The server version
w The current day of the week in three-letter format (Mon, Tue, …)
Y The current year, four digits
y The current year, two digits
_ A space
  A space (a space follows the backslash)
' Single quote
" Double quote
\ A literal “” backslash character
x

x, for any “x” not listed above

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

本机登入数据库,直接实行-A也是足以的。

[root@fsailing1 ~]# mysql -uroot -p --prompt="\u@\h:\d \r:\m:\s>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 378
Server version: 5.0.95 Source distribution

莫不大家在命令行操作mysql的时候会非常悄然未来的操作的是特别数据库吧,至少本身就纠结过,大概您会说自个儿打一条命令不就领会了,是的这么做真正能够的。
而是明天给大家介绍个好用的下令--prompt

[2] 运营数据库自动备份脚本
[[email protected] ~]# chmod 700 mysql-backup.sh  改换脚本属性,让其只得让root客户实施
[[email protected] ~]# ./mysql-backup.sh   运转脚本
[[email protected] ~]# ls -l /backup/mysql/   确认一下是不是备份成功
total 8
drwxr-x--- 2 mysql mysql 4096 Sep 1 16:54 mysql   已成功备份到/backup/mysql目录中

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

root@localhost:(none) 08:23:32>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

[[email protected] ~]# mysql -uroot -p --prompt="\[email protected]\h:\d \r:\m:\s>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 378
Server version: 5.0.95 Source distribution

mysql> exit  ← 退出MySQL服务器
Bye

    意思是 预读那么些库中表以及表列音信,一般原因是当库中表比比较多,表中多少十分大时,就能够油可是生施行use <库名>后半天没反应,连接极慢的情状,化解办法就是 -A 格局登陆数据库,不会预读库中表信息。

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

复制代码 代码如下:

# The Password of MySQL
ROOTPASS=********  此处请将星号替换到MySQL的root密码

mysql> kill 16545618;

Database changed
mysql(root@localhost:test)>

[mysql]
prompt=mysql(\[email protected]\h:\d)>

Database changed
mysql> update test set name='Shit,Windows';  ← 然后将test中表的值重新定义为“Shit,Windows”(原来为“Hello,CentOS”)
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 

您也能够在在my.cnf配置文件里开展配备:

你也能够在在my.cnf配置文件里开展示公布局:

# Remake the Directory of Backup
rm -rf $BACKDIR
mkdir -p $BACKDIR

删去那么些锁表的情景,小编的mysql就能够寻常访谈了。

此间也交由必要的一些个选取供大家参照他事他说加以考察。

[[email protected] ~]# vim /etc/my.cnf
[[email protected] ~]# service mysqld restart
停止 MySQL:                                               [确定]
启动 MySQL:                                               [确定]
[[email protected] ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution

# The Directory of Backup
BACKDIR=/backup/mysql

另一种景况,不只怕切换访谈数据库,提醒此音讯。(小编没蒙受过,一并贴过来询问下)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

那边也交由要求的部分个选拔供大家参谋。

PATH=/usr/local/sbin:/usr/bin:/bin

由于MYSQL中数据库太大,导致读取预读时间太长,进而展示这一个提醒,借使以前都尚未遇上这些难点,那么爆发这些难点的案由或者是由于有改换数据库音信的操作,例如drop三个十分的大的表(几千万数码)而中途终止.

复制代码 代码如下:

default-character-set=utf8

接下去,大家重新登入到MySQL服务器中,删除刚刚建构的测量试验用的数据库test,以便于测验数据苏醒能还是无法打响。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root顾客密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 13 to server version: 4.1.20

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

像这种类型做的利润便是让大家我们在操作数据库时特别方便飞速。

mysql([email protected]:(none))>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select * from test;  ← 查看数据库中的内容
+------+--------------------+
| num | name  |
+------+--------------------+
| 1 | Hello,CentOS|
+------+--------------------+
1 row in set (0.01 sec)

    登入数据库后,采纳数据库时开采以下提醒,

default-character-set=utf8

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show processlist ;    (查看进程)

Database changed
root@localhost:test 08:23:44>exit;

Database changed
[email protected]:test 08:23:44>exit;

 

www.9159.com 1

唯恐我们在命令行操作mysql的时候会那多少个悄然未来的操作的是特别数据库吧,至少作者就纠结过,大概您会说作者打一条命令不就掌握了,是的那样坚实在能够的。
只是明天给大家介绍个好用的指令--prompt

[email protected]:(none) 08:23:32>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Database changed
mysql>

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

...

mysql> show databases;  ← 查看当前留存的数据库
+-------------+
| Database |
+-------------+
| mysql |
| test  |  ← 确认刚刚被删去的test数据库已经打响被恢复回来!
+------------+
2 rows in set (0.00 sec)

Database changed
mysql> q
Bye
[root@localhost ~]# mysql -u root -p -A
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

在此地,为了使数据库教程备份和恢复生机的符合大家的其实要求,用一段符合供给的Shell脚本来完毕全数备份进程的电动化...

shell> mysql -h hostname -u username -P port -p -A

[root@fsailing1 ~]# vim /etc/my.cnf
[root@fsailing1 ~]# service mysqld restart
停止 MySQL:                                               [确定]
启动 MySQL:                                               [确定]
[root@fsailing1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution

那般做的补益正是让大家大家在操作数据库时进一步方便神速。

# Get the Name of Database
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test
Database changed
mysql>

mysql(root@localhost:(none))>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

以上结果表示,数据库被改变后,用备份后的数据库成功的将数据苏醒到了被“篡改”前的情事。
测试后…
测量检验完了后,将测验用过的残存信息删除。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root顾客密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 19 to server version: 4.1.20

上图中锁表的id为16545618,则能够运用kill命令,甘休它.

复制代码 代码如下:

复制代码 代码如下:

Database changed
mysql> show tables;  ← 查看test数据库中存在的表
+-------------------+
| Tables_in_test |
+-------------------+
| test  |
+-------------------+
1 row in set (0.00 sec)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

[mysql]
prompt=mysql(\u@\h:\d)>

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

mysql> exit  ← 退出MySQL服务器
Bye

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

Database changed
mysql([email protected]:test)>

mysql> drop database test;  ← 删除测验用数据库test
Query OK, 0 rows affected (0.00 sec)

复制代码 代码如下:

Database changed
mysql> drop table test;  ← 删除test数据库中的表
Query OK, 0 rows affected (0.01 sec)

mysql> use test  ← 连接到test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> exit  ← 退出MySQL服务器
Bye

mysql> use test  ← 连接到test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> exit  ← 退出MySQL服务器
Bye

创立机关备份脚本
在此地,为了使数据库教程备份和恢复的适合我们的莫过于须要,用一段符合供给的Shell脚本来完毕全方位备份进程的自动化。
[[email protected] ~]# vi mysql教程-backup.sh  ← 创建数据库自动备份脚本,如下:

mysql> use test  ← 连接到test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> use test  ← 连接到这几个数据库
Database changed

[1] 当数据库被删去后的回复措施
率先创造八个测量试验用的数据库。
[[email protected] ~]# mysql -u root -p   ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root客商密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 4.1.20

然后,我们重新运营数据库备份脚本,将近期场合包车型客车数据库,再做一回备份。
[[email protected] ~]# cd  ← 回到脚本所在的root顾客的根目录
[[email protected] ~]# ./mysql-backup.sh  ← 运行脚本进行数据库备份

mysql> show databases;
+---------------+
| Database |
+---------------+
| mysql |  ← 确认测验用的test数据库已不真实、已被去除
+---------------+
1 row in set (0.01 sec)

mysql> use test  ← 连接到test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

以上,大家就等于模拟了数据库被歪曲的进度。接下来,是数据库被“篡改”后,用备份进行回复的法子。
[[email protected] ~]# /bin/cp -CR-Vf /backup/mysql/test/ /var/lib/mysql/  ← 复制备份的数据库test到相应目录

mysql> select * from test;  ← 确认test中的表被定义的值
+------+--------------------+
| num | name  |
+------+-------------------+
| 1 | Shit,Windows |  ← 确认已经将原test数据库表中的值修改为新的值“Shit,Windows”
+------+-------------------+
1 row in set (0.00 sec)

mysql> exit  ← 退出MySQL服务器
Bye

mysql> create database test;  ← 创设一个测量检验用的数据库test
Query OK, 1 row affected (0.00 sec)

接下来,再一次登陆到MySQL服务器上,看数据库是还是不是被恢复生机到了被“篡改”从前的情况。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root顾客密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16 to server version: 4.1.20

[3] 让数据库备份脚本天天活动运维
[[email protected] ~]# crontab -e  ← 编辑自动运维法规(然后会并发编辑窗口,操作同vi)
00 03 * * * /root/mysql-backup.sh   加多这一行到文件中,让数据库备份每一日早上3点扩充

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test  ← 连接到test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

接下去,大家再一次登陆到MySQL服务器中,对测量试验用的数据库test举行部分更动,以便于测验数据苏醒能无法打响。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root客户密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 15 to server version: 4.1.20

mysql> use test  ← 连接到测验用的test数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> show databases;  ← 查看当前存在的数据库
+-------------+
| Database |
+-------------+
| mysql |
| test  |
+------------+
2 rows in set (0.00 sec)

mysql> insert into test values(1,'Hello,CentOS');  ← 插入一个值到这一个表(这里以“Hello,CentOS”为例)
Query OK, 1 row affected (0.02 sec)

Database changed
mysql> drop table test;  ← 删除数据中的表
Query OK, 0 rows affected (0.04 sec)

实际和方面所述的“数据库被剔除后的过来措施”相就像是。这里,测量检验用数据库接着使用刚刚在前边用过的test。这里为了使刚刚接触数据库的朋友不至于精通混乱,大家重新登入到MySQL服务器上承认一下恰恰创立的测量试验用的数据库test的有关消息。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root客户密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 4.1.20

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

接下来,再一次登陆到MySQL服务器上,看是或不是业已成功苏醒了数据库。
[[email protected] ~]# mysql -u root -p  ← 用root登录到MySQL服务器
Enter password:  ← 输入MySQL的root客商密码
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 4.1.20

mysql> drop database test;  ← 删除测量检验用数据库test
Query OK, 0 rows affected (0.01 sec)

Database changed
mysql> select * from test;  ← 查看数据库中的内容
+------+----------------+
| num | name  |
+------+----------------+
| 1| Hello,CentOS | ← 确认数据表中的从头到尾的经过与被涂改前定义的“Hello,CentOS”一样!
+------+----------------+
1 row in set (0.01 sec)

mysql> exit  ← 退出MySQL服务器
Bye

Database changed
mysql> show tables;  ← 查看test数据库中存在的表
+-------------------+
| Tables_in_test |
+-------------------+
| test  |
+-------------------+
1 row in set (0.00 sec)

mysql> create table test(num int, name varchar(50));  ← 在数据库中树立多个表
Query OK, 0 rows affected (0.07 sec)

mysql> select * from test;  ← 查看数据库中的内容
+------+-----------------+
| num | name |
+------+-----------------+
|1  | Hello,Centos |  ← 确认刚刚插入到表中的值的存在
+------+------------------+
1 row in set (0.01 sec)

#!/bin/bash

如上,我们就等于模拟了数据库被毁掉的长河。接下来,是数据库被“破坏”后,用备份举办还原的主意。
[[email protected] ~]# /bin/cp -CR-Vf /backup/mysql/test/ /var/lib/mysql/  ← 复制备份的数据库test到相应目录
[[email protected] ~]# chown -Enclave mysql:mysql /var/lib/mysql/test/  ← 退换数据库test的归属为mysql
[[email protected] ~]# chmod 700 /var/lib/mysql/test/  ← 退换数据库目录属性为700
[[email protected] ~]# chmod 660 /var/lib/mysql/test/*  ← 退换数据库中数据的特性为660

mysql> select * from test;  ← 查看数据库中的内容
+------+---------------------+
| num | name  |
+------+---------------------+
| 1 | Hello,CentOS |  ← 确认数据表中的从头到尾的经过与删除前定义的“Hello,CentOS”同样!
+------+---------------------+
1 row in set (0.01 sec)

# Backup with Database
for dbname in $DBLIST
do
mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
done

mysql> show databases;  ← 查看当前留存的数据库
+-------------+
| Database |
+-------------+
| mysql |  ← 确认测量试验用数据库test不设有、已被去除
+-------------+
1 row in set (0.00 sec)

下一场,运维刚才建构的数据库备份脚本,备份刚刚创制的测量检验用的数据库。
[[email protected] ~]# cd ← 回到脚本所在的root客商的根目录
[[email protected] ~]# ./mysql-backup.sh  ← 运营脚本实行数据库备份

本文由9159.com发布于www.9159.com,转载请注明出处:用一段符合要求的Shell脚本来实现整个备份过程的

关键词:

上一篇:没有了
下一篇:没有了