数据库备份与恢复以及多实例部署
mysql数据库备份与恢复
数据库常用备份方案：
全量备份：
这是大多数人常用的方式，它可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，一周做一次完全备份。
增量备份：
增量备份是指上一次备份之后,对发生变化的数据进行的备份。 增量备份的优点:没有重复的备份数据,因此每次增量备份的数据量不大,备份所需的时间很短,但是必须保证每次备份的可靠性
差异备份：
它是只备份数据库一部分的另一种方法，它不使用事务日志，相反，它使用整个数据库的一种新映象。它比最初的完全备份小，因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。
mysql备份工具mysqldump
常用的选项有：
-u 指定数据库用户名
-h 使用ip地址指定服务器主机
-p 指定数据库用户的密码
-P 指定数据库监听的端口
全备：备份整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xingge |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use xingge;
Database changed
mysql>
mysql> show tables;
+------------------+
| Tables_in_xingge |
+------------------+
| student1 |
| student2 |
| student3 |
+------------------+
3 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@controller ~]#
[root@controller ~]# mysqldump -uroot -predhat123! -h192.168.200.10 -P3307 --all-databases > all-2023-9-5.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass
[root@controller ~]#
备份xingge数据库里的student1表和student2表
[root@controller ~]# mysqldump -uroot -predhat123! -h192.168.200.10 -P3307 xingge student1 student2 > table-2023-9-5.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql
[root@controller ~]#
备份xingge数据库
[root@controller ~]# mysqldump -uroot -predhat123! -h192.168.200.10 -P3307 --databases xingge > xingge.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
模拟误删数据库
[root@controller ~]# mysql -e 'drop database xingge;'
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
mysql数据恢复
恢复被删xingge数据库里的
[root@controller ~]# mysql -uroot -predhat123! < xingge.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xingge |
+--------------------+
[root@controller ~]#
恢复xingge数据库的student1表和student2表
[root@controller ~]# mysql -e 'show tables from xingge;'
+------------------+
| Tables_in_xingge |
+------------------+
| student1 |
| student2 |
| student3 |
+------------------+
[root@controller ~]#
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
[root@controller ~]# mysql -e 'drop table xingge.student1;'
[root@controller ~]# mysql -e 'drop table xingge.student2;'
[root@controller ~]#
[root@controller ~]# mysql -e 'show tables from xingge;'
+------------------+
| Tables_in_xingge |
+------------------+
| student3 |
+------------------+
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql>
mysql> use xingge;
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>
mysql> source table-2023-9-5.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>
mysql> show tables;
+------------------+
| Tables_in_xingge |
+------------------+
| student1 |
| student2 |
| student3 |
+------------------+
3 rows in set (0.00 sec)
mysql>
模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xingge |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.03 sec)
mysql> drop database performance_schema;
Query OK, 88 rows affected, 2 warnings (0.00 sec)
mysql>
mysql> drop database sys;
Query OK, 101 rows affected, 2 warnings (0.04 sec)
mysql> drop database xingge;
Query OK, 3 rows affected, 2 warnings (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> quit;
Bye
[root@controller ~]#
恢复整个数据库
[root@controller ~]# ls
all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123! < all-2023-9-5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| xingge |
+--------------------+
[root@controller ~]#
performance_schema这个数据库是在初始化数据库时生成的，这边删除的数据库已经全部恢复。
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
编辑my.cnf配置文件设置服务器标识符，开启二进制日志功能
[root@controller ~]# vim /etc/my.cnf
[mysqld]
...
server-id = 10
log-bin = mysql_bin
[root@controller ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@controller ~]#
由于之前把performance_schema这个数据库删除了，需要重新初始化数据库生成performance_schema文件，否则进行完全备份会失败
先创建一个目录存放原来的数据库文件
[root@controller opt]# mkdir backup
[root@controller opt]# ls
backup data
[root@controller opt]#
[root@controller opt]# cd data/
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000001 mysql.pid server-cert.pem
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000002 private_key.pem server-key.pem
ca.pem controller.err ib_logfile0 mysql mysql_bin.index public_key.pem xingge
[root@controller data]#
[root@controller data]# mv * ../backup/
[root@controller data]# cd ../backup/
[root@controller backup]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000001 mysql.pid server-cert.pem
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000002 private_key.pem server-key.pem
ca.pem controller.err ib_logfile0 mysql mysql_bin.index public_key.pem xingge
[root@controller backup]#
重新初始化数据库
[root@controller ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
...
2023-09-05T11:43:06.341444Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-05T11:43:06.404502Z 1 [Note] A temporary password is generated for root@localhost: nwLjvacKv7(8
[root@controller ~]#
将原来的数据库文件拷贝到data目录中覆盖掉新生成数据库文件
[root@controller ~]# cd /opt/data/
[root@controller data]# ls
auto.cnf client-cert.pem ibdata1 mysql performance_schema server-cert.pem
ca-key.pem client-key.pem ib_logfile0 mysql_bin.000001 private_key.pem server-key.pem
ca.pem ib_buffer_pool ib_logfile1 mysql_bin.index public_key.pem sys
[root@controller data]#
[root@controller data]# \cp -r /opt/backup/* .
[root@controller data]#
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000001 mysql.pid public_key.pem sys
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000002 performance_schema server-cert.pem xingge
ca.pem controller.err ib_logfile0 mysql mysql_bin.index private_key.pem server-key.pem
[root@controller data]#
有些数据库文件没有被覆盖掉需要重新给data目录添加属主和属组
[root@controller data]# chown -R mysql.mysql /opt/data/
[root@controller data]# ll /opt/
total 8
drwxr-xr-x 4 root root 4096 Sep 5 19:42 backup
drwxr-xr-x 6 mysql mysql 4096 Sep 5 19:46 data
[root@controller data]#
重新启动数据库，再次登录数据库时可以使用之前的密码，不需要使用新生成的临时密码
[root@controller data]# service mysqld start
Starting MySQL. SUCCESS!
[root@controller data]#
[root@controller data]# ss -anlt
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 128 [::]:22 [::]:*
LISTEN 0 80 *:3307 *:*
[root@controller data]#
对数据库进行完全备份
完全备份
[root@controller ~]# mysqldump -uroot -predhat123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202309051036.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# ls
all-202309051036.sql all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
增加新内容
mysql> use xingge;
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 student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.01 sec)
mysql>
mysql> insert student1(name,age) values('mhy',66);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | mhy | 66 |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql>
mysql> update student1 set age = 50 where id = 7;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from student1;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | mhy | 66 |
+----+-------------+------+
12 rows in set (0.00 sec)
mysql>
mysql差异备份恢复
模拟误删数据
[root@controller ~]# mysql -uroot -predhat123! -e 'drop database xingge;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
刷新创建新的二进制日志
[root@controller ~]# cd /opt/data/
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000005 performance_schema server-cert.pem
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.index private_key.pem server-key.pem
ca.pem controller.err ib_logfile0 mysql mysql.pid public_key.pem sys
[root@controller data]#
[root@controller data]# cat mysql_bin.index
./mysql_bin.000005
[root@controller data]#
[root@controller data]# mysqladmin -uroot -predhat123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@controller data]#
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000005 mysql.pid public_key.pem sys
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000006 performance_schema server-cert.pem
ca.pem controller.err ib_logfile0 mysql mysql_bin.index private_key.pem server-key.pem
[root@controller data]#
恢复完全备份
[root@controller ~]# mysql -uroot -predhat123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@controller ~]#
[root@controller ~]# ls
all-202309051036.sql all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123! < all-202309051036.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xingge |
+--------------------+
[root@controller ~]#
[root@controller ~]# mysql -uroot -predhat123! -e 'show tables from xingge;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_xingge |
+------------------+
| student1 |
| student2 |
| student3 |
+------------------+
[root@controller ~]#
恢复差异备份
检查误删数据库的位置在什么地方
[root@controller data]# ls
auto.cnf client-cert.pem ib_buffer_pool ib_logfile1 mysql_bin.000005 mysql.pid public_key.pem sys
ca-key.pem client-key.pem ibdata1 ibtmp1 mysql_bin.000006 performance_schema server-cert.pem xingge
ca.pem controller.err ib_logfile0 mysql mysql_bin.index private_key.pem server-key.pem
[root@controller data]#
[root@controller data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>
mysql> show binlog events in 'mysql_bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000005 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000005 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000005 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 219 | Query | 10 | 293 | BEGIN |
| mysql_bin.000005 | 293 | Table_map | 10 | 350 | table_id: 141 (xingge.student1) |
| mysql_bin.000005 | 350 | Write_rows | 10 | 395 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000005 | 395 | Xid | 10 | 426 | COMMIT /* xid=504 */ |
| mysql_bin.000005 | 426 | Anonymous_Gtid | 10 | 491 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 491 | Query | 10 | 565 | BEGIN |
| mysql_bin.000005 | 565 | Table_map | 10 | 622 | table_id: 141 (xingge.student1) |
| mysql_bin.000005 | 622 | Update_rows | 10 | 679 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000005 | 679 | Xid | 10 | 710 | COMMIT /* xid=506 */ |
| mysql_bin.000005 | 710 | Anonymous_Gtid | 10 | 775 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000005 | 775 | Query | 10 | 873 | drop database xingge |
| mysql_bin.000005 | 873 | Rotate | 10 | 920 | mysql_bin.000006;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>
使用mysqlbinlog恢复差异备份
[root@controller data]# mysqlbinlog --stop-position=775 /opt/data/mysql_bin.000005 |mysql -uroot -predhat123!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@controller data]#
[root@controller data]# mysql -uroot -predhat123! -e 'select * from xingge.student1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | 50 |
| 8 | chenshou | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | mhy | 66 |
+----+-------------+------+
[root@controller data]#
mysql多实例部署
创建各实例数据存放的目录
[root@controller ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@controller ~]#
[root@controller ~]# rm -rf /opt/data/*
[root@controller ~]# ls /opt/data/
[root@controller ~]#
[root@controller ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@controller ~]# ls /opt/data/
3306 3307 3308
[root@controller ~]#
[root@controller ~]# chown -R mysql.mysql /opt/data/
[root@controller ~]# ll /opt/data/
total 0
drwxr-xr-x 2 mysql mysql 6 Sep 5 20:57 3306
drwxr-xr-x 2 mysql mysql 6 Sep 5 20:57 3307
drwxr-xr-x 2 mysql mysql 6 Sep 5 20:57 3308
[root@controller ~]#
初始化各实例
[root@controller ~]# mysqld --insecure-initialize --user=mysql --datadir=/opt/data/3306
...
2023-09-05T13:01:57.854140Z 0 [Note] Shutting down plugin 'sha256_password'
2023-09-05T13:01:57.854142Z 0 [Note] Shutting down plugin 'mysql_native_password'
2023-09-05T13:01:57.854224Z 0 [Note] Shutting down plugin 'binlog'
2023-09-05T13:01:57.854313Z 0 [Note] mysqld: Shutdown complete
[root@controller ~]#
[root@controller ~]# mysqld --insecure-initialize --user=mysql --datadir=/opt/data/3307
...
2023-09-05T13:03:31.593893Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2023-09-05T13:03:31.593896Z 0 [Note] Shutting down plugin 'sha256_password'
2023-09-05T13:03:31.593897Z 0 [Note] Shutting down plugin 'mysql_native_password'
2023-09-05T13:03:31.593982Z 0 [Note] Shutting down plugin 'binlog'
2023-09-05T13:03:31.594066Z 0 [Note] mysqld: Shutdown complete
[root@controller ~]#
[root@controller ~]# mysqld --insecure-initialize --user=mysql --datadir=/opt/data/3308
...
2023-09-05T13:04:24.611408Z 0 [Note] Shutting down plugin 'sha256_password'
2023-09-05T13:04:24.611412Z 0 [Note] Shutting down plugin 'mysql_native_password'
2023-09-05T13:04:24.611674Z 0 [Note] Shutting down plugin 'binlog'
2023-09-05T13:04:24.611953Z 0 [Note] mysqld: Shutdown complete
[root@controller ~]#
[root@controller ~]# ls /opt/data/*
/opt/data/3306:
ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql_bin.index
/opt/data/3307:
ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql_bin.index
/opt/data/3308:
ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql_bin.index
[root@controller ~]#
安装perl
[root@controller ~]# yum -y install perl
...
qt5-srpm-macros-5.15.2-1.el8.noarch redhat-rpm-config-125-1.el8.noarch
rust-srpm-macros-5-2.el8.noarch systemtap-sdt-devel-4.5-3.el8.x86_64
unzip-6.0-45.el8_4.x86_64 zip-3.0-23.el8.x86_64
Complete!
[root@controller ~]#
配置/etc/my.cnf文件
配置前先将之前的my.cnf覆盖掉
[root@controller ~]# > /etc/my.cnf
[root@controller ~]#
[root@controller ~]# 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
启动各实例
启动前先将3306 ，3307 ，3308里的文件删除，不然会失败
[root@controller ~]# rm -rf /opt/data/3306/*
[root@controller ~]# mysqld_multi start 3306
...
2023-09-05T13:29:35.665669Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-05T13:29:35.666046Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-05T13:29:35.746833Z 1 [Note] A temporary password is generated for root@localhost: a,8UuPeY5XB>
[root@controller ~]#
[root@controller ~]# echo 'a,8UuPeY5XB>' > 3306
[root@controller ~]#
[root@controller ~]# rm -rf /opt/data/3307/*
[root@controller ~]#
[root@controller ~]# mysqld_multi start 3307
...
2023-09-05T13:31:08.775253Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-05T13:31:08.775569Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-05T13:31:08.861775Z 1 [Note] A temporary password is generated for root@localhost: VYKe4t<reTUX
[root@controller ~]#
[root@controller ~]# echo 'VYKe4t<reTUX' > 3307
[root@controller ~]#
[root@controller ~]# rm -rf /opt/data/3308/*
[root@controller ~]#
[root@controller ~]# mysqld_multi start 3308
...
2023-09-05T13:31:43.156614Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-09-05T13:31:43.157234Z 0 [Warning] CA certificate ca.pem is self signed.
2023-09-05T13:31:43.284977Z 1 [Note] A temporary password is generated for root@localhost: &jdCZMq3!%kf
[root@controller ~]#
[root@controller ~]# echo '&jdCZMq3!%kf' > 3308
[root@controller ~]#
[root@controller ~]# ss -anlt
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 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
[root@controller ~]#
初始化密码
给3306 ，3307 ，3308设置新的密码
[root@controller ~]# ls
3306 3307 3308 all-202309051036.sql all-2023-9-5.sql anaconda-ks.cfg pass table-2023-9-5.sql xingge.sql
[root@controller ~]#
[root@controller ~]# cat 3306
a,8UuPeY5XB>
[root@controller ~]#
[root@controller ~]# mysql -uroot -p'a,8UuPeY5XB>' -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.
...
mysql>
mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> quit;
Bye
[root@controller ~]#
[root@controller ~]# cat 3307
VYKe4t<reTUX
[root@controller ~]#
[root@controller ~]# mysql -uroot -p'VYKe4t<reTUX' -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.
...
mysql>
mysql> set password = password('mhy123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> quit;
Bye
[root@controller ~]#
[root@controller ~]# cat 3308
&jdCZMq3!%kf
[root@controller ~]#
[root@controller ~]# mysql -uroot -p'&jdCZMq3!%kf' -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.
...
mysql>
mysql> set password = password('op123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> quit;
Bye
[root@controller ~]#
登录mysql验证
[root@controller ~]# mysql -uroot -predhat123! -h127.0.0.1 -P3306
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
...
mysql>
mysql> quit;
Bye
[root@controller ~]#
[root@controller ~]# mysql -uroot -pmhy123! -h127.0.0.1 -P3307
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 7
...
mysql>
mysql> quit;
Bye
[root@controller ~]#
[root@controller ~]# mysql -uroot -pop123! -h127.0.0.1 -P3308
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 3
...
mysql>
mysql> quit;
Bye
[root@controller ~]#
79
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
