您所在的位置:首页 > 新闻中心 > mysql切换数据库提示警告,prompt一个特别好用的命令
公司要闻
mysql切换数据库提示警告,prompt一个特别好用的命令
发布时间:2020-04-02 17:48
访问量:359

mysql> kill 16545618;

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

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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> create database test;  ← 建立一个测试用的数据库test
Query OK, 1 row affected (0.00 sec)

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

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

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

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

复制代码 代码如下:

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

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

[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

以上,我们就等于模拟了数据库被破坏的过程。接下来,是数据库被“破坏”后,用备份进行恢复的方法。
[[email protected] ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/  ← 复制备份的数据库test到相应目录
[[email protected] ~]# chown -R 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

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)

[[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

 

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

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

mysql> use test
Database changed
mysql>

复制代码 代码如下:

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

mysql> show processlist ;    (查看进程)

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

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

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

这样做的好处就是让我们大家在操作数据库时更加方便快捷。

然后,我们再次运行数据库备份脚本,将当前状态的数据库,再做一次备份。
[[email protected] ~]# cd  ← 回到脚本所在的root用户的根目录
[[email protected] ~]# ./mysql-backup.sh  ← 运行脚本进行数据库备份

删除这些锁表的情况,我的mysql就能正常访问了。

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

mysql> exit  ← 退出MySQL服务器
Bye

Database changed
mysql>

default-character-set=utf8

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

    登录数据库后,选择数据库时发现以下提示,

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

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

另一种情况,无法切换访问数据库,提示此信息。(我没遇到过,一并贴过来了解下)

...

mysql> exit  ← 退出MySQL服务器
Bye

Enter password:

您也可以在在my.cnf配置文件里进行配置:

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

由于MYSQL中数据库太大,导致读取预读时间太长,从而显示这个提示,如果之前都没有遇到这个问题,那么产生这个问题的原因可能是由于有改变数据库信息的操作,比如drop一个很大的表(几千万数据)而中途终止.

这里也给出需要的一些个选项供大家参考。

[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

    意思是 预读这个库中表以及表列信息,一般原因是当库中表很多,表中数据很大时,就会出现执行use <库名>后半天没反应,连接很慢的情况,解决办法就是 -A 方式登录数据库,不会预读库中表信息。

[[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

建立自动备份脚本
在这里,为了使数据库教程备份和恢复的符合我们的实际要求,用一段符合要求的Shell脚本来实现整个备份过程的自动化。
[[email protected] ~]# vi mysql教程-backup.sh  ← 建立数据库自动备份脚本,如下:

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

想必大家在命令行操作mysql的时候会十分发愁现在的操作的是那个数据库吧,至少我就纠结过,可能您会说我打一条命令不就知道了,是的这样做确实可以的。
可是今儿给大家介绍个好用的命令--prompt

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

澳门新濠登录网址 1

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

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

本机登录数据库,直接执行-A也是可以的。

复制代码 代码如下:

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

上图中锁表的id为16545618,则可以使用kill命令,结束它.

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

以上,我们就等于模拟了数据库被篡改的过程。接下来,是数据库被“篡改”后,用备份进行恢复的方法。
[[email protected] ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/  ← 复制备份的数据库test到相应目录

(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

然后,再次登录到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

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不存在、已被删除
+-------------+
1 row in set (0.00 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

在这里,为了使数据库教程备份和恢复的符合我们的实际要求,用一段符合要求的Shell脚本来实现整个备份过程的自动化...

以上结果表示,数据库被修改后,用备份后的数据库成功的将数据恢复到了被“篡改”前的状态。
测试后…
测试完成后,将测试用过的遗留信息删除。
[[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

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

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

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

 

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

mysql> exit  ← 退出MySQL服务器
Bye

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

[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目录中

具体和上面所述的“数据库被删除后的恢复方法”相类似。这里,测试用数据库接着使用刚刚在前面用过的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

mysql> show databases;  ← 查看当前存在的数据库
+-------------+
| Database |
+-------------+
| mysql |
| test  |
+------------+
2 rows in set (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

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

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

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

#!/bin/bash

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

# The Directory of Backup
BACKDIR=/backup/mysql

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

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  |  ← 确认刚刚被删除的test数据库已经成功被恢复回来!
+------------+
2 rows in set (0.00 sec)

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

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

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

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

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

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

接下来,我们再次登录到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

然后,运行刚才建立的数据库备份脚本,备份刚刚建立的测试用的数据库。
[[email protected] ~]# cd ← 回到脚本所在的root用户的根目录
[[email protected] ~]# ./mysql-backup.sh  ← 运行脚本进行数据库备份

[3] 让数据库备份脚本每天自动运行
[[email protected] ~]# crontab -e  ← 编辑自动运行规则(然后会出现编辑窗口,操作同vi)
00 03 * * * /root/mysql-backup.sh   添加这一行到文件中,让数据库备份每天凌晨3点进行

mysql> exit  ← 退出MySQL服务器
Bye

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> 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> exit  ← 退出MySQL服务器
Bye

接下来,我们再次登录到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