#数据库备份与恢复及多实例部署
###数据库的备份
冷备份:数据库服务关闭,把数据库对于的数据目录下的数据文件拷贝一份 物理备份(不建议使用)
温备份:??
热备份:数据库服务正常运行的时候,直接对数据库备份 (常用)
数据库热备份的方式手段:
全量备份:将数据库的所有数据全部备份
增量备份:基于第一次全备的基础上增加备份,第二次增备是基于上一次增备的基础上(优点:备份快,占用少。缺点:恢复数据难 要按照增备的顺序恢复,顺序乱了恢复不了 一般不用)
差异备份:第一次是全备;第二次差异是备份第一次完备产生变化的信息然后出入到全备中,第三次备份基于上一次全备变化的信息和第二次的信息,在插入一份到上一次全备中
###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
###热备份
1.恢复表
[root@localhost ~]# mysqldump zuoye students > zuoye
[root@localhost ~]# vim zuoye
[root@localhost ~]# mysql -uroot -p@123
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 5
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> use zuoye
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> delete from students where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 2 | Green | 23 | 男 | 158 | 2 |
| 3 | Henry | 23 | 女 | 185 | 1 |
| 4 | Jane | 22 | 男 | 162 | 3 |
| 5 | Jim | 24 | 女 | 175 | 2 |
| 6 | John | 21 | 女 | 172 | 4 |
| 7 | Lily | 22 | 男 | 165 | 4 |
| 8 | Susan | 23 | 男 | 170 | 5 |
| 9 | Thomas | 22 | 女 | 178 | 5 |
| 10 | Tom | 23 | 女 | 165 | 5 |
| 11 | LiMing | 22 | 男 | 180 | 7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql>
[root@localhost ~]# mysql -uroot -p@123 zuoye < zuoye
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
mysql> select * from students;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | 男 | 160 | 1 |
| 2 | Green | 23 | 男 | 158 | 2 |
| 3 | Henry | 23 | 女 | 185 | 1 |
| 4 | Jane | 22 | 男 | 162 | 3 |
| 5 | Jim | 24 | 女 | 175 | 2 |
| 6 | John | 21 | 女 | 172 | 4 |
| 7 | Lily | 22 | 男 | 165 | 4 |
| 8 | Susan | 23 | 男 | 170 | 5 |
| 9 | Thomas | 22 | 女 | 178 | 5 |
| 10 | Tom | 23 | 女 | 165 | 5 |
| 11 | LiMing | 22 | 男 | 180 | 7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
mysql>
恢复数据库
[root@localhost ~]# mysql -uroot -p@123 mysql < zuoye
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zbc |
| zuoye |
+--------------------+
6 rows in set (0.00 sec)
mysql>
###差异备份与恢复
###mysql差异备份
开启MySQL服务器的二进制日志功能
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1 设置服务器标识符
log-bin=mysql_bin 开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. ERROR! The server quit without updating PID file (/opt/data/mysql.pid).
[root@localhost ~]#
[root@localhost ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%y')
[root@localhost ~]# ls
all-22 home mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz passwd yyds
file ifcfg-ens33 outfile ss
[root@localhost ~]#
对数据进行增删
mysql> delete from tb_course where id =1
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> insert tb_course(course_name) values('english');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
| 6 | english |
+----+-------------+
5 rows in set (0.00 sec)
mysql>
mysql> drop database zuoye;
Query OK, 1 row affected (0.00 sec)
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
[root@localhost ~]# ll /opt/data/
total 122988
-rw-r-----. 1 mysql mysql 56 Jul 27 05:57 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 05:57 client-key.pem
-rw-r-----. 1 mysql mysql 311 Jul 29 13:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 14:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 05:57 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:00 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 27 05:57 mysql
-rw-r-----. 1 mysql mysql 858 Jul 29 14:03 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 Jul 29 14:00 mysql_bin.index
-rw-r-----. 1 mysql mysql 5 Jul 29 13:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 27 05:57 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 27 05:57 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 27 05:57 sys
-rw-r-----. 1 mysql mysql 28465 Jul 29 13:57 localhost.err
[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 122992
-rw-r-----. 1 mysql mysql 56 Jul 27 05:57 auto.cnf
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 client-cert.pem
-rw-------. 1 mysql mysql 1680 Jul 27 05:57 client-key.pem
-rw-r-----. 1 mysql mysql 311 Jul 29 13:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 14:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 05:57 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 14:00 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jul 27 05:57 mysql
-rw-r-----. 1 mysql mysql 905 Jul 29 14:05 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 Jul 29 14:05 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 Jul 29 14:05 mysql_bin.index
-rw-r-----. 1 mysql mysql 5 Jul 29 13:57 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Jul 27 05:57 performance_schema
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Jul 27 05:57 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Jul 27 05:57 server-cert.pem
-rw-------. 1 mysql mysql 1676 Jul 27 05:57 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Jul 27 05:57 sys
-rw-r-----. 1 mysql mysql 28465 Jul 29 13:57 localhost.err
[root@localhost ~]# cd /opt/data/
[root@localhost data]# cat mysql_bin.index
./mysql_bin.000002
./mysql_bin.000003
[root@localhost data]#
完全恢复数据库
[root@localhost data]# cd
[root@localhost ~]# mysql < all-22.sql
[root@localhost ~]# mysql < /root/all-22.sql
[root@localhost ~]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zuoye |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> use zuoye
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 tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
+----+-------------+
5 rows in set (0.00 sec)
mysql>
恢复差异备份
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 | 10 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 10 | 292 | BEGIN |
| mysql_bin.000002 | 292 | Table_map | 10 | 348 | table_id: 143 (zuoye.tb_course) |
| mysql_bin.000002 | 348 | Write_rows | 10 | 396 | table_id: 143 flags: STMT_END_F |
| mysql_bin.000002 | 396 | Xid | 10 | 427 | COMMIT /* xid=482 */ |
| mysql_bin.000002 | 427 | Anonymous_Gtid | 10 | 492 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 492 | Query | 10 | 565 | BEGIN |
| mysql_bin.000002 | 565 | Table_map | 10 | 621 | table_id: 143 (zuoye.tb_course) |
| mysql_bin.000002 | 621 | Delete_rows | 10 | 667 | table_id: 143 flags: STMT_END_F |
| mysql_bin.000002 | 667 | Xid | 10 | 698 | COMMIT /* xid=484 */ |
| mysql_bin.000002 | 698 | Anonymous_Gtid | 10 | 763 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 763 | Query | 10 | 858 | drop database zuoye |
| mysql_bin.000002 | 858 | Rotate | 10 | 905 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
mysql>
[root@zzz ~]# mysqlbinlog --stop-position=794 /opt/data/mysql_bin.000002 |mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
###mysql多实例部署
1.下载二进制格式的mysql软件包
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-29 15:28:28-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.199.216.78, 2600:1406:3400:681::2e31, 2600:1406:3400:68d::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.199.216.78|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz [following]
--2022-07-29 15:28:30-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.33.16.230
Connecting to cdn.mysql.com (cdn.mysql.com)|23.33.16.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’
mysql-5.7.38-linux-glib 100%[===============================>] 643.57M 1.60MB/s in 4m 33s
2022-07-29 15:33:04 (2.36 MB/s) - ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’ saved [674830866/674830866]
[root@localhost ~]#
2.配置用户和组并解压二进制程序至/usr/local下
创建用户和组
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /sbin.nologin -r -g mysql mysql
[root@localhost ~]# id mysql
uid=994(mysql) gid=665(mysql) groups=665(mysql)
[root@localhost ~]#
3.解压软件至/usr/local/
[root@localhost ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.38-linux-glibc2.12-x86_64 share
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 11 2019 bin
drwxr-xr-x. 2 root root 6 May 11 2019 etc
drwxr-xr-x. 2 root root 6 May 11 2019 games
drwxr-xr-x. 2 root root 6 May 11 2019 include
drwxr-xr-x. 2 root root 6 May 11 2019 lib
drwxr-xr-x. 2 root root 6 May 11 2019 lib64
drwxr-xr-x. 2 root root 6 May 11 2019 libexec
lrwxrwxrwx. 1 root root 36 Jul 29 15:42 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 29 15:40 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 May 11 2019 sbin
drwxr-xr-x. 5 root root 49 Jun 28 02:50 share
drwxr-xr-x. 2 root root 6 May 11 2019 src
[root@localhost local]#
3.修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 29 15:42 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
[root@localhost local]#
4.配置环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# . /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost local]#
5.创建各实例数据存放的目录
[root@localhost local]#
[root@localhost local]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost local]# chown -R mysql.mysql /opt/data/
[root@localhost local]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3306
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3307
drwxr-xr-x. 2 mysql mysql 6 Jul 29 15:46 3308
[root@localhost local]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308
3 directories, 0 files
[root@localhost local]#
6.初始化实例
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-29T07:48:49.626067Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:48:49.908611Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:48:49.946727Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:48:50.014836Z 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: e2606034-0f12-11ed-b7d3-000c29c7a87b.
2022-07-29T07:48:50.016338Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:48:50.131818Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:48:50.131843Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:48:50.132290Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:48:50.323184Z 1 [Note] A temporary password is generated for root@localhost: mO0ZJLw4g?6k
[root@localhost ~]# echo 'mO0ZJLw4g?6k'
mO0ZJLw4g?6k
[root@localhost ~]# echo 'mO0ZJLw4g?6k' > /root/3306passwd
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-29T07:49:58.016431Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:49:58.203443Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:49:58.239447Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:49:58.296321Z 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: 0b134dbc-0f13-11ed-bbcc-000c29c7a87b.
2022-07-29T07:49:58.297926Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:49:58.479904Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:49:58.479961Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:49:58.480406Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:49:58.498303Z 1 [Note] A temporary password is generated for root@localhost: wIwte:pJE3QA
[root@localhost ~]# echo 'wIwte:pJE3QA' > /root/3307passwd
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-29T07:50:39.459311Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-29T07:50:39.666551Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-29T07:50:39.700837Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-29T07:50:39.761079Z 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: 23ca52e7-0f13-11ed-bc54-000c29c7a87b.
2022-07-29T07:50:39.762412Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-29T07:50:39.960420Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:50:39.960439Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-29T07:50:39.960837Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-29T07:50:40.003629Z 1 [Note] A temporary password is generated for root@localhost: tVe!>tP.v8r,
[root@localhost ~]# echo 'tVe!>tP.v8r,' > /root/3308passwd
[root@localhost ~]#
7.安装perl
[root@localhost ~]# yum -y install perl
8.配置配置文件/etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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
[root@localhost ~]#
9.启动实例
[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
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@localhost ~]#
10.初始化密码
[root@zzz ~]# mysql -uroot -p'mO0ZJLw4g?6k' -S /tmp/mysql3306.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]# cat 3307passwd
wIwte:pJE3QA
[root@zzz ~]# mysql -uroot -p'wIwte:pJE3QA' -S /tmp/mysql3307.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]# mysql -uroot -p'tVe!>tP.v8r,' -S /tmp/mysql3308.sock -e 'set password = password("@123");' --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@zzz ~]#
```# 数据库备份与恢复及多实例部署
数据库的备份
冷备份:数据库服务关闭,把数据库对于的数据目录下的数据文件拷贝一份 物理备份(不建议使用)
温备份:??
热备份:数据库服务正常运行的时候,直接对数据库备份 (常用)
数据库热备份的方式手段:
- 全量备份:将数据库的所有数据全部备份
- 增量备份:基于第一次全备的基础上增加备份,第二次增备是基于上一次增备的基础上(优点:备份快,占用少。缺点:恢复数据难 要按照增备的顺序恢复,顺序乱了恢复不了 一般不用)
- 差异备份:第一次是全备;第二次差异是备份第一次完备产生变化的信息然后出入到全备中,第三次备份基于上一次全备变化的信息和第二次的信息,在插入一份到上一次全备中
mysql备份工具mysqldump
语法:
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
常用的OPTIONS:
-uUSERNAME 指定数据库用户名
-hHOST 指定服务器主机,请使用ip地址
-P# 指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307