MySQL数据库备份与恢复与多实例部署

最新推荐文章于 2024-01-09 16:27:53 发布
最新推荐文章于 2024-01-09 16:27:53 发布
阅读量331 收藏
点赞数
文章标签： 数据库 mysql mariadb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mw5258/article/details/126045284
版权

文章目录

MySQL数据库备份与恢复

数据库常用备份方案

数据库备份方案：

  • 全量备份
  • 增量备份
  • 差异备份
备份方案特点
全量备份全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长
增量备份增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件
第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推
没有重复的备份数据
备份时间短
恢复数据时必须按一定的顺序进行
差异备份备份上一次的完全备份后发生变化的所有文件
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时
我们只需对第一次全量备份和最后一次差异备份进行恢复

MySQL备份工具mysqldump

语法

mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

常用的选项：

-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

//备份整个数据库（全量备份）
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test2          |
+----------------+
2 rows in set (0.00 sec)

[root@139 ~]# vim .my.cnf
[mysqldump]
user=root
password=123456
[root@139 ~]# mysqldump --all-databases > all-20220728.sql
[root@139 ~]# ls
all-20220728.sql  anaconda-ks.cfg

//备份test库里的表
[root@139 ~]# mysqldump test test1 test2 > test_table-20220727.sql
[root@139 ~]# ls
all-20220728.sql  anaconda-ks.cfg  test_table-20220727.sql

//备份test库
[root@139 ~]# mysqldump --databases test > test_db-20220728.sql
[root@139 ~]# ls
all-20220728.sql  anaconda-ks.cfg  test_db-20220728.sql  test_table-20220727.sql

MySQL数据恢复

//误删test数据库
mysql> drop database test;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

//恢复test数据库
[root@139 ~]# mysql -uroot -p123456  < test_db-20220728.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@139 ~]# mysql -uroot -p123456  -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

//恢复test库里的表
mysql> show tables;
Empty set (0.00 sec)

mysql> source test_table-20220727.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test2          |
+----------------+
2 rows in set (0.00 sec)
或者
mysql> show tables;
Empty set (0.00 sec)

mysql> quit
Bye
[root@139 ~]# mysql -uroot -p123456 test < test_table-20220727.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@139 ~]# mysql -uroot -p123456 -e "use test;show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test2          |
+----------------+

//恢复整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
+--------------------+
3 rows in set (0.00 sec)
mysql> source all-20220728.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

.....
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

差异备份与恢复

MySQL差异备份

开启MySQL服务器的二进制日志功能

[root@139 ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql_bin
[root@139 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

数据库进行完全备份

[root@139 ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202207.sql
[root@139 ~]# ls
all-202207.sql  anaconda-ks.cfg

给表修改内容

mysql> delete from test1 where id = 8;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where id = 5;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(name,age) value('ll',22);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | one   |   20 |
|  2 | two   |   21 |
|  4 | four  |   21 |
|  6 | six   |   20 |
|  7 | seven |   23 |
|  9 | nine  |   24 |
| 10 | ten   |   26 |
| 11 | ll    |   22 |
+----+-------+------+
8 rows in set (0.00 sec)

MySQL差异备份恢复

误删数据

mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新创建新的二进制日志文件

[root@139 mysql_data]# ll -d mysql*
drwxr-x---. 2 mysql mysql     4096 Jul 28 21:17 mysql
-rw-r-----. 1 mysql mysql     1375 Jul 28 21:32 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Jul 28 21:24 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 Jul 28 21:22 mysql.pid 

[root@139 mysql_data]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@139 mysql_data]# ll -d mysql*
drwxr-x---. 2 mysql mysql 4096 Jul 28 21:17 mysql
-rw-r-----. 1 mysql mysql 1422 Jul 28 21:33 mysql_bin.000002
-rw-r-----. 1 mysql mysql  154 Jul 28 21:33 mysql_bin.000003
-rw-r-----. 1 mysql mysql   38 Jul 28 21:33 mysql_bin.index
-rw-r-----. 1 mysql mysql    6 Jul 28 21:22 mysql.pid

先恢复完全备份

[root@139 ~]# mysql -uroot -p123456 < all-202207.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@139 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
| test2          |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from test1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | one   |   20 |
|  2 | two   |   21 |
|  3 | three |   22 |
|  4 | four  |   21 |
|  5 | five  |   25 |
|  6 | six   |   20 |
|  7 | seven |   23 |
|  8 | keven |   19 |
|  9 | nine  |   24 |
| 10 | ten   |   26 |
+----+-------+------+
10 rows in set (0.00 sec)

再恢复差异备份

//查看日志查找误删操作
mysql> show binlog events in 'mysql_bin.000002';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000002 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  219 | Query          |         1 |         291 | BEGIN                                 |
| mysql_bin.000002 |  291 | Table_map      |         1 |         343 | table_id: 140 (test.test1)            |
| mysql_bin.000002 |  343 | Delete_rows    |         1 |         390 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 |  390 | Xid            |         1 |         421 | COMMIT /* xid=482 */                  |
| mysql_bin.000002 |  421 | Anonymous_Gtid |         1 |         486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  486 | Query          |         1 |         558 | BEGIN                                 |
| mysql_bin.000002 |  558 | Table_map      |         1 |         610 | table_id: 140 (test.test1)            |
| mysql_bin.000002 |  610 | Delete_rows    |         1 |         657 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 |  657 | Xid            |         1 |         688 | COMMIT /* xid=483 */                  |
| mysql_bin.000002 |  688 | Anonymous_Gtid |         1 |         753 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 |  753 | Query          |         1 |         825 | BEGIN                                 |
| mysql_bin.000002 |  825 | Table_map      |         1 |         877 | table_id: 140 (test.test1)            |
| mysql_bin.000002 |  877 | Delete_rows    |         1 |         923 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 |  923 | Xid            |         1 |         954 | COMMIT /* xid=484 */                  |
| mysql_bin.000002 |  954 | Anonymous_Gtid |         1 |        1019 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 1019 | Query          |         1 |        1091 | BEGIN                                 |
| mysql_bin.000002 | 1091 | Table_map      |         1 |        1143 | table_id: 140 (test.test1)            |
| mysql_bin.000002 | 1143 | Write_rows     |         1 |        1187 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000002 | 1187 | Xid            |         1 |        1218 | COMMIT /* xid=486 */                  |
| mysql_bin.000002 | 1218 | Anonymous_Gtid |         1 |        1283 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 1283 | Query          |         1 |        1375 | drop database test                    |
| mysql_bin.000002 | 1375 | Rotate         |         1 |        1422 | mysql_bin.000003;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
25 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@139 ~]# mysqlbinlog --stop-position=1283 /opt/mysql_data/mysql_bin.000002 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@139 ~]# mysql -uroot -p123456 -e "select * from test.test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | one   |   20 |
|  2 | two   |   21 |
|  4 | four  |   21 |
|  6 | six   |   20 |
|  7 | seven |   23 |
|  9 | nine  |   24 |
| 10 | ten   |   26 |
| 11 | ll    |   22 |
+----+-------+------+

MySQL多实例部署

//下载二进制格式mysql包
[root@139]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /opt/

//解压软件包
[root@139 opt]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@139 opt]# ls
mysql-5.7.38-linux-glibc2.12-x86_64

//创建用户
[root@139 opt]# useradd -M -r -s /sbin/nologin mysql
[root@139 opt]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)

//创建软连接
[root@139 opt]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@139 opt]# ll
total 0
lrwxrwxrwx. 1 root root  36 Jul 26 18:34 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 18:30 mysql-5.7.38-linux-glibc2.12-x86_64

//修改属主和属组
[root@139 opt]# chown -R mysql.mysql mysql
[root@139 opt]# chown -R mysql.mysql mysql-5.7.38-linux-glibc2.12-x86_64/
[root@139 opt]# ll
total 0
lrwxrwxrwx. 1 mysql mysql  36 Jul 26 18:34 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 26 18:30 mysql-5.7.38-linux-glibc2.12-x86_64

//配置环境变量
[root@139 opt]# cd mysql
[root@139 mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@139 mysql]# echo 'export PATH=$PATH:/opt/mysql/bin' >/etc/profile.d/mysql.sh
[root@139 mysql]# source /etc/profile.d/mysql.sh
[root@139 mysql]# which mysql
/opt/mysql/bin/mysql

[root@139 mysql]# ln -s /opt/mysql/include  /usr/include/mysql
[root@139 mysql]# ll /usr/include/
total 0
lrwxrwxrwx. 1 root root 18 Jul 26 18:39 mysql -> /opt/mysql/include

[root@139 mysql]# echo '/opt/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@139 mysql]# cat /etc/ld.so.conf.d/mysql.conf
/opt/mysql/lib

[root@139 mysql]# vim /etc/man_db.conf
MANDATORY_MANPATH                       /opt/mysql/man

//创建实例数据存放的目录
[root@139 ~]# mkdir -p /opt/mysql_data/{3306,3307,3308}
[root@139 ~]# ls /opt/mysql_data/
3306  3307  3308
[root@139 ~]# chown -R mysql.mysql /opt/mysql_data/
[root@139 ~]# ll /opt/mysql_data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 28 21:53 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 28 21:53 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 28 21:53 3308

//初始化实例
[root@139 ~]# mysqld --initialize --datadir=/opt/mysql_data/3306 --user=mysql
2022-07-28T13:55:54.038954Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T13:55:54.248843Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T13:55:54.277926Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T13:55:54.336678Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ff7b7003-0e7c-11ed-a488-000c2994f252.
2022-07-28T13:55:54.338575Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T13:55:54.457929Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:55:54.458029Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:55:54.458506Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T13:55:54.505499Z 1 [Note] A temporary password is generated for root@localhost: RT=R5y)qtfgS
[root@139 ~]# echo "RT=R5y)qtfgS" > 3306

[root@139 ~]# mysqld --initialize --datadir=/opt/mysql_data/3307 --user=mysql
2022-07-28T13:56:30.073682Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T13:56:30.226436Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T13:56:30.259318Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T13:56:30.315416Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 14ed5bb5-0e7d-11ed-a53b-000c2994f252.
2022-07-28T13:56:30.316100Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T13:56:30.422984Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:56:30.423028Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:56:30.423392Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T13:56:30.513751Z 1 [Note] A temporary password is generated for root@localhost: a<nCVxF/#6;G
[root@139 ~]# echo "a<nCVxF/#6;G" >3307

[root@139 ~]# mysqld --initialize --datadir=/opt/mysql_data/3308 --user=mysql
2022-07-28T13:56:48.465821Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-28T13:56:48.656873Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-28T13:56:48.689024Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-28T13:56:48.748491Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1fea05ae-0e7d-11ed-a875-000c2994f252.
2022-07-28T13:56:48.749098Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-28T13:56:48.919387Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:56:48.919420Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-28T13:56:48.919712Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-28T13:56:48.962207Z 1 [Note] A temporary password is generated for root@localhost: p2wtoygOV7<f
[root@139 ~]# echo "p2wtoygOV7<f" >3308

//安装perl
[root@139 ~]# dnf -y install perl

//配置文件配置
[root@139 ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /opt/mysql/bin/mysqld_safe
mysqladmin = /opt/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/mysql_data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/mysql_data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/mysql_data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/mysql_data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/mysql_data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/mysql_data/3308/mysql_3308.pid
log-error=/var/log/3308.log

//启动实例
[root@139 ~]# mysqld_multi start 3306
[root@139 ~]# mysqld_multi start 3307
[root@139 ~]# mysqld_multi start 3308
[root@139 ~]# ss -antl|grep 330
LISTEN 0      80                 *:3306            *:*
LISTEN 0      80                 *:3307            *:*
LISTEN 0      80                 *:3308            *:*

//初始化密码
[root@139 ~]# mysql -uroot -p'RT=R5y)qtfgS' -S /tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 current input statement.

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

[root@139 ~]# cat 3307
a<nCVxF/#6;G
[root@139 ~]# mysql -uroot -p'a<nCVxF/#6;G' -S /tmp/mysql3307.sock -e "set password = password('123456');" --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@139 ~]# cat 3308
p2wtoygOV7<f
[root@139 ~]# mysql -uroot -p'p2wtoygOV7<f' -S /tmp/mysql3308.sock -e "set password = password('123456');" --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
时羽天
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
linux 安装mysql与常见问题
u010980938的专栏
09-14 1297
1.安装mysql 1.1 检查本地是否已经安装和卸载 检查命令 rpm -qa | grep mysql 删除命令 rpm -e mysql rpm -e --nodeps mysql rpm -e mysql：普通删除模式 rpm -e --nodeps mysql： 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除 1.2 下载和安装 建议使用5.7 || 5.8 版本，在8.0版本会有几个小坑 wget https://dev.mysq..
Mysql 5.7.17 初始化完成登录mysql提示密码过期
leo__1990的博客
06-25 1049
说明： 安装Mysql 5.7.17 登录root 用户使用初始化密码提示如下错误： [root@dg1 bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2017-09-15T17:02:21.325100Z 0 [Warning] TIM...
centos7 安装mysql
独孤行风的博客
05-23 502
Centos7 安装和配置Mysql5.7 第一步：下载和安装mysql源 -bash: wget: 未找到命令 我们先安装下wget [root@localhost ~]# yum -y install wget 然后执行wgethttps://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [ro...
MySQL多实例部署和Xtrabackup备份与恢复
一别的博客
08-29 350
MySQL多实例部署 软件下载 配置用户和组并解压二进制程序至/usr/local下 创建各实例数据事件的目录 初始化各实例 安装perl 配置配置文件/etc/my.cnf 启动各实例 初始化密码 软件下载 //下载二进制格式的mysql软件包 [root@localhost ~]# cd /usr/src/ [root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-l.
mysql多实例部署
Zhangrl_的博客
12-16 320
软件下载 //下载二进制格式的mysql软件包 [root@localhost ~]# cd /usr/src/ [root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz --2018-08-13 23:56:27-- ht...
mysql备份脚本多库版 适合多实例部署的版本
07-21
mysql备份脚本 可以同时备份一个实例下的多个库 适合多实例部署的版本 各路径请进行相应的修改 别忘了脚本赋权
docker环境下数据库的备份(postgresql, mysql) 实例代码
09-29
主要介绍了docker环境下数据库的备份(postgresql, mysql) ,需要的朋友可以参考下
达梦数据库使用，包括安装，配置，适配Mysql、备份与还原
01-08
接着，打开数据库配置助手创建数据库实例，建议把页大小设置大一点，以免记录超过页大小需要设置表的开启长记录设置。 对于 Docker 部署，需要编写 Compose 配置文件，版本为 3。服务 dmdbms 的镜像为 dm8_single:...
MySQL数据库运维.pdf
06-16
总之，这门课程涵盖了MySQL数据库运维的各个方面，从基础的系统规划和安装，到复杂的性能优化和安全控制，再到关键的备份与恢复策略，旨在培养出具备全面MySQL运维能力的专业人士。通过深入学习，学员将能够应对各种...
MySQL数据库架构与运维实践.docx
10-14
综上所述，MySQL数据库的架构与运维实践是一门深奥的学问，涉及到数据库选型、架构设计、监控报警、备份恢复和云服务等多个方面。理解并掌握这些知识，对于保证业务系统的稳定运行至关重要。通过不断学习和实践，...
一、MySQL安装
m0_50708207的博客
04-25 169
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 MySQLmysql安装环境一、二进制安装二、源码安装三、删除mysql mysql安装环境 centos7.5 一、二进制安装 1.清理本地yum源自带mariadb [root@mysql ~]# rpm -qa|grep mariadb 找出来的包一个一个删除 [root@mysql ~]# yum remove mariadb-libs -y 2.创建用户 [root@mysql ~]# useradd mysql -M -s /
【MySql】Mysql之备份与恢复
五彩斑斓的黑@的博客
10-05 7461
mysql的日志默认保存位置为：/usr/local/mysql/data 下面二进制日志由三种不同的记录格式①STTATEMENT（基于sql语句）每一条涉及到被修改的sql语句都会记录在binlog中缺点日志量过大，如sleep（）函数，last_insert_id()>（聚合函数）,以及user-definedfuctions(udf)、垂从复制等架构记录日志时会出现问题。
mysql 通过备份脚本进行导入新库，数据库密码发生改变
健康平安的活着的专栏
08-25 294
1.在项目现场实现了将数据进行备份操作，备份数据库的机器为192.168.1.241，其用户名和密码为 root/123导入新mysql数据库的机器为192.168.152.2，其用户名和密码为 root/root ,将脚本导入到新库中，新库的密码变为了123，原先的已有 数据库连接无法进行登录。...
MySQL逻辑备份工具mysqldump,mysqlpump,mydumper详细介绍，建议收藏
白话机器学习
08-23 2580
mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的，这里会有个限制是如果某个数据库有一张表非常大，可能大部分的时间都是消耗在这个表的备份上面，并行备份的效果可能就不明显。该参数默认开启，表示快递启动–add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset选项，通过 --skip-opt 关闭。
MySQL详解六：备份与恢复
m0_74170357的博客
09-20 2035
是对整个数据库、数据库结构和文件结构的备份保存的是备份完成时刻的数据库是差异备份与增量备份的基础是自上一次备份后增加和变化的文件或内容。
看完这篇都有DBA级别的MySQL备份经验了，再也不害怕删库跑路了
征服Bug的博客
05-19 1245
Xtrabackup是由percona开源的免费数据库热备份软件，它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）；mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，如果数据库大于50G，mysqldump备份就不太适合。Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup。
MySQL的数据备份和故障恢复自动化工具推荐
vipfanxu的博客
09-13 614
每个工具都有其独特的优势和适用场景，用户可以根据自己的需求选择合适的工具来保障数据库的稳定性和数据的安全性。mydumper还支持增量备份和增量恢复，可以显著减少备份和恢复所需的时间和资源。mysqldump是MySQL提供的备份工具，广泛应用于MySQL的数据备份和迁移中。它提供了统一的界面，方便用户进行备份配置和监控。随着互联网的不断发展和数据规模的不断增长，数据库的稳定性和数据的安全性变得尤为重要。MySQL作为一种流行的数据库管理系统，备份和故障恢复的自动化工具成为了企业和个人开发者的必备选择。
mysqldump的详细使用教程，定时备份脚本
最新发布
qq_46645079的博客
01-09 2536
mysqldump是一个用于备份MySQL数据库的命令行工具。它可以将数据库结构和数据导出到一个SQL文件中，以便在需要时进行恢复或迁移数据库。mysqldump可以备份整个数据库、特定的表或者特定的查询结果。使用mysqldump可以轻松地创建数据库的备份，并且备份文件可以在不同的MySQL服务器上进行恢复。它还可以用于将数据库从一个服务器迁移到另一个服务器，或者将数据导出到其他数据存储系统中。mysqldump支持许多选项和参数，可以根据需要进行定制备份的方式。
mysql5.7.17启动失败_解决Mysql5.7.17在windows下安装启动时提示不成功问题
weixin_29777019的博客
01-28 361
在机器上首次安装MySQL，操作系统是win7mysql 的安装文件是 zip 格式的，版本是5.7.17解压之后，安装步骤是1、首先找个文件存放这些安装程序以及 数据假设是E:\\mysql2、把解压的文件放在E:\\mysql下，并且新建个data文件夹3、创建环境变量mysql 路径是E:\\mysql添加到path 中 %MYSQL%\bin;4、在解压的文件夹下有个my-defaul...
MySQL主从部署教程：安装、配置与实例扩展
配置多个数据库用户及其权限，以便在主从架构中实现数据的读写分离。 7. **配置主从复制**: - 主从复制是MySQL高可用和容灾的关键部分。文档中提到配置了三个实例，分别指定了不同的配置，包括主服务器、从服务器...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值