草庐IT

mysql数据备份与恢复和mysql多实例部署

marymary 2023-04-17 原文

目录

1.mysql数据库备份与恢复

1.1 数据库常用备份方案

数据库备份方案：

  • 冷备份：先把数据库服务停掉，然后拷贝数据库目录下的文件进行备份 物理备份
  • 温备份：
  • 热备份：数据库服务正常运行情况，直接对数据库进行备份
    • 全量备份：全部备份
    • 增量备份：第一次全备，第二次在第一次全备更改的基础上备份
    • 差异备份：全部备份，差异备份-->全备
备份方案 特点
全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据，备份时间短，恢复数据时必须按一定的顺序进行
差异备份 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内，对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

1.2 mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables ...]//备份表（多表之间用空格连接）
mysqldump [OPTIONS] --all-databases [OPTIONS]//全备
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]//备份指定的数据库

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

[root@mr ~]# mysqldump -uroot -pmarui runtime tb_course > tb_course-$(date '+%Y%m%d%H%M%S').sql  （备份表）
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mr ~]# ls

tb_course-20220731140014.sql

[root@mr ~]# vim .my.cnf（免密登录）
[mysqldump]
user=root
password=marui
（实现免密登录）
[root@mr ~]# mysqldump runtime tb_course > tb_course-$(date '+%Y%m%d%H%M%S').sql
[root@mr ~]# ls

tb_course-20220731140014.sql
tb_course-20220731140726.sql

[root@mr ~]# mysqldump --databases runtime > runtime-$(date '+%Y%m%d%H%M%S').sql（备份数据库）
[root@mr ~]# ls

runtime-20220731141008.sql

[root@mr ~]# mysqldump --all-databases > all-$(date '+%Y%m%d%H%M%S').sql（全备）
[root@mr ~]# ls

all-20220731141206.sql

[root@mr ~]#

1.3 mysql数据恢复

mysql> delete from tb_course where id = 5 or id = 6;（删除数据）
Query OK, 2 rows affected (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
+----+-------------+
4 rows in set (0.00 sec)

mysql> 
[root@mr ~]# vim .my.cnf
[mysqldump]
user=root
password=marui

[client]
user=root
password=marui
方法一：
[root@mr ~]# mysql runtime < tb_course-20220731140726.sql

mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| tb_course         |
| tb_students_info  |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 
方法二：
mysql> source tb_course-20220731140726.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)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 


物理备份
[root@mr ~]# cd /opt/data/
[root@mr data]# ls
auto.cnf         client-key.pem  ib_logfile1  mysql_bin.000003  performance_schema  server-cert.pem
ca-key.pem       ib_buffer_pool  ibtmp1       mysql_bin.000004  private_key.pem     server-key.pem
ca.pem           ibdata1         mr.err       mysql_bin.index   public_key.pem      sys
client-cert.pem  ib_logfile0     mysql        mysql.pid         runtime
[root@mr data]# mkdir /opt/mysql_bak/
[root@mr data]# mv * /opt/mysql_bak/
[root@mr data]# ls
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> 
[root@mr data]# mv /opt/mysql_bak/* .
[root@mr data]# ls
auto.cnf         client-key.pem  ib_logfile1  mysql_bin.000003  performance_schema  server-cert.pem
ca-key.pem       ib_buffer_pool  ibtmp1       mysql_bin.000004  private_key.pem     server-key.pem
ca.pem           ibdata1         mr.err       mysql_bin.index   public_key.pem      sys
client-cert.pem  ib_logfile0     mysql        mysql.pid         runtime
[root@mr data]#
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

1.4 差异备份

[root@mr ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=10
log-bin=mysql_bin
[root@mr ~]# systemctl restart mysqld
[root@mr ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all.chayi.sql
（--single-transaction是事务日志，--flush-logs是刷新日志，--master-data=2指定master标志符，--delete-master-logs删除主的日志）
[root@mr ~]# ls

all.chayi.sql

[root@mr ~]# 
mysql> insert tb_course(course_name) values('English'),('math');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  5 | C++         |
|  6 | HTML        |
|  7 | English     |
|  8 | math        |
+----+-------------+
7 rows in set (0.00 sec)

mysql> 
刷新二进制日志
[root@mr ~]# ll /opt/data/
total 123060

-rw-r-----. 1 mysql mysql      713 Jul 31 16:04 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 31 15:59 mysql_bin.index

drwxr-x---. 2 mysql mysql     8192 Jul 27 16:21 sys
[root@mr ~]# cat /opt/data/mysql_bin.index 
./mysql_bin.000003
[root@mr ~]# mysqladmin -uroot -pmarui flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mr ~]# ll /opt/data/
total 123064

drwxr-x---. 2 mysql mysql     4096 Jul 27 16:21 mysql
-rw-r-----. 1 mysql mysql      760 Jul 31 16:09 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 31 16:09 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 31 16:09 mysql_bin.index

[root@mr ~]# cat /opt/data/mysql_bin.index 
./mysql_bin.000003
./mysql_bin.000004
[root@mr ~]# 
[root@mr ~]# mysql < all.chayi.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use runtime;
Database changed
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 
mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |        10 |         294 | BEGIN                                 |
| mysql_bin.000003 | 294 | Table_map      |        10 |         352 | table_id: 140 (runtime.tb_course)     |
| mysql_bin.000003 | 352 | Write_rows     |        10 |         410 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 410 | Xid            |        10 |         441 | COMMIT /* xid=480 */                  |
| mysql_bin.000003 | 441 | Anonymous_Gtid |        10 |         506 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 506 | Query          |        10 |         581 | BEGIN                                 |
| mysql_bin.000003 | 581 | Table_map      |        10 |         639 | table_id: 140 (runtime.tb_course)     |
| mysql_bin.000003 | 639 | Delete_rows    |        10 |         682 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 682 | Xid            |        10 |         713 | COMMIT /* xid=482 */                  |
| mysql_bin.000003 | 713 | Rotate         |        10 |         760 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)

mysql> 
[root@mr ~]# mysqlbinlog --stop-position=682 /opt/data/mysql_bin.000003 | mysql -uroot -pmarui
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mr ~]# 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use runtime;
Database changed
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | English     |
|  8 | math        |
+----+-------------+
8 rows in set (0.00 sec)

mysql>

2 mysql多实例部署

  • 1 软件下载
[root@mr ~]# cd /usr/src/
[root@mr src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
  • 2 配置用户和组并解压二进制程序至/usr/local下
[root@mr src]# id mysql
id: ‘mysql’: no such user
[root@mr src]# useradd -r -M -s /sbin/nologin mysql
[root@mr src]# ls
debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@mr src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mr src]# cd /usr/local/
[root@mr local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Jul  6 09:33 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
drwxr-xr-x. 9 root root 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jul  6 09:33 share
drwxr-xr-x. 2 root root   6 May 19  2020 src
[root@mr local]# 
[root@mr local]# ln -s mysql-5.7.38-linux-glibc2.12-x86_64 mysql
[root@mr local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Jul  6 09:33 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  35 Jul 31 17:27 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jul  6 09:33 share
drwxr-xr-x. 2 root root   6 May 19  2020 src
[root@mr local]# 
[root@mr local]# chown -R mysql.mysql mysql*
[root@mr local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 19  2020 bin
drwxr-xr-x. 2 root  root    6 May 19  2020 etc
drwxr-xr-x. 2 root  root    6 May 19  2020 games
drwxr-xr-x. 2 root  root    6 May 19  2020 include
drwxr-xr-x. 2 root  root    6 May 19  2020 lib
drwxr-xr-x. 3 root  root   17 Jul  6 09:33 lib64
drwxr-xr-x. 2 root  root    6 May 19  2020 libexec
lrwxrwxrwx. 1 mysql mysql  35 Jul 31 17:27 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jul 31 17:26 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 May 19  2020 sbin
drwxr-xr-x. 5 root  root   49 Jul  6 09:33 share
drwxr-xr-x. 2 root  root    6 May 19  2020 src
[root@mr local]# pwd
/usr/local
[root@mr local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src
[root@mr local]# cd mysql
[root@mr mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@mr mysql]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@mr mysql]# source /etc/profile.d/mysql.sh 
[root@mr mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@mr mysql]# chown -R mysql.mysql /usr/include/mysql
[root@mr mysql]# ll -d /usr/include/mysql
lrwxrwxrwx. 1 mysql mysql 25 Jul 31 17:31 /usr/include/mysql -> /usr/local/mysql/include/
[root@mr mysql]# 
[root@mr mysql]# vim /etc/ld.so.conf.d/mysql.conf

/usr/local/mysql/lib
[root@mr mysql]# vim /etc/man_db.conf 

MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man
  • 3 创建各实例数据存放的目录
[root@mr ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@mr ~]# chown -R mysql.mysql /opt/data/
[root@mr ~]# ll -d /opt/data/
drwxr-xr-x. 5 mysql mysql 42 Jul 31 17:40 /opt/data/
[root@mr ~]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308

3 directories, 0 files
[root@mr ~]#
  • 4 初始化各实例
[root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2022-07-31T09:44:28.907046Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T09:44:29.040275Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T09:44:29.064827Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T09:44:29.068832Z 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: 5f337cfb-10b5-11ed-8041-000c2950070e.
2022-07-31T09:44:29.069409Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T09:44:29.481826Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:44:29.481870Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:44:29.482251Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T09:44:29.507332Z 1 [Note] A temporary password is generated for root@localhost: ds=WqMxEw5B%
[root@mr ~]# echo 'ds=WqMxEw5B%' > 3306
[root@mr ~]# cat 3306
ds=WqMxEw5B%
[root@mr ~]# 
[root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2022-07-31T09:45:57.115760Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T09:45:57.253402Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T09:45:57.274926Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T09:45:57.279112Z 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: 93c74f09-10b5-11ed-844a-000c2950070e.
2022-07-31T09:45:57.280633Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T09:45:57.576351Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:45:57.576387Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:45:57.576764Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T09:45:57.596717Z 1 [Note] A temporary password is generated for root@localhost: LzXC#WG-x1Kd
[root@mr ~]# echo 'LzXC#WG-x1Kd' > 3307
[root@mr ~]# cat 3307
LzXC#WG-x1Kd
[root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2022-07-31T09:46:48.315446Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T09:46:48.483622Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T09:46:48.509017Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T09:46:48.517038Z 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: b251987e-10b5-11ed-8604-000c2950070e.
2022-07-31T09:46:48.517584Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T09:46:48.708884Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:46:48.708917Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T09:46:48.709402Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T09:46:48.811564Z 1 [Note] A temporary password is generated for root@localhost: ,L&rJy+Sm9z:
[root@mr ~]# echo ',L&rJy+Sm9z:' > 3308
[root@mr ~]# cat 3308
,L&rJy+Sm9z:
[root@mr ~]# 
[root@mr ~]# ls
3306  3307  3308  anaconda-ks.cfg
  • 5 安装perl
[root@mr ~]# yum -y install perl
......
  python3-rpm-macros-3-42.el8.noarch                                                                            
  qt5-srpm-macros-5.15.3-1.el8.noarch                                                                           
  redhat-rpm-config-125-1.el8.noarch                                                                            
  rust-srpm-macros-5-2.el8.noarch                                                                               
  systemtap-sdt-devel-4.7-1.el8.x86_64                                                                          
  unzip-6.0-46.el8.x86_64                                                                                       
  zip-3.0-23.el8.x86_64                                                                                         

Complete!
[root@mr ~]#
    1. 配置配置文件/etc/my.cnf

[root@mr ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

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

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

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log
    1. 启动各实例
[root@mr ~]# mysqld_multi start 3306
[root@mr ~]# ss -antl
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
LISTEN       0            80                             *:3306                        *:*                      
LISTEN       0            128                         [::]:22                       [::]:*                      
[root@mr ~]# mysqld_multi start 3307
[root@mr ~]# mysqld_multi start 3308
[root@mr ~]# ss -antl
State        Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process      
LISTEN       0            128                      0.0.0.0:22                    0.0.0.0:*                      
LISTEN       0            80                             *:3306                        *:*                      
LISTEN       0            80                             *:3307                        *:*                      
LISTEN       0            80                             *:3308                        *:*                      
LISTEN       0            128                         [::]:22                       [::]:*                      
[root@mr ~]#
    1. 初始化密码
[root@mr ~]# ls
3306  3307  3308  anaconda-ks.cfg
[root@mr ~]# cat 3306
ds=WqMxEw5B%
[root@mr ~]# mysql -uroot -pds=WqMxEw5B% -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('3306');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mr ~]# mysql -uroot -p3306 -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 4
Server version: 5.7.38 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> 

[root@mr ~]# cat 3307
LzXC#WG-x1Kd
[root@mr ~]# mysql -uroot -pLzXC#WG-x1Kd -S /tmp/mysql3307.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('3307');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mr ~]# mysql -uroot -p3307 -S /tmp/mysql3307.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 4
Server version: 5.7.38 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> 


[root@mr ~]# cat 3308
,L&rJy+Sm9z:
[root@mr ~]# mysql -uroot -p',L&rJy+Sm9z:' -S /tmp/mysql3308.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('3308');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mr ~]# mysql -uroot -p'3308' -S /tmp/mysql3308.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 4
Server version: 5.7.38 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>
mysql数据rootdrwxr-xr-xLinux

有关mysql数据备份与恢复和mysql多实例部署的更多相关文章

  1. ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2

    我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i

  2. ruby-on-rails - 如何使用 instance_variable_set 正确设置实例变量？ - 2

    我正在查看instance_variable_set的文档并看到给出的示例代码是这样做的:obj.instance_variable_set(:@instnc_var,"valuefortheinstancevariable")然后允许您在类的任何实例方法中以@instnc_var的形式访问该变量。我想知道为什么在@instnc_var之前需要一个冒号:。冒号有什么作用？ 最佳答案 我的第一直觉是告诉你不要使用instance_variable_set除非你真的知道你用它做什么。它本质上是一种元编程工具或绕过实例变量可见性的黑客攻击

  3. ruby 正则表达式 - 如何替换字符串中匹配项的第 n 个实例 - 2

    在我的应用程序中，我需要能够找到所有数字子字符串，然后扫描每个子字符串，找到第一个匹配范围(例如5到15之间)的子字符串，并将该实例替换为另一个字符串“X”。我的测试字符串s="1foo100bar10gee1"我的初始模式是1个或多个数字的任何字符串，例如，re=Regexp.new(/\d+/)matches=s.scan(re)给出["1","100","10","1"]如果我想用“X”替换第N个匹配项，并且只替换第N个匹配项，我该怎么做？例如，如果我想替换第三个匹配项“10”(匹配项[2])，我不能只说s[matches[2]]="X"因为它做了两次替换“1fooX0barXg

  4. ruby-on-rails - Rails - 从另一个模型中创建一个模型的实例 - 2

    我有一个正在构建的应用程序，我需要一个模型来创建另一个模型的实例。我希望每辆车都有4个轮胎。汽车模型classCar轮胎模型classTire但是，在make_tires内部有一个错误，如果我为Tire尝试它，则没有用于创建或新建的activerecord方法。当我检查轮胎时，它没有这些方法。我该如何补救？错误是这样的:未定义的方法'create'forActiveRecord::AttributeMethods::Serialization::Tire::Module我测试了两个环境:测试和开发，它们都因相同的错误而失败。 最佳答案

  5. ruby-on-rails - 每次我尝试部署时，我都会得到 - (gcloud.preview.app.deploy) 错误响应 : [4] DEADLINE_EXCEEDED - 2

    我是Google云的新手，我正在尝试对其进行首次部署。我的第一个部署是RubyonRails项目。我基本上是在关注thisguideinthegoogleclouddocumentation.唯一的区别是我使用的是我自己的项目，而不是他们提供的“helloworld”项目。这是我的app.yaml文件runtime:customvm:trueentrypoint:bundleexecrackup-p8080-Eproductionconfig.ruresources:cpu:0.5memory_gb:1.3disk_size_gb:10当我转到我的项目目录并运行gcloudprevie

  6. ruby - Ruby 有 `Pair` 数据类型吗？ - 2

    有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？ 最佳

  7. ruby-on-rails - RSpec:避免使用允许接收的任何实例 - 2

    我正在处理旧代码的一部分。beforedoallow_any_instance_of(SportRateManager).toreceive(:create).and_return(true)endRubocop错误如下:Avoidstubbingusing'allow_any_instance_of'我读到了RuboCop::RSpec:AnyInstance我试着像下面那样改变它。由此beforedoallow_any_instance_of(SportRateManager).toreceive(:create).and_return(true)end对此:let(:sport_

  8. ruby-on-rails - 使用 ruby​​ 将多个实例变量转换为散列的更好方法？ - 2

    我收到格式为的回复#我需要将其转换为哈希值(针对活跃商家)。目前我正在遍历变量并执行此操作:response.instance_variables.eachdo|r|my_hash.merge!(r.to_s.delete("@").intern=>response.instance_eval(r.to_s.delete("@")))end这有效，它将生成{:first="charlie",:last=>"kelly"},但它似乎有点hacky和不稳定。有更好的方法吗？编辑:我刚刚意识到我可以使用instance_variable_get作为该等式的第二部分，但这仍然是主要问题。

  9. ruby - 我如何添加二进制数据来遏制 POST - 2

    我正在尝试使用Curbgem执行以下POST以解析云curl-XPOST\-H"X-Parse-Application-Id:PARSE_APP_ID"\-H"X-Parse-REST-API-Key:PARSE_API_KEY"\-H"Content-Type:image/jpeg"\--data-binary'@myPicture.jpg'\https://api.parse.com/1/files/pic.jpg用这个:curl=Curl::Easy.new("https://api.parse.com/1/files/lion.jpg")curl.multipart_form_

  10. 世界前沿3D开发引擎HOOPS全面讲解——集3D数据读取、3D图形渲染、3D数据发布于一体的全新3D应用开发工具 - 2

    无论您是想搭建桌面端、WEB端或者移动端APP应用，HOOPSPlatform组件都可以为您提供弹性的3D集成架构，同时，由工业领域3D技术专家组成的HOOPS技术团队也能为您提供技术支持服务。如果您的客户期望有一种在多个平台（桌面/WEB/APP，而且某些客户端是“瘦”客户端）快速、方便地将数据接入到3D应用系统的解决方案，并且当访问数据时，在各个平台上的性能和用户体验保持一致，HOOPSPlatform将帮助您完成。利用HOOPSPlatform，您可以开发在任何环境下的3D基础应用架构。HOOPSPlatform可以帮您打造3D创新型产品，HOOPSSDK包含的技术有：快速且准确的CAD

随机推荐